PostgreSQL maintenance that actually runs

2026-04-25

Is there bloat, and where

Guessing from database size on disk tells you nothing, because dead tuples are reused before the file grows. Look at the statistics instead:

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

Two readings matter. A table with a high dead_pct whose last_autovacuum is old is being skipped — usually because something holds a long-running transaction or an idle-in-transaction connection open. A table where autovacuum runs constantly and the number stays high is being rewritten faster than it can be cleaned, and that is a workload problem, not a vacuum problem.

-- what is pinning the horizon
SELECT pid, state, xact_start, query_start, left(query, 60)
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;

idle in transaction is the usual answer, and it is an application bug that presents as a database problem. Vacuum cannot remove anything newer than the oldest snapshot, so one forgotten BEGIN can bloat an entire cluster.

The one deadline that is not negotiable

Transaction IDs are 32-bit. When a table's frozen XID gets too close, PostgreSQL refuses writes and vacuums everything in sight, in the foreground, while you watch.

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

SELECT c.relname, age(c.relfrozenxid) AS xid_age
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relpersistence = 'p' AND c.relkind IN ('r','m')
ORDER BY xid_age DESC LIMIT 10;

Compare autovacuum_freeze_max_age against your write rate, not against a default copied from a blog. The table you should worry about is the one nobody vacuums because it is "small" — small tables with old rows are exactly the ones that reach the limit.

Two settings worth touching

autovacuum_vacuum_scale_factor — the default 0.2 means a ten-million-row table waits for two million dead tuples. For hot tables, set it per table to something like 0.02 with a floor via autovacuum_vacuum_cost_limit, so the small ones still do not vacuum on every insert.
vacuum_cost_limit — vacuum is deliberately throttled. On a box with idle I/O that throttle is the reason the backlog grows. Raise it for the autovacuum worker rather than running manual VACUUM FULL, which rewrites the table and takes an access-exclusive lock.

Index builds and reindexing

CREATE INDEX CONCURRENTLY ... ;   -- no table lock, two table scans, cannot run in a transaction
REINDEX INDEX CONCURRENTLY ... ;  -- for an index that bloated after a bulk delete

CONCURRENTLY leaves an INVALID index behind if it fails halfway; check with \d+ or pg_index.indisvalid and drop it explicitly. A bloated index and a bloated table look identical in query latency, and only one of them needs a rewrite.

The connection count

Four hundred connections on a four-core machine is not a capacity problem to be solved by raising max_connections. Every backend gets its own memory context and its own snapshot; past a few dozen active queries you are scheduling, not serving. The fix is at the client or in a pooler, and it usually looks like "the application opens a connection per request and forgets to return it".

← manuals