What do VACUUM and autovacuum do in PostgreSQL?

Think of a hostel mess register. When a student changes their meal plan, the clerk does not erase the old line; they strike it out and write a new line below. After a few months the register is full of struck-out lines. Someone has to go through it and mark those lines as free space for new entries. That is VACUUM.

In PostgreSQL every UPDATE writes a new version of the row and leaves the old one behind, and every DELETE only marks a row as gone. These leftovers are called dead tuples. Nobody can see them any more, but they still take space until VACUUM cleans them up.

-- How many dead rows does each table have, and when was it last vacuumed?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

-- Run it by hand on one table, and refresh planner statistics at the same time
VACUUM (VERBOSE, ANALYZE) orders;

Sample result of the first query:

Here sessions has far more dead rows than live ones and has never been auto-vacuumed. That is worth investigating.

A plain VACUUM does three main jobs:

1. Reclaims dead tuples: it removes dead row versions and their index entries, and records the free space so new rows can reuse it. It usually does not shrink the file on disk, except for empty pages at the very end.
2. Updates the visibility map: pages where every row is visible to everyone are marked, which lets index-only scans skip reading the table.
3. Freezes old rows: transaction IDs are 32-bit and eventually wrap around. VACUUM marks very old rows as "frozen" so they stay visible forever. If this never happens, PostgreSQL will eventually refuse new writes to protect data.

Plain VACUUM runs alongside normal reads and writes. VACUUM FULL is different: it rewrites the whole table into a new file and gives space back to the operating system, but it takes an exclusive lock that blocks all reads and writes for the duration.

Autovacuum checks tables regularly. By default it vacuums a table when dead tuples exceed 50 rows plus 20% of the table, and analyzes it when changes exceed 50 rows plus 10%. These are the settings autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor and their analyze versions.

• A session or cart table with constant updates and deletes needs autovacuum to keep up, or it bloats and slows down.
• On very large tables, 20% is too many rows to wait for, so teams lower the scale factor per table: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);.
• After a bulk load, running ANALYZE (or VACUUM ANALYZE) gives the planner fresh statistics.

• Turning autovacuum off because it "uses resources". Bloat and wraparound risk are far worse.
• Running VACUUM FULL on a busy production table during the day. It locks the table completely.
• Ignoring long-running or "idle in transaction" sessions. VACUUM cannot remove rows that an open snapshot might still need, so cleanup stalls.
• Confusing VACUUM with ANALYZE. VACUUM cleans space; ANALYZE collects statistics for the planner.

• What is transaction ID wraparound?
• Why can a long-running transaction cause bloat?
• How would you reclaim disk space from a bloated table without long locks?