Vacuum and bloat: keeping the storage tax bounded

An autovacuum worker ran for 30 minutes on your orders table, removed zero dead tuples, and logged “287 million are not yet removable.” The table keeps growing. This is not an autovacuum configuration problem — it is a snapshot pinning problem, and knowing which is which determines the fix.

Where the cost is paid

Each UPDATE creates one fresh tuple plus marks the old tuple dead — both on disk, both in the heap, both eligible for index entries. Until VACUUM clears the dead tuples, every sequential scan walks past them and every page is bigger than the strict minimum.

A common rule of thumb at production scale: target dead-tuple ratio below 20% for tables above 50 GB. Smaller tables tolerate higher bloat (30–50%) because the absolute waste is small.

VACUUM never physically shrinks the file — it just marks space reusable inside the file. Reclaiming disk back to the operating system requires:

• VACUUM FULL — rewrites the whole table while holding an ACCESS EXCLUSIVE lock, blocking every reader and writer
• pg_repack extension — rewrites concurrently, swaps at the end (covered in lesson 07)

How autovacuum actually decides to run

Autovacuum is a background process pool (3 workers by default) that wakes every minute and inspects every table for two threshold conditions:

• Dead-tuple threshold: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup (default: 50 + 20% of live tuples)
• Insert threshold: similar formula for fresh inserts that need ANALYZE

When a table crosses a threshold, autovacuum dispatches a worker. The worker takes a SHARE UPDATE EXCLUSIVE lock — strong enough to block other VACUUMs and schema changes, weak enough to let regular reads and writes proceed in parallel.

The worker computes the global oldest xmin across all sessions (the cap on reclaimable tuples), scans the heap, identifies dead tuples whose t_xmax is older than oldest xmin, marks their slots reusable, walks each index to remove stale pointers, and rebuilds the table’s free-space map.

Throughout, autovacuum is rate-limited by a cost-based delay: every page read costs a few units, every page write costs more units, and once the worker exceeds autovacuum_vacuum_cost_limit, it sleeps for autovacuum_vacuum_cost_delay milliseconds.

What pins the oldest xmin

The most common cause of bloat that autovacuum cannot reclaim: a long-running transaction or orphan replication slot is holding back the global oldest xmin.

Diagnose with:

SELECT pid, backend_xmin, now() - xact_start AS duration
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin LIMIT 5;

SELECT slot_name, xmin FROM pg_replication_slots WHERE xmin IS NOT NULL;

Fix: terminate the long transaction (SELECT pg_terminate_backend(pid)) or drop the unused slot (SELECT pg_drop_replication_slot('name')). The very next autovacuum cycle then reclaims the pinned bloat.

Prevention: set idle_in_transaction_session_timeout to 5–15 minutes in production. This kills sessions that have an open transaction without activity, preventing them from pinning xmin indefinitely.

The hot_standby_feedback wrinkle

Streaming replicas can be configured with hot_standby_feedback = on, which sends the replica’s oldest active xmin back to the primary so the primary’s autovacuum knows not to reclaim tuples a replica still needs. Without it, a long analytical query on a replica can fail with canceling statement due to conflict with recovery.

With it, the primary’s bloat is held hostage by the replica’s longest-running query. Most production setups choose off on the replica and accept the occasional query cancellation, because pinning primary bloat indefinitely is the worse failure mode. The alternative is to route long analytics to a logical replica that maintains its own snapshot policy independently.