PostgreSQL VACUUM and Autovacuum

PostgreSQL never overwrites a row in place. An UPDATE writes a new version of the row and marks the old version as dead. A DELETE marks the row as dead too. Dead rows keep using disk space until something cleans them up.

  Before UPDATE:   [Row v1 live]
  After UPDATE:    [Row v1 dead] [Row v2 live]
  After VACUUM:    [free space ] [Row v2 live]

Imagine a notebook where you never erase. You cross out an old line and write the new line below it. After many edits, the pages fill with crossed-out lines. VACUUM acts like a tidy-up session that marks those crossed-out lines as free space for new writing.

Why Old Versions Exist

PostgreSQL uses a design called MVCC. Each running query sees a consistent snapshot of the data. A reader can finish its work on old row versions while a writer creates new ones. Readers never block writers, and writers never block readers. The cost is dead rows that need cleaning.

What Too Many Dead Rows Cause

• Tables grow larger than their real data needs. People call this bloat.
• Queries read useless pages and slow down.
• Indexes grow and lose efficiency.
• In extreme cases, transaction ID wraparound threatens the database.

Manual Cleanup

Three commands clean tables and refresh planner statistics by hand.

Running VACUUM

VACUUM orders;

Plain VACUUM marks dead row space as reusable. It runs while users keep reading and writing. The file on disk usually stays the same size, because the freed space waits for new rows.

VACUUM VERBOSE orders;

VERBOSE prints a report of what the command found and cleaned.

VACUUM FULL

VACUUM FULL orders;

VACUUM FULL rewrites the whole table into a new compact file and returns the space to the operating system. It locks the table completely during the work. Use it rarely, and only in quiet periods.

Feature	VACUUM	VACUUM FULL
Table locked	No	Yes, fully
Returns space to the operating system	Seldom	Yes
Speed	Fast	Slow
Routine use	Yes	No

ANALYZE

ANALYZE collects statistics about the data: how many rows exist, which values appear often, and how values spread. The planner reads these statistics to pick fast plans.

ANALYZE orders;
VACUUM ANALYZE orders;

The second command does both jobs in one pass.

Autovacuum

PostgreSQL ships with a background helper named autovacuum. It watches tables and runs VACUUM and ANALYZE when enough rows change. It works like a night cleaner who visits every office and decides which rooms need attention.

  Many changes in table --> autovacuum wakes up --> VACUUM + ANALYZE

Keep autovacuum switched on. Check its setting with this command.

SHOW autovacuum;

When Autovacuum Acts

A table qualifies when dead rows pass a limit. The formula uses two settings.

Setting	Default
autovacuum_vacuum_threshold	50 rows
autovacuum_vacuum_scale_factor	0.2 (20 percent of the table)

A table with 100000 rows triggers cleanup after about 20050 dead rows. Huge tables with heavy traffic may need a lower scale factor. Set it for one table like this.

ALTER TABLE orders
SET (autovacuum_vacuum_scale_factor = 0.05);

Checking Dead Rows

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Tables near the top of this list collect the most dead rows. A high n_dead_tup with an old last_autovacuum date signals that cleanup falls behind.

Long Transactions Block Cleanup

VACUUM cannot remove a dead row while an old transaction might still need to see it. A forgotten open transaction holds the cleaner back for every table. Close transactions quickly, and watch for sessions marked "idle in transaction" in pg_stat_activity.

Good Habits

• Leave autovacuum enabled.
• Monitor dead rows on busy tables.
• Avoid long idle transactions.
• Run ANALYZE after loading a large batch of data.
• Reserve VACUUM FULL for rare maintenance windows.