Table Bloat & Autovacuum — Reclaiming What MVCC Leaves Behind

Because MVCC never erases a row in place — a DELETE stamps xmax, an UPDATE writes a whole new version — every table steadily fills with dead tuples: old versions no living snapshot can see. That accumulated dead space is bloat; VACUUM is the garbage collector that makes it reusable; autovacuum is the daemon that runs VACUUM before the table drowns. The keystone trap: plain VACUUM does not return space to the OS — it makes dead space reusable by future rows. That single fact explains most "why is my table still huge after I vacuumed?" confusion.

Intermediate vacuum autovacuum table-bloat dead-tuple vacuum-full pg-repack visibility-map freezing xid-wraparound oldest-xmin anti-wraparound interview-priority Created 2026-06-17 · Updated 2026-06-30

Key Components

Dead tuple: An old row version whose deleting transaction has committed and sits below the OldestXmin horizon, so no live snapshot can ever see it again. A dead tuple is only removable when its xmax committed and xmax < OldestXmin. Accumulated dead tuples plus internal free space inside pages are what "bloat" means.
Plain VACUUM vs VACUUM FULL: Plain VACUUM marks dead space reusable and records it in the FSM under a lightweight SHARE UPDATE EXCLUSIVE lock — the file does not shrink (it can only truncate trailing empty pages). VACUUM FULL rewrites the entire table into a new file with zero dead space and hands the space back to the OS, but takes an ACCESS EXCLUSIVE lock that blocks everything and needs ~2× the disk.
Free Space Map (FSM): A per-relation fork that tracks how much free space each page has. When VACUUM reclaims dead tuples it records the freed room in the FSM so future INSERTs/UPDATEs refill those gaps instead of extending the file. This is why reused space beats file growth — and why a vacuumed file stays the same size.
Visibility map (VM) — all-visible / all-frozen: The _vm fork stores two bits per page. The all-visible bit powers index-only scans and lets normal VACUUM skip the page. The all-frozen bit lets even an aggressive / anti-wraparound VACUUM skip the page — the freeze scan is the expensive one, so this is what keeps wraparound vacuums affordable on huge cold tables.
Freezing / XID wraparound: xmin/xmax are 32-bit and compared modulo 2³² — a circle with no endpoint. If an old tuple is never handled, once its age exceeds ~2³¹ its insert flips from past to future and silently disappears. Freezing escapes the circle: VACUUM stamps sufficiently-old tuples HEAP_XMIN_FROZEN = "older than every normal XID, forever."
OldestXmin: The visibility horizon: the oldest transaction ID any snapshot could still need. VACUUM can only reclaim a dead tuple whose xmax < OldestXmin. A single long-open transaction, orphaned replication slot, or stuck prepared transaction pins this horizon low — and then VACUUM runs but reclaims nothing.
Autovacuum: A background launcher that spawns workers to run VACUUM and ANALYZE automatically. It fires when dead tuples cross a per-table threshold and also refreshes planner statistics, so tables get vacuumed before bloat runs away — without an operator pulling the trigger.

Concrete Example

Two catalog queries diagnose almost every bloat-and-wraparound problem. The first ranks tables by dead-tuple load; the second reads the wraparound "fuel gauge" for every database:

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

SELECT datname, age(datfrozenxid) FROM pg_database;  -- wraparound fuel gauge

A high n_dead_tup with a recent last_autovacuum is the classic paradox: autovacuum is running yet the table keeps bloating. The usual root cause is a pinned-low OldestXmin — a long-open transaction, an orphaned replication slot (pg_replication_slots.xmin), or a stuck prepared transaction (pg_prepared_xacts) — so every dead tuple fails the xmax < OldestXmin test and nothing is removable. A large and climbing age(datfrozenxid) is the wraparound warning long before Postgres starts refusing writes.

The second decision is which tool to reach for when a table has already bloated. The choice hinges on one question — do you need the disk back, and can you afford a lock?

-- Routine, frequent: reusable space, reads & writes keep flowing.
VACUUM events;              -- file size unchanged; FSM updated

-- Emergency only: rewrites the whole table into a new file.
VACUUM FULL events;         -- ACCESS EXCLUSIVE lock → blocks everything, ~2× disk

-- Production shrink-job: rebuilds table + indexes with only a brief lock.
pg_repack -t events -d mydb -- no outage

The doctrine: run plain VACUUM often enough to never need VACUUM FULL. When a shrink is genuinely required in production, the answer is almost always pg_repack — VACUUM FULL's exclusive lock is an outage, not a maintenance window. Tune a hot, big table to vacuum sooner with a per-table override:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);
-- default 0.1 → a 1M-row table waits ~100,050 dead tuples before autovacuum;
-- 0.02 fires it roughly 5× sooner.

Visual Model

Picture a library that never discards old editions. When a book is revised, its old edition is not burned — just stamped "withdrawn" on the spine (that is xmax; the dead tuple). VACUUM is the nightly round: the librarian pulls withdrawn editions and, instead of demolishing shelving, jots each newly empty slot on an index card at the front desk (the FSM) so tomorrow's arrivals refill the gaps. The building never shrinks — only relocating every kept book into a fresh, smaller building (VACUUM FULL) does that, and the library must lock its doors while it happens. And she can never clear a withdrawn edition early, because one scholar dozing with an hours-old ticket (the pinned OldestXmin) might still ask for it.

Which shrink tool? A heatmap comparison

The three garbage-collection tools trade off along four axes. Greener is the friendlier choice on that dimension; redder is the cost you pay. Toggle the theme to recolor.

Dimension	plain `VACUUM`	`VACUUM FULL`	`pg_repack`
Returns space to OS	No — reusable only (truncates trailing empty pages)	Yes — old file dropped	Yes — rebuilt into a fresh file
Lock level / blocking	`SHARE UPDATE EXCLUSIVE` — reads & writes proceed	`ACCESS EXCLUSIVE` — blocks everything (an outage)	brief lock only — no outage
Speed / cost	routine, cheap, frequent	full rewrite, slow, ~2× disk	online rebuild — more work, no downtime
relfilenode rewrite	unchanged — no rewrite	new file → rewrite op, needs ~2× disk	new file, but built online

What plain VACUUM actually does to a page

Step through one heap page as VACUUM cleans it. Watch that the line pointers of removed tuples become LP_UNUSED rather than disappearing, the freed room is recorded in the FSM, the visibility map bit is set — and the file still does not shrink.

Step 1 of N

Loading…

Deeper — Edge Cases & Gotchas

How plain VACUUM works, mechanically

Three blocks, each tied to a known structure:

Scan & collect dead TIDs into maintenance_work_mem. If that buffer fills, VACUUM makes multiple index passes — so under-sized memory multiplies the cost on a large table.
Clean indexes — remove every index entry pointing at a dead TID. More indexes therefore means a costlier VACUUM.
Vacuum the heap page-by-page — remove dead tuples, defragment (slide live tuples together, in-page compaction), update the FSM and visibility map, and finally truncate any trailing empty pages.

Two precise facts underpin the "why doesn't the file shrink?" answer. First, line pointers are not freed, only marked LP_UNUSED: reclaiming a slot would shift offset numbers, change live rows' TIDs, and force rewriting every index entry — so slots recycle in place, keeping TIDs stable. Second, the VM lets VACUUM skip all-visible pages (a full scan becomes targeted), and a ring buffer keeps VACUUM from evicting hot pages out of shared_buffers.

XID wraparound and the freeze deadline

The age comparison is relative, not an absolute crossing: age = current_xid − xmin (mod 2³²), flipping a tuple from "past" to "future" once the gap exceeds ~2³¹. Freezing stamps sufficiently-old tuples as older than every normal XID forever (a flag since PG 9.4; older clusters set xmin = FrozenTransactionId = 2). The machinery: pg_class.relfrozenxid (oldest unfrozen XID), vacuum_freeze_min_age (freeze threshold), and autovacuum_freeze_max_age (the hard deadline). The failure ladder is loud then fatal: at ~40M XIDs left, WARNING: must be vacuumed within N transactions; at ~3M left, ERROR: database is not accepting commands to avoid wraparound data loss — writes refused until vacuumed. A parallel multixact ID wraparound exists for shared row locks.

Anti-pattern #1: believing "VACUUM will shrink the file." It won't.

DELETE FROM events WHERE created < '2020-01-01';  -- 9M of 10M rows
VACUUM events;
-- ...and the file on disk is essentially the same size. 🤔❌

Why it's expected: VACUUM made the 9M dead tuples' space reusable and recorded it in the FSM, but the freed slots are scattered, not trailing — there are no all-empty trailing pages to truncate, so the file cannot shrink. What actually shrinks it: VACUUM FULL (rewrites the table, but ACCESS EXCLUSIVE lock = outage) or pg_repack (rebuilds table + indexes online, brief lock, no outage). Plain VACUUM did its job — it just isn't a shrink tool.

Anti-pattern #2: disabling autovacuum to "avoid the I/O" on an append-only or write-heavy table.

ALTER TABLE audit_log SET (autovacuum_enabled = false);  -- ❌ time bomb

Why it breaks: autovacuum_freeze_max_age forces an anti-wraparound autovacuum even on an append-only table and even when autovacuum is off — because freezing is not optional, it is what prevents silent data loss. Turning autovacuum off doesn't skip that work; it defers it into one giant, unavoidable, badly-timed vacuum (and risks the wraparound shutdown that refuses all writes). Tune autovacuum to run sooner and gentler instead of switching it off.

Autovacuum's trigger, precisely

A launcher spawns workers (autovacuum_max_workers, default 3) every autovacuum_naptime (10s). The vacuum threshold is:

vacuum threshold = autovacuum_vacuum_threshold (50)
                 + autovacuum_vacuum_scale_factor (0.1) × n_live_tuples

So a 1M-row table waits for ~100,050 dead tuples before it fires — which is why hot, large tables usually need a per-table scale-factor override. Autovacuum also runs ANALYZE to refresh planner statistics (histograms, n_distinct), and its I/O is throttled by autovacuum_vacuum_cost_delay/cost_limit, balanced across all workers.

Test Yourself

You DELETE 9M of 10M rows, then run plain VACUUM, and the file barely changes size. Which statement correctly explains what happened?

Autovacuum is clearly running on a table (you can see it in pg_stat_activity), yet the table keeps bloating. What is the most likely root cause?

What is XID wraparound, why is the comparison circular, what does freezing concretely do — and which parameter forces an anti-wraparound vacuum even on an append-only table with autovacuum turned off?