PostgreSQL Monitoring Checklist: The Signals Every Production Database Needs

PostgreSQL exposes hundreds of counters across the pg_stat_* views, yet most production outages trace back to a small set of undetected conditions. Bloat accumulates silently until vacuum cannot catch up. Replication lag grows until failover becomes a data-loss event. Transaction ID age crosses a threshold and the database stops accepting writes. The problem is rarely a lack of metrics. It is knowing which signals to instrument at each stage of operational maturity.

This checklist maps the essential PostgreSQL signals to an eight-stage maturity model. It is not an encyclopedia of every view and column. It is the minimum set of signals that must be visible, alerted, and staffed with a runbook before you can claim a given operational level. If you are watching connection count but not transaction ID age, you are one wraparound emergency away from a reactive firefight.

Use this article to audit your current observability. Identify the highest stage where every signal is monitored with a defined threshold. Close the gaps stage by stage rather than trying to build a perfect dashboard overnight.

Stage 1: Escape Reactive Mode

Stage 1 teams respond to user complaints and have no telemetry. Before you can monitor signals, you must expose them. The transition enablers are architectural, not metric tweaks.

Enable pg_stat_statements in shared_preload_libraries. Without it, query-level performance is invisible. Configure streaming replication so pg_stat_replication exists. Deploy a connection pooler such as PgBouncer in transaction mode. Without pooling, connection count is a meaningless signal because max_connections becomes a false ceiling. Establish continuous WAL archiving and test a restore. Backups that have never been restored are not backups.

Until these prerequisites are in place, the stages below are theoretical.

Stage 2: Health & Capacity Signals

Stage 2 teams have dashboards and alerts for survival-level metrics. These signals detect capacity exhaustion and availability risk before applications fail.

Decompose replication lag when it spikes. pg_stat_replication exposes write_lag, flush_lag, and replay_lag. If write_lag grows but replay_lag stays low, the network or WAL sender is the bottleneck. If replay_lag grows while the others stay flat, the replica is slow to apply WAL, often due to disk I/O or a long-running query blocking replay.

Stage 3: Workload Quality Signals

Stage 3 teams baseline query performance and tune per-table autovacuum. Add these signals to detect plan regressions, bloat, and index inefficiency before they degrade user experience.

Sampling caveat: pg_stat_statements normalizes query text, stripping literal values. Two executions with the same structure but different parameters are aggregated. A stable mean_time with high stddev_time often means the query is fast for most parameters and pathological for one value. Use auto_explain with log_min_duration_statement to capture the actual plan for outlier executions.

Stage 4: Contention Signals

Stage 4 teams understand locking and hot standby behavior. Monitor these signals to detect serialization bottlenecks and replica friction.

Discriminate wait event types carefully. Lock events on user tables indicate application contention, often from UPDATE or DELETE on the same rows. LWLock events indicate internal serialization pressure, buffer mapping contention, or WAL write saturation. High CPU with low query throughput and many LWLock waits means the database is bottlenecked on internal coordination, not query complexity.

Stage 5: Plan Stability & Deadlock-Free Operation

Stage 5 teams enforce query plan stability and eliminate deadlocks through application design. The signals here validate that previous tuning has stuck and that the workload is predictable.

At this stage, latency SLOs should be met consistently, not just on average. If stddev_time rises after a deployment, investigate statistics changes or parameter sniffing before the mean time degrades.

Stage 6: Durability & Recovery Signals

Stage 6 teams treat backups and WAL as first-class concerns. These signals prevent the silent failures that turn recoverable incidents into data loss.

Note on XID age: PostgreSQL takes emergency actions, including refusing writes, as the XID age approaches 2 billion to prevent wraparound. Operational alerts must fire at 500 million so you have weeks to correct autovacuum behavior before an emergency freeze is required.

Stage 7: Optimizer Health Signals

Stage 7 teams tune the planner with custom statistics and verify partition pruning. These signals catch the statistical failures that cause plan regressions on large or skewed data sets.

Monitor pg_class.relpages growth rate against pg_class.reltuples. If pages grow faster than tuples, bloat or fillfactor issues are confusing the planner’s cost model.

Stage 8: Predictive & Autotuned Signals

Stage 8 teams automate remediation and predict capacity exhaustion. These signals drive automation rather than human runbooks.

At this stage, manual intervention for performance should be rare. When these signals fire, the response is typically an automated scaling action, a scheduled repack, or a deployment hold rather than a 3 a.m. page.

How Netdata Helps

Netdata collects PostgreSQL metrics directly from the pg_stat_* views and correlates them with OS-level CPU, memory, disk, and network telemetry on the same dashboard.

• It exposes pg_stat_statements metrics including calls, total time, and mean time, making it easier to spot workload regressions without running manual queries during an incident.
• Replication lag, connection utilization, and cache hit ratio are collected with per-second granularity, so you can correlate a query latency spike with a checkpoint I/O burst or autovacuum worker launch.
• Netdata alerts on composite conditions such as high dead tuple ratio combined with a stale last_autovacuum, which indicates vacuum is falling behind.
• The PostgreSQL collector surfaces pg_stat_replication_slots, flagging inactive slots that retain WAL before they fill the disk.
• System-level metrics from the same agent reveal whether a database issue is rooted in the OS or storage layer, reducing misattribution.

Related guides

• How PostgreSQL actually works in production: a mental model for operators
• PostgreSQL monitoring maturity model: from reactive to self-healing
• PostgreSQL FATAL: too many connections — causes and fixes
• PostgreSQL connection exhaustion: detection, diagnosis, and prevention
• PgBouncer pool exhausted: how to diagnose and fix client waits
• PostgreSQL connection refused: pg_hba, listen_addresses, and TCP diagnosis
• PostgreSQL idle in transaction: detecting and killing zombie sessions
• PgBouncer vs Pgpool-II vs Odyssey: choosing a PostgreSQL connection pooler
• PostgreSQL deadlock detected: how to diagnose and prevent deadlocks
• PostgreSQL ERROR: could not obtain lock — diagnosis and recovery
• PostgreSQL blocking queries: finding the root blocker in a lock cascade
• PostgreSQL row-level lock contention: SELECT FOR UPDATE patterns and fixes