skip to content

Vacuum & Freezing

Because PostgreSQL leaves dead row versions behind, VACUUM must reclaim them, freeze old XIDs before wraparound, and rewrite bloated tables. It is the most PostgreSQL-specific interview topic.

part ofPostgreSQLread it →
on this pageshow

explore

questions

16

In PostgreSQL, what does a plain VACUUM do with dead tuples, and why does the table file usually stay the same size afterwards?

level: juniorimportance: must knowfreq 78%

answer

  1. space reused, not returned
  2. free space map fork
  3. visibility map bits
  4. only trailing empty pages shrink

basics

~20 s

Plain VACUUM removes dead tuples no transaction can still see, records the freed room in the free space map for new rows, and sets visibility map bits. It returns disk space only when empty pages sit at the table's end.

solid answer

~50 s

A plain `VACUUM` scans the heap, removes tuple versions that no transaction can still see, deletes the index entries pointing at them, and records each page's free space in the table's **free space map** (the `_fsm` fork) so later `INSERT`s and `UPDATE`s reuse it. It also sets **visibility map** bits (the `_vm` fork) on pages whose tuples are all visible, so the next vacuum can skip them. It takes only a `SHARE UPDATE EXCLUSIVE` lock, so reads and writes keep running. The file shrinks only when whole empty pages sit at the end of the table and vacuum can briefly get an `ACCESS EXCLUSIVE` lock to truncate them; otherwise the freed space stays inside the file for reuse. Handing the middle of the file back to the operating system needs a rewrite such as `VACUUM FULL`.

code

sql · 8 lines
sql
-- user_sessions: a constantly updated table in a social app
VACUUM (VERBOSE) user_sessions;

-- The heap file keeps its size; freed space is reused by later writes
SELECT pg_size_pretty(pg_relation_size('user_sessions')) AS heap_size;

-- Skip the tail truncation (and its brief ACCESS EXCLUSIVE lock) for this table
ALTER TABLE user_sessions SET (vacuum_truncate = false);

go deeper

for a junior

Recall that plain VACUUM makes dead tuples' space reusable inside the table, records it in the free space map, and lets reads and writes continue.

for a middle

Explain the pass: heap scan that skips all-visible pages, index cleanup it may skip when there is little to do, free space map and visibility map updates, and the tail truncation needing a brief ACCESS EXCLUSIVE lock.

for a senior

Show the steady-state model: a table is sized by its peak between vacuums, so you vacuum more often to cap growth and choose a rewrite only when the space must go back and its lock and disk cost fit.

for a principal

Treat vacuum as a continuous cost of keeping row versions in the heap: it needs I/O headroom and monitoring like any workload, not an occasional manual chore.

## Why dead tuples exist In PostgreSQL an `UPDATE` does not overwrite a row in place. It writes a **new tuple version** into the heap and marks the old version as deleted by the updating transaction; a `DELETE` only marks the tuple. The old version must stay on its page while any snapshot might still need to read it. Once no transaction can see it any more, it is a **dead tuple**: it still occupies bytes on the page and index entries still point at it, but nothing will ever read it. Something has to turn that space back into usable room, and in PostgreSQL that is `VACUUM`, run by hand or by the **autovacuum** daemon. ## What one plain VACUUM pass does A plain `VACUUM` (without `FULL`) works on the table in place: 1. **Scan the heap.** It reads the table's pages, skipping pages the visibility map already marks all-visible, and collects the item identifiers of tuples that are dead to every snapshot. 2. **Clean the indexes.** For each index it removes the entries that point at those dead tuples. With the default `INDEX_CLEANUP AUTO` it may skip this step when it found very few dead tuples. If the collected list fills its memory budget, it runs an index pass early and then keeps scanning, so a big table can need several index passes. 3. **Clean the heap pages.** It marks the dead line pointers unused, so the space on each page can take new tuples. 4. **Record free space and visibility.** It updates the free space map and sets visibility map bits on pages that now hold only all-visible tuples. 5. **Try to truncate.** If whole empty pages sit at the end of the file, it tries to cut them off. 6. **Refresh `pg_class`.** It updates `relpages` and `reltuples`, which both the planner and autovacuum's trigger read. `VACUUM` cannot run inside a transaction block: it works through each table in its own transaction and commits as it goes. ## The two side files it maintains Every table has extra **relation forks** stored next to its main data file, named after the filenode plus a suffix. | Fork | File suffix | What it records | Who reads it | |---|---|---|---| | Free space map | `_fsm` | about how much free space each page has, one byte per page, arranged as a tree | `INSERT` and `UPDATE` looking for a page with room | | Visibility map | `_vm` | two bits per heap page: all-visible and all-frozen | the next `VACUUM`, index-only scans, anti-wraparound vacuums | The **free space map** is why vacuuming controls growth: without it, new tuples would be appended at the end of the file even though earlier pages had room. The **visibility map** is why vacuuming gets cheaper over time: a page whose all-visible bit is set has nothing to clean, so vacuum skips it, and any change to the page clears the bit again. How index-only scans use that bit belongs to the B-tree indexing topic, and the all-frozen bit belongs to XID wraparound. ## Why the file usually does not shrink The space vacuum frees is **inside** existing pages, scattered across the file. Plain `VACUUM` never moves live tuples to compact them, so the file keeps its length and the freed space waits in the free space map for the next writes. A table that is vacuumed often enough settles at a **steady state**: its minimum size plus whatever gets used up between two vacuums. The exception is the tail of the file. If the last pages of the table are completely empty, vacuum tries to take an `ACCESS EXCLUSIVE` lock for a moment and truncates them, returning that space to the operating system. If it cannot get the lock quickly it gives up and leaves the pages in place. The step is governed by: - the `TRUNCATE` option of the `VACUUM` command; - the `vacuum_truncate` storage parameter on a table; - from PostgreSQL 18, a server setting also called `vacuum_truncate` (default `on`). Turning truncation off avoids that brief exclusive lock at the cost of never handing the tail back. If you need space back from the middle of the file, plain vacuum will not do it. That takes a rewrite — `VACUUM FULL`, `CLUSTER` or an online tool such as pg_repack — each with its own locking and disk cost, which is the bloat-and-rewrite topic. ## How it coexists with traffic - Plain `VACUUM` holds a `SHARE UPDATE EXCLUSIVE` lock on the table, so `SELECT`, `INSERT`, `UPDATE` and `DELETE` keep running. - That lock conflicts with itself, so two vacuums of one table never run at once; it also conflicts with `ANALYZE`, `CREATE INDEX CONCURRENTLY` and many `ALTER TABLE` forms. - It costs I/O and generates WAL, which is why autovacuum is throttled by cost-based delay. - It can remove only tuples that are dead to **every** snapshot; an old open transaction makes it report them as dead but not yet removable. ## The interview version VACUUM makes dead space **reusable**, not **returned**. Autovacuum never runs `VACUUM FULL`; its aim is to vacuum often enough that a table stops growing. A table that is vacuumed too rarely grows to its peak and stays that size until something rewrites it.

  • Why does PostgreSQL refuse to run VACUUM inside a BEGIN ... COMMIT block?
    `VACUUM` processes each table in its own transaction and commits as it goes, so it cannot nest inside a caller's transaction; PostgreSQL raises an error instead. Migration tools that wrap every statement in a transaction therefore need a non-transactional step to run it.
  • After a VACUUM, why can a newly inserted row land on a page in the middle of the table instead of at its end?
    The insert asks the free space map for a page with enough room. Vacuum recorded the space it freed there, so the new tuple goes into an old page that has a gap. Only when no page has room is the file extended at the end.
  • When would you turn off vacuum truncation for a table?
    When the brief `ACCESS EXCLUSIVE` lock vacuum takes to cut empty tail pages disrupts traffic. Set the `vacuum_truncate` storage parameter to false on that table (PostgreSQL 18 also has a server-wide `vacuum_truncate`). The cost is that empty tail pages are never handed back to the operating system.

Plain VACUUM is a car park attendant who tows abandoned cars and updates the vacancy board at the gate: new arrivals take the freed bays, but the car park only gets smaller if the last rows at the far end are completely empty.

  • VACUUM returns the freed space to the operating system as soon as it finishes.
  • Plain VACUUM locks the table, so writes have to stop while it runs.
  • VACUUM compacts the live rows toward the start of the file.
  • Autovacuum switches to VACUUM FULL when a table gets badly bloated.
  • Wrapping VACUUM in a transaction lets you roll it back if it goes wrong.
open as a page

In PostgreSQL, what does VACUUM FULL do that plain VACUUM does not, and what does it cost while it runs?

level: juniorimportance: must knowfreq 72%

basics

~20 s

Plain VACUUM makes dead-row space reusable inside the table's existing files while reads and writes continue. VACUUM FULL writes a compacted new copy of the table and rebuilds its indexes, returning space to the OS, but blocks all access and needs extra disk.

open as a page

In PostgreSQL, why do 32-bit transaction IDs make freezing necessary, and what does VACUUM's freezing actually change on a row?

level: juniorimportance: must knowfreq 72%

basics

~20 s

PostgreSQL compares 32-bit transaction IDs modulo 2^32, so a row's inserting XID looks past for only about two billion transactions. VACUUM freezes old rows, setting a header flag that makes them visible to every future transaction.

open as a page

In PostgreSQL 18, what thresholds make autovacuum pick a table for VACUUM or ANALYZE, and why do the defaults fail large tables?

level: middleimportance: must knowfreq 68%

basics

~20 s

Autovacuum vacuums a table once dead tuples pass 50 + 0.2 × reltuples (capped at 100 million in PostgreSQL 18) or inserts pass a similar bar, and analyzes after 50 + 0.1 × reltuples changes, so big tables wait for millions of dead rows.

open as a page

A PostgreSQL payroll ledger table doubled on disk after a one-off backfill and must stay writable during payroll runs; how would you shrink it with pg_repack, and what can go wrong?

level: seniorimportance: must knowfreq 52%

basics

~20 s

pg_repack copies the table while a trigger logs concurrent writes, builds the copy's indexes, replays the log, then swaps the copy in under a brief ACCESS EXCLUSIVE lock. It needs a primary or unique NOT NULL key and about double the disk.

open as a page

Your PostgreSQL IoT telemetry database logged 'must be vacuumed within' warnings and now rejects writes to avoid wraparound; how do you restore service?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Stay in normal multi-user mode. Clear what pins the oldest XID, old prepared transactions, long-running sessions and stale replication slots, then run a plain VACUUM as a superuser in the named database, oldest tables first.

open as a page

In PostgreSQL, VACUUM VERBOSE reports many tuples as dead but not yet removable; what can hold cleanup back, and how do you find it?

level: middleimportance: should knowfreq 60%

basics

~10 s

VACUUM removes only tuples older than the oldest xmin still needed: a long or idle-in-transaction session (pg_stat_activity backend_xmin, backend_xid), a forgotten prepared transaction (pg_prepared_xacts), or a replication slot or standby feedback (pg_replication_slots xmin).

open as a page

In PostgreSQL, how does CLUSTER differ from VACUUM FULL, and how much free disk should you plan for each while it runs?

level: middleimportance: should knowfreq 33%

basics

~20 s

Both rewrite the table into new files under an ACCESS EXCLUSIVE lock and rebuild every index. CLUSTER also orders rows by a chosen index, once only, and its sort can need about twice the table plus indexes; VACUUM FULL just compacts.

open as a page

In PostgreSQL, how do pgstattuple and pgstattuple_approx measure a table's bloat, and when would you choose one over the other?

level: middleimportance: should knowfreq 42%

basics

~20 s

pgstattuple reads every page and returns exact live, dead and free bytes, so bloat is dead plus free space as a share of table_len. pgstattuple_approx skips all-visible pages via the visibility map and estimates the rest, far more cheaply.

open as a page

In PostgreSQL, how do you measure how close each database and table is to XID wraparound, and which freeze settings act at which age?

level: middleimportance: should knowfreq 55%

basics

~10 s

Query age(datfrozenxid) from pg_database and age(relfrozenxid) from pg_class, taking the larger of each table and its TOAST table. Compare with vacuum_freeze_table_age (150M), autovacuum_freeze_max_age (200M) and the roughly 2.1-billion hard limit.

open as a page

A PostgreSQL sessions table in a social app keeps growing even though autovacuum runs on it; how do cost throttling and per-table settings explain and fix it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

Autovacuum workers share one throttled cost budget (200 credits, then a 2 ms nap), so a constantly updated table can outpace them. Give the table its own lower trigger and higher cost limit with ALTER TABLE ... SET, after ruling out xmin blockers.

open as a page

On a PostgreSQL telemetry database, an autovacuum marked '(to prevent wraparound)' is blocking an ALTER TABLE; how does it differ from normal autovacuum, and what changes once vacuum_failsafe_age is crossed?

level: seniorimportance: should knowfreq 42%

basics

~20 s

An anti-wraparound autovacuum is forced by relfrozenxid age, runs even with autovacuum off, normally scans every not-all-frozen page, and is not interrupted by conflicting locks. Past vacuum_failsafe_age (1.6 billion) VACUUM stops throttling and skips index vacuuming.

open as a page

In PostgreSQL, how can MultiXact IDs wrap around independently of transaction IDs, and how do their limits and recovery differ?

level: seniorimportance: should knowfreq 30%

basics

~20 s

MultiXact IDs, created when several transactions lock one row, are a separate 32-bit counter with their own member storage. VACUUM freezes them by relminmxid age; exhaustion blocks only writes needing a new MultiXact, and slots do not pin them.

open as a page

In PostgreSQL 18, what does eager freezing change for an append-mostly telemetry table, and what does vacuum_max_eager_freeze_failure_rate control?

level: middleimportance: nice to knowfreq 24%

basics

~10 s

Before 18, normal vacuums skipped all-visible pages, leaving freezing to one huge aggressive vacuum. PostgreSQL 18 lets normal vacuums eagerly freeze some all-visible pages; vacuum_max_eager_freeze_failure_rate (default 0.03) caps failed attempts before eager scanning pauses.

open as a page

In PostgreSQL, how do you read pg_stat_progress_vacuum to tell where a long-running vacuum is spending its time?

level: seniorimportance: nice to knowfreq 30%

basics

~10 s

Join pg_stat_progress_vacuum to pg_stat_activity on pid, then read phase, heap_blks_scanned against heap_blks_total, index_vacuum_count (above 1 means dead-tuple memory filled) and, in PostgreSQL 18, delay_time for time spent throttled.

open as a page

In PostgreSQL, a VACUUM FULL has run for an hour; how do you use pg_stat_progress_cluster to tell how far along it is, or whether it has started?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

pg_stat_progress_cluster has a row per backend running CLUSTER or VACUUM FULL: phase names the stage, heap_blks_scanned over heap_blks_total gives scan progress, and index_rebuild_count counts rebuilt indexes. No row on an active session means it still waits for its lock.

open as a page