1 Β· Reclaim dead tuples
Marks the space of dead tuples as free within the table's own pages, so future INSERT/UPDATEs reuse it. The file stays the same size β bloat is recycled, not returned to the OS.
In Postgres, an UPDATE doesn't overwrite a row and a DELETE doesn't free its space β both leave the old version behind as a dead tuple. VACUUM is the housekeeper that reclaims those corpses. Ignore it and tables bloat, queries slow, and β worst case β the database stops accepting writes to save itself. This is how VACUUM works and how to keep it ahead of you.
Postgres uses MVCC β Multi-Version Concurrency Control β so readers never block writers and writers never block readers. The trick: keep multiple versions of each row and let every transaction see the version that was valid when it started. Each row version (a tuple) carries two hidden system columns:
| Hidden column | Meaning |
|---|---|
xmin | The transaction ID that created this version (INSERT or the new half of an UPDATE). |
xmax | The transaction ID that deleted this version (DELETE or the old half of an UPDATE). Zero if still live. |
A row is visible to your transaction if its xmin is committed-and-in-the-past and its xmax is not. Crucially, an UPDATE is really a delete + insert: it stamps xmax on the old tuple and writes a brand-new one. The old version becomes a dead tuple the moment no active or future transaction could ever need it again.
SELECT started before your UPDATE must still see the old value for a consistent snapshot. Versioning is what makes that possible without locks β and dead tuples are the unavoidable exhaust of that design.A plain VACUUM scans the table and does four jobs. It does not lock the table against reads or writes, and (normally) does not shrink the file on disk.
Marks the space of dead tuples as free within the table's own pages, so future INSERT/UPDATEs reuse it. The file stays the same size β bloat is recycled, not returned to the OS.
Records how much room each page now has, so the executor knows where new rows can go without extending the file.
Flags pages where every tuple is visible to all transactions. This powers index-only scans and lets the next vacuum skip those pages entirely.
Marks very old rows as permanently visible to prevent transaction-ID wraparound (see Β§5). This is the job you can never skip.
-- Plain vacuum: reclaim & update maps, no heavy lock, no file shrink
VACUUM orders;
-- Also refresh planner statistics in the same pass (very common):
VACUUM (ANALYZE) orders;
-- Verbose, parallel across indexes, on Postgres 13+:
VACUUM (ANALYZE, VERBOSE, PARALLEL 4) orders;
VACUUM reclaims dead space; ANALYZE updates the statistics the planner uses to choose plans. Autovacuum runs both on their own schedules β and bad plans from stale stats are a top cause of "mystery" slow queries. See query performance for that side.They sound like siblings but behave nothing alike. Reaching for VACUUM FULL on a live production table is a classic outage.
VACUUM | VACUUM FULL | |
|---|---|---|
| How | Marks dead space reusable in place | Rewrites the whole table into a brand-new file, tightly packed |
| Lock | Light β reads & writes continue | ACCESS EXCLUSIVE blocks all reads & writes |
| Disk to OS | Not returned (recycled internally) | Returned β the table physically shrinks |
| Extra space | None | Needs room for a full second copy during the rewrite |
| Indexes | Cleaned in place | Rebuilt from scratch (a bonus β de-bloats them too) |
| When | Routine, constant, automatic | Rare, one-off reclaim after a massive delete |
VACUUM FULL as routine maintenance. The exclusive lock stalls your whole application, and healthy autovacuum keeps bloat in a steady state without it. If you genuinely must reclaim disk from a table that shed most of its rows, prefer pg_repack β it compacts the table online without the blocking lock.You rarely type VACUUM by hand. The autovacuum launcher wakes periodically and dispatches worker processes (up to autovacuum_max_workers, default 3) to any table that has drifted past its threshold. A table earns a vacuum when its dead tuples cross:
threshold = autovacuum_vacuum_threshold -- default 50 rows
+ autovacuum_vacuum_scale_factor -- default 0.2 (= 20%)
Γ reltuples -- estimated live rows
So a 1,000,000-row table waits for 50 + 0.20 Γ 1,000,000 = 200,050 dead tuples before autovacuum fires. That default is deliberately lax β fine for small tables, far too patient for large, hot ones. A separate pair of settings (autovacuum_analyze_threshold / _scale_factor, default 0.1) governs the automatic ANALYZE.
scale_factor per table so big tables get vacuumed more often, not less.Autovacuum deliberately throttles itself so it never floods your I/O: after doing autovacuum_vacuum_cost_limit (default 200) units of work it naps for autovacuum_vacuum_cost_delay (default 2 ms). On modern SSD-backed servers this throttle is often too conservative β if autovacuum can't keep up, raising the cost limit lets it work faster.
idle in transaction session, a stale replication slot, or an abandoned prepared transaction pins the horizon β and dead tuples pile up even though autovacuum is running constantly. Watch pg_stat_activity and age(backend_xid).Transaction IDs are 32-bit β about 4.2 billion values, then they wrap around to zero. Postgres decides visibility by asking "is this tuple's xmin in my past?" If IDs silently wrapped, ancient rows would suddenly look like they're from the future and vanish. To prevent that, VACUUM freezes old tuples β marking them "committed and visible to everyone, forever," so their exact xmin no longer matters.
| Setting | Default | Role |
|---|---|---|
vacuum_freeze_min_age | 50M | How old a tuple must be before a vacuum freezes it. |
autovacuum_freeze_max_age | 200M | Table age that forces an anti-wraparound autovacuum, even if the table is otherwise quiet. |
vacuum_freeze_table_age | 150M | When a vacuum escalates to scanning the whole table (not just changed pages) to freeze aggressively. |
You track risk with the age of a table's oldest unfrozen ID. As it climbs toward 200M, autovacuum launches a mandatory anti-wraparound pass β these cannot be skipped and will run even on tables autovacuum otherwise ignores.
-- How close is any table to wraparound? (higher age = more urgent)
SELECT relname,
age(relfrozenxid) AS xid_age,
round(100 * age(relfrozenxid) /
2000000000.0, 1) AS pct_toward_limit
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
database is not accepting commands to avoid wraparound data loss. Recovery means a single-user vacuum while the app is down. It is entirely preventable β it only happens when anti-wraparound autovacuum has been blocked (by a long transaction, a stuck worker, or someone disabling autovacuum) for a very long time. Alert on age(relfrozenxid) well before 1.5 billion.Bloat is the dead-tuple and empty-page overhead a table carries beyond its live data. Some bloat is healthy β it's reusable headroom. The problem is unbounded bloat: a table that's 4 GB on disk holding 400 MB of live rows makes every sequential scan read 10Γ the pages it needs.
-- Quick estimate: dead vs live tuples per table
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
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
For an exact (heavier) measurement, the pgstattuple extension reports the real dead-space percentage of a table or index:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders'); -- dead_tuple_percent, free_percentβ¦
REINDEX INDEX CONCURRENTLY idx_name; (Postgres 12+). This is the routine, safe way to de-bloat an index in production.Three views tell you whether vacuum is winning. Start from the aggregate, then watch a live run if one is in progress.
Start here Per-table n_dead_tup, last_autovacuum, autovacuum_count, and n_mod_since_analyze. Rising dead tuples with an old last_autovacuum means autovacuum isn't keeping up on that table.
Live run Real-time progress of any vacuum in flight β current phase, heap blocks scanned vs total, dead tuples collected. Answers "is this vacuum nearly done, and why is it slow?"
Wraparound The single most important number to alert on. Climbing steadily toward autovacuum_freeze_max_age is normal; refusing to drop after anti-wraparound vacuums means something is blocking freezing.
-- Watch a vacuum happening right now:
SELECT p.pid, t.relname, p.phase,
p.heap_blks_scanned, p.heap_blks_total,
p.num_dead_tuples
FROM pg_stat_progress_vacuum p
JOIN pg_stat_user_tables t ON t.relid = p.relid;
The defaults are one-size-fits-all. The highest-leverage change is almost always to make autovacuum more aggressive on your busiest tables β set it per table so you don't disturb everything else.
-- A hot, million-row table: vacuum at 2% dead, not 20%, and hit it hard
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.02, -- 20% β 2%
autovacuum_vacuum_threshold = 1000,
autovacuum_vacuum_cost_limit = 2000, -- let it work ~10Γ faster
autovacuum_vacuum_cost_delay = 2
);
-- Insert-only table (e.g. append-only log)? Freeze proactively so an
-- anti-wraparound vacuum never has to scan it all at once:
ALTER TABLE audit_log SET (autovacuum_freeze_min_age = 0);
| Symptom | Likely cause | Move |
|---|---|---|
| Big table, dead tuples always high | Scale-factor trap | Lower autovacuum_vacuum_scale_factor per table |
| Autovacuum runs but never finishes in time | Throttled too hard | Raise autovacuum_vacuum_cost_limit / more workers |
| Dead tuples grow despite constant vacuuming | Long-running transaction pinning the horizon | Kill idle-in-transaction; drop stale replication slots |
age(relfrozenxid) climbing, never resetting | Anti-wraparound blocked | Clear the blocker; ensure autovacuum is on |
A Heap-Only Tuple (HOT) update happens when an UPDATE changes no indexed column and the new version fits on the same page. Then Postgres skips adding index entries, and the dead old version can be cleaned by cheap "HOT pruning" on the fly β no full vacuum needed. To leave room for HOT updates on a heavily-updated table, lower its fillfactor (e.g. ALTER TABLE t SET (fillfactor = 85);) so each page keeps spare space. Fewer indexes on hot columns β more HOT updates β less bloat.
A jobs table backs a work queue: rows are INSERTed, UPDATEd through a few states, then deleted. It holds ~50,000 live rows but sits at 6 GB on disk and every poll query has gotten slow.
SELECT n_live_tup, n_dead_tup, last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'jobs';
n_live_tup | n_dead_tup | last_autovacuum | autovacuum_count
------------+------------+------------------------+------------------
51203 | 8874512 | 2026-07-30 02:14:09+00 | 3
8.8 million dead tuples against 51k live β the table is 99% dead, and autovacuum has only run 3 times. Something is stopping it from keeping up.
SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;
pid | state | xact_age | query
-------+----------------------+-----------------+---------------------
28471 | idle in transaction | 6 days 04:11:52 | BEGIN; SELECT ...
Root cause: a leaked connection has held a transaction open for six days. VACUUM can't remove any tuple newer than that snapshot β so it runs, finds nothing removable, and dead tuples accumulate regardless of settings.
-- 1. End the ancient transaction (fix the app leak too!)
SELECT pg_terminate_backend(28471);
-- 2. Now autovacuum can advance β a manual pass reclaims immediately
VACUUM (VERBOSE, ANALYZE) jobs;
-- 3. Reclaim the 6 GB of disk online (blocking-free), since the table
-- shed almost all its rows and won't grow back to 6 GB:
-- pg_repack -t jobs (or VACUUM FULL in a maintenance window)
-- Vacuum this churny table far more eagerly than the 20% default
ALTER TABLE jobs SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 500,
autovacuum_vacuum_cost_limit = 3000
);
idle_in_transaction_session_timeout so a leaked transaction can never linger for days, and make sure connections always COMMIT/ROLLBACK. See concurrency design for pooling & connection hygiene.| Question | Short answer |
|---|---|
| Why do dead tuples exist? | MVCC β UPDATE/DELETE keep the old row version for concurrent snapshots. |
| Does VACUUM free disk to the OS? | No β it recycles space in place. VACUUM FULL/pg_repack shrink the file. |
| Is VACUUM FULL safe on live tables? | No β ACCESS EXCLUSIVE lock blocks everything. Use pg_repack. |
| What triggers autovacuum? | dead tuples > threshold + scale_factor Γ rows (default 20%). |
| Big tables bloat anyway β why? | The scale-factor is a %; lower it per table. |
| Dead tuples grow despite vacuuming? | A long-running transaction is pinning the snapshot horizon. |
| What is wraparound? | 32-bit txids wrap at ~4.2B; freezing old tuples prevents it. |
| Number to alert on? | age(relfrozenxid) β well before 1.5 billion. |
| De-bloat an index safely? | REINDEX INDEX CONCURRENTLY. |
| Should I disable autovacuum? | Never. Tune it β throttle, per-table, more workers. |
Six questions. Pick an answer for each, then check your score.
The other half β EXPLAIN ANALYZE, indexes & statistics once bloat is under control.
The app side β connection pools & idle-in-transaction hygiene that keep vacuum unblocked.
Where the DB runs β replicas, scaling & the observability to alert on wraparound.
The hub β LLMs, agents, data & infra.