PostgreSQL monitoring in production is one of those disciplines that looks straightforward until your database starts misbehaving at 2 AM. This guide covers the key metrics to track in a PostgreSQL deployment, what they mean in practice, and how to build a monitoring setup that catches problems before they become incidents.
Why PostgreSQL Monitoring Requires Its Own Approach
PostgreSQL exposes a rich set of internal statistics through its system catalog views – pg_stat_activity, pg_stat_bgwriter, pg_stat_user_tables, and others. These views update in real time and give you a window into what the database engine is actually doing. Most generic server monitoring tools only scratch the surface of this data, collecting basic connection counts and leaving the rest untouched.
Understanding which views matter and how to interpret them is what separates teams that catch issues early from those that get surprised by them.
Connection Metrics – The First Place Things Go Wrong
PostgreSQL has a hard limit on the number of concurrent connections, set by the max_connections parameter. The default is typically 100. When that limit is reached, new connection attempts fail immediately – there’s no queuing, no backoff, just an error returned to the application.
A common failure pattern: a connection leak in application code, introduced during a routine deployment, slowly exhausts the connection pool over several hours. By the time the on-call engineer gets paged, the database is rejecting connections and the application is throwing errors across the board.
Track active connections, idle connections, and idle-in-transaction connections separately. Idle-in-transaction is particularly dangerous – it holds locks and blocks the vacuum process. If this number is growing, something is holding transactions open without committing.
Connection pool monitoring adds another layer here: pool size, checkout wait time, and pool exhaustion events should be part of the same dashboard as your database connection metrics.
Query Performance and the Metrics That Reveal Slow Execution
The pg_stat_statements extension is essential for production PostgreSQL monitoring. It tracks execution counts, total time, mean time, and rows returned for every distinct query pattern. Without it, diagnosing query performance is mostly guesswork.
Enable it in postgresql.conf by adding pg_stat_statements to shared_preload_libraries. After a restart, create the extension with CREATE EXTENSION pg_stat_statements. From that point, you have a queryable view of aggregated query performance data.
Watch for queries where mean_exec_time is climbing over time without a corresponding increase in rows – this often indicates index degradation or table bloat. Also watch total_exec_time for queries that run frequently but for short durations; a 5ms query called 100,000 times per minute adds up quickly.
Table Bloat and the Vacuum Process
This is the area most teams underestimate. PostgreSQL’s MVCC (Multi-Version Concurrency Control) model means deleted and updated rows aren’t removed immediately – they leave dead tuples behind. The autovacuum process reclaims this space, but it can fall behind under heavy write workloads.
Bloated tables cause index scans to slow down and sequential scans to become more expensive. Checking pg_stat_user_tables for n_dead_tup and last_autovacuum gives a quick picture of vacuum health. If n_dead_tup is large and last_autovacuum is hours or days ago, autovacuum may be configured too conservatively or blocked by long-running transactions.
Track autovacuum activity as a first-class metric. Many production incidents trace back to bloat that accumulated silently over weeks.
Replication Monitoring in Production
If you’re running PostgreSQL with streaming replication, replication lag is a critical metric. It’s measured in bytes (via pg_stat_replication on the primary) or seconds (using clock timestamps on replicas). Both matter depending on your use case.
Byte lag tells you how much WAL data the replica hasn’t applied yet. Time lag tells you how stale the replica’s data is – important if any read traffic is being directed there. In a healthy setup, lag should stay below a few megabytes and a few seconds. Sustained growth in either metric means the replica is falling behind.
Monitor replication slot status as well. Unused or stalled replication slots will cause WAL files to accumulate on disk indefinitely, which can fill your primary’s disk if left unaddressed.
Turning Metrics Into Actionable Alerts
Collecting metrics is only half the problem. The other half is knowing which ones to alert on and at what thresholds. Real-time alerting on database metrics requires careful threshold tuning – too sensitive and you get alert fatigue, too loose and you miss early warning signs.
Reasonable starting thresholds for PostgreSQL:
– Connection usage above 80% of max_connections: warning
– Idle-in-transaction connections older than 5 minutes: warning
– Replication lag above 30 seconds: warning, above 5 minutes: critical
– Dead tuples exceeding 20% of live tuples on a table: review autovacuum configuration
– Query mean execution time doubling from baseline: investigate
Pair these with database health metrics tracked at the server level – disk I/O, memory usage, and CPU – since PostgreSQL performance is tightly coupled to the underlying hardware it runs on.
The Myth That Uptime Equals Database Health
A widespread misconception is that if PostgreSQL is accepting connections and queries are returning results, the database is healthy. In practice, a database can be in serious trouble while appearing to function normally from the outside.
Autovacuum may be completely blocked. Indexes may be bloated to the point where the query planner is choosing sequential scans. Replication may be hours behind. A long-running transaction may be holding locks that will eventually block writes across the entire application.
Uptime is a floor, not a ceiling. Real PostgreSQL monitoring measures what’s happening inside the engine, not just whether the process is responding to connections.
Frequently Asked Questions
How often should PostgreSQL metrics be collected?
For production databases, 30-second intervals are a reasonable starting point. Connection counts and query statistics can change fast enough that 1-minute intervals may miss short spikes. For pg_stat_statements data, a 1–5 minute scrape interval is typically sufficient since it aggregates across all executions.
Does enabling pg_stat_statements affect database performance?
The overhead is low in practice – typically less than 1% of query execution time. The extension hashes query text and updates counters in shared memory, which is a lightweight operation. For most workloads, the diagnostic value far outweighs any measurable performance cost.
What should I do if autovacuum can’t keep up with write load?
First check for long-running transactions blocking vacuum with SELECT pid, now() – xact_start, query FROM pg_stat_activity WHERE state = ‘idle in transaction’. Terminate sessions that have been open for more than a few minutes. If the problem persists, consider tuning autovacuum_vacuum_scale_factor and autovacuum_vacuum_cost_delay for your highest-write tables.
Building a Reliable PostgreSQL Monitoring Practice
PostgreSQL gives you the data you need – the challenge is building a monitoring setup that captures it consistently, surfaces the right signals, and alerts without overwhelming the team. Start with connections, query performance, vacuum health, and replication lag. Establish baselines before production load hits so you know what normal looks like. From there, tune alert thresholds based on what actually matters for your workload and SLAs.
The teams that handle PostgreSQL incidents well aren’t necessarily the ones with the most dashboards – they’re the ones who understand what their metrics mean and have clear playbooks for acting on them.