VACUUM, autovacuum, and bloat: why is the table still large after DELETE? Intermediate

Problem

A job table is updated constantly: pending, running, done. A few weeks later count(*) still hovers around one million rows, but the table file has grown several times over. The operations team DELETEs old jobs: the row count drops, the file on disk barely changes.

This is normal MVCC behavior: UPDATE/DELETE leave old versions for snapshots that are still reading, and VACUUM cleans them up and marks the space as free for reuse, but usually does not return the file to the operating system.

Three things often get mixed up:

• Dead tuple: a version nobody can see anymore.
• Free space: empty space in the file, reusable.
• Bloat: significant excess space compared with live data and upcoming writes. A large file about to receive many writes is not necessarily a problem; a bloated file and indexes along with rising latency is.

Four commands, four different jobs

	What it does	Lock	Returns disk to the OS?
VACUUM	Removes dead tuples and their index entries, updates the visibility map, freezes old XIDs	Does not block normal reads/writes	Usually not. Exception: empty pages at the end of the table can be truncated if an exclusive lock is easy to get
ANALYZE	Samples to update planner statistics	Light	No
VACUUM FULL	Rewrites the whole table into a new file with no free space	ACCESS EXCLUSIVE: blocks all access; needs extra disk about the size of the table	Yes
autovacuum	A background process that runs VACUUM and ANALYZE automatically by threshold; also runs anti-wraparound	Like VACUUM	Like VACUUM

Do not turn off autovacuum to save I/O: it is paying the mandatory cost of MVCC and protecting XID safety (see wraparound).

A heap page before and after (illustration)

Before VACUUM  (one page, 6 slots)
  [ live ][ DEAD ][ live ][ DEAD ][ DEAD ][ live ]

After VACUUM
  [ live ][ free ][ live ][ free ][ free ][ live ]   file does NOT shrink; free space serves later INSERT/UPDATE

After VACUUM FULL
  [ live ][ live ][ live ]                           new, smaller file; the table is locked throughout

Autovacuum thresholds: defaults and the math

Autovacuum runs VACUUM on a table when the number of dead tuples exceeds:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x reltuples

Parameter (PostgreSQL 17)	Default
autovacuum_naptime	1min
autovacuum_max_workers	3
autovacuum_vacuum_threshold / scale_factor	50 / 0.2
autovacuum_vacuum_insert_threshold / insert_scale_factor	1000 / 0.2
autovacuum_analyze_threshold / scale_factor	50 / 0.1
autovacuum_vacuum_cost_delay	2ms

Worked example: a 10-million-row table, defaults. The threshold is 50 + 0.2 x 10,000,000 = 2,000,050 dead tuples before it triggers. For a heavily updated job table, autovacuum arrives too late, and each run has to clean up a huge batch.

Tune only the hot table, without changing cluster-wide defaults:

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 10M rows: threshold 1,000 + 100,000 = 101,000 instead of 2,000,050
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_vacuum_cost_delay   = 0       -- yields less I/O, only for the hot table
);

PostgreSQL 18 adds autovacuum_vacuum_max_threshold, a fixed cap on dead tuples so very large tables do not wait on a percentage. PostgreSQL 18 also adds autovacuum_worker_slots so autovacuum_max_workers can change without a restart.

What stops VACUUM from cleaning: the “xmin horizon”

VACUUM only removes versions that no snapshot still needs. Anything holding an old snapshot blocks cleanup for every table, and dead tuples accumulate even though autovacuum keeps running:

Source	How to detect	How to prevent
Long transactions, or idle in transaction sessions	pg_stat_activity by xact_start	idle_in_transaction_session_timeout (default 0, i.e. off) and statement_timeout
Forgotten prepared transactions	pg_prepared_xacts	Roll back or commit
Stale replication slots	pg_replication_slots	Drop dead slots (carefully, the replica may need rebuilding)
A replica with hot_standby_feedback running long queries	Check on the replica	Shorten queries on the replica

UPDATE creates an old version; VACUUM reclaims it when safe

Enlarge diagram

UPDATE → Heap versionsv1 → v2

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

UPDATE creates a new version; the old version stays in the heap because an open snapshot may still need it.

Observing

-- Tables with the most dead tuples; n_dead_tup is an estimate, use it for trends
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
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- VACUUM in progress (this view only has data while it is running)
SELECT relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

-- Who holds the oldest transaction
SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

Quick reading tips:

• An old last_autovacuum does not necessarily mean autovacuum is off: the table may not have reached the threshold.
• pg_relation_size() not shrinking after VACUUM is normal. Use pg_total_relation_size() to include indexes and TOAST.
• Index bloat is a separate issue: fix it with REINDEX CONCURRENTLY, not VACUUM FULL.
• Measuring bloat precisely in bytes needs the pgstattuple extension; n_dead_tup cannot replace it.

Real-world example

A 10-million-row jobs table grows from 4 GB to 30 GB

Workers change each job’s status 3 times, each time creating a new version. With defaults, autovacuum only runs past 2 million dead tuples; meanwhile a report holds a six-hour transaction open, holding back the horizon, so the next cleanup removes even less.

The fix:

1. Find and stop the long transaction.
2. ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01).
3. Delete old done jobs in batches, or better, partition by day and DETACH PARTITION/DROP TABLE old partitions (PostgreSQL has no DROP PARTITION command; see partitioning).
4. Do not run VACUUM FULL at peak hours.

The 4 GB and 30 GB figures are an illustrative scenario. Queue tables are especially sensitive to bloat, see SKIP LOCKED.

Demo (creates and drops a vacuum_demo table; do not run it on a database with a table of the same name):

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-vacuum/vacuum-demo.sql

The script creates 10,000 rows, UPDATEs all of them once, prints the heap size before and after, runs VACUUM (VERBOSE, ANALYZE), and prints again.

The takeaway: count(*) is still 10,000 and the file is not required to shrink after VACUUM. It uses a regular table, not TEMP, because TEMP tables are not handled by autovacuum.

When VACUUM FULL is acceptable

• Most of the data has been deleted permanently and the table will not grow back.
• There is a maintenance window, enough disk for the new copy, and blocking all access to the table is acceptable.
• Never as a wraparound rescue: it rewrites the table, takes a strong lock, and may itself need an XID.

To shrink a table with less disruption, use external tools such as pg_repack or pg_squeeze (check availability on your platform).

Production checklist

1. Monitor per table: n_dead_tup, dead_pct, last_autovacuum, heap and index size over time.
2. Heavily updated tables (queues, sessions, counters): set a low autovacuum_vacuum_scale_factor (around 0.01-0.05) for that table only.
3. Set idle_in_transaction_session_timeout and statement_timeout for the app; alert on transactions longer than a few minutes.
4. Periodically check prepared transactions, inactive replication slots, and hot_standby_feedback.
5. Remove old data by partition instead of bulk DELETE where possible.
6. Index bloat: REINDEX CONCURRENTLY. VACUUM FULL only as a planned job with a window.
7. Do not turn off autovacuum, even for insert-only tables (they still need freezing).

Trade-offs

Regular VACUUM

• Runs alongside the app and reclaims space for later writes.
• Autovacuum tracks the workload on its own and protects against wraparound.
• VACUUM (ANALYZE) refreshes planner statistics after large changes.

When you want to shrink the file

• Costs I/O; if it runs late or is blocked by the horizon, bloat accumulates.
• Usually does not return space to the OS.
• VACUUM FULL returns disk but rewrites the table, needs extra disk, and blocks access.

Common mistakes

Common pitfall

“I deleted a million rows, ran VACUUM, and disk usage did not drop, so VACUUM must be broken.” VACUUM prepares free space for reuse. Look at the size trend and the upcoming write rate before deciding you need to return disk to the OS.

Common pitfall

“Turn off autovacuum to reduce I/O load.” Dead tuples pile up and automatic freezing disappears. For a very hot table, tune its threshold and cost settings individually.

Common pitfall

“VACUUM FULL is harmless maintenance.” It takes ACCESS EXCLUSIVE, rewrites the table, and needs temporary free space. In production, account for lock waits, run time, and disk space first.

Summary

Key takeaways

• UPDATE/DELETE leave old versions; VACUUM cleans them once no snapshot needs them and leaves the space for reuse.
• By default, autovacuum only runs when dead tuples exceed 50 + 20% of the rows: large, hot tables need their own thresholds.
• Long transactions, prepared transactions, replication slots, and hot_standby_feedback block cleanup for the whole database.
• VACUUM FULL is a rewrite with a strong lock, not a routine fix; for index bloat use REINDEX CONCURRENTLY.

Self-check: (1) Why is the file not smaller after a committed DELETE? (2) How many dead tuples does a 10-million-row table need before autovacuum runs with defaults, and how would you tune it for a hot table? (3) What does a long-open read-only transaction have to do with dead tuples?

References

• Routine Vacuuming — PostgreSQL 17
• VACUUM — PostgreSQL 17
• Automatic Vacuuming — PostgreSQL 17
• Statistics Views — PostgreSQL 17
• Progress Reporting — PostgreSQL 17
• Release Notes — PostgreSQL 18