← All tutorials
PostgreSQL VACUUM

PostgreSQL VACUUM

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.

The one-sentence model. Postgres never edits a row in place; it writes a new version and marks the old one dead. VACUUM's job is to find dead versions no running transaction can still see, and make their space reusable β€” quietly, in the background, forever.

Foundations  MVCC & dead tuples

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 columnMeaning
xminThe transaction ID that created this version (INSERT or the new half of an UPDATE).
xmaxThe 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.

How dead tuples are created and reclaimed A live tuple is updated, which marks the old version dead and writes a new live version. Repeated updates pile up dead tuples as bloat, until VACUUM marks their space reusable. Live tuple v1xmin=100 xmax=0 UPDATE Dead v1xmax=101 Live v2xmin=101 …more Dead tuples pile up= table & index BLOATwasted pages, slower scans VACUUM 🧹 Space reusabledead tuples removed,page free space recorded VACUUM only removes versions no running transaction can still see
Why not overwrite in place? Because a long-running 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.

The tool  What VACUUM actually does

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.

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.

2 Β· Update the Free Space Map (FSM)

Records how much room each page now has, so the executor knows where new rows can go without extending the file.

3 Β· Update the Visibility Map (VM)

Flags pages where every tuple is visible to all transactions. This powers index-only scans and lets the next vacuum skip those pages entirely.

4 Β· Freeze old tuples

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 β‰  ANALYZE. 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.

Two tools, one name  VACUUM vs VACUUM FULL

They sound like siblings but behave nothing alike. Reaching for VACUUM FULL on a live production table is a classic outage.

VACUUMVACUUM FULL
HowMarks dead space reusable in placeRewrites the whole table into a brand-new file, tightly packed
LockLight β€” reads & writes continueACCESS EXCLUSIVE blocks all reads & writes
Disk to OSNot returned (recycled internally)Returned β€” the table physically shrinks
Extra spaceNoneNeeds room for a full second copy during the rewrite
IndexesCleaned in placeRebuilt from scratch (a bonus β€” de-bloats them too)
WhenRoutine, constant, automaticRare, one-off reclaim after a massive delete
Never schedule 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.

The daemon  Autovacuum β€” the part that runs itself

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.

The scale-factor trap: because the trigger is a percentage, the bigger the table the more dead rows it tolerates before cleanup β€” exactly backwards from what you want. The fix (see Β§8) is to lower 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.

Autovacuum's silent enemy: long transactions. VACUUM can only remove tuples older than the oldest running snapshot. One forgotten 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).

The scary one  Transaction-ID wraparound & freezing

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.

SettingDefaultRole
vacuum_freeze_min_age50MHow old a tuple must be before a vacuum freezes it.
autovacuum_freeze_max_age200MTable age that forces an anti-wraparound autovacuum, even if the table is otherwise quiet.
vacuum_freeze_table_age150MWhen 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;
The write-stopping cliff. If freezing falls ~2 billion transactions behind, Postgres refuses new writes: 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.

The symptom  Detecting table & index bloat

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…
Indexes bloat too β€” and often worse. B-tree indexes accumulate their own dead entries. When an index is bloated, rebuild it without a blocking lock: REINDEX INDEX CONCURRENTLY idx_name; (Postgres 12+). This is the routine, safe way to de-bloat an index in production.

Observe  Monitoring vacuum health

Three views tell you whether vacuum is winning. Start from the aggregate, then watch a live run if one is in progress.

πŸ“Š pg_stat_user_tables

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.

⏳ pg_stat_progress_vacuum

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?"

🧊 age(relfrozenxid)

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;

Practice  Tuning autovacuum

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);
SymptomLikely causeMove
Big table, dead tuples always highScale-factor trapLower autovacuum_vacuum_scale_factor per table
Autovacuum runs but never finishes in timeThrottled too hardRaise autovacuum_vacuum_cost_limit / more workers
Dead tuples grow despite constant vacuumingLong-running transaction pinning the horizonKill idle-in-transaction; drop stale replication slots
age(relfrozenxid) climbing, never resettingAnti-wraparound blockedClear the blocker; ensure autovacuum is on
Don't turn autovacuum off. The classic mistake is disabling it "to control I/O," then discovering weeks later that bloat exploded or wraparound is imminent. Tune it to run more smoothly instead β€” throttle, schedule per table, add workers. Off is never the answer.
Bonus: HOT updates & fillfactor β€” reducing dead tuples at the source

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.

End to end  Worked example: a bloating queue table

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.

Step 1 Β· Look, don't guess

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.

Step 2 Β· Diagnose the blocker

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.

Step 3 Β· Clear the horizon, then reclaim

-- 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)

Step 4 Β· Stop it recurring

-- 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
);
What actually fixed it: not a vacuum command β€” ending the six-day transaction. Bloat that "autovacuum won't clean" is almost always a pinned snapshot, not a tuning problem. Fix the horizon first; then reclaim, then tune so the churn is handled continuously.
The permanent fix is in the app: set 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.

Cheat sheet

QuestionShort 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.

Knowledge check

Six questions. Pick an answer for each, then check your score.

1. Why does an UPDATE in Postgres create a dead tuple?

2. After a plain VACUUM, why doesn't the table file shrink on disk?

3. When is VACUUM FULL a problem on a live production table?

4. A 1,000,000-row table still bloats badly under default autovacuum. The most likely reason?

5. Dead tuples keep growing even though autovacuum runs constantly. First thing to check?

6. What does "freezing" a tuple prevent?

Next steps

🐘 Query performance

The other half β€” EXPLAIN ANALYZE, indexes & statistics once bloat is under control.

🧡 Concurrency design

The app side β€” connection pools & idle-in-transaction hygiene that keep vacuum unblocked.

☸️ Cloud native

Where the DB runs β€” replicas, scaling & the observability to alert on wraparound.

πŸ““ All tutorials

The hub β€” LLMs, agents, data & infra.