The Hidden Mess Underneath Every Postgres Table
Every time you run an UPDATE or DELETE in Postgres, something a little strange happens. The old version of that row doesn't disappear. It just sits there, invisible to new queries, taking up space on disk like a houseguest who never got the memo that the party ended. These leftover row versions are called dead tuples — and if nobody cleans them up, your tables will slowly bloat into slow, disk-hungry monsters.
This isn't a bug. It's actually a deliberate design decision tied to one of Postgres's most powerful features: MVCC, or Multi-Version Concurrency Control. The idea is that instead of locking a row when you update it, Postgres writes a brand new version of that row and marks the old one as expired. Readers see the old version until their transaction is done. Writers move forward without blocking readers. It's elegant. But it leaves a trail of dead rows that someone has to clean up eventually.
That someone is VACUUM. I've spent a lot of time staring at bloated tables and autovacuum logs trying to understand why things go wrong, and the more I dug in, the more I realized that VACUUM is one of the most misunderstood and underappreciated pieces of the entire Postgres engine. It's not just a cleanup tool — it's fundamental infrastructure that determines whether your database breathes or suffocates.
What Exactly Is a Dead Tuple?
Let's make this concrete. Imagine a row in a users table. That row lives on a physical disk page — a fixed 8KB chunk of storage. Every row on that page has a header with two hidden system columns: xmin and xmax. Think of xmin as a birth certificate (the transaction ID that created this row) and xmax as a death certificate (the transaction ID that deleted or superseded it).
When you run UPDATE users SET email = 'new@example.com' WHERE id = 42, Postgres doesn't overwrite anything. It writes a fresh row version somewhere on a disk page with a new xmin, and it fills in the xmax of the old row to mark it as dead. That old row is now a dead tuple. It still occupies space on the page. It still shows up when Postgres scans that page, even though no query will ever return it as a result.
The same thing happens with DELETE. You issue the command, Postgres stamps the xmax, and that row becomes a ghost. The table's page count doesn't shrink. The row is just invisible, floating in storage limbo. A useful analogy: it's like crossing words out in a physical notebook rather than erasing them. The page fills up with crossed-out content, and eventually you run out of room even though most of what's there is garbage.
VACUUM's Main Job: Reclaiming Dead Rows
VACUUM's primary mission is straightforward: scan through table pages, identify dead tuples that no running transaction could possibly need anymore, and mark that space as reusable. The key phrase there is no running transaction could possibly need. VACUUM can't just delete anything with an xmax — it has to be careful that no active transaction is still reading an old snapshot that includes that dead row.
This is where the visibility map and transaction ID horizon come in. Before VACUUM declares a tuple truly dead, it checks whether the tuple's xmax is older than the oldest active transaction ID in the system (called xmin horizon). If every active transaction started after this row was deleted, nobody can see it anymore. It's safe to reclaim. If some long-running transaction started before the deletion, VACUUM has to leave that dead tuple in place. This is exactly why long-running transactions are so dangerous for table bloat — they push back the horizon and prevent cleanup.
When VACUUM does confirm a tuple is dead, it doesn't immediately hand that space back to the operating system. Instead, it updates the Free Space Map (FSM) — a lightweight data structure that tracks which pages have available space — so future INSERT operations can reuse that space. The page itself stays in the file, but Postgres knows it can write new rows there. This is regular VACUUM behavior. Actually shrinking the file requires VACUUM FULL, which is a much heavier operation that rewrites the entire table.
Autovacuum: The Daemon That Does the Work
You rarely need to run VACUUM manually in a production database. Postgres ships with autovacuum, a background daemon that monitors tables and triggers VACUUM runs automatically based on configurable thresholds. It's one of those systems that quietly does its job and only gets attention when something goes wrong.
The default autovacuum trigger logic is simple but important to understand. A VACUUM run kicks off on a table when the number of dead tuples exceeds autovacuum_vacuum_threshold (default: 50) plus autovacuum_vacuum_scale_factor (default: 0.2) multiplied by the total row count. For a table with 100,000 rows, that means autovacuum fires after roughly 20,050 dead tuples accumulate. You can check how close any table is to triggering by querying pg_stat_user_tables.
SELECT
schemaname,
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;Run that query and you'll immediately see which tables are accumulating the most garbage. I've found this to be the single most useful starting point when investigating performance problems on a Postgres instance. Tables with large n_dead_tup values and a last_autovacuum timestamp from hours ago are a warning sign that autovacuum can't keep up.
When Autovacuum Can't Keep Up
Autovacuum isn't infinitely fast or aggressive by default. It intentionally runs with throttling to avoid hammering disk I/O and impacting production workloads. This is controlled by autovacuum_vacuum_cost_delay and related cost parameters — essentially, autovacuum pauses periodically to give other processes a chance to breathe.
The problem is that on high-write tables, dead tuples can accumulate faster than autovacuum can chew through them. The daemon finishes one pass, the table already has thousands of new dead tuples by the time it starts the next pass, and you're on a treadmill going backwards. I've seen this happen on tables that receive heavy UPDATE traffic — analytics event tables, order status tables, anything with frequent state changes. The symptoms are subtle at first: slightly slower sequential scans, marginally higher I/O — then one day queries that used to take 50ms are taking two seconds.
The fix is usually to tune autovacuum more aggressively for those specific tables. Postgres lets you set storage parameters per-table, which is fantastic. You don't have to change global settings and affect every table in the database.
ALTER TABLE orders
SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2,
autovacuum_vacuum_threshold = 100
);This tells autovacuum to trigger on the orders table when just 1% of rows are dead (instead of the default 20%), and to pause for only 2 milliseconds between cost windows instead of the default 20ms. That makes autovacuum much more responsive on this specific table without touching anything else. It's a surgical approach, and it works well.
The Visibility Map and Index Vacuuming
Regular VACUUM does more than just clean up dead tuples on heap pages. It also maintains the visibility map — a compact bitmap that tracks which pages contain only tuples visible to all current and future transactions. Pages marked as "all-visible" can be skipped entirely during future VACUUM runs, which makes subsequent passes dramatically faster.
This visibility map matters for another reason: Index Only Scans. When Postgres executes an index only scan (where all needed columns are in the index), it still needs to verify tuple visibility. Without the visibility map, it would have to fetch the actual heap page for every row just to check if it's visible. With the visibility map, it can skip that check for pages marked all-visible. A well-maintained table with a healthy visibility map can serve index-only scans with almost zero heap I/O. A bloated, poorly vacuumed table kills that optimization entirely.
Index vacuuming is also part of a regular VACUUM run. Indexes in Postgres contain pointers to heap tuple locations, including pointers to dead tuples. If you only clean the heap, indexes still hold references to dead rows. VACUUM walks the indexes and removes those stale pointers, which keeps index scans efficient and prevents index bloat from compounding the heap bloat problem. This is one reason VACUUM runs can take a while on large tables with many indexes — it's doing real work across multiple data structures simultaneously.
Transaction ID Wraparound: The Catastrophic Edge Case
There's a darker reason VACUUM exists beyond just cleaning up space, and it's one that can cause genuine database emergencies if ignored. Postgres uses 32-bit transaction IDs, which means there are about 2.1 billion possible IDs before they wrap around. The system uses modular arithmetic to determine which transactions are "in the past" versus "in the future" relative to any given point.
If transaction IDs wrap around without proper management, old live tuples could suddenly appear to be in the "future" and become invisible to the database. That's catastrophic. Postgres would refuse to let you write new transactions and display a terrifying message about needing VACUUM. This is actually a well-documented failure mode, and VACUUM is the mechanism that prevents it.
VACUUM performs a special operation called freezing. When a tuple's xmin is old enough — controlled by vacuum_freeze_min_age — VACUUM replaces the actual transaction ID in that tuple's header with a special frozen transaction ID that is considered older than everything. Frozen tuples are safe from wraparound forever. You can monitor how close any database is to the wraparound danger zone with a query like this:
SELECT
datname,
age(datfrozenxid) AS xid_age,
2147483647 - age(datfrozenxid) AS remaining_transactions
FROM pg_database
ORDER BY xid_age DESC;The age() function returns how many transactions have happened since the database's oldest unfrozen transaction ID. When that age gets close to 200 million, Postgres will start issuing warnings. When it gets to around 1.6 billion, the server will stop accepting writes entirely until you run VACUUM FREEZE. I've seen teams get blindsided by this in production because they disabled autovacuum on some tables for performance reasons without understanding the wraparound consequences. Never do that.
Diagnosing and Responding to Bloat
Understanding VACUUM internals is only useful if you can translate it into action when things go wrong. The pgstattuple extension is your best friend for getting precise measurements of actual heap bloat versus the rougher estimates in pg_stat_user_tables.
-- Install if not already available
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- Get detailed bloat information
SELECT
table_len,
tuple_count,
tuple_len,
dead_tuple_count,
dead_tuple_len,
free_space,
round((dead_tuple_len::numeric / table_len) * 100, 2) AS dead_pct,
round((free_space::numeric / table_len) * 100, 2) AS free_pct
FROM pgstattuple('orders');If dead_pct is above 10-15% on a frequently accessed table, you have a bloat problem worth addressing. A high free_pct after a recent VACUUM run is actually good — it means space has been reclaimed and is available for reuse. What you want to avoid is a combination of high dead percentage and low free percentage, which means neither cleanup nor reclamation is keeping up.
In my experience, most bloat problems trace back to one of three causes: a long-running transaction blocking the VACUUM horizon, autovacuum thresholds that are too conservative for the table's write rate, or a missing index causing autovacuum itself to do expensive sequential scans while vacuuming. That last one is subtle but real — if VACUUM has to scan a massive table to find a relatively small number of dead tuples, it can be slow enough to fall perpetually behind.
Keeping the Engine Running
VACUUM is infrastructure, not maintenance. It's not something you do occasionally when things slow down — it's a continuous process that needs to be healthy at all times for Postgres to operate correctly. The tables that breathe are the ones with autovacuum properly tuned, transaction durations kept short, and visibility maps that are current enough to support fast index-only scans.
The biggest mental shift for most engineers is moving from thinking "VACUUM cleans up dead rows" to thinking "VACUUM is the heartbeat of Postgres storage." When it runs well, you never think about it. When it falls behind, almost everything suffers in ways that look like unrelated performance problems. Slow queries, bloated indexes, weird I/O patterns, creeping disk usage — a huge proportion of Postgres performance issues I've investigated trace back, at least partially, to something going wrong in the vacuum pipeline.
Dead tuples don't bury themselves. But with the right configuration and understanding, you can build a system where the cleanup happens so reliably and efficiently that you genuinely never have to think about it. That's the goal, and it's completely achievable once you understand what's actually happening underneath.