VACUUM: Garbage Collection and Autovacuum

The orders table is 200 GB, but only 50 GB is real data. The other 150 GB is garbage from millions of UPDATEs over the past months. Queries are slow, disk is full. VACUUM FULL fixes it in 2 hours of downtime. pg_repack fixes it in the same 2 hours without taking the service offline.

• **Slack** (2019) discovered the channels table had grown to 250 GB of bloat after autovacuum fell behind during the COVID growth spike. They had to run pg_repack in production in emergency mode. Today dead_pct > 15% triggers an automatic alert.
• **Shopify** sets autovacuum_vacuum_scale_factor = 0.01 on the orders table (10M+ rows). With the default 0.2, autovacuum would fire at 2M dead tuples: too late.
• **Gitlab** monitors txid wraparound risk through `age(datfrozenxid)` in pg_database. Approaching 1.5B they force a VACUUM FREEZE so the cluster never hits emergency freeze.

Dead Tuples: Where the Garbage Comes From

Every UPDATE in PG creates a new row version and marks the old one as dead (a dead tuple). DELETE also leaves a dead tuple. The row physically stays in place; only xmax is set to the deleting transaction's txid. Accumulating dead tuples causes table bloat: the table file grows, seq scans get slower, and shared_buffers fills with garbage.

A long-lived transaction (a stuck analytical query or an open cursor) holds the xmin of its snapshot. VACUUM cannot remove dead tuples whose xmax is below that xmin. One hour-long transaction can pile up gigabytes of garbage on active tables.

VACUUM vs VACUUM FULL: Two Different Tools

Regular VACUUM marks dead tuples as reusable but does not return space to the OS. The physical file size does not shrink. VACUUM FULL rewrites the table entirely into a new file: space is returned, but it needs an exclusive lock (AccessExclusiveLock) for the entire operation. On a 100 GB table that can take hours.

Autovacuum Tuning: Right Thresholds

Autovacuum fires when `n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup`. Defaults: threshold=50, scale_factor=0.2. For a 100M-row table that means 20M dead tuples before autovacuum runs: far too rare. On hot tables you should lower scale_factor to 0.01 or even 0.001.

autovacuum_vacuum_cost_delay controls throttling: vacuum sleeps for the configured delay after accumulating cost_limit work points (read a page = 1 point, write = 20). This keeps vacuum from killing I/O. On SSD servers you can drop it to 0-2 ms.

Table Bloat: Diagnosis and Measurement

Bloat is the gap between physical table size and actual data volume. It happens when dead tuples accumulate faster than VACUUM removes them, or when VACUUM cleared the garbage but the file did not shrink. Index bloat is critical: a bloated index reads more slowly and stops fitting in cache.

Index bloat: `SELECT * FROM pgstatindex('idx_orders_user_id')` shows leaf_fragmentation and avg_leaf_density. Density < 50% signals heavy index bloat. REINDEX CONCURRENTLY rebuilds without locking (PG 12+).

pg_repack: Defragmentation Without Locks

pg_repack rebuilds a table into a new file without an exclusive lock. It creates a copy, applies changes through a trigger, and then atomically swaps. AccessExclusiveLock is held only for the final swap: milliseconds. It is the production-safe replacement for VACUUM FULL.

pg_repack creates a trigger on the source table to capture changes during the copy. So the table must have a PRIMARY KEY (without one pg_repack does not work). Write load increases slightly during the run because of the trigger overhead.

Key Ideas

• Dead tuples = physically live rows with xmax set. VACUUM marks them reusable but does not return space to the OS.
• Autovacuum threshold = 50 + scale_factor * live_rows. For large tables drop scale_factor to 0.01 per table.
• VACUUM FULL = a full lock for hours. pg_repack = a rebuild without downtime via trigger-captured changes.

Related Topics

VACUUM is part of the MVCC and monitoring ecosystem in PG:

• MVCC and dead tuples — Dead tuples are a direct consequence of MVCC: UPDATE does not overwrite, it appends. Without VACUUM the heap grows without bound.
• Transactions and long snapshots — Long-lived REPEATABLE READ transactions hold xmin. VACUUM cannot remove dead tuples older than that xmin.
• Indexes — Index bloat accumulates the same way table bloat does. REINDEX CONCURRENTLY and pg_repack --only-indexes fix it without locking.

Вопросы для размышления

• What happens to autovacuum behavior if you set autovacuum_vacuum_scale_factor = 0 and autovacuum_vacuum_threshold = 100?
• Why can a table be faster after VACUUM FULL than after pg_repack? Hint: row order on disk.
• txid wraparound: pg_database.datfrozenxid hits 2^31. What does PG do if VACUUM FREEZE was not run in time?

Связанные уроки

• db-09-indexes-btree