PostgreSQL Performance Tuning: The 6 Metrics That Matter

It's one of the most common stories in PostgreSQL operations. A CloudWatch alarm fires at 85% CPU on the production database for the third night running. Someone opens the RDS console, sees a Multi-AZ db.r6g.xlarge working hard, and files the change that always gets approved under pressure: move to a db.r6g.2xlarge. That's roughly $700 a month extra, forever. Weeks later somebody finally runs pg_stat_statements and finds the real culprit: 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 story 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. On managed Postgres (RDS, Cloud SQL, Azure Database for PostgreSQL), tuning is about the bill as much as the latency.

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. Every query runs 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.

01Workload

Slow queries

A few statements carry most of the CPU. One regression and the instance looks too small.

02Memory

Cache hit ratio

Below roughly 95%, the working set has outgrown shared_buffers and reads go to disk.

03Indexes

Unused and bloated

Indexes nothing reads still tax every write and take cache from real data.

04Access

Sequential scans

Full scans on large tables, usually one missing index away from milliseconds.

05Capacity

Connections

Hundreds of idle backends and stale idle-in-transaction sessions eat memory and block vacuum.

06Maintenance

Autovacuum lag

Dead tuples pile up faster than autovacuum clears them, and every scan pays for it.

---

Why "add a bigger instance" keeps winning

The upgrade wins because of how the alarm arrives, not because anyone is careless. CloudWatch, Cloud Monitoring and Azure Monitor see the database from the outside: CPU, freeable memory, IOPS, connections. They can tell you the instance is busy. They can't tell you which statement is making it busy, because that answer lives inside Postgres, in views nobody has open at 2 a.m.

01Growth

Table crosses a size

A query written against a small dataset meets 40 million rows.

02Symptom

CPU alarm

Sequential scans pin the CPU and evict the hot working set.

03Pressure

Upgrade approved

A bigger instance class is the fastest fix anyone can sign off on.

04Result

Cost stays

The query is still wrong. The bill is now permanently higher.

So the person on call has two options with very different evidence costs. Scaling up needs no diagnosis and fixes the symptom tonight. Finding the query needs pg_stat_statements, an EXPLAIN, and enough context to know whether an index is safe on a hot table. Under pressure the first option wins, and since nothing forces anyone to come back once the alarm stops, it stays.

The six metrics below are the diagnosis. Each one is a question the infrastructure metrics can't answer.

---

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 to 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 already in shared_preload_libraries in the default parameter group, so you only need CREATE EXTENSION pg_stat_statements;. Cloud SQL loads it for you too, so CREATE EXTENSION (run as a cloudsqlsuperuser member) is all it takes. Azure Database for PostgreSQL Flexible Server preloads it, but you allowlist it in the azure.extensions server parameter before CREATE EXTENSION works. On self-managed Postgres, add it to shared_preload_libraries and restart 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. Read the result in two directions. High total_ms with high calls is a frequency problem (an N+1 loop, a missing cache). High mean_ms with few calls is a query-shape problem (a missing index, a bad plan).

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 to 60%, which is 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();

This counts a hit only 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 to 6 first. Bloat and bad indexes inflate the working set artificially, and teams routinely reclaim 20 to 40% of it without adding a gigabyte of RAM.

3. Unused and bloated indexes

What it is. Indexes cost twice: disk (which competes for cache) and write amplification, since every INSERT and 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 when stats were last reset first (stats_reset in pg_stat_database). If they were reset yesterday, "unused" means nothing. Exclude unique and primary-key indexes, since 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 to 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. That's fine on a 2,000-row lookup table and 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 opening story's instance upgrade.

How to detect it. Rank tables by rows read through 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 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 to 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 and other managed equivalents), not a higher max_connections. Setting idle_in_transaction_session_timeout puts a ceiling on the stale sessions while the application gets fixed.

Typical impact. Teams that add pooling typically drop real connection counts from hundreds to dozens, reclaim memory for cache, and remove a whole category of "database is down" incidents that were really 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 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 to 20% of a large table, or a hot table whose last_autovacuum is days old, means autovacuum is losing. The default autovacuum_vacuum_scale_factor of 0.2 waits for 20% of a table to change before vacuuming, which on a 100-million-row table is 20 million dead rows. The usual fixes are per-table overrides and hunting the long-running transactions that prevent cleanup:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);

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

---

The six metrics at a glance

Why the quarterly tuning pass keeps failing

Each check above takes minutes. Databases degrade anyway because 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. That's 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. CloudThinker connects to PostgreSQL with a read-only role and watches slow queries, index health, connection saturation and bloat continuously. It surfaces regressions the day they appear, with the evidence attached, and proposes fixes that never execute without your approval. Teams that work through these six metrics typically avoid at least one instance-class upgrade per year. Part three shows how it works.

Related reading

• Neon Postgres performance: what slows serverless Postgres down
• MySQL slow queries: 5 root causes and how to catch each one
• Auto Mode: how CloudThinker earns write access

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