# VACUUM and ANALYZE

> Maintain row visibility and planner statistics with VACUUM, ANALYZE, and autovacuum.

---

LLMS index: [llms.txt](/en/llms.txt)

---

PostgreSQL uses multi-version concurrency control (MVCC): an `UPDATE` does not overwrite a row, it writes a new version and marks the old one *dead*. A `DELETE` only marks rows dead — they stay on disk. `VACUUM` reclaims that space and prevents transaction-ID wraparound. `ANALYZE` updates the planner's statistics so it makes good decisions. PostgreSQL runs both automatically via *autovacuum*, but understanding what they do — and when to nudge them — is part of being a competent operator.

## What you'll learn {#learn}

- Why MVCC produces dead rows and what VACUUM does about them
- The difference between `VACUUM`, `VACUUM FULL`, and `VACUUM ANALYZE`
- What `ANALYZE` actually computes
- Reading `pg_stat_user_tables` to see autovacuum activity
- The transaction-ID-wraparound horizon and why you cannot ignore it

## Example {#example}

```sql {num="53.1" caption="VACUUM and ANALYZE"}
-- Inspect the per-table autovacuum stats
SELECT relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 5;

-- Manually vacuum and analyze a table
VACUUM (VERBOSE) fruits;
ANALYZE VERBOSE fruits;

-- Combined: VACUUM ANALYZE (most common manual invocation)
VACUUM ANALYZE fruits;

-- See the planner's stored statistics for a column
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'fruits';

-- Bloat estimate (approximation): pg_stat_user_tables gives live vs dead row counts
-- pgstattuple extension gives exact numbers if you need them.

-- VACUUM FULL: rewrites the whole table, fully reclaims space, takes an exclusive lock.
-- Use sparingly.
-- VACUUM FULL fruits;
```

MVCC is what lets PostgreSQL achieve high concurrency: writers do not block readers and vice versa, because each transaction sees a consistent snapshot. The cost is dead rows. `VACUUM` walks the table, marks dead rows' space as free for reuse, and updates the visibility map (a per-page bitmap that lets index-only scans avoid heap visibility checks on all-visible pages).

Plain `VACUUM` normally makes space reusable within the table. It can also truncate empty pages at the end of the table and return that space to the operating system. `VACUUM FULL` rewrites the entire table in a new file, releasing the old one — it reclaims space but takes an `ACCESS EXCLUSIVE` lock and is slow. Use it rarely; the routine maintenance is autovacuum.

`ANALYZE` samples the table to update `pg_statistic`. The planner uses these statistics — number of distinct values, most common values, histograms — to estimate row counts and pick plans. Stale statistics produce bad plans.

PostgreSQL's *autovacuum* daemon runs `VACUUM` and `ANALYZE` automatically based on per-table thresholds (`autovacuum_vacuum_scale_factor`, `autovacuum_analyze_scale_factor`). Autovacuum handles routine maintenance, but its progress and thresholds need monitoring, especially after bulk changes and on large or frequently updated tables.

## Run the example {#run}

To run:

```text
$ psql -f source/vacuum-and-analyze.sql postgres
 relname | n_live_tup | n_dead_tup | last_vacuum | ...
---------+------------+------------+-------------+----
 ...
INFO:  vacuuming "public.fruits"
INFO:  ...
VACUUM
 ...
```

## Common pitfalls {#pitfalls}

- Disabling autovacuum to "save resources" — almost always a mistake. The dead rows pile up, queries slow down, and the eventual catch-up is much worse than steady-state autovacuum overhead.
- Running `VACUUM FULL` on a busy table — it blocks all reads and writes for the duration. Use `pg_repack` (extension) for online compaction.
- Forgetting to `ANALYZE` after a bulk import — the planner has no statistics for the new rows and may pick terrible plans until autovacuum catches up. Run `ANALYZE schema.table;` manually after big imports.
- Long-running transactions block autovacuum from cleaning up rows visible to them. A forgotten `BEGIN;` in psql can prevent vacuuming across the whole database — watch `pg_stat_activity` for `state = 'idle in transaction'`.

> [!TIP] Tip
> `pg_stat_user_tables.n_dead_tup` is the canonical "do I need to vacuum?" metric. A high ratio of `n_dead_tup / n_live_tup` on a hot table is a sign autovacuum cannot keep up — tune the per-table thresholds with `ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.02);`.

> [!NOTE] Try it
> Run a bunch of `UPDATE`s on a table, then look at `n_dead_tup` in `pg_stat_user_tables`. Run `VACUUM` and re-check. Then run `EXPLAIN ANALYZE` on a query before and after `ANALYZE` to see if the estimates changed.

## Downloads {#downloads}

SQL file: [vacuum-and-analyze.sql](/examples/source/vacuum-and-analyze.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)