Why this lesson exists
"Everything got slower over the last weeks and the database keeps growing" - and nobody changed anything. The usual reason is that VACUUM is not keeping up: dead row versions pile up, tables and indexes bloat, queries read ever more pages, and in the worst case PostgreSQL runs out of transaction IDs and stops accepting writes to protect your data. VACUUM is the one maintenance job every PostgreSQL needs and the one an SRE is expected to understand: what it does, when autovacuum runs it, and what silently stops it from working.
What you need to know already: MVCC, dead tuples, xmin and idle-in-transaction sessions (the last lesson), pg_stat_activity, setting contexts and reload (lesson 2).
The words you need first
- VACUUM - removes dead tuples from a table (and its indexes), marks the space reusable, updates the visibility map and the free space map. It does not give disk space back to the OS (except empty pages at the very end of the table). Takes
SHARE UPDATE EXCLUSIVE: reads and writes carry on. - VACUUM FULL - rewrites the whole table into a new file and gives the space back - under an
ACCESS EXCLUSIVElock, so the table is unusable while it runs. - ANALYZE - samples a table and updates the statistics the planner uses (lesson 8).
- autovacuum - the background launcher + workers that run VACUUM and ANALYZE on tables that need it, all the time, so you do not have to.
- Bloat - space in a table or index taken by dead tuples or reusable-but-empty space.
- xmin horizon - the oldest transaction any session (or replica, or slot) may still need. Dead tuples newer than the horizon cannot be removed, however often VACUUM runs.
- Transaction ID (XID) wraparound - XIDs are 32-bit counters. To keep old rows visible forever, VACUUM freezes them before the counter wraps around; if it cannot, the server eventually refuses new transactions.
Dead tuples, live
The lab has a busy table, events. Look at its statistics:
$ sudo -u postgres psql -d orders -c "select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables where relname = 'events'"
relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | size
---------+------------+------------+-------------+-----------------+---------
events | 30000 | 25000 | | | 5112 kB
(1 row)Tens of thousands of dead tuples from two UPDATEs, and no vacuum yet. Run one by hand and read what it says:
$ sudo -u postgres psql -d orders -c "vacuum (verbose) events"
INFO: vacuuming "orders.public.events"
INFO: finished vacuuming "orders.public.events": index scans: 1
pages: 0 removed, 468 remain, 468 scanned (100.00% of total), 0 eagerly scanned
tuples: 10000 removed, 30000 remain, 0 are dead but not yet removable
removable cutoff: 826, which was 0 XIDs old when operation ended
new relfrozenxid: 768, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 468 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan needed: 421 pages from table (89.96% of total) had 19988 dead item identifiers removed
index "events_pkey": pages: 167 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.005 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 1552 hits, 0 reads, 3 dirtied
WAL usage: 1479 records, 3 full page images, 223622 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "orders.pg_toast.pg_toast_24702"
INFO: finished vacuuming "orders.pg_toast.pg_toast_24702": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 826, which was 0 XIDs old when operation ended
new relfrozenxid: 826, which is 63 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.021 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 9 hits, 1 reads, 0 dirtied
WAL usage: 2 records, 0 full page images, 394 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUMThe line to read is tuples: N removed, M remain, K are dead but not yet removable, then removable cutoff - the xmin horizon this VACUUM used. Here everything dead was removed, because no other transaction was open. The table did not shrink (pg_size_pretty would show the same size): the space is now free inside the table, reused by the next inserts and updates.
What stops VACUUM: the horizon
Now a session opens a transaction and sits on it - say a report that started, read a bit, and went to make coffee:
$ sudo -u postgres psql -d orders -qc "update events set processed = true where id % 5 = 0"
$ sudo -u postgres psql -d orders -c "vacuum (verbose) events" 2>&1 | grep -E 'tuples:|removable cutoff'
tuples: 0 removed, 36000 remain, 6000 are dead but not yet removable
removable cutoff: 826, which was 2 XIDs old when operation ended
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 826, which was 2 XIDs old when operation ended... are dead but not yet removable: the dead tuples are newer than the horizon, and the open transaction might still need to see them. VACUUM ran, did its work, and achieved nothing. Who holds the horizon:
$ sudo -u postgres psql -c "select pid, application_name, state, backend_xmin, age(backend_xmin) as xmin_age, now() - xact_start as xact_age from pg_stat_activity where backend_xmin is not null order by age(backend_xmin) desc"
pid | application_name | state | backend_xmin | xmin_age | xact_age
-------+------------------+---------------------+--------------+----------+------------
17419 | nightly-report | idle in transaction | 826 | 2 | 00:00:30.5
17428 | psql | active | 826 | 2 |
(2 rows)The four things that hold the horizon back - check all four, in this order:
| holder | where you see it | fix |
|---|---|---|
| a long or idle-in-transaction session | pg_stat_activity.backend_xmin, xact_start | end it; idle_in_transaction_session_timeout |
a forgotten prepared transaction (PREPARE TRANSACTION) | pg_prepared_xacts | COMMIT PREPARED / ROLLBACK PREPARED |
a replica with hot_standby_feedback = on running a long query | pg_stat_replication.backend_xmin | end the query on the replica |
| a replication slot (logical, or physical with feedback) | pg_replication_slots.xmin / catalog_xmin | drop the stale slot (lesson 13) |
End the report and vacuum again:
$ sudo -u postgres psql -Atc "select pg_terminate_backend(pid) from pg_stat_activity where application_name = 'nightly-report'"
t
$ sudo -u postgres psql -d orders -c "vacuum (verbose) events" 2>&1 | grep -E 'tuples:|removable cutoff'
tuples: 6000 removed, 30000 remain, 0 are dead but not yet removable
removable cutoff: 830, which was 0 XIDs old when operation ended
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 830, which was 0 XIDs old when operation endedRemoved. One open transaction on a busy system for a weekend is how "everything got slow since Friday" happens: autovacuum runs and runs, removes nothing, and every table bloats.
autovacuum
You almost never run VACUUM by hand; autovacuum does, per table, when enough has changed:
vacuum when dead tuples > autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) x rows
(capped at autovacuum_vacuum_max_threshold, new in 18: 100 million)
or inserted tuples > autovacuum_vacuum_insert_threshold (1000) + insert_scale_factor (0.2) x rows
analyze when changed tuples > autovacuum_analyze_threshold (50) + autovacuum_analyze_scale_factor (0.1) x rows$ sudo -u postgres psql -c "select name, setting, unit from pg_settings where name like 'autovacuum%' order by name"
name | setting | unit
---------------------------------------+-----------+------
autovacuum | on |
autovacuum_analyze_scale_factor | 0.1 |
autovacuum_analyze_threshold | 50 |
autovacuum_freeze_max_age | 200000000 |
autovacuum_max_workers | 3 |
autovacuum_multixact_freeze_max_age | 400000000 |
autovacuum_naptime | 60 | s
autovacuum_vacuum_cost_delay | 2 | ms
autovacuum_vacuum_cost_limit | -1 |
autovacuum_vacuum_insert_scale_factor | 0.2 |
autovacuum_vacuum_insert_threshold | 1000 |
autovacuum_vacuum_max_threshold | 100000000 |
autovacuum_vacuum_scale_factor | 0.2 |
autovacuum_vacuum_threshold | 50 |
autovacuum_work_mem | -1 | kB
autovacuum_worker_slots | 16 |
(16 rows)The launcher wakes every autovacuum_naptime (1 min) per database and starts up to autovacuum_max_workers (3) workers. Each worker throttles itself (autovacuum_vacuum_cost_limit / cost_delay) so it does not hurt the application - the defaults are gentle on purpose and often too gentle for a busy, large database.
The 20% default is the classic problem on big tables: a table with 500 million rows waits for 100 million dead tuples before autovacuum touches it (the 18 cap helps). Tune the hot tables, not the whole server:
$ sudo -u postgres psql -d orders -c "alter table events set (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000)"
ALTER TABLE
$ sudo -u postgres psql -d orders -c "select relname, reloptions from pg_class where relname = 'events'"
relname | reloptions
---------+------------------------------------------------------------------------
events | {autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=1000}
(1 row)The other dangerous reloption is autovacuum_enabled = false - someone turns it off "during a bulk load" and forgets. \d+ events shows it under Options:. And make autovacuum visible: log_autovacuum_min_duration (default 10min since 15) logs every autovacuum that takes longer; set 0 while you investigate and you will see each run with the same tuples: removed ... dead but not yet removable line.
What is running right now:
$ sudo -u postgres psql -c "select pid, datname, relid::regclass, phase, heap_blks_scanned, heap_blks_total from pg_stat_progress_vacuum"
pid | datname | relid | phase | heap_blks_scanned | heap_blks_total
-----+---------+-------+-------+-------------------+-----------------
(0 rows)(Empty when nothing is being vacuumed. Autovacuum workers also show in pg_stat_activity as backend_type = 'autovacuum worker' with a query like autovacuum: VACUUM public.events.)
Bloat and getting space back
VACUUM makes space reusable, it does not return it. A table that grew to 10 GB during a bloat incident stays 10 GB on disk after the cleanup - mostly empty pages that future rows will fill. If you need the disk back:
| tool | lock | notes |
|---|---|---|
VACUUM FULL table | ACCESS EXCLUSIVE for the whole rewrite | needs free disk for a full copy; an outage for that table |
CLUSTER table USING index | ACCESS EXCLUSIVE | same, and orders the rows |
pg_repack (extension) | brief exclusive locks only at start and end | the online way; installed on most managed services |
REINDEX INDEX CONCURRENTLY | none that blocks writes | indexes bloat too, and are often the bigger part |
Measure before you act: pg_total_relation_size vs what the rows need, or the pgstattuple extension (select * from pgstattuple('events') gives dead_tuple_percent and free_percent).
Transaction ID wraparound
Every write transaction takes a 32-bit XID. Comparing XIDs works modulo 2^32, so a row is in "the past" only for about 2.1 billion transactions; after that it would suddenly look like it is in the future and disappear. VACUUM prevents that by freezing old rows (marking them as visible to everyone). The age of the oldest unfrozen XID per database:
$ sudo -u postgres psql -c "select datname, age(datfrozenxid) as xid_age, round(100.0 * age(datfrozenxid) / 2147483647, 2) as pct_to_wraparound from pg_database order by 2 desc"
datname | xid_age | pct_to_wraparound
-----------+---------+-------------------
postgres | 89 | 0.00
template1 | 89 | 0.00
template0 | 89 | 0.00
orders | 89 | 0.00
(4 rows)What happens as the age grows:
| age | what PostgreSQL does |
|---|---|
autovacuum_freeze_max_age (200 million) | forces an anti-wraparound autovacuum on the table - even if autovacuum is off. It does not give up and is not cancelled by lock conflicts |
| 40 million XIDs left | every transaction logs WARNING: database "orders" must be vacuumed within 39985967 transactions |
| 3 million left | refuses anything that needs a new XID: ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "orders", with a HINT to vacuum that database (and to end old prepared transactions or drop stale slots) |
The fix at that point is a database-wide VACUUM (or vacuumdb --all --freeze) - and first removing whatever held the horizon, otherwise it cannot freeze. Monitor age(datfrozenxid) and alert long before 200 million is in sight; on a healthy system it saw-tooths between 50 and 200 million as anti-wraparound vacuums run.
In an interview: "Autovacuum runs all the time but the tables keep bloating - why?" - VACUUM can only remove dead tuples older than the xmin horizon; something holds it back: a long or idle-in-transaction session (backend_xmin in pg_stat_activity), a forgotten prepared transaction, a replica's hot_standby_feedback, or a stale replication slot. VACUUM VERBOSE shows it as "dead but not yet removable". Remove the holder, then let autovacuum (or a manual VACUUM) catch up; tune per-table scale factors for big hot tables.
What you can do now
- Read
n_dead_tup,last_autovacuumandVACUUM VERBOSEoutput. - Find what holds the xmin horizon (sessions, prepared transactions, replicas, slots) and remove it.
- Work out when autovacuum will run on a table and tune it per table.
- Choose between
VACUUM,VACUUM FULL,pg_repackandREINDEX CONCURRENTLY. - Read
age(datfrozenxid)and explain the wraparound warnings and the stop.