Skip to content
Tutorials Center
My learning

Topics

Lesson 48 of 90 ยท Transactions, MVCC and Concurrency

VACUUM, Autovacuum and Bloat

Learn what VACUUM and autovacuum do in PostgreSQL: reclaim dead tuples, VACUUM vs VACUUM FULL, ANALYZE, per-table autovacuum tuning, freezing and monitoring.

MVCC has a bill, and VACUUM pays it. Every UPDATE and DELETE leaves old row versions behind (MVCC), and every rolled-back insert leaves versions nobody can see. Without cleanup, tables and indexes would grow forever and queries would slow down. VACUUM finds those dead row versions and makes their space reusable. It also updates statistics for the planner and protects old rows from transaction ID wraparound.

You rarely run VACUUM by hand: a background system called autovacuum does it for you. But autovacuum's defaults are tuned for small and medium tables, and on a busy, large database you will need to understand what it is doing, how to see that it is falling behind, and how to adjust it per table. This lesson covers all of that. The server tools are read-only blocks; the live examples simulate the space accounting and the trigger formula.

What VACUUM does

Reclaim Marks dead row versions' space as reusable.
Analyze Optionally refreshes planner statistics.
Visibility map Records pages with no dead rows, which enables index-only scans.
Freeze Protects old rows from transaction ID wraparound.

Plain VACUUM runs alongside normal reads and writes.

1 / 2

Dead tuples and bloat

A dead tuple is a row version that no running transaction can see anymore. The table still holds it. Bloat is the space those dead tuples (and the free space they leave behind) take up in tables and indexes.

flowchart LR
  A["Live row version"] -->|"UPDATE or DELETE commits"| B["Dead tuple<br/>(still on the page)"]
  B -->|"VACUUM"| C["Free space<br/>(reusable inside the table)"]
  C -->|"new INSERT or UPDATE"| A
  C -.->|"VACUUM FULL (rewrites table)"| D["Space returned<br/>to the operating system"]

Plain VACUUM recycles space inside the table file; only a rewrite gives it back to the operating system.

You can see the numbers for each table:

SQL
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, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;
TEXT
  relname  | n_live_tup | n_dead_tup | dead_pct |       last_autovacuum        |       last_autoanalyze
-----------+------------+------------+----------+------------------------------+------------------------------
 events    |   48210933 |    9120441 |     15.9 | 2026-11-02 03:12:44.1+00     | 2026-11-02 03:14:02.8+00
 sessions  |     912044 |     402331 |     30.6 | 2026-11-08 08:01:12.4+00     | 2026-11-08 08:01:13.0+00
 orders    |    2210033 |      11820 |      0.5 | 2026-11-08 07:40:09.9+00     | 2026-11-08 07:40:10.5+00

These counters are estimates updated by the statistics system, which is good enough for monitoring. For exact measurements of bloat, the pgstattuple extension reads the table itself (SELECT * FROM pgstattuple('events')), but it is expensive on large tables.

VACUUM: reclaim space for reuse

SQL
VACUUM orders;                 -- one table
VACUUM;                        -- every table in the database
VACUUM (VERBOSE, ANALYZE) orders;

A plain VACUUM does the following for a table:

  1. Scans the table pages that might contain dead tuples. A visibility map records pages where every row is visible to everyone; those pages are skipped.
  2. Removes the matching entries from every index (this is often the most expensive step).
  3. Marks the dead tuples' space as free on the page and records it in the free space map, so later inserts and updates can reuse it.
  4. Updates the visibility map and, where needed, freezes old rows.
  5. If the last pages of the file are completely empty, truncates the file and gives those pages back to the operating system.

It takes a SHARE UPDATE EXCLUSIVE lock: reads and writes continue while it runs. You will not usually see the table shrink. After a big delete, the file stays the same size, but the space is available for new rows, so the file stops growing. That is the correct and healthy outcome.

Here is what verbose output looks like for a table with a long-running transaction in the way (the typical "why isn't it cleaning up" case):

TEXT
INFO:  vacuuming "public.sessions"
INFO:  table "sessions": found 0 removable, 402331 nonremovable row versions in 3920 out of 18211 pages
DETAIL:  402331 dead row versions cannot be removed yet, oldest xmin: 7721903

"Cannot be removed yet" means that some transaction holds a snapshot old enough to still need those versions. Look for long-running or idle in transaction sessions, abandoned replication slots, forgotten prepared transactions and replicas with hot_standby_feedback (see the MVCC lesson, and Transactions and ACID).

VACUUM FULL: the heavy hammer

SQL
VACUUM FULL orders;

VACUUM FULL writes a new compact copy of the table and all its indexes, then swaps it in. The result is the smallest possible files, with the operating system getting the space back. The price:

  • It takes an ACCESS EXCLUSIVE lock for the whole time. The table cannot be read or written.
  • It needs enough free disk for a complete second copy.
  • It rewrites everything, so it takes as long as the table is big.

Use it rarely: for example after deleting most of a table that will not grow again. On a live system, tools like pg_repack or pg_squeeze rebuild a table with only a short lock at the end. Often the best answer is not to shrink at all: if the table will fill up again, the free space is not wasted.

If you delete data in bulk on a regular schedule (such as old logs), partitioning lets you drop a whole partition instead, which is instantaneous and leaves no bloat.

Seeing the difference in space

The in-browser engine is SQLite, not PostgreSQL, but SQLite has a similar idea: deleted rows leave free pages inside the file until a rebuild. In PostgreSQL terms, the first step below is what happens after DELETE plus VACUUM (space is free but the file keeps its size). SQLite's own VACUUM command rebuilds the database file, so it behaves like PostgreSQL's VACUUM FULL:

SQL Free space inside the file vs a rebuild (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

After the delete the file is exactly as large as before (96 pages), but most pages are on the free list, ready for reuse: that is the state of a PostgreSQL table after a plain VACUUM. The rebuild shrinks the file to about 21 pages, like VACUUM FULL, at the cost of rewriting everything (and, in PostgreSQL, locking the table).

ANALYZE: statistics for the planner

The planner chooses plans from statistics about your data: how many rows each table has, the distribution of values in each column, and so on. ANALYZE samples the table and refreshes them:

SQL
ANALYZE orders;
ANALYZE orders (customer_id, status);    -- only some columns
VACUUM ANALYZE;                          -- both steps

After a bulk load, a big delete or any large change in data distribution, stale statistics lead to bad plans. Autovacuum runs ANALYZE as well, but after a bulk load it is worth running by hand, especially before the first queries (see Statistics and the Query Planner).

Autovacuum

Autovacuum is a launcher process that starts worker processes for tables that need attention. It wakes up every autovacuum_naptime (1 minute), looks at the statistics counters, and starts up to autovacuum_max_workers (3 by default) workers. A table is vacuumed when:

TEXT
dead tuples  >  autovacuum_vacuum_threshold  +  autovacuum_vacuum_scale_factor  x  live rows
                (default 50)                    (default 0.2 = 20%)

It is analyzed when changed rows exceed autovacuum_analyze_threshold (50) plus autovacuum_analyze_scale_factor (0.1, 10%) times the row count. Since PostgreSQL 13, tables that are only inserted into (never updated) are also vacuumed, after autovacuum_vacuum_insert_threshold (1000) plus autovacuum_vacuum_insert_scale_factor (0.2) times the rows, so that their rows get frozen and marked in the visibility map.

Autovacuum is deliberately gentle. A cost-based delay (autovacuum_vacuum_cost_delay, 2 ms by default since PostgreSQL 12, and autovacuum_vacuum_cost_limit, 200 unless changed) makes the worker sleep after a certain amount of I/O, so it does not flood the disks.

The scale factor trap

Percentages scale badly. With the default 20%, a 1,000-row table is vacuumed after about 250 dead rows. A table with 500 million rows is vacuumed only after about 100 million dead rows, by which point it holds a huge amount of garbage and the vacuum itself will take hours. This simulation computes the trigger point for different table sizes and scale factors:

SQL When does autovacuum start?

Press Run (or Ctrl + Enter) to see the result here.

For events the default trigger is 100,000,050 dead rows, and only 5,000,050 with a 1% scale factor. The right setting depends on the table, which is why PostgreSQL lets you set these parameters per table:

SQL
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000,
  autovacuum_analyze_scale_factor = 0.02
);

-- for a very busy small table that must stay compact:
ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.0,
                          autovacuum_vacuum_threshold = 1000);

-- see what is set:
SELECT relname, reloptions FROM pg_class WHERE relname IN ('events', 'sessions');

Making autovacuum faster

If a vacuum starts on time but never catches up, the throttle is the limit. For big tables on fast storage you can raise the work budget, either globally in postgresql.conf or per table:

SQL
ALTER TABLE events SET (autovacuum_vacuum_cost_limit = 2000);

(Per-table cost limits are balanced across active workers, so with several workers the effective speed is shared.) If many large tables need vacuuming at once, raise autovacuum_max_workers and memory settings such as autovacuum_work_mem (it defaults to maintenance_work_mem; a larger value means fewer passes over the indexes).

Never turn autovacuum off. Without it, tables bloat, statistics go stale and the cluster will eventually approach transaction ID wraparound and refuse writes. Even then, PostgreSQL starts an anti-wraparound autovacuum on its own. Fix what is wrong instead: the thresholds, the throttle, or the long transactions that keep dead rows alive.

Freezing

Freezing is the part of VACUUM that protects old rows from transaction ID wraparound (explained in the MVCC lesson). Normal vacuums freeze rows older than vacuum_freeze_min_age (50 million transactions). Once a table's oldest unfrozen row passes autovacuum_freeze_max_age (200 million), autovacuum starts an aggressive vacuum even if the table has no dead tuples, and it is not cancelled by conflicting lock requests the way a normal autovacuum is. To keep an eye on this:

SQL
SELECT c.relname,
       age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class AS c
WHERE c.relkind = 'r'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 5;

For a very large table you can freeze deliberately during a quiet period with VACUUM (FREEZE) table, rather than waiting for the anti-wraparound vacuum to hit during peak traffic.

Monitoring checklist

SQL
-- Is a vacuum running right now, and how far along is it?
SELECT pid, relid::regclass AS table_name, phase,
       heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;
TEXT
  pid  | table_name |       phase       | heap_blks_scanned | heap_blks_total
-------+------------+-------------------+-------------------+-----------------
 20144 | events     | scanning heap     |            410022 |         1210033

Things to look at regularly:

  • n_dead_tup and last_autovacuum per table: large dead counts with an old last_autovacuum mean autovacuum is not keeping up or is blocked.
  • Oldest transactions and slots: pg_stat_activity (xact_start, backend_xmin) and pg_replication_slots (xmin, catalog_xmin).
  • age(relfrozenxid) and age(datfrozenxid): should stay well below autovacuum_freeze_max_age (200 million by default).
  • Logs: set log_autovacuum_min_duration = 0 (or 1s) temporarily to log each autovacuum run, with how many dead tuples it removed and how long it took.
  • Size over time: a table whose size grows while its live-row count does not is bloated.

A good habit: make the five or ten tables that take most of your updates and deletes (queues, sessions, counters, "status" tables) the first ones you give custom autovacuum settings. Frequent small vacuums are cheaper than rare giant ones.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. After a plain VACUUM on a table where you deleted most rows, what usually happens to the table file?

    Plain VACUUM marks space as reusable inside the file and only truncates empty pages at the very end. Shrinking the file in general needs a rewrite such as VACUUM FULL or pg_repack.
  2. 2. Why is VACUUM FULL risky on a busy production table?

    VACUUM FULL rewrites the table while blocking all access, and it needs disk space for the new copy. Plain VACUUM runs with a lock that allows reads and writes.
  3. 3. With default settings, roughly how many dead rows must a 100-million-row table collect before autovacuum starts on it?

    The trigger is 50 plus 20% of the rows, so about 20 million dead rows. That is why large tables get per-table scale factors such as 0.01.
  4. 4. VACUUM VERBOSE reports 400000 dead row versions cannot be removed yet. What is the most likely cause?

    Versions can only be removed when no snapshot could still see them. The oldest open snapshot, wherever it comes from, sets the horizon.
  5. 5. Which is the best response when a busy table keeps bloating?

    Tune autovacuum per table so it runs often and finishes quickly, and remove whatever keeps dead rows alive. Disabling it, or relying on nightly exclusive-lock rewrites, makes things worse.

Summary

  • Dead tuples come from UPDATE, DELETE and aborted transactions; VACUUM makes their space reusable, updates the visibility map and freezes old rows.
  • Plain VACUUM runs concurrently with normal traffic and rarely shrinks files; VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock, so use it sparingly (or use pg_repack).
  • ANALYZE refreshes planner statistics; run it by hand after bulk changes.
  • Autovacuum starts when dead tuples exceed 50 + 0.2 x rows by default; set smaller scale factors per table for big or busy tables, and raise the cost limit when it cannot keep up.
  • Never disable autovacuum. If it cannot clean up, look for long transactions, replication slots, prepared transactions and standby feedback.
  • Monitor pg_stat_user_tables, pg_stat_progress_vacuum, transaction ages and table sizes.

Next, the Functions chapter begins with SQL Functions and Procedures.