Understand MVCC and routine maintenance

PostgreSQL uses MVCC, multiversion concurrency control, so readers and writers can often work at the same time. Instead of overwriting a row in place, an update creates a new row version. The old version stays until no transaction needs it. Readers that started before your update keep seeing the old version; nobody blocks anybody for a plain read.

The price is garbage. Updated and deleted rows leave dead row versions behind, physically present in the table but invisible to new queries. A table with heavy update traffic can hold far more dead versions than live rows, wasting disk and slowing scans.

Autovacuum is the cleanup crew

Autovacuum reclaims reusable space and runs ANALYZE to refresh planner statistics. It wakes up on its own, checks which tables changed enough, and cleans them. Do not disable it as a performance shortcut. The workload spike you avoid today becomes table bloat and a bad query plan next month.

The thing that quietly defeats autovacuum is a long transaction. Cleanup can only remove versions no transaction might need, so one session sitting idle in transaction since this morning pins every dead row created since then, across the whole database.

Statistics have a manual lever too. Refresh statistics after a large test-data load:

ANALYZE app.notes;

The planner estimates row counts from statistics. Right after loading a million rows, the statistics still describe the empty table, and plans come out wrong until the next analyze.

Look before you guess

Check what maintenance has been doing:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

A table with a large n_dead_tup and an old last_autovacuum is a table where cleanup is losing, and the next query tells you why. Inspect current sessions and waits:

SELECT pid, usename, application_name, state,
       wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE datname = current_database();

Look for long idle in transaction sessions, old queries, and lock waits. That idle-in-transaction session is the classic finding: an application checked out a connection, started a transaction, and went off to do something else, holding locks and pinning dead rows the whole time.

Diagnose them before increasing connection limits or disabling maintenance. Operational “fixes” applied without this look — more connections, less vacuuming — usually feed the underlying problem instead of solving it.