Offensive Security Researcher & Quantum Cryptography Analyst

How to Monitor PostgreSQL Performance

Abdul Wahab Junaid
How to Monitor PostgreSQL Performance

There’s a big difference between fixing a performance problem after users complain and knowing about it before they do. Early in my career, monitoring meant checking whether the database process was still running. These days I treat monitoring as something closer to a continuous diagnostic process — a set of views, extensions, and dashboards that tell me not just whether PostgreSQL is up, but whether it’s actually healthy. In this article, I’ll walk through the tools and views I actually use day to day to monitor PostgreSQL performance, what the numbers mean, and how I turn them into something actionable.

The Built-In Statistics Views

PostgreSQL tracks an enormous amount of internal statistics automatically, exposed through a family of pg_stat_* views. You don’t need to install anything to start using these — they’re always there.

pg_stat_activity: What’s Happening Right Now

This is the view I check first whenever something feels off in real time.

SELECT pid, usename, application_name, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

This shows every active connection, what it’s currently doing, how long it’s been doing it, and — importantly — what it’s waiting on, if anything. The wait_event column is one of the most underused diagnostic tools I know of; a query that’s been “running” for ten minutes but is actually stuck waiting on a lock looks very different from one that’s genuinely doing ten minutes of CPU-bound work, and this column tells you which.

pg_stat_user_tables: Table-Level Activity

SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

I look at this for two main things: tables with a high seq_scan count relative to their size (a sign of a missing index), and tables with a high n_dead_tup relative to n_live_tup (a sign of bloat that autovacuum isn’t keeping up with).

pg_stat_user_indexes: Index Usage

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Indexes near the bottom of this list, especially large ones, are candidates for removal — every index has write overhead, so an index nobody uses is pure cost.

pg_stat_database: Database-Wide Health

SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit, tup_returned, tup_fetched, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

A high blks_read relative to blks_hit means a lot of your reads are going to disk instead of being served from PostgreSQL’s shared buffer cache — I’ll come back to this ratio (the cache hit ratio) shortly, since it’s one of the single most useful high-level health numbers you can track.

The Cache Hit Ratio

This is one of the first numbers I check on any unfamiliar database.

SELECT
    sum(blks_hit) AS cache_hits,
    sum(blks_read) AS disk_reads,
    round(sum(blks_hit)::numeric / nullif(sum(blks_hit) + sum(blks_read), 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database;

For most OLTP workloads, I want to see this consistently above 99%. If it’s noticeably lower, that means PostgreSQL is going to disk far more than it should, which usually points to either an undersized shared_buffers setting relative to your working data set, or queries that are scanning far more data than necessary (which circles back to indexing).

pg_stat_statements: Finding Your Worst Queries

I mentioned this extension in my article on query optimization, but it deserves emphasis here too, because it’s genuinely the single highest-leverage monitoring tool available in PostgreSQL.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Remember it needs to be added to shared_preload_libraries and the server restarted before it starts collecting meaningful data.

SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

I typically build two views out of this: one sorted by total_exec_time (where is cumulative time actually going — good for prioritizing what to optimize) and one sorted by mean_exec_time for queries with a meaningful calls count (which surfaces individually slow queries that might not run often enough to dominate the total but are still bad user experiences when they do run).

Resetting the stats after a deploy or a major optimization effort helps you measure whether a change actually helped:

SELECT pg_stat_statements_reset();

Monitoring Vacuum and Bloat

Autovacuum is critical to PostgreSQL’s health, and a database where it’s falling behind will slowly degrade in ways that are easy to miss until they’re serious.

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_ratio DESC
LIMIT 20;

A high dead tuple ratio, especially combined with an old or missing last_autovacuum timestamp, tells me autovacuum isn’t keeping pace with the write volume on that table — usually because the default autovacuum thresholds are too conservative for a high-churn table, and I need to tune autovacuum_vacuum_scale_factor more aggressively for it specifically:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

I also keep an eye on transaction ID wraparound risk, which is a more severe, database-wide version of the same underlying issue:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

If xid_age is climbing toward the multi-hundred-million range without bound, that’s worth investigating urgently — PostgreSQL will eventually force aggressive vacuuming (and in extreme cases, refuse new writes) to prevent transaction ID wraparound corruption.

Monitoring Locks and Blocking

I covered this in depth in my article on managing locks, but from a monitoring perspective, the queries worth turning into a recurring check are:

SELECT pid, query, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

and a check for long-running idle-in-transaction sessions:

SELECT pid, usename, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;

Both are cheap to run on a schedule and catch problems well before they escalate into a full outage.

Connection Monitoring

SELECT count(*), state
FROM pg_stat_activity
GROUP BY state;

I like tracking this over time relative to max_connections, since approaching that ceiling is a leading indicator of the kind of connection exhaustion problems I covered in my connection pooling article.

SHOW max_connections;

Replication Lag (If You’re Running Replicas)

For any setup with streaming replication, monitoring lag is essential — a replica falling too far behind isn’t just a staleness problem, it can also cause WAL to accumulate on the primary in ways that affect disk usage.

On the primary:

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

On a replica, checking how far behind it is:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Bringing It Together: External Monitoring Tools

Querying these views manually is fine for ad hoc investigation, but for ongoing monitoring I want dashboards, historical trends, and alerting — not something I’m expected to remember to check. A few tools worth knowing about:

For log-based analysis to be useful, I make sure log_min_duration_statement is configured to capture slow queries:

log_min_duration_statement = 500  # log queries slower than 500ms

Common Use Cases

Troubleshooting Tips

Cache hit ratio looks fine overall but specific queries are still slow. The aggregate ratio can hide per-table or per-query problems. Check pg_statio_user_tables for per-table heap and index block hit ratios instead of relying solely on the database-wide number.

pg_stat_statements shows nothing. Almost always a shared_preload_libraries and restart issue — check my extensions article for the details.

Dead tuple counts keep climbing despite autovacuum being enabled. Check autovacuum_vacuum_scale_factor and autovacuum_vacuum_cost_limit — the defaults are conservative and often need tuning per-table for high-churn tables rather than relying on the global defaults.

Replication lag spikes intermittently. Check for long-running queries on the replica (if hot_standby_feedback isn’t tuned appropriately, replay can be delayed to avoid canceling replica queries), and check network throughput between primary and replica.

Best Practices

  1. Set up pg_stat_statements from day one, not after the first performance incident.
  2. Track the cache hit ratio and dead tuple ratios over time, not just as a one-off check.
  3. Alert on wait_event patterns and blocked sessions, not just raw query duration.
  4. Tune autovacuum per-table for high-churn tables rather than relying only on global defaults.
  5. Monitor replication lag actively if you’re running replicas — don’t wait for a stale-read bug report to notice.
  6. Invest in a real dashboarding tool (Prometheus/Grafana, pgwatch2, or your managed provider’s console) rather than relying purely on manual psql checks for ongoing visibility.
  7. Reset pg_stat_statements after significant changes so your before/after comparisons are meaningful.

A Real-World Example: Catching a Slow Leak Before It Became an Outage

I want to describe a case where monitoring caught a problem days before it would have become a real incident, since that’s really the point of doing all this proactively rather than reactively. A weekly dashboard review showed the cache hit ratio on one particular database had been slowly drifting down over about three weeks — from a healthy 99.7% to just under 97%. Nothing was on fire, nothing had paged anyone, but the trend was consistent and clearly not noise.

SELECT date_trunc('day', now()) AS day,
       round(sum(blks_hit)::numeric / nullif(sum(blks_hit) + sum(blks_read), 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database;

Cross-referencing with pg_stat_user_tables, the growth was concentrated in one table whose row count had roughly tripled over the same period, due to a new feature that was writing far more data than the original design anticipated. The table’s working set had simply outgrown what fit comfortably in shared_buffers, and more of its reads were falling through to disk as a result.

Because this was caught early via the trend rather than after users started noticing slow page loads, the fix was calm and unhurried: partitioning the fast-growing table by date (see my article on partitioning) so most queries only touched a small, recent partition that fit comfortably in cache, plus a modest increase to shared_buffers to give some additional headroom. Had this gone unnoticed for another month or two, it likely would have surfaced instead as a sudden, confusing “why did the dashboard get so slow” complaint from users, investigated under time pressure instead of on a calm Tuesday afternoon.

Building a Minimal Daily Health Check

I keep a short script, run daily via cron and posted to a monitoring channel, that captures the handful of numbers I care about most without needing a full dashboarding stack. It’s not a replacement for real monitoring infrastructure, but it’s a good starting point for a project that doesn’t have one yet:

SELECT
    (SELECT round(sum(blks_hit)::numeric / nullif(sum(blks_hit) + sum(blks_read), 0) * 100, 2)
     FROM pg_stat_database) AS cache_hit_ratio,
    (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction'
     AND now() - xact_start > interval '5 minutes') AS long_idle_in_txn,
    (SELECT count(*) FROM pg_stat_activity) AS total_connections,
    (SELECT max(age(datfrozenxid)) FROM pg_database) AS max_xid_age,
    (SELECT count(*) FROM pg_stat_user_tables
     WHERE n_dead_tup > 100000 AND n_dead_tup > n_live_tup) AS bloated_tables;

Any of these numbers moving in the wrong direction over consecutive days is worth a closer look, well before it becomes urgent. I’ve found that this kind of lightweight, consistent check catches far more problems early than any amount of ad hoc investigation after something’s already gone wrong.

Frequently Asked Questions

How often should I check these views? For active investigation, as often as needed. For ongoing health, I check the core metrics (cache hit ratio, connection counts, dead tuple ratios, pg_stat_activity blocking) at least daily via an automated check, with a deeper manual review weekly.

Does querying pg_stat_statements itself slow down the database? The overhead of collecting the statistics is small and generally acceptable in production, since it’s designed for exactly this purpose. Querying the view itself is cheap; just be mindful of the pg_stat_statements.max setting, which caps how many distinct query shapes are tracked before older entries get evicted.

Do I need external tools, or are the built-in views enough? The built-in views are enough to diagnose almost anything if you know what to query, but they don’t give you history or alerting on their own — for that, an external tool that polls and stores these metrics over time is genuinely necessary, not just a nice-to-have.

What’s the very first thing to check when a database “feels slow”? pg_stat_activity filtered to non-idle sessions, sorted by duration, so I can see immediately whether something is actually stuck, and the wait_event column to understand why.

Should I monitor CPU, memory, and disk I/O at the OS level too, or is the database-level view enough? Both matter, and they answer different questions. The pg_stat_* views tell you what PostgreSQL itself thinks is happening — cache hits, index usage, blocking — but they won’t directly tell you if the underlying host is starved for CPU or hitting disk I/O limits imposed by the infrastructure. I always pair database-level monitoring with basic OS/infrastructure metrics, since a query that looks fine in EXPLAIN ANALYZE can still be slow in practice if the host itself is under resource pressure from something entirely unrelated to PostgreSQL, like a noisy neighbor process or a saturated network interface.

Is it worth setting up alerting, or is a dashboard enough to check manually? For anything running in production, alerting is worth the setup effort. A dashboard only helps if someone happens to be looking at it at the right moment; alerting on the handful of leading indicators I described above — climbing dead tuple ratios, a dropping cache hit ratio, long idle-in-transaction sessions, replication lag — means the problem finds you instead of the other way around, well before it turns into something users notice.

Wrapping Up

Monitoring PostgreSQL well doesn’t require exotic tooling — the vast majority of what I check day to day comes straight from views that ship with PostgreSQL itself, plus pg_stat_statements, which is about as close to mandatory as an extension gets. What matters more than the specific tool is building the habit of checking these signals regularly, before they turn into incidents, rather than only reaching for them once users are already complaining. A database that’s being watched consistently tends to stay healthy; one that’s only checked during outages tends to keep having them.

Exit mobile version