Postgres Autovacuum: Why Your Deletes Don’t Free Up Space and What to Do About I

by

Postgres Autovacuum: Why Your Deletes Don’t Free Up Space and What to Do About It

If you want the visual walkthrough, there’s a companion video that covers these internals on screen. This article stands entirely on its own—read straight through, and you’ll come away knowing how autovacuum works and what to pay attention to.

When misconfigured or ignored, autovacuum falls behind. This leads to disk space exhaustion, degraded query performance, and in extreme cases, forced database shutdowns. Understanding how this system works under the hood is necessary for maintaining any production Postgres database.

Most engineers coming from other databases assume that UPDATE writes the new value right over the old one, and DELETE physically removes the row from disk. Postgres doesn’t work that way. It never overwrites a row in place. Once you understand that, everything else—autovacuum, bloat, transaction ID wraparound—follows from the same core design: multiversion concurrency control, or MVCC.

Why Postgres Doesn't Delete Rows Immediately (MVCC)

Every row version in a Postgres table has two hidden system columns: xmin and xmax. xmin records the transaction ID (XID) that created this row version. xmax records the transaction ID that deleted it, or zero if the row is still alive.

When you run UPDATE, Postgres doesn’t touch the existing row. Instead, it inserts a brand-new row version with a higher xmin, and marks the old version’s xmax as the current transaction’s XID. The old row becomes a dead tuple. DELETE simply sets xmax on the target row to the deleting transaction’s XID—again, no physical removal.

Why this indirection? Because your SELECT might have started before that UPDATE committed, and it needs to see the database as it existed at the snapshot when the query began. Each query uses a snapshot that determines which XIDs are visible. If a row version’s xmin is committed and less than the snapshot’s XID, and its xmax is either zero or was committed after the snapshot, the row is visible. By keeping the dead version around, Postgres can show the correct data to older snapshots without complicated locking. Once all transactions that could possibly see a dead tuple have finished, the tuple can finally be cleaned up—and that’s where autovacuum comes in.

A quick worked example: Suppose XID 500 inserts a row with xmin=500, xmax=0. XID 501 starts a long-running query. XID 502 updates that row. Postgres creates a new version with xmin=502, xmax=0 and sets xmax=502 on the original. Transaction 501 is still running; its snapshot predates XID 502, so it must see the original row. The original is dead (its xmax is set) but cannot be removed until 501 finishes. If 501 stays open for hours, that dead tuple hangs around, and any dead tuples pointing to the same rows that would normally be cleaned will pile up.

What Autovacuum Actually Does: Four Jobs

Autovacuum isn’t one thing. It’s four distinct maintenance duties that all share the same worker infrastructure.

  1. Reclaim dead tuple space.
    When autovacuum runs on a table, it scans the pages and removes dead tuples whose xmax is older than the oldest running transaction’s XID (the xmin horizon). The space those dead tuples occupied becomes free inside the data page. It’s not returned to the operating system unless the freed pages happen to be at the physical end of the table. Instead, that space gets reused by future INSERTs and UPDATEs in that same table, which prevents the table file from growing indefinitely.

  2. Update planner statistics (ANALYZE).
    The query planner relies on statistics about column values and row counts. Autovacuum triggers an ANALYZE when a table crosses its own change thresholds. Without fresh statistics, plan choices degrade: the planner might underestimate row counts and pick a nested loop where a hash join would be faster.

  3. Freeze old transaction IDs to prevent wraparound.
    This one is non-negotiable. XID space is only 32 bits (about 4 billion values), and Postgres treats XIDs older than 2 billion as “in the future,” causing data to silently vanish. Autovacuum freezes old row versions—marks them with a special “frozen” XID that is always visible—so wraparound never actually hits the unsafe zone. More on this below.

  4. Update the visibility map.
    A visibility map bit tells the system whether all tuples on a data page are visible to all current and future transactions. When autovacuum cleans a page, it updates this map. Index-only scans then use the map to decide whether they can return results straight from the index without visiting the heap, which can cut random I/O dramatically on large tables.

Transaction ID Wraparound: The Failure Mode That Shuts Your Database Down

The 32-bit XID counter wraps after 4,294,967,295. Postgres interprets any XID roughly 2 billion behind the current XID as belonging to the future, because half the XID space is considered “past.” If the oldest unfrozen XID in any database gets too far behind, the next XID allocation could wrap around and make that oldest tuple appear to live in the future—rendering it invisible. To prevent actual data corruption, Postgres forces a shutdown: it refuses all new transactions when the oldest XID age exceeds 2 billion minus 10 million.

Autovacuum prevents this by scanning tables whose oldest unfrozen XID age approaches autovacuum_freeze_max_age (default 200 million). It freezes old tuples by setting their xmin to the special FrozenTransactionId (XID 2), which is always visible regardless of wraparound math. This shrinks the age back to a safe range.

You can check the current freeze age with a query against pg_database:

SELECT datname, age(datfrozenxid) AS freeze_age
FROM pg_database
ORDER BY freeze_age DESC;

When freeze_age climbs past 200 million, autovacuum kicks off an anti-wraparound vacuum on its own—even if the normal dead-tuple thresholds haven’t been met. If you see values approaching 1 billion, you have a serious problem. Past 2,147,483,647 (2^31 – 1) minus 10 million, Postgres will shut down and require a manual VACUUM FREEZE in single-user mode to recover.

How Autovacuum Decides When to Run

The autovacuum launcher wakes up every autovacuum_naptime seconds (default 60). It checks the statistics collector for tables that exceed either the vacuum threshold or the analyze threshold.

For vacuum, the decision formula is:

dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

reltuples is the approximate number of live rows from pg_class. The defaults are autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2. That means a small table with 1,000 rows needs only 50 + (0.2 * 1000) = 250 dead tuples to trigger a vacuum. But a 10-million-row table needs:

50 + 0.2 * 10,000,000 = 2,000,050 dead tuples

That’s 2 million dead tuples—20% of the table—before autovacuum so much as looks at it. By the time you hit that number, the table is bloated, and a single vacuum run can take a while.

The analyze threshold works the same way, with autovacuum_analyze_threshold (default 50) and autovacuum_analyze_scale_factor (default 0.1). A table triggers an autoanalyze when the number of rows changed (inserts, updates, deletes) crosses:

autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Vacuum freezing has its own separate trigger based on the age(relfrozenxid) of a table vs. autovacuum_freeze_max_age and the vacuum_freeze_min_age settings; that mechanism forces a vacuum regardless of dead tuple count to avoid wraparound.

Checking Autovacuum Health with pg_stat_user_tables

You can see directly whether autovacuum is keeping up by querying the view pg_stat_user_tables. This query gives you dead tuple ratios and the last-run timestamps for both vacuum and analyze:

SELECT
  schemaname || '.' || relname AS table_name,
  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,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
  • n_live_tup — approximate number of live rows.
  • n_dead_tup — approximate number of dead rows awaiting cleanup.
  • last_autovacuum — timestamp of the most recent

Leave a Comment