A high cache hit ratio with degraded latency suggests the bottleneck is not memory-related. Investigate in this order: First, check pg_stat_statements for new slow queries—maybe a new query pattern emerged that wasn't present in the previous baseline. Second, check pg_stat_activity for lock contention: run SELECT * FROM pg_stat_activity WHERE wait_event_type IS NOT NULL to see if queries are waiting on locks. Third, check replication lag if using replicas: a lagging replica could cause read latency to appear as write latency if your application uses synchronous replication. Fourth, check for index bloat using pgstatindex() on your most-used indexes—a bloated index has higher tree depth and requires more I/O per lookup even if the buffer cache is warm. Fifth, check disk I/O latency (iostat on the host): a saturated disk queue causes latency even with high cache hit ratios. Finally, check for increased connection counts—if max_connections is being approached, queries may be waiting in the connection queue.

Threshold setting is an iterative process. Start with theoretical limits: max_connections (e.g., 100), disk capacity (e.g., 500GB), shared_buffers (e.g., 8GB). Set initial thresholds at 70% (warning) and 85% (critical) of theoretical limits. Then refine based on observed baseline: capture metrics for 2-4 weeks to establish baseline behavior. Set warning thresholds at baseline + 3 standard deviations for highly variable metrics (query latency), and at fixed percentages for monotonic resources (disk usage, connection count). Use composite alerts to reduce noise: fire only when both cache hit ratio drops below 90% AND transaction latency exceeds 500ms simultaneously. Review and adjust thresholds quarterly or after any significant load change.

pg_stat_statements tracks query performance across all queries globally: total calls, total time, total I/O time, and cache hit ratio per normalized query. It requires the extension to be installed (CREATE EXTENSION pg_stat_statements) and does not track which session or user ran a query. pg_stat_activity is session-level: it shows every currently active connection, the query each session is running, wait events, transaction state, and query start time. Use it to diagnose currently running queries and lock contention in real time. pg_stat_user_tables (and pg_stat_sys_tables) tracks per-table statistics: sequential scans vs index scans, individual table hit/read/insert/update/delete rates, and vacuum/autovacuum activity. Use it to identify which tables are hot spots and whether indexes are being used effectively.

Configure pg_stat_statements with track_utility = on to capture all query types including COPY and VACUUM. Set a low log_min_duration_statement threshold (e.g., 100ms) during peak hours only, using a scheduled configuration change or pg_scheduler. Capture pg_stat_statements snapshots hourly and diff them to identify queries whose total time increases disproportionately during peak periods. Look specifically at total_exec_time / calls (average per execution) rather than total time alone—a query that runs fast but 100,000 times per hour will show high total time. Use pg_stat_activity with state = 'active' and query_start > now() - interval '5 minutes' filtered to active queries during peak to catch currently-running slow queries. For recurring issues, add auto_explain with auto_explain.log_min_duration = '500ms' to capture query plans for slow queries automatically.

Stable p50 with increasing p99 is a classic sign of a subset of queries experiencing degradation while most queries are fine. This rules out system-wide issues (CPU, memory, disk) and points to query-specific problems. Diagnosis steps: (1) Compare p99 query list this week vs last week in pg_stat_statements — look for queries with high max_exec_time that were previously faster; (2) Check for new queries added to the system; (3) Check if specific business events caused traffic pattern changes (new feature launch, scheduled jobs); (4) Examine execution plans for the degraded queries — look for changes like degraded index scans to sequential scans, or increased row estimates causing different join strategies; (5) Check for lock contention on specific tables or rows — pg_stat_activity with wait_event_type filters for specific tables; (6) Check for parameter changes in pg_stat_statements settings that might have changed query plans.

Alert design principles for sustainable monitoring: (1) Alert on symptoms, not causes — "database latency is high" is better than "cache hit ratio is low"; the symptom is what affects users; (2) Use composite conditions — fire only when disk > 80% AND query latency p99 > 1s simultaneously; single thresholds fire too easily; (3) Require persistence — fire only when condition persists for 5+ minutes, not on momentary spikes; (4) Use graduated severity — warning (investigate within 24h) vs critical (respond now) vs page (wake someone up); (5) Include context — every alert should state: what is wrong, which system, since when, and suggested first action; (6) Tune quarterly — if you ignore an alert, either fix the underlying issue or adjust the threshold; (7) Review alert rate — if you're getting more than 10 warnings per day per system, thresholds are too sensitive; (8) Separate business impact from system metrics — "checkout success rate dropped" is more actionable than "CPU at 90%".

pg_stat_activity is real-time session monitoring — it shows what's happening RIGHT NOW. For each active connection: current state (idle, active, waiting), current query (or last query if idle), query start time, wait events if blocked, transaction age. Use for: diagnosing currently running queries, identifying blocking locks, seeing connection counts by state, watching query execution in real time. pg_stat_statements is historical query aggregation — it summarizes ALL queries across all sessions over time. Metrics: total calls, total time, total I/O time, cache hit ratio per normalized query. Use for: identifying top queries by total time, comparing query performance week-over-week, finding queries to optimize. Key difference: pg_stat_activity tells you what's running NOW; pg_stat_statements tells you what has been running OVER TIME. Both are essential for complete monitoring.

Database health check in order: (1) Disk space: SELECT pg_size_pretty(pg_database_size(current_database())) and check df.blocks - df.free; (2) Connection status: SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state; (3) Long-running queries: SELECT now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '5 minutes'; (4) Cache hit ratio: SELECT blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0) AS cache_hit_ratio FROM pg_stat_database; (5) Replication lag: SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag on replica; (6) Lock contention: SELECT * FROM pg_locks WHERE granted = false; (7) Autovacuum status: SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5; (8) Top queries by total time: from pg_stat_statements. Run these in under 2 minutes to get a complete health picture.

pg_stat_user_tables tracks table-level statistics: number of live/dead tuples, sequential scans vs index scans,_INSERT/UPDATE/DELETE counts, last vacuum and analyze times, vacuum and analyze count. Use when: diagnosing which tables are hot spots, checking if autovacuum is keeping up with dead tuples, understanding read vs write patterns per table, verifying ANALYZE ran recently. pg_stat_user_indexes tracks per-index statistics: number of index scans (idx_scan), tuples read and fetched via the index, index size. Use when: identifying unused indexes (idx_scan = 0 — these can be dropped), comparing which indexes are actually being used for queries, diagnosing if adding an index improved access patterns. Combine both: high sequential scans on a table with a corresponding index that has low idx_scan indicates the index is not being used and queries are doing full table scans.

Database DoS indicators and monitoring: (1) Connection flood — spike in connection count above normal baseline; alert when current_connections > 80% of max_connections from unknown sources; (2) Query flood — sudden spike in queries per second beyond normal traffic patterns; compare against historical baseline from pg_stat_statements; (3) Expensive query pattern — many concurrent queries with similar expensive patterns (full table scans) suggest an attack or misconfigured application; (4) Authentication failures — monitor PostgreSQL logs for spike in failed login attempts; (5) Lock exhaustion — many queries waiting on the same lock; attacker might try to hold a lock for extended period. Mitigations: connection limiters at the application layer, PgBouncer with strict pool sizing, rate limiting on the application server, firewall rules restricting access to known IP ranges, failed login monitoring and temporary IP blocks.

Mixed workload monitoring strategy: (1) Separate connection pools — OLTP uses one pool (smaller, higher max connections), OLAP uses a separate pool (larger, fewer connections, different work_mem settings); (2) Separate resource groups or query priority — PostgreSQL doesn't have built-in resource governance, but Linux cgroups can limit OLAP query resource usage; (3) OLTP monitoring — monitor transaction latency p50/p95/p99, connection utilization, lock wait ratio; (4) OLAP monitoring — monitor query duration, temp file usage (indicates work_mem spilling), I/O rates during large scans; (5) Conflict monitoring — OLAP scans holding SHARE locks can block OLTP writes; monitor pg_stat_activity for lock waits involving long-running queries; (6) Alerting — set separate p99 thresholds for OLTP (e.g., 100ms) and OLAP (e.g., 60s); mixing them in one alert rule hides OLTP issues. Dashboard: two separate panels for OLTP latency and OLAP query duration.

Capacity planning dashboard metrics: (1) Disk usage over time — current used vs total, with projection line; (2) Disk growth rate — GB per week; (3) CPU utilization trend — average and p95 over time; (4) Memory utilization — shared_buffers vs effective_cache_size vs total RAM; (5) Connection count trend — current vs max_connections; (6) Cache hit ratio trend — weekly average; (7) Query latency trend — p50, p95, p99 over time; (8) Replication lag — if using replicas; (9) Table and index sizes — top 10 largest tables, top 10 fastest growing; (10) Dead tuple accumulation — n_dead_tup trend for top tables. Projection: use Grafana's threshold visualization to show when current growth rate hits 80% and 90% of provisioned capacity. This gives ops team runway visibility — they should know 60-90 days before hitting limits.

EXPLAIN ANALYZE with BUFFERS shows actual execution details: (1) Look for rows estimates vs actual rows — large discrepancy (> 10x) means stale statistics, run ANALYZE; (2) Look for sequential scans on large tables — often indicates missing index; (3) Check sort and hash operations — high actual rows vs estimated rows for sort indicates work_mem too low, causing disk spills; (4) Look for nested loop on large datasets — may benefit from hash join or merge join; (5) Buffers: shared_hit (from cache) vs shared_read (from disk) — low hit ratio indicates cache is too small for working set; (6) Actual time per node — identifies which step is slowest; (7)loops — if loops > 1, the operation ran multiple times, multiply time per loop to get total contribution to query time. The key is comparing estimated vs actual for rows and time to pinpoint where planner assumptions differ from reality.

pg_stat_bgwriter tracks the background writer process. Metrics to monitor: (1) checkpoints — frequency of checkpoints; too many checkpoints indicate issues with checkpoint_segments or checkpoint_timeout; (2) buffers_checkpoint — buffers written during checkpoint; (3) buffers_clean — buffers written by the bgwriter; high buffers_clean relative to buffers_checkpoint means bgwriter is doing more work than checkpoints; (4) maxwritten_clean — how many times bgwriter stopped because it reached bgwriter_lru_maxpages; if this is high, bgwriter can't keep up. High maxwritten_clean: increase bgwriter_lru_maxpages or decrease bgwriter_delay. High buffers_backend (backends writing directly): same tuning. High buffers_backend_fsync: this is disk being overwhelmed, tune disk or reduce write volume. The goal is a healthy balance where bgwriter handles most buffer writes and backends rarely write directly.

pg_partman monitoring additions: (1) Partition count — monitor number of partitions; too many partitions cause performance degradation; pg_partman maintains a part_config table tracking this; (2) Partition creation lag — ensure partitions are being created ahead of data arrival; check partman's run_maintenance_log; (3) Parent table bloat — parent table should be tiny (only contains partition boundaries); if it grows, something is inserting directly into parent; (4) Detached partition monitoring — pg_partman can detach old partitions for archival; monitor that this is happening on schedule; (5) Child table sizes — monitor that partitions are evenly sized; lopsided sizes indicate skewed data patterns. SQL for partition monitoring: SELECT parent_table, COUNT(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') GROUP BY parent_table. Alert on partition count exceeding thresholds (e.g., > 1000 partitions warrants review).

Same-query slowness with no data change causes: (1) Statistics are stale — run ANALYZE on the table; (2) Index was dropped or disabled — check pg_stat_user_indexes for idx_scan changes; (3) Another query is holding a lock — check pg_stat_activity for wait events on the same table; (4) Connection pooling issue — query might be running under a different session or with different settings; (5) Configuration change — check postgresql.conf for work_mem, random_page_cost, or other planner-affecting settings that changed; (6) Planner regression — PostgreSQL sometimes chooses different plans for the same query due to planner statistics noise; use SET plan_seed to reproduce; (7) Memory pressure — another session might be consuming memory, causing this query to spill to disk; (8) Table bloat — VACUUM hasn't run recently and dead tuples are slowing scans. Start with EXPLAIN (ANALYZE, BUFFERS) and compare to an old EXPLAIN if saved.

Query optimization detection monitoring: (1) Track p99 query duration per query — when p99 increases > 2x baseline for specific queries, flag for review; (2) Monitor sequential scan rate per table — high sequential scans without corresponding data growth indicate index loss or statistics issues; (3) Monitor work_mem spills — queries with high temp buffer usage (sort to disk) via pg_stat_statements.total_plan_time vs total_exec_time; (4) Track query plan changes — if using pg_stat_statements with plan capture, compare plans over time; a plan change often precedes performance regression; (5) Monitor index usage ratio — for each table, compare seq_scan vs idx_scan; increasing seq_scan on tables with indexes indicates optimizer is not using indexes; (6) Set up slow query capture with EXPLAIN plans — auto_explain.log_min_duration captures plans for slow queries. Alert threshold: any query whose p99 increases > 50% week-over-week, or any table with > 10x increase in sequential scans without corresponding data growth.

HA cluster monitoring additions: (1) Replication lag — per replica, in bytes and seconds; alert at 30s for normal workloads, lower for financial applications; (2) Replica connection status — ensure replicas are connected and receiving WAL; (3) Failover readiness — check pg_stat_replication for state and lag; (4) Primary election — monitor for unexpected primary switches; (5) Patroni or similar orchestrator health — ensure consensus is healthy; (6) Quorum presence — for quorum-based systems, ensure majority is reachable; (7) DNS/VIP resolution — if using virtual IP, ensure it resolves correctly after failover; (8) Connection pool health — PgBouncer or PgCat instances remain reachable after failover; (9) Time to recovery — track how long failover takes from start to new primary accepting writes; (10) Split brain detection — for active-active setups, monitor for writes to both sides simultaneously. HA monitoring is only valuable if tested — run failover drills annually to verify monitoring and procedures work.

PgBouncer monitoring metrics: (1) Pool active vs idle — active connections using DB, idle connections in pool; high active count approaching pool limit indicates shortage; (2) Waiting clients — if clients are queuing, pool is undersized or queries are holding connections too long; (3) Server connection lifetime — long-lived server connections indicate connections are being reused rather than created/destroyed (good); (4) Query wait time — how long queries wait in queue before being executed; (5) Bytes received/sent — throughput monitoring; (6) Error rate — connection errors, query errors. PgBouncer show stats command shows: num_ws_acquired, num_wq_auth, num_server_connections, etc. Alert thresholds: wait queue > 0 sustained (indicates pool undersized), server connection errors > 0 (indicates backend issues), pool utilization > 90% sustained. Monitoring: export PgBouncer stats to Prometheus via postgres_exporter and visualize in Grafana.

Production PostgreSQL go-live checklist: (1) Disk space — alert at 85%, critical at 90%; (2) Connection count — alert at 80% of max_connections; (3) Cache hit ratio — alert below 95%; (4) Query latency p99 — baseline established, alert at 2x baseline; (5) Replication lag — alert at 30s for replicas; (6) Long-running queries — alert at 60s; (7) Lock waits — alert for queries waiting > 10s; (8) Database errors — log errors to monitoring system; (9) Backup success/failure — verify backups are running and test restore; (10) Autovacuum activity — ensure autovacuum is running and not falling behind on any table. Beyond these minimums, add: slow query logging with auto_explain, monitoring for specific critical queries, custom business metrics (order placement latency), and capacity planning dashboards showing growth trends. The minimums prevent outages; the additions prevent slowdowns and enable optimization.