PostgreSQL Performance Tuning: The 6 Metrics That Matter

It is one of the most common stories in PostgreSQL operations: a production database gets upgraded from a Multi-AZ db.r6g.xlarge to a db.r6g.2xlarge — roughly $700/month extra, forever — and the real culprit only surfaces weeks later: one query filtering a 40-million-row events table on an unindexed tenant_id column. A single CREATE INDEX, about 900 MB on disk, would have dropped CPU from a sustained 85% back to 20%. The upgrade keeps running anyway.

That pattern is the core economics of PostgreSQL performance tuning: slow queries are compute cost. Every sequential scan, every bloated index, every query burning 400 ms that should take 4 ms consumes CPU and I/O you eventually pay for as a bigger instance, more IOPS, or a read replica you didn't need. Tuning isn't just about latency — on managed Postgres (RDS, Cloud SQL, Azure Database), it's directly about the bill.

The good news is that Postgres tells you almost everything through its pg_stat_* views. This guide covers the six metrics that predict trouble, what a bad number looks like, one detection query for each, and the typical impact. All queries run read-only against production.

This is part one of a three-part series. Part two is a hands-on PostgreSQL performance audit using only native tools; part three covers automating continuous tuning with an AI agent.

1. Slow queries — the ones that become instance upgrades

What it is. A handful of queries almost always dominate total execution time. In a typical mid-market workload, the top 10 statements account for 60–90% of database CPU. If one of them regresses — a missing index after a schema change, a plan flip when a table crosses a size threshold — total load jumps, and the usual response is a bigger instance rather than a diagnosis.

Why it happens. Queries are written against small dev datasets, ship fast, and degrade slowly as tables grow. Nobody notices until CPU alarms fire, and by then the cheap fix (an index) and the expensive fix (an upgrade) look equally urgent.

How to detect it. Enable the pg_stat_statements extension — on RDS it's preloaded and you only need CREATE EXTENSION pg_stat_statements;; on Cloud SQL you may also have to add it to the shared_preload_libraries flag first — then rank by total time:

SELECT
  substring(query, 1, 80)      AS query,
  calls,
  round(total_exec_time::numeric, 0)  AS total_ms,
  round(mean_exec_time::numeric, 2)   AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

(On PostgreSQL 12 and earlier the columns are total_time and mean_time.) High total_ms with high calls is a frequency problem; high mean_ms with few calls is a query-shape problem. For catching outliers as they happen, the postgres slow query log is the complement: set log_min_duration_statement = 500 (milliseconds) and every statement slower than that lands in the log with its full text. Part two covers reading both in depth.

Typical impact. Fixing the top two or three offenders commonly cuts database CPU by 30–60% — often the difference between upgrading an instance class and not.

2. Cache hit ratio

What it is. The share of block reads served from shared_buffers rather than disk. For an OLTP workload, you want this above 99%; below roughly 95%, the working set no longer fits in memory and queries start paying disk latency — and on provisioned-IOPS storage, paying literally.

How to detect it.

SELECT
  datname,
  round(100.0 * blks_hit / GREATEST(blks_hit + blks_read, 1), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

Note this counts a hit when the block is in Postgres's own buffer cache; reads served by the OS page cache still show as blks_read, so treat the number as a floor. A steadily falling ratio matters more than the absolute value — it means data growth is outrunning memory.

Typical impact. A cache-starved database is the one legitimate reason to buy a bigger instance — but check metrics 3–6 first. Bloat and bad indexes inflate the working set artificially; teams routinely reclaim 20–40% of it without adding a gigabyte of RAM.

3. Unused and bloated indexes

What it is. Indexes cost twice: disk (which counts against cache) and write amplification — every INSERT/UPDATE maintains every index on the table. Indexes that nothing ever reads are pure overhead, and long-lived B-tree indexes on update-heavy tables accumulate bloat, growing to several times their rebuilt size.

Why it happens. Indexes get added speculatively during incidents and never audited. ORMs generate redundant ones. Nobody drops an index, because dropping feels risky and disk feels cheap — until write latency and cache pressure say otherwise.

How to detect it. Find indexes with zero scans since the stats were last reset:

SELECT
  schemaname,
  relname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Check pg_stat_reset() history first — if stats were reset yesterday, "unused" means nothing. Exclude unique and primary-key indexes (they enforce constraints regardless of scans), and check replicas before dropping anything: an index unused on the primary may serve reads elsewhere.

Typical impact. It's common to find 10–30% of total index storage completely unused. Dropping it speeds up every write to those tables and frees cache for data that earns its keep.

4. Sequential scans on large tables

What it is. A sequential scan reads the entire table. Fine on a 2,000-row lookup table; a disaster on a 40-million-row events table, where each scan evicts your hot working set from cache and burns I/O in bulk. This is the metric behind the intro's instance upgrade.

How to detect it. Rank tables by rows read via sequential scans:

SELECT
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 15;

A large table with high seq_scan and low idx_scan is a missing-index candidate. Confirm with EXPLAIN (ANALYZE, BUFFERS) on the suspect query before creating anything — the planner documentation covers how to read the output, and part two of this series walks through it step by step.

Typical impact. The single highest-leverage fix in Postgres. One well-placed index on a hot filter column routinely turns a 2-second query into a 5-millisecond one — a 100x class of improvement no instance upgrade can match.

5. Connection saturation

What it is. Every Postgres connection is an OS process with real memory overhead. As you approach max_connections, new connections fail with "too many clients"; well before that, hundreds of mostly-idle connections waste memory that should be cache. The classic tell is a pile of idle in transaction sessions — connections holding locks and blocking vacuum while doing nothing.

How to detect it.

SELECT
  state,
  count(*),
  max(now() - state_change) AS max_in_state
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state
ORDER BY count(*) DESC;

Compare the total against SHOW max_connections;. Sustained usage above roughly 70–80% of the limit, or any idle in transaction session older than a few minutes, is a finding. The durable fix is server-side pooling with PgBouncer (or RDS Proxy / managed equivalents), not raising max_connections.

Typical impact. Teams that add pooling typically drop real connection counts from hundreds to dozens, reclaim memory for cache, and eliminate a whole category of "database is down" incidents that were actually connection exhaustion.

6. Autovacuum lag and table bloat

What it is. Postgres's MVCC design means UPDATE and DELETE leave dead row versions behind for autovacuum to reclaim. When autovacuum can't keep up — big tables, default thresholds, long-running transactions pinning old rows — dead tuples accumulate as bloat. A table that's 40% dead rows makes every scan 40% slower and inflates backups, replicas, and storage bills alike.

How to detect it.

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

Dead tuples above 10–20% of a large table, or a hot table whose last_autovacuum is days old, means autovacuum is losing. The usual fixes are per-table autovacuum_vacuum_scale_factor overrides and hunting the long-running transactions that prevent cleanup.

Typical impact. On update-heavy schemas, reclaiming bloat commonly shrinks hot tables by 20–50%, with matching gains in scan speed and cache efficiency — and it's storage you stop paying for.

Why the quarterly tuning pass keeps failing

Each check above takes minutes. The reason databases degrade anyway is that none of these metrics stay fixed. Every deploy ships new queries. Every feature shifts the access pattern. Tables cross size thresholds and plans flip overnight. An index that was essential in March is dead weight by August.

A quarterly tuning pass means each regression runs for an average of six weeks before anyone looks — six weeks of degraded latency, and often a "temporary" instance upgrade that becomes permanent because nobody circles back once the alarms stop. The checks aren't hard; the cadence is wrong.

Start manual anyway: part two of this series turns these six checks into a full DIY audit with the slow query log, pg_stat_statements, and EXPLAIN — no third-party tools required.

Make the checks continuous

Everything in this guide can run unattended. Tony, CloudThinker's database agent, connects to PostgreSQL read-only and watches slow queries, index health, connection saturation, and bloat continuously — surfacing regressions the day they appear, with evidence, and proposing fixes that never execute without your approval. Teams that work through these six metrics typically avoid at least one instance-class upgrade per year.

Try CloudThinker free — 100 premium credits, no card required — or continue with the hands-on native-tools audit.