PostgreSQL monitoring: the essential metrics to watch
10 min read
CPU load, IO and cache hit ratio, connections, locks, a single health score: the PostgreSQL metrics that really matter, and how they compare to Oracle's AWR.
Why PostgreSQL monitoring needs a specific approach
PostgreSQL exposes a considerable amount of data through its pg_stat_* views (activity, database, statements, bgwriter...), but provides neither a native dashboard, nor a before/after comparison concept, nor a consolidated health score: each view must be queried and interpreted separately. Effective monitoring therefore consists of choosing a small number of truly decisive metrics, rather than collecting every available counter with no hierarchy.
CPU load and parallelism (Load)
The number of simultaneously active sessions (pg_stat_activity, 'active' state) related to max_worker_processes gives a direct estimate of the pressure on available CPU: beyond this ceiling, active sessions compete for already-saturated resources rather than progressing in parallel.
A load that durably exceeds 1x this limit is a strong signal of a CPU bottleneck, often caused by one or more expensive queries rather than by an overall traffic volume.
IO and cache hit ratio
The cache hit ratio (share of reads served from shared_buffers rather than from disk, computed from blks_hit and blks_read in pg_stat_database) is one of the most telling indicators of an instance's memory sizing: a ratio dropping below 95% deserves watching, below 90% it becomes genuinely concerning for most workloads.
A sudden drop in the cache hit ratio alongside a rise in slow queries usually points to a specific query reading an unusual volume of data, rather than a diffuse, global degradation.
Connections and the connection pool
The number of active client connections related to max_connections signals pool saturation before it turns into refused-connection errors on the application side. It's not just a capacity question: a pool close to saturation also increases the latency of every query, including ones that would normally be fast.
Locks and blocked sessions
A session waiting for a lock held by another transaction (wait_event_type = 'Lock' in pg_stat_activity) isn't slow: it's blocked. The distinction matters, because the fix is completely different — optimizing a query solves nothing if the real problem is a transaction holding a lock too long.
Blocked sessions have an immediate contagion effect: each one can itself hold on to resources and block other sessions in a cascade, making it one of the metrics to monitor as an absolute priority.
A single health score: the Health Score
Multiplying charts is useful for detailed investigation, but impractical for a daily glance. PWR combines Load, blocked sessions, connections used, P95 latency and cache hit ratio into a single score from 0 to 100, with a weighting that reflects their real impact (blocked sessions, for example, weigh more than the cache ratio, since their contagion effect is more immediate).
This score is then translated into a simple badge (Healthy, Warning, Critical) that lets you know in one second whether a database needs immediate attention, even before opening the detail of the individual metrics.
PostgreSQL vs Oracle AWR: what changes
Oracle has long offered the Automatic Workload Repository (AWR), a reference report based on comparing two snapshots. PostgreSQL has never had an official equivalent: every team has to assemble its own diagnostic queries from the pg_stat_* views.
PWR follows AWR's proven logic (comparable snapshots, root cause rather than raw collection) while adding what Oracle doesn't offer natively: a health engine that runs continuously without waiting for a report to be manually generated, automatically tracked state transitions, and alerts sent as soon as an incident is confirmed.