Vacuum and bloat

VACUUM is PostgreSQL’s garbage collector. Every update and delete leaves the old row version behind; VACUUM removes versions nobody can see any more, records the freed space for reuse, and freezes old rows so 32-bit transaction IDs can safely wrap around. Bloat is what you get when it can’t keep up: tables and indexes much larger than their live data. Autovacuum usually copes; the skill is spotting when it can’t.

Picture it first: the station cloakroom

A busy railway station cloakroom stores bags on numbered racks. When a bag is collected, its slip stays in the slot until nobody could still claim it. Every few hours an attendant walks the racks, clears every slot whose slips are dead, and writes the free slots on a board so new bags go there first.

Three things go wrong:

• The rack never shrinks. Clearing makes room for new bags, but the rack stays as long. Shortening it means moving every bag to a smaller rack while nobody can collect anything.
• One passenger blocks the round. If someone might still come back for a bag from this morning, nothing left since this morning can be cleared.
• The tickets wrap. Tickets come from a four-digit roll. When 9999 rolls over to 0000, an old ticket 0412 would look newer than today’s, so old bags must be stamped “permanent” first.

Map it back: slots are space in 8 kB heap pages, uncollected slips are dead tuples, the attendant’s round is VACUUM, the board is the free space map, the passenger with a claim is a long transaction holding back the horizon, moving to a smaller rack is VACUUM FULL, and stamping bags permanent is freezing before the 32-bit transaction ID counter wraps.

Why PostgreSQL needs vacuum

PostgreSQL’s MVCC never overwrites a row: an UPDATE writes a new tuple and stamps the old one with the updater’s transaction ID, and a DELETE only stamps. Old versions stay until no running snapshot can need them. Reads never block and rollback is free; clean-up gets the bill.

Dead tuples cost you while they wait:

• Scans read them, and index scans follow entries to tuples that turn out to be invisible.
• Indexes grow, because every non-HOT update adds an entry to every index.
• Cache fills with garbage. A table that is 60% dead needs 1 ÷ 0.4 = 2.5 times the memory and I/O for the same live rows.

The routine vacuuming chapter lists four jobs for VACUUM: recover or reuse space, update planner statistics (with ANALYZE), update the visibility map, and protect against transaction ID and multixact ID wraparound.

What plain VACUUM does, step by step

A plain VACUUM (what autovacuum runs) works through a table in phases you can watch in pg_stat_progress_vacuum:

1. Scan the heap. Skip pages the visibility map says are clean. On the rest, prune dead tuples, defragment the page, collect the IDs of dead line pointers, and freeze old tuples.
2. Vacuum the indexes. Remove every index entry pointing at a collected dead tuple. If the dead-tuple list outgrows maintenance_work_mem (or autovacuum_work_mem), indexes are vacuumed more than once.
3. Vacuum the heap. Mark those line pointers unused so new tuples can take the slots.
4. Record the results. Update the free space map (one byte per page, so inserts find room) and the visibility map.
5. Truncate the tail. If pages at the very end of the file are empty, cut them off and return them to the operating system.

The visibility map stores two bits per heap page: all-visible (nothing on the page needs vacuuming, and index-only scans can skip the heap) and all-frozen (even an anti-wraparound vacuum can skip it). Only vacuum sets the bits; any change to the page clears them. It is tiny: a 100 GB table has 100 GB ÷ 8 kB ≈ 13.1 million pages, and 13.1 million × 2 bits ≈ 3.3 MB of map.

Locks. Plain VACUUM takes SHARE UPDATE EXCLUSIVE, so reads and writes carry on; only schema changes and other maintenance wait. Tail truncation needs a brief ACCESS EXCLUSIVE lock, avoidable with VACUUM (TRUNCATE false) or vacuum_truncate. Autovacuum steps aside when a session requests a conflicting lock, unless it is running to prevent wraparound.

What it doesn’t do. Plain VACUUM doesn’t compact the table or return space to the operating system, apart from those tail pages. The space is reused by the same table, which is the intended steady state: each table at its minimum size plus whatever is used between vacuum runs.

HOT pruning and fillfactor

Some cleanup happens without VACUUM. When an update changes no indexed column and the new version fits on the same page, PostgreSQL makes it a HOT (heap-only tuple) update: no new index entries. Intermediate versions in a HOT chain can then be pruned during normal page access, even by a SELECT, leaving a redirect from the original slot.

HOT needs free room on the page, so update-heavy tables benefit from a lower fillfactor, which makes inserts fill pages only part way:

-- Orders change status 5 to 6 times each; leave 30% of every page for new versions
ALTER TABLE orders SET (fillfactor = 70);
-- Applies to newly written pages; rewrite the table to apply it everywhere

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

You gain fewer index writes, less index bloat and less vacuum work. You pay with a bigger table: at fillfactor 70 the same live rows take about 100 ÷ 70 ≈ 1.43 times the pages. Indexing a frequently updated column such as status rules out HOT for every update that changes it. The design reasons behind this cost, compared with InnoDB’s in-place updates, are in PostgreSQL vs MySQL.

How autovacuum decides

The launcher starts a worker in each database every autovacuum_naptime (1 minute), with at most autovacuum_max_workers (3) at once. Each worker checks every table against three triggers (defaults):

• Dead-tuple trigger. Vacuum when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples, that is 50 + 0.2 × rows. PostgreSQL 18 caps this at autovacuum_vacuum_max_threshold, 100,000,000 by default.
• Insert trigger (PostgreSQL 13+). Vacuum when rows inserted since the last vacuum exceed 1,000 + 0.2 × rows (PostgreSQL 18 multiplies by the share of pages not yet all-frozen), so append-only tables get visibility-map bits too.
• Analyze trigger. ANALYZE when rows inserted, updated or deleted since the last analyze exceed 50 + 0.1 × rows.

Tables past autovacuum_freeze_max_age are vacuumed regardless (see wraparound below).

Why the defaults fail on big tables

A scale factor suits a 10,000-row table (vacuum after 50 + 2,000 = 2,050 dead rows) and fails a big one. Take a 500-million-row payments table:

• Default trigger: 50 + 0.2 × 500,000,000 = 100,000,050 dead tuples. PostgreSQL 18’s 100,000,000 cap only starts to matter above 500 million rows.
• At 2,000 updates per second, that takes 100,000,000 ÷ 2,000 = 50,000 s, about 14 hours, and each run must then clean 100 million tuples and their index entries.

The fix is per-table storage parameters on your largest, busiest tables:

ALTER TABLE payments SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- 50 + 0.01 × 500M ≈ 5M dead tuples
  autovacuum_vacuum_insert_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

Now vacuum runs about every 5,000,050 ÷ 2,000 ≈ 2,500 s, roughly 42 minutes, with a twentieth of the work per run.

Throttling: why a running vacuum can still be too slow

Autovacuum is deliberately gentle. It counts a cost for every page: 1 for a page found in shared buffers, 2 for a page read from disk, 20 for a page it dirties. After vacuum_cost_limit (200) units it sleeps for autovacuum_vacuum_cost_delay (2 ms). The limit is shared across all running workers.

Ignoring the work itself, that allows at most 200 units per 2 ms, or 100,000 a second. If every page is read and dirtied (2 + 20 = 22 units), that is about 4,500 pages a second, roughly 36 MB/s, shared by all workers. A 500 GB table then takes 500,000 MB ÷ 36 MB/s ≈ 13,900 s, almost four hours, per pass. On fast storage, raise the cost limit before adding workers, since more workers just share the same budget.

What blocks cleanup: a diagnosis checklist

VACUUM can only remove a dead tuple once no snapshot anywhere could still see it. That limit, the xmin horizon, is set by whichever holder is oldest, and while it is pinned autovacuum runs, finds nothing removable, and runs again. The MVCC article shows how one forgotten transaction pins it; here is how to find the culprit, in order:

-- 1. Long-running or idle-in-transaction sessions
SELECT pid, state, now() - xact_start AS xact_age,
       age(backend_xmin) AS xmin_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

-- 2. Replication slots (an abandoned slot never advances)
SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

-- 3. Standbys with hot_standby_feedback = on report their oldest query here
SELECT application_name, age(backend_xmin) AS xmin_age
FROM pg_stat_replication;

-- 4. Forgotten two-phase-commit transactions
SELECT gid, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

If none of these is old, the horizon is fine and the problem is vacuum itself (thresholds or throttling); see the runbook below.

Measuring bloat

Start with the cheap counters in pg_stat_user_tables:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, autovacuum_count,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

These are estimates, and they show only dead tuples not yet vacuumed. A table vacuumed an hour ago can still be mostly empty space, so measure the pages directly with pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('payments');               -- exact, reads every page

SELECT * FROM pgstattuple_approx('payments'); -- faster, uses the visibility map

SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('payments_pkey');          -- B-tree density

pgstattuple always scans the whole table (under only a read lock, so the numbers are not an instantaneous snapshot). pgstattuple_approx skips pages the visibility map marks all-visible and takes their free space from the free space map, so prefer it on large tables. A table with 30% live tuples and 65% free space is bloated even if n_dead_tup is zero. Cheap bloat-estimate queries built on catalog statistics exist; treat them as rough, since they guess row widths.

Index bloat is separate. The REINDEX docs describe a bloated index as one with many empty or nearly empty pages, which B-trees can accumulate under some access patterns, such as deleting most but not all old keys of a range in a queue-like table. A low avg_leaf_density from pgstatindex is the signal (a fresh B-tree’s leaves are filled to its fillfactor, 90% by default). The fix is a rebuild, which since PostgreSQL 12 can run online:

REINDEX INDEX CONCURRENTLY payments_created_at_idx;

REINDEX CONCURRENTLY builds a new index without blocking writes, then swaps it in. It scans the table twice, waits for transactions that might use the index, cannot run inside a transaction block, and if it fails it leaves an invalid index suffixed _ccnew, which you drop before retrying.

Removing bloat: VACUUM FULL, CLUSTER and pg_repack

Once a table is mostly empty space, plain VACUUM can’t shrink it. Your options:

• VACUUM FULL writes a fresh, compact copy of the table and its indexes. It holds ACCESS EXCLUSIVE for the whole run and needs roughly the table’s size in free disk until the old copy is released. CLUSTER does the same while also sorting rows by an index. Fine for a small table in a maintenance window; an outage for a big one.
• pg_repack creates a log table and a trigger that records changes, copies rows into a new table, builds its indexes, replays the logged changes, then swaps the files. It holds ACCESS EXCLUSIVE only briefly at the start and at the swap. It needs a primary key or a unique index on a not-null column, and free disk about twice the size of the table plus its indexes.
• pg_squeeze does the same job using logical decoding instead of a trigger, so it needs wal_level = logical and an identity index.
• TRUNCATE or dropping a partition is the cheapest of all when the data is disposable: the space goes back immediately with no vacuum. Time-partitioned tables turn “delete last year’s rows and vacuum” into DROP TABLE on one partition (table partitioning).

After any rewrite, fix the cause, or the table will bloat again.

Transaction ID wraparound

Every writing transaction gets a 32-bit transaction ID (XID), and tuples are stamped with them. XIDs are compared modulo 2³², so the space is a circle: for any XID, about 2 billion are “older” and 2 billion “newer”. A tuple stamped with XID 100 looks like the past for the next 2³¹ ≈ 2.1 billion transactions, then suddenly like the future, and invisible. The docs call that catastrophic data loss, even though the data is still on disk.

Freezing prevents it. VACUUM marks old enough tuples frozen, and frozen tuples count as older than every normal XID forever. Each table records relfrozenxid, the oldest unfrozen XID it may hold, and each database datfrozenxid, the minimum over its tables. Their age is what you monitor.

The settings, with their defaults:

• vacuum_freeze_min_age (50 million): tuples older than this are frozen when vacuum visits their page.
• vacuum_freeze_table_age (150 million): past this, a vacuum becomes aggressive and scans every page not already all-frozen, not just pages with dead tuples.
• autovacuum_freeze_max_age (200 million): past this, an anti-wraparound autovacuum is forced, even if autovacuum is disabled, and conflicting lock requests don’t interrupt it; pg_stat_activity shows it as (to prevent wraparound).
• vacuum_failsafe_age (1.6 billion): the last resort. Vacuum drops throttling, skips index cleanup and uses all of shared buffers to finish freezing.

If that fails too, warnings start 40 million transactions from wraparound (“database “mydb” must be vacuumed within … transactions”), and at 3 million left PostgreSQL refuses new XIDs: running transactions continue, but only read-only ones can start. The current docs say single-user mode is not needed: resolve old prepared transactions, end long transactions, drop stale replication slots, then run a plain database-wide VACUUM as a superuser. Not VACUUM FULL, which needs an XID, and not VACUUM FREEZE, which does extra work.

How fast can you get there? Only writing transactions consume XIDs. At 10,000 of them a second, the 200-million forced vacuum comes due every 200,000,000 ÷ 10,000 = 20,000 s, about 5.6 hours, and the whole 2.1-billion runway lasts 2,100,000,000 ÷ 10,000 = 210,000 s, about 2.4 days. A replication slot abandoned on Friday evening can stop a busy database by Monday.

Monitor both levels and alert well before 200 million turns into a billion:

SELECT datname, age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS mxid_age
FROM pg_database ORDER BY xid_age DESC;

SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;

Multixacts wrap too. When several transactions lock one row (foreign-key checks taking FOR KEY SHARE, say), xmax holds a 32-bit multixact ID listing the lockers. They have their own freeze settings (autovacuum_multixact_freeze_max_age defaults to 400 million) and the same 40-million warning and 3-million stop, but running out blocks only writes that need a new multixact.

Two outages, in their own words

Sentry, 2015. A write-heavy service hit wraparound protection and was down for most of a US working day. Per its write-up, the team let running autovacuums finish, found after nearly three hours that one massive table was still unvacuumed, and truncated it (data it could afford to lose); service was back five minutes later. Their diagnosis: autovacuum_freeze_max_age set too high, the default three workers, and too much cost delay.

Mandrill (Mailchimp), 2019. One shard of a sharded store took more writes than the others; its autovacuum fell behind or failed, and it hit the XID limit. Mailchimp’s post-mortem says XIDs had been seen at about half the limit three months earlier, with a monitoring ticket filed. Recovery vacuum estimates ran as high as 40 days; truncating two terabyte-scale tables the app could live without let it finish in about an hour.

The common thread: wraparound strikes the biggest, busiest table, where vacuum is slowest. Alert on age(datfrozenxid), and know which large tables you could truncate in an emergency.

InnoDB’s analogue: purge and the history list

InnoDB updates rows in place and keeps previous versions in undo logs (see MVCC). A DELETE only delete-marks the row; background purge threads remove delete-marked rows and index records, and free undo, once no read view needs them. Committed undo awaiting purge is the history list, the number to watch:

SHOW ENGINE INNODB STATUS;   -- TRANSACTIONS section: "History list length 20"

Per the purge documentation, it is usually below a few thousand; heavy writes or long transactions, even read-only ones like mysqldump --single-transaction, make it grow. The knobs:

• innodb_purge_threads: 1 by default with 16 or fewer logical processors, otherwise 4, up to 32. DML concentrated on one table is purged by one thread.
• innodb_max_purge_lag (0, off): when set, InnoDB delays INSERT, UPDATE and DELETE once the history list exceeds it, by (purge_lag / innodb_max_purge_lag − 0.9995) × 10000, capped by innodb_max_purge_lag_delay (in microseconds). With a limit of 1,000,000 and a history of 1,100,000, that is (1.1 − 0.9995) × 10,000 ≈ 1,005, about a millisecond per statement if read in the cap’s microseconds. It trades write latency for bounded undo.
• Undo truncation. With innodb_undo_log_truncate on (the 8.4 default) and at least two undo tablespaces, purge truncates undo tablespaces past innodb_max_undo_log_size (1 GiB), so undo space returns after a long transaction ends.

InnoDB rows carry a 6-byte transaction ID, and its documentation describes no freezing pass. At 100,000 write transactions a second, 2⁴⁸ IDs last 2⁴⁸ ÷ 100,000 ≈ 2.8 billion seconds, about 89 years, so wraparound is not the hazard it is in PostgreSQL.

Runbook: what to do when

Cheat sheet

How to say it in the interview

“PostgreSQL’s MVCC leaves old row versions in the table, so VACUUM removes the ones no snapshot can see, deletes their index entries, records the free space and sets visibility-map bits for index-only scans. It also freezes old rows, because 32-bit transaction IDs wrap after about two billion. Autovacuum fires at 50 plus 20% of a table’s rows in dead tuples, far too late for a 500-million-row table, so I set per-table scale factors around 1% and raise the cost limit. When vacuum ‘doesn’t work’, the horizon is usually pinned by a long or idle transaction, an abandoned replication slot or standby feedback. I alert on those and on age(datfrozenxid), and rebuild an already-bloated table online with pg_repack after fixing the cause.”

Why doesn’t VACUUM shrink my table? Plain VACUUM makes space reusable inside the table and returns only empty pages at the end of the file. Shrinking means rewriting: VACUUM FULL or CLUSTER under an exclusive lock, or pg_repack online. A table that settles at its live size plus one vacuum cycle of churn is healthy.

What is transaction ID wraparound and how do you prevent it? XIDs are 32 bits compared on a circle, so a row older than about 2.1 billion transactions would suddenly look like the future. VACUUM freezes old rows so they always count as past. Autovacuum forces freezing at age 200 million, and PostgreSQL stops assigning XIDs 3 million short of the limit. Never block vacuum for long, and alert on age(datfrozenxid).

Why is autovacuum not keeping up on a big table? The 20% trigger lets 100 million dead tuples pile up on a 500-million-row table; the shared cost limit caps all workers together at a few tens of MB/s; or something holds the horizon so each run removes nothing. Lower the per-table scale factor, raise the cost limit, find the holder.

How is this different in MySQL? InnoDB updates in place and keeps old versions in undo, which purge threads clean continuously; history list length is the backlog signal. Long transactions grow undo rather than the table, and 6-byte transaction IDs make wraparound a non-issue.

Does hot_standby_feedback cause bloat? It can: the primary keeps rows that standby queries still need, so replica queries aren’t cancelled, but a long one holds back cleanup on the primary.

Where to go next

• MVCC: why dead tuples exist, and how one long transaction pins the horizon.
• PostgreSQL vs MySQL: heap plus vacuum vs clustered index plus undo.
• Transaction isolation levels: the snapshots that decide what vacuum may remove.
• Serializable isolation: why long transactions also hurt SSI.
• Database indexes: B-trees, index-only scans and why index bloat matters.
• Table partitioning: dropping old partitions instead of deleting and vacuuming.
• Data replication: replication slots, standbys and feedback.
• Write-ahead logging: what vacuum’s changes cost in WAL.
• Observability: logs, metrics and traces: turning these queries into alerts.

References and further reading

1. PostgreSQL 18 documentation: Routine Vacuumingpostgresql.orgWhat VACUUM is for, space recovery vs VACUUM FULL, the autovacuum trigger formulas, freezing, the 40-million and 3-million wraparound limits and the recovery steps.
2. PostgreSQL 18 documentation: Vacuuming configurationpostgresql.orgDefaults for thresholds and scale factors, autovacuum_vacuum_max_threshold, cost-based delay, freeze ages and vacuum_failsafe_age.
3. PostgreSQL 18 documentation: VACUUMpostgresql.orgPlain vs FULL, the TRUNCATE, INDEX_CLEANUP, PARALLEL and SKIP_LOCKED options, and why plain VACUUM rarely returns space to the operating system.
4. PostgreSQL 18 documentation: Visibility Mappostgresql.orgTwo bits per heap page (all-visible, all-frozen), set only by vacuum and cleared by any change to the page.
5. PostgreSQL 18 documentation: pgstattuplepostgresql.orgExact and approximate tuple-level statistics for tables, and pgstatindex for B-tree density and fragmentation.
6. PostgreSQL 18 documentation: REINDEXpostgresql.orgRebuilding bloated indexes, the CONCURRENTLY option and its caveats, and recovering from an invalid _ccnew index.
7. pg_repack documentationreorg.github.ioOnline table rebuild using a log table and trigger, short exclusive locks at start and swap, the primary-key requirement and about twice the disk space.
8. Transaction ID Wraparound in Postgres (Sentry, 2015)blog.sentry.ioA write-heavy service that hit wraparound protection, waited hours on autovacuum, and recovered by truncating one huge table.
9. What We Learned from the Recent Mandrill Outage (Mailchimp, 2019)mailchimp.comA hot shard reached the XID limit; vacuum estimates ran to 40 days; truncating terabyte-sized tables let it finish in about an hour.
10. MySQL 8.4 Reference Manual: Purge Configurationdev.mysql.comPurge threads and batch size, history list length, innodb_max_purge_lag and its delay formula, and undo tablespace truncation.