What is it? #
Because an UPDATE or DELETE leaves the old row version on disk, PostgreSQL accumulates dead tuples — rows that are physically present but visible to nobody.
VACUUM finds them and marks their space reusable. ANALYZE separately updates the planner's statistics so it keeps choosing good query plans.
Autovacuum runs both automatically in the background, and on most systems it does the job without intervention. The situations where it falls behind — very large or very busy tables — are worth recognising, because the symptom is a database that grows and slows for no obvious reason.
One thing to be clear about from the start: VACUUM FULL is a different and far heavier operation than VACUUM, and it is not routine maintenance.
Think of it like this #
A warehouse where superseded boxes are marked "obsolete" rather than removed immediately, because someone might still be reading them.
VACUUM is the cleaner who walks the aisles, confirms nobody needs a marked box, and frees the space for new stock. The shelf stays; the space becomes usable again.
ANALYZE is the stock-taker who counts roughly what is on each aisle. The manager plans routes based on those counts, so if they are a year out of date, the plans are wrong.
VACUUM FULL is emptying the entire warehouse into a new building, perfectly packed. It genuinely reclaims space — and the warehouse is closed for the whole operation.
Simple example #
A sessions table is updated on every request. After a month it holds 50,000 live rows but occupies 40 GB, and queries have become slow.
The table is bloated: mostly dead tuples that autovacuum could not keep up with. Diagnosing and fixing that is this lesson.
Code #
-- ---------- Seeing dead tuples and bloat ----------
SELECT relname AS table,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
round(100.0 * n_dead_tup /
NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- dead_pct above ~20% on a large table means autovacuum is not keeping up.-- ---------- Running them manually ----------
VACUUM orders; -- reclaim dead space (does NOT block reads or writes)
ANALYZE orders; -- refresh planner statistics
VACUUM ANALYZE orders; -- both, the usual manual form
VACUUM (VERBOSE, ANALYZE) orders; -- show what it actually did
VACUUM; -- whole database---------- VACUUM vs VACUUM FULL ----------
VACUUM
- Marks dead tuple space REUSABLE BY THAT TABLE
- Does NOT return space to the operating system
- Takes only a SHARE UPDATE EXCLUSIVE lock
- READS AND WRITES CONTINUE NORMALLY
- Safe to run any time. This is routine maintenance.
VACUUM FULL
- REWRITES the entire table into a new file, perfectly packed
- DOES return space to the operating system
- Takes an ACCESS EXCLUSIVE lock
- !! BLOCKS EVERYTHING — reads, writes, everything — for the whole rewrite
- !! Needs enough free disk for a SECOND COPY of the table
- A 200 GB table may be unavailable for HOURS
VACUUM FULL IS NOT ROUTINE MAINTENANCE.
Use it only for a one-off reclaim after deleting a large proportion of a
table, during a planned maintenance window, with a verified backup.# ---------- The better alternative: pg_repack ----------
# Rebuilds a table with almost no locking — it works on a copy and swaps at
# the end, so the table stays available throughout.
sudo apt install postgresql-16-repack # package name follows your version
pg_repack -d shop -t orders --no-superuser-check
# Requires roughly double the table's disk space during the operation,
# same as VACUUM FULL — but without the outage.-- ---------- Why autovacuum falls behind: the three causes ----------
-- CAUSE 1: a long-running transaction holds an old snapshot.
-- VACUUM cannot remove any row version newer than the OLDEST open snapshot,
-- ACROSS THE WHOLE DATABASE — even on tables that transaction never touched.
SELECT pid,
now() - xact_start AS transaction_age,
state,
left(query, 60) AS query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
AND xact_start < now() - interval '5 minutes'
ORDER BY xact_start;
-- This is by far the most common cause of unexplained bloat.
-- Prevention: SET idle_in_transaction_session_timeout (see the locks lesson).-- CAUSE 2: the default threshold scales badly on large tables.
-- Autovacuum triggers when:
-- dead_tuples > autovacuum_vacuum_threshold
-- + autovacuum_vacuum_scale_factor * total_rows
-- Defaults: threshold = 50, scale_factor = 0.2 (20%)
--
-- On a 100-row table: vacuum after ~70 dead rows. Fine.
-- On a 100,000,000-row table: vacuum after ~20,000,000 dead rows.
-- That is a great deal of bloat first.
-- Fix: lower the scale factor FOR THAT TABLE.
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02); -- 2%
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.01);
-- Check what is set on a table:
SELECT reloptions FROM pg_class WHERE relname = 'orders';-- CAUSE 3: autovacuum is throttled and cannot keep up with write volume.
SHOW autovacuum_max_workers; -- default 3
SHOW autovacuum_vacuum_cost_limit; -- default 200 — the throttle
SHOW autovacuum_naptime; -- default 1min
-- On a busy server with fast disks, the default throttle is very conservative:
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000; -- allow more work
ALTER SYSTEM SET autovacuum_max_workers = 5; -- needs a RESTART
SELECT pg_reload_conf();
-- Is autovacuum even on? It must be. Never turn it off.
SHOW autovacuum; -- must be "on"-- ---------- Watching autovacuum work ----------
SELECT pid,
now() - xact_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';
-- "autovacuum: VACUUM public.orders (to prevent wraparound)"
-- ^ this form is the ANTI-WRAPAROUND vacuum. It cannot be skipped and
-- will not give up its lock easily. Do not kill it — see below.---------- Transaction ID wraparound: the one that takes databases down ----------
Every row records the transaction id that created it. Those ids are 32-bit
and eventually wrap around. PostgreSQL must "freeze" old rows before that
happens, and VACUUM is what does the freezing.
If freezing falls far enough behind, PostgreSQL issues warnings, and then
REFUSES ALL WRITES to protect the data:
ERROR: database is not accepting commands to avoid wraparound data loss
HINT: Stop the postmaster and vacuum that database in single-user mode.
Recovering from that is an outage. Monitor it and it can never happen:-- How close is each database to wraparound? (2 billion is the limit)
SELECT datname,
age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- Per table:
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
-- Below 200 million is healthy. Above ~1 billion, investigate now.
-- ALERT ON THIS. It is one of the few PostgreSQL problems that
-- genuinely stops the database.-- ---------- ANALYZE: keeping the planner honest ----------
ANALYZE orders; -- one table
ANALYZE; -- everything
-- When statistics are stale the planner makes bad choices (see the
-- query-performance lesson). Run ANALYZE manually after:
-- * a large bulk load or import
-- * a big DELETE or UPDATE
-- * a restore from backup <- easily forgotten, and very noticeable
-- More detail for a column with an uneven distribution:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; -- default is 100
ANALYZE orders;-- ---------- Index bloat ----------
-- Indexes bloat too. Rebuild without blocking:
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
REINDEX TABLE CONCURRENTLY orders;
-- Plain REINDEX (no CONCURRENTLY) locks out writes — avoid on production.How it works #
VACUUM scans a table for tuples that no open transaction can still see and marks their space free for reuse. The crucial detail is the phrase "no open transaction": VACUUM cannot clean up any row version newer than the oldest open snapshot in the entire database. A single transaction left open for hours therefore blocks cleanup everywhere, including on tables it never touched. This is why the transactions and locks lessons stress keeping transactions short, and why idle_in_transaction_session_timeout is one of the highest-value production settings.
Autovacuum decides when to act using a threshold plus a proportion of the table: roughly 50 + 20% of rows. That proportion is the problem on large tables, because 20% of a hundred million rows is twenty million dead tuples before anything happens. Lowering autovacuum_vacuum_scale_factor per table is the standard fix and is well worth applying to your largest, busiest tables.
Autovacuum is also deliberately throttled so it does not overwhelm the disks. autovacuum_vacuum_cost_limit defaults to a conservative value chosen for modest hardware; on a server with SSDs it often prevents autovacuum from keeping pace with write volume. Raising it lets autovacuum do more work per round.
Bloat is the accumulated effect. Dead tuples occupy pages, so PostgreSQL reads more pages to find the same live rows, the cache fills with dead data, and queries slow down. Disk usage grows while the logical data size does not.
VACUUM FULL fixes bloat completely by rewriting the table — and takes an ACCESS EXCLUSIVE lock for the entire rewrite, blocking every read and write, while requiring free disk space for a second copy. On a large table that is hours of downtime. pg_repack achieves the same result by building the copy online and swapping at the end, which is why it is the tool of choice in production.
Transaction ID wraparound is the failure mode that genuinely stops a database. Transaction ids are 32-bit, so PostgreSQL must "freeze" sufficiently old rows before the counter wraps. VACUUM performs that freezing. If it falls far enough behind, PostgreSQL refuses all writes to avoid losing data, and recovery requires single-user mode. It is entirely preventable by monitoring age(datfrozenxid) — and it is worth an alert, because it is one of very few PostgreSQL problems with a hard stop.
ANALYZE is unrelated to space. It samples columns and updates the statistics the planner uses to estimate row counts. Stale statistics cause bad plans, which is why the query-performance lesson recommends running it before adding indexes.
Real-world use #
Leave autovacuum enabled. The instinct to disable it "because it is causing load" is understandable and always wrong — the load simply returns later, larger, as an anti-wraparound vacuum that cannot be postponed.
Tune it per table rather than globally. A handful of large, heavily updated tables usually account for all the bloat, and lowering their scale factors solves the problem without changing behaviour everywhere else.
Monitor three things routinely: dead tuple percentage on large tables, the age of the oldest open transaction, and age(datfrozenxid). The first two catch bloat early; the third catches the one problem that stops everything.
Run ANALYZE explicitly after bulk operations. The case most often forgotten is after restoring a backup — a freshly restored database has no statistics at all, and the first hours of queries can be dramatically slow until autoanalyze catches up. Making ANALYZE the last step of any restore procedure is a small habit with a large payoff, and the restore lesson repeats it for that reason.
Treat VACUUM FULL as a scheduled, announced operation with a verified backup, or avoid it entirely in favour of pg_repack. The common trigger is a one-off deletion of most of a large table's rows; that is a legitimate reason to reclaim space, but it belongs in a maintenance window.
For tables that grow forever, consider whether partitioning would remove the problem instead. Dropping an old partition is instantaneous and frees space immediately, whereas deleting a year of rows from one enormous table creates exactly the bloat this lesson is about.
Common mistakes #
- Disabling autovacuum to reduce load, which guarantees a worse forced vacuum later.
- Running VACUUM FULL on a large production table without realising it blocks all access for hours.
- Leaving long transactions open, which prevents VACUUM from cleaning anything database-wide.
- Leaving default autovacuum scale factors on very large tables, so 20% bloat accumulates first.
- Forgetting to run ANALYZE after a restore or bulk load, leaving the planner with no statistics.
Practice #
Create a table, insert 500,000 rows, then update all of them and check n_dead_tup in pg_stat_user_tables. Run VACUUM (VERBOSE) and read what it reports. Then open a second session, begin a transaction and leave it idle; update the table again, run VACUUM, and confirm it can no longer remove the dead tuples. Close that transaction, vacuum again, and watch them disappear. Finally, check age(datfrozenxid) for your databases and note the value.