skip to content

Dead Tuples & Autovacuum

VACUUM marks dead tuples' space reusable and sets visibility map bits, and autovacuum decides when to run it. Interviewers ask why tables keep growing and what holds back cleanup.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

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

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

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

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