Postgres VACUUM and Table Bloat: How It Works and How to Keep It Under Control

A Postgres table that's 2 GB of live data occupying 15 GB on disk, queries that slowed down for no obvious reason, a warning in the logs about transaction ID wraparound — all of these trace back to VACUUM. Most of the time autovacuum handles it quietly. When it can't keep up, you need to understand what it's doing.

Why bloat exists: MVCC

Postgres uses multi-version concurrency control (MVCC). An UPDATE doesn't overwrite a row in place; it writes a new version of the row and marks the old one as expired. A DELETE marks the row expired. The old versions — dead tuples — must stay around as long as any running transaction might still need to see them.

This is what lets readers and writers avoid blocking each other. (Database transactions explained.) The cost is garbage: dead tuples accumulate until something cleans them up.

What VACUUM does

A plain VACUUM:

1. Finds dead tuples that no transaction can see any more and marks their space as reusable (recorded in the free space map). The table file usually doesn't shrink; future inserts and updates reuse the space.
2. Updates the visibility map, which enables index-only scans and lets future vacuums skip clean pages.
3. Freezes old transaction IDs (see wraparound, below).

It runs alongside normal reads and writes — no exclusive lock.

ANALYZE (often run together) is different: it refreshes the statistics the query planner uses to choose plans. (Postgres EXPLAIN ANALYZE.)

Autovacuum

The autovacuum daemon vacuums and analyzes tables automatically. A table is vacuumed when its dead tuples exceed:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × table rows
         (default 50)                    (default 0.2)

There's a similar insert-based trigger (so append-only tables still get vacuumed and frozen) and an analyze trigger (default scale factor 0.1).

The large-table problem

With a 20% scale factor, a 100-million-row table waits for 20 million dead tuples before autovacuum starts — then needs a long time to finish. Meanwhile bloat grows and the planner works from stale statistics. PostgreSQL 18 added autovacuum_vacuum_max_threshold to cap this, but for big, busy tables it's still common to tune per table:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum at 1% dead
  autovacuum_analyze_scale_factor = 0.02
);

Autovacuum too slow

Autovacuum is deliberately throttled (autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay) so it doesn't hurt your workload. On modern SSDs, the defaults are often too gentle for write-heavy databases. Raising the cost limit — and autovacuum_max_workers if many tables need attention — lets it keep up.

What blocks cleanup

VACUUM can only remove dead tuples older than the oldest transaction still running anywhere in the cluster (the "xmin horizon"). Things that hold that horizon back:

• Long-running transactions — a report query running for hours, an analytics job.
• Sessions "idle in transaction" — an app that ran BEGIN, did something, and never committed (often a bug or a crashed worker holding a pooled connection). Set idle_in_transaction_session_timeout.
• Abandoned replication slots — a logical replication slot whose consumer went away retains everything.
• Old prepared transactions.

Find them:

SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

If autovacuum runs constantly but bloat keeps growing, something here is almost always the cause.

Transaction ID wraparound

Postgres transaction IDs are 32-bit and wrap around after about 2 billion. To keep old rows visible, VACUUM freezes them — marks them as definitely in the past. If freezing falls too far behind:

• at autovacuum_freeze_max_age (default 200 million transactions), Postgres forces an aggressive anti-wraparound vacuum, even if autovacuum is otherwise off;
• if things get much worse, Postgres eventually stops accepting writes to protect your data until a vacuum completes.

Monitor it:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Alert well before 1 billion. Whatever blocks normal vacuum (above) also blocks freezing.

Measuring bloat

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

For accurate numbers, the pgstattuple extension measures actual free and dead space; community bloat-estimate queries give a cheaper approximation.

Reclaiming space

Plain VACUUM makes space reusable but rarely returns it to the operating system. To actually shrink a bloated table:

• VACUUM FULL rewrites the table compactly — but takes an ACCESS EXCLUSIVE lock for the whole duration. Reads and writes stop. Fine for small tables or maintenance windows; not for a busy large table.
• pg_repack (an extension) rebuilds tables and indexes online with only brief locks. The usual choice in production.
• Indexes bloat too. REINDEX INDEX CONCURRENTLY rebuilds an index without blocking writes.

Reducing bloat in the first place

• HOT updates: if an update doesn't change any indexed column and there's room on the same page, Postgres can do a "heap-only tuple" update, which is much cheaper to clean up. Lowering fillfactor (e.g. 90) on frequently updated tables leaves room for this. Avoid indexing columns that change constantly.
• Don't update rows needlessly — an UPDATE that sets a column to its existing value still creates a new row version.
• Partition time-series data and drop old partitions instead of mass DELETEs. (Postgres table partitioning.)
• Batch large deletes and let vacuum catch up between batches.

The summary

• MVCC leaves dead row versions; VACUUM makes their space reusable and freezes old rows.
• Tune autovacuum per table for large, busy tables, and let it run faster on modern disks.
• Long transactions, idle-in-transaction sessions and stale replication slots block cleanup.
• Monitor age(datfrozenxid) for wraparound.
• Shrink tables with pg_repack or REINDEX CONCURRENTLY, not VACUUM FULL on live tables.

---

EasySpawn runs PostgreSQL on your server with daily backups, and Claude Code can query pg_stat_user_tables and pg_stat_activity directly to find what's bloating — and why. See how it works or join the waitlist.

Related: Postgres Connection Pooling · Postgres Point-in-Time Recovery · Postgres Major Version Upgrades · Soft Deletes and Audit Logs · Postgres MVCC Explained