# PostgreSQL VACUUM: Why It Exists (and What Happens When It Falls Behind) If a table's disk size keeps climbing even though the row count hasn't — and queries against it have gotten slower for no reason you can point to — you're looking at the same root cause almost every time: **MVCC left dead row versions behind, and VACUUM hasn't caught up.** Here's exactly what's happening, and what to do about it. ## The problem: Postgres doesn't update rows in place Postgres uses **MVCC** (Multi-Version Concurrency Control) so readers never block writers and writers never block readers. The mechanism behind that: an `UPDATE` never modifies a row in place. It writes a brand-new row version and marks the old one dead. A `DELETE` doesn't remove anything immediately either — it just marks the row dead. You can see this directly with the hidden system columns `xmin` and `xmax`: ```sql -- before SELECT xmin, xmax, id, balance FROM accounts WHERE id = 1; -- xmin | xmax | id | balance -- ------+------+----+--------- -- 104 | 0 | 1 | 500 UPDATE accounts SET balance = balance - 50 WHERE id = 1; -- after SELECT xmin, xmax, id, balance FROM accounts WHERE id = 1; -- xmin | xmax | id | balance -- ------+------+----+--------- -- 118 | 0 | 1 | 450 ``` The row you see now has a new `xmin` (the transaction that created it). The *old* version — `xmin=104`, `balance=500` — still physically exists on disk with `xmax=118` stamped on it, marking it dead as of transaction 118. Any transaction that started before 118 can still see it; that's the whole point of MVCC. Nobody deleted it. It's just sitting there. ## What "dead" actually means on disk A dead tuple isn't reclaimed the moment it becomes invisible. It stays in its heap page, taking up the same physical space it always did, until something scans that page and decides it's safe to reuse. Until that happens: * The table's on-disk size includes every dead tuple, not just live rows. * A sequential scan has to read past dead tuples to get to live ones — more pages read for the same result set. * Indexes pointing at those dead tuples carry the same weight (index bloat is the same problem, one layer up). This is the tradeoff MVCC makes: lock-free reads and writes, in exchange for a cleanup bill that has to get paid eventually. ## What VACUUM actually does `VACUUM` is that cleanup. Run against a table, it: 1. **Scans heap pages** for tuples that are dead and no longer visible to *any* running transaction. 2. **Marks that space reusable** — future `INSERT`s and `UPDATE`s can write into it. Note: this does not shrink the file on disk or return space to the OS; it just makes the space available for Postgres to reuse internally. 3. **Updates the visibility map**, which is what lets Postgres skip pages entirely during an index-only scan — this is a big part of why bloated tables also break index-only scan performance. 4. **Freezes old tuples** — stamps them so their visibility no longer depends on a specific transaction ID, which is the mechanism behind the wraparound protection covered below. `VACUUM ANALYZE` does the same thing plus refreshes the planner statistics the query planner relies on — worth knowing because stale statistics on a heavily-written table cause bad plans independent of bloat. ## VACUUM vs. VACUUM FULL vs. autovacuum These three get confused constantly. They are not interchangeable: | | `VACUUM` | `VACUUM FULL` | autovacuum | | --- | --- | --- | --- | | Reclaims space to the OS | No — marks it reusable internally | Yes — rewrites the entire table | No | | Locks the table | No — runs concurrently with reads/writes | Yes — `ACCESS EXCLUSIVE`, blocks everything | No | | Triggered | Manually | Manually | Automatically, based on dead-tuple thresholds | | When to use it | Rarely needed manually — this is autovacuum's job | Only after severe bloat, in a maintenance window | Should be on, essentially always | `VACUUM FULL` looks tempting when a table is visibly bloated, but it takes an exclusive lock and physically rewrites the table — on a production table of any real size, that's real downtime. It's a last resort, not routine maintenance. ## Where autovacuum actually falls behind Autovacuum triggers per table once dead tuples cross a threshold (`autovacuum_vacuum_scale_factor`, default 20% of the table's rows, plus `autovacuum_vacuum_threshold`, default 50 rows). Two situations reliably break this: * **High-churn tables at scale.** On a 50-million-row table, a 20% threshold means 10 million dead tuples accumulate before autovacuum even starts. For write-heavy tables, the default is simply too loose. * **Long-running transactions.** This is the one that catches people off guard: a transaction that's been open for an hour can hold back vacuum on *every* table in the database, not just the one it's touching — because Postgres can't be sure that old transaction won't still need to see those "dead" rows. A forgotten `BEGIN` in a debugging session or a long batch job is enough to stall cleanup database-wide. ## What it costs you when vacuum lags Three consequences, in order of how quickly you'll notice them: * **Table and index bloat.** Disk usage climbs with no corresponding growth in actual data. * **Slower scans.** Sequential and index scans alike read through more dead weight to reach live rows. * **Transaction ID wraparound risk.** Postgres transaction IDs are a 32-bit counter. If a table goes too long without being frozen, its transaction ID "age" approaches the wraparound limit — and Postgres's protection against that is to force the database into a read-only, superuser-only state until an emergency `VACUUM FREEZE` runs. This is the failure mode that turns a maintenance oversight into an incident. ## Diagnosing it A few queries answer "is this actually a problem right now": ```sql -- dead tuples per table, and when autovacuum last ran SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20; -- transaction ID age per database — the wraparound risk gauge SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC; -- vacuum runs currently in progress SELECT * FROM pg_stat_progress_vacuum; -- open transactions that could be blocking cleanup SELECT pid, now() - xact_start AS duration, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY duration DESC; ``` An `xid_age` climbing steadily with no plateau, or a `n_dead_tup` that's a large fraction of `n_live_tup` on a table you query often, is the signal to act before it becomes an incident. ## Tuning autovacuum for tables that actually need it The database-wide defaults are conservative on purpose. For specific high-write tables, override them per table rather than globally: ```sql ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.02, -- trigger at 2% dead, not 20% autovacuum_vacuum_cost_limit = 2000, -- let it work faster before throttling autovacuum_vacuum_cost_delay = 2 -- ms delay between cost-limited work cycles ); ``` Lowering `scale_factor` makes autovacuum trigger sooner and more often — smaller, more frequent cleanups instead of one enormous one. Raising `cost_limit` (and/or dropping `cost_delay`) lets each vacuum run do more work before autovacuum throttles itself, which matters once a table is big enough that the defaults can't keep pace with its write rate. ## Final thoughts VACUUM isn't an optional maintenance chore — it's the other half of MVCC. Postgres gives you lock-free concurrent reads and writes by leaving old row versions in place instead of blocking on them; VACUUM is what makes that trade solvent. Leave autovacuum on defaults for most tables, override the threshold and cost settings for your genuinely high-write ones, and keep an eye on long-running transactions — they're the quiet way vacuum lag turns into a wraparound incident nobody saw coming. For the full parameter list and defaults, the [PostgreSQL documentation on routine vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html) is the source of truth.