PostgreSQL Monitoring & Observability: pg_stat Views & Metrics

Q: How do I know if my cache hit ratio is a problem?

Compute it from pg_statio_user_tables (heap blocks hit ÷ total heap blocks). For OLTP workloads you generally want it very high — around 99% or above; a consistently lower ratio suggests your working set doesn't fit in memory, pointing to undersized shared_buffers or a need for more RAM. Use it as a directional signal alongside query latency, not an absolute target.

PostgreSQL Monitoring & Observability

Monitoring PostgreSQL means watching the signals that tell you whether the database is healthy before users feel it — slow queries creeping up, connections filling, replication falling behind, tables bloating, locks piling up. Postgres exposes a rich set of built-in statistics views (the **pg_stat_*** family) plus the pg_stat_statements extension, and the skill is knowing which numbers actually matter and what thresholds should wake someone up.

Good database observability is the difference between "we noticed replication lag climbing and fixed it" and "the read replicas served stale data for three hours and we found out from customers." This page covers the key data sources, the metrics worth tracking, and what to alert on — the Postgres-specific application of general observability and monitoring practice.

TL;DR

Postgres exposes health data through built-in **pg_stat_* views and the pg_stat_statements** extension (top queries by time).
Track: connections (vs limit), cache hit ratio, query latency / slow queries, replication lag, bloat & dead tuples, locks & blocked queries, and transaction age (wraparound).
pg_stat_statements is the single most valuable tool for finding what's actually slow — enable it everywhere.
Alert on the things that precede outages: connections near the limit, replication lag rising, long-running transactions, and (critically) transaction ID wraparound risk.
Export metrics to your monitoring stack (Prometheus + a Postgres exporter, or your provider's dashboards) rather than eyeballing views ad hoc.
Monitoring pairs with performance tuning — it tells you what to fix; tuning is how.

Quick Example

The built-in views answer most health questions in plain SQL. A few essential checks:

These views are always available — the foundation of Postgres observability before any external tooling.

Core Concepts

The Data Sources

pg_stat_statements is the standout — it's the primary way to find which queries consume the most total time, so you optimize by evidence rather than guessing. Enable it on every production database.

The Metrics That Matter

Connections vs limit — nearing max_connections means impending "too many connections" errors; a sign you need pooling.
Cache hit ratio — the fraction of reads served from memory. Consistently below ~99% for OLTP suggests undersized shared_buffers or a working set that doesn't fit RAM.
Query latency & slow queries — from pg_stat_statements; watch the top total-time queries and rising means.
Replication lag — how far behind standbys are. Rising lag means replicas serve staler data and failover would lose more; a key alert.
Bloat & dead tuples — from pg_stat_user_tables (n_dead_tup); climbing dead tuples signal autovacuum falling behind and coming slowdowns. See Performance Tuning.
Locks & blocked queries — long lock waits and blocking chains cause stalls; pg_locks and pg_stat_activity wait events reveal them.
Transaction ID (XID) age — the one that can shut the database down if ignored (below).

Transaction ID Wraparound: The One You Must Not Ignore

Postgres uses 32-bit transaction IDs and relies on vacuuming (freezing) to avoid XID wraparound. If autovacuum can't keep up and the oldest unfrozen transaction gets too old, Postgres will, as a last resort, refuse new writes to protect data integrity — an outage. It's rare with healthy autovacuum, but it's the classic "database suddenly went read-only" catastrophe. Monitor the age of the oldest transaction (age(datfrozenxid)) and alert well before the danger zone. This is non-negotiable for any serious Postgres deployment.

Best Practices

Enable pg_stat_statements on Every Database

It's the highest-value observability tool Postgres offers — it surfaces your most expensive and most frequent queries so you fix what actually costs you. Turn it on by default in production; investigating "why is the DB slow?" without it is guesswork.

Export Metrics Into Your Monitoring Stack

Don't rely on running SELECTs by hand during an incident. Scrape the pg_stat_* data with a Postgres exporter into Prometheus/Grafana (or use your managed provider's dashboards), so you have history, trends, and alerting. Trends matter as much as current values — a metric creeping up over days is the early warning.

Alert on Leading Indicators, Not Just Outages

Alert on the signals that precede problems: connections approaching the limit, replication lag rising past a threshold, long-running/idle-in-transaction sessions, dead-tuple growth, and — critically — XID age nearing wraparound. Catching these early turns would-be incidents into routine fixes.

Watch Long-Running and Idle-in-Transaction Sessions

A single long-lived or idle-in-transaction session pins old row versions (blocking vacuum → bloat), can hold locks (blocking others), and retains replication slots. Monitor for them in pg_stat_activity and alert on sessions open beyond a sane threshold.

Correlate Database and Application Signals

Database metrics are most useful next to application metrics — a latency spike in the app lined up with rising DB query time or lock waits points straight at the cause. Feed DB observability into the same observability stack as the rest of your system.

Common Mistakes

Not Enabling pg_stat_statements

Ignoring Transaction ID Wraparound

Treating autovacuum as "it just works" and never monitoring XID age — until the database refuses writes to prevent wraparound and you have a surprise outage. Monitor age(datfrozenxid) and alert with plenty of headroom.

Alerting Only When It's Already Down

Monitoring that fires only on hard failure (DB unreachable) misses the slow slides — creeping replication lag, filling connections, growing bloat — that you could have fixed early. Alert on leading indicators with sensible thresholds.

FAQ

What are the most important PostgreSQL metrics to monitor?

Connections versus the limit, cache hit ratio, query latency and slow queries (via pg_stat_statements), replication lag, bloat/dead tuples, locks and blocked queries, and transaction ID age (wraparound risk). These cover the failure modes that actually cause incidents — resource exhaustion, stale replicas, autovacuum falling behind, and lock contention. Track their trends, not just current values.

What is pg_stat_statements and why does everyone recommend it?

It's an extension that records aggregated statistics per query — total and mean execution time, call counts, rows — so you can see exactly which queries consume the most time across the whole database. That makes it the primary tool for performance work: you optimize the real top consumers instead of guessing. Enable it on every production database as a default.

What is transaction ID wraparound and should I worry about it?

Postgres uses 32-bit transaction IDs and depends on vacuuming to "freeze" old rows and avoid the counter wrapping. If autovacuum falls badly behind and the oldest transaction gets too old, Postgres will refuse new writes to protect data integrity — effectively an outage. It's uncommon with healthy autovacuum but is a real, severe risk worth a dedicated alert: monitor age(datfrozenxid) and act well before the threshold.

How do I know if my cache hit ratio is a problem?

Compute it from pg_statio_user_tables (heap blocks hit ÷ total heap blocks). For OLTP workloads you generally want it very high — around 99% or above; a consistently lower ratio suggests your working set doesn't fit in memory, pointing to undersized shared_buffers or a need for more RAM. Use it as a directional signal alongside query latency, not an absolute target.

Do managed databases handle monitoring for me?

They provide dashboards and some alerting (RDS, Cloud SQL, Neon, Supabase expose metrics and often pg_stat_statements), which covers a lot. But you should still know which metrics matter, set alerts on leading indicators for your workload, and — where the platform allows — integrate database metrics into your own observability stack alongside application signals. Don't assume the defaults alert on everything that matters (like XID age).