What causes table bloat and dead tuples in PostgreSQL?

PostgreSQL implements MVCC by keeping multiple versions of a row. An UPDATE does not overwrite the row in place. It writes a new tuple version and marks the old one as expired. A DELETE only marks the existing tuple as expired. The old versions stay on disk until no running transaction's snapshot can still see them. After that point they are dead tuples: they occupy space but nothing will ever read them.

Plain VACUUM removes dead tuples and records the freed space so later inserts and updates can reuse it. It does not return that space to the operating system. The only exception is a run of completely empty pages at the end of the file, which it can truncate. If dead tuples pile up faster than vacuum clears them, the table grows. Reclaiming that space later usually needs a rewrite. The result is table bloat: more pages to scan, cache, and back up for the same number of live rows.

Indexes bloat differently. Every non-HOT update adds a new index entry, even when the indexed value has not changed. Vacuum can remove dead index entries, but a B-tree page is recycled only after it becomes entirely empty. Sparse, half-filled pages therefore persist. Index bloat can also outlast heap cleanup.

This problem mostly affects backend and platform engineers who run queue-like tables, job or session tables, counters, and other rows that are updated constantly.

Measuring bloat has a tradeoff. Estimates built from planner statistics are cheap but approximate. Exact numbers, for example from the pgstattuple extension, require reading the whole relation, and you pay the I/O cost of that scan.

 +------------------+      +--------------------------------------+
 | UPDATE / DELETE  |----->| Heap page                            |
 | (MVCC)           |      | [live v2] [dead v1] [live] [dead]    |
 +------------------+      +--------------------------------------+
          |                                  |  old versions turn dead
          | non-HOT update                   |  once no snapshot sees them
          v                                  v
 +------------------+      +--------------------------------------+
 | B-tree index     |      | Plain VACUUM                         |
 | new entry even if|      | dead space -> reusable inside table  |
 | key is unchanged |      | truncates only empty trailing pages  |
 +------------------+      +--------------------------------------+
          |                                  |
          v                                  v
 +------------------+      +--------------------------------------+
 | VACUUM removes   |      | File size mostly unchanged           |
 | dead entries, but|      | if dead tuples outpace vacuum,       |
 | page recycled    |      | table grows: TABLE BLOAT             |
 | only when empty  |      | reclaiming space needs a rewrite     |
 | -> INDEX BLOAT   |      |                                      |
 +------------------+      +--------------------------------------+
          |                                  |
          v                                  v
 +----------------------------------------------------------------+
 | Measure: planner-statistics estimate -> cheap, approximate     |
 |          pgstattuple -> exact, full relation scan, I/O cost    |
 +----------------------------------------------------------------+

How MVCC updates and deletes leave dead tuples, why plain VACUUM makes heap space reusable without shrinking the file, how B-tree index bloat persists separately, and the tradeoff between estimated and exact bloat measurement.

Who should use autovacuum tuning?

The defaults are a reasonable starting point for small and moderately sized databases with modest write rates. Tuning becomes worth the effort when one or more of these is true:

  • You have large tables with high update or delete rates. Order, event, job, session and counter tables are typical. A proportional trigger lets dead tuples reach millions of rows on these tables before vacuum starts.
  • Table or index size keeps growing while the live row count stays flat. That gap is bloat, and it means vacuum is not keeping up.
  • You see slow scans, falling cache hit rates or rising backup sizes on tables whose data volume has not changed much.
  • You are the person on call for the database. Platform engineers, SREs and DBAs who own Postgres in production need to know which tables have overrides and why.

It is less useful for append-only tables with few updates or deletes, and for small tables that the defaults already vacuum often. If cleanup is blocked by an old transaction or replication slot, tuning does not help at all. The diagnosis section below shows how to tell these cases apart.

How does autovacuum work?

Autovacuum is a set of background processes built into the server. It depends on the statistics collector, so track_counts must be on, which it is by default.

  1. The launcher schedules work. A single autovacuum launcher process runs continuously. Roughly every autovacuum_naptime per database, it starts a worker for that database, up to autovacuum_max_workers workers running at once across the cluster.
  2. A worker picks tables. The worker reads the per-table activity counters, such as dead tuple counts and rows changed since the last analyze. It compares them against each table's thresholds, using per-table settings where they exist and global settings otherwise.
  3. It runs VACUUM and ANALYZE where needed. Tables over the vacuum trigger get a vacuum. Tables over the analyze trigger, controlled by autovacuum_analyze_threshold and autovacuum_analyze_scale_factor, get fresh planner statistics.
  4. Vacuum cleans the heap and indexes. It scans heap pages that may contain dead tuples, using the visibility map to skip pages already known to be all-visible. It removes dead tuples that are older than the xmin horizon, deletes the matching index entries, records the free space in the free space map, and updates the visibility map. It also freezes old transaction IDs so they don't wrap around.
  5. Throttling paces the I/O. While it works, the worker accumulates cost points for page hits, misses and dirtied pages, and sleeps whenever it reaches the cost limit.

The next sections explain the trigger formula and throttling in detail, because those two settings decide whether a busy table is vacuumed early enough and fast enough.

How does autovacuum decide when to vacuum a table?

Autovacuum checks each table against a trigger: it vacuums once the dead tuple count exceeds autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples, where reltuples is the planner's row estimate. The defaults are a threshold of 50 and a scale factor of 0.2, so a table must accumulate dead tuples equal to roughly 20% of its size before a run starts.

That works for small tables and fails for large ones. The trigger grows with table size, but the damage from dead tuples does not scale the same way. Indexes bloat, scans read more pages, and a single slow vacuum pass takes longer.

Illustrative estimate. Assume a 100-million-row orders table that receives 2,000 updates per second, each producing one dead tuple. The trigger is about 20,000,050 dead tuples. At that write rate, autovacuum waits roughly 10,000 seconds, almost three hours, before it starts. It then has to process 20 million dead tuples while new ones keep arriving.

Once a run starts, cost-based throttling controls its pace. Each page hit, miss, or dirtied page adds to a cost counter. When the counter reaches autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay. That limit is a global budget shared by all active workers, not a per-worker budget. If you raise autovacuum_max_workers from 3 to 6 without raising the cost limit, each worker gets about half the I/O budget it had before. You get more tables in progress at the same time, but each one finishes more slowly, and total throughput stays about the same.

How do you tell whether autovacuum is not starting, too slow, or blocked?

Before changing any setting, find out which of three problems you have. Each one needs a different fix, and the wrong fix can make things worse. For example, raising autovacuum_max_workers does nothing if the cleanup is blocked by an old transaction.

Begin with pg_stat_user_tables. Compare n_dead_tup against n_live_tup, and check last_autovacuum and autovacuum_count. Sample these columns a few minutes apart. A single snapshot can mislead you because n_dead_tup is an estimate and the counters reset when statistics are reset.

Next, look at pg_stat_activity and filter on backend_type = 'autovacuum worker'. This shows which tables the workers are processing, how long each has been running, and whether any are waiting on a lock. For a long-running worker, pg_stat_progress_vacuum shows the current phase and how many heap blocks it has scanned.

Failure mode What you see Likely cause
Not starting n_dead_tup keeps rising, last_autovacuum is old or null, autovacuum_count stays flat Trigger threshold is too high for a large table, or all workers are busy on other tables
Too slow Worker runs on the table for a long time, autovacuum_count grows slowly, dead tuples pile up faster than they are cleared Cost-based throttling, too few workers for the number of busy tables
Running but cannot remove tuples autovacuum_count increases, yet n_dead_tup barely drops after each run The xmin horizon is held back by something else

The third case is the one engineers most often misdiagnose. To confirm it, look for sessions in pg_stat_activity with an old backend_xmin or a long-running xact_start. The causes behind this, and the fixes, are covered in the section on what blocks dead tuple cleanup.

-- 1. Tables with the most dead tuples. Run this a few minutes apart and compare.
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,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- 2. What each autovacuum worker is doing, whether it is waiting, and its progress.
SELECT a.pid,
       a.query,                              -- e.g. 'autovacuum: VACUUM public.orders'
       now() - a.xact_start AS running_for,
       a.wait_event_type,                    -- 'Lock' means it is blocked
       a.wait_event,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total
FROM pg_stat_activity a
LEFT JOIN pg_stat_progress_vacuum p ON p.pid = a.pid
WHERE a.backend_type = 'autovacuum worker'
ORDER BY running_for DESC;

-- 3. Sessions holding back the xmin horizon (failure mode 3).
SELECT pid,
       datname,
       usename,
       state,
       backend_xmin,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

Three diagnostic queries. Each one maps to a failure mode. The first shows dead-tuple buildup and autovacuum history per table. The second shows what the autovacuum workers are doing and how far they have progressed. The third finds sessions whose old backend_xmin stops vacuum from removing tuples.

How do you implement per-table autovacuum tuning?

The non-starting and too-slow cases are the ones you can fix with configuration, and the fix usually belongs on the table itself. Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. With the default scale factor of 0.2, a 200-million-row table waits for roughly 40 million dead tuples (illustrative estimate). Running ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000) on that table brings the trigger down to about 2 million. If vacuum starts on time but can't keep up, raise autovacuum_vacuum_cost_limit or lower autovacuum_vacuum_cost_delay on the same table so each run does more work before it sleeps.

Per-table settings work better than global changes because bloat is rarely spread evenly. A few hot tables account for most dead tuples, while hundreds of small or static tables are fine on the defaults. If you lower the global scale factor, every small table crosses its threshold constantly. With a fixed autovacuum_max_workers, workers then spend their time on trivial tables while the large hot table waits in the queue.

Aggressive settings have costs:

  • Vacuuming faster moves load around rather than removing it. A higher cost limit means more read and write I/O per second. On storage that is already saturated, that shows up as foreground query latency.
  • Per-table settings are invisible from postgresql.conf. They live in pg_class.reloptions. Engineers who check only the config file will miss them, and values chosen for last year's workload will drift out of date.

Record these settings in your schema migrations so they get reviewed like any other change.

-- Lower the trigger point on one hot table only.
-- Default: 50 + 0.2 * reltuples. For 200M rows this is about 40M dead tuples.
-- New: 10000 + 0.01 * reltuples. For 200M rows this is about 2M dead tuples.
ALTER TABLE order_events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000,
  -- Let each run do more work before sleeping, for when vacuum starts on time
  -- but falls behind. This costs more I/O per second, so watch query latency.
  autovacuum_vacuum_cost_limit   = 2000,
  autovacuum_vacuum_cost_delay   = 1   -- milliseconds
);

-- Per-table overrides do not appear in postgresql.conf. List them here.
SELECT n.nspname, c.relname, c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.reloptions IS NOT NULL
ORDER BY n.nspname, c.relname;

-- Check that autovacuum now fires before dead tuples pile up.
SELECT relname,
       n_dead_tup,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'order_events';

-- To return to the global defaults later, use a migration:
-- ALTER TABLE order_events RESET (
--   autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold,
--   autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay);

This example applies autovacuum overrides to a single hot table, lists every per-table override from pg_class.reloptions so none stay hidden, and checks with pg_stat_user_tables that vacuum now keeps up.

What blocks dead tuple cleanup no matter how you tune autovacuum?

Vacuum can only remove a dead tuple once no snapshot anywhere in the cluster could still need it. The cutoff is the oldest xmin still held, called the xmin horizon. Cost limits, scale factors and worker counts do not move it. If something pins the horizon, autovacuum runs on schedule, scans the table, and leaves the dead tuples in place. VACUUM VERBOSE reports this as dead row versions that "cannot be removed yet."

Common xmin holders

  • Long-running transactions. A multi-hour report or batch job holds back cleanup on every table, including tables it never reads.
  • Sessions idle in transaction. An application that opens a transaction and never commits pins the horizon while doing no work.
  • Abandoned replication slots. A slot whose consumer has gone away keeps its xmin or catalog_xmin indefinitely and also retains WAL.
  • hot_standby_feedback on replicas. The standby reports its oldest query's xmin to the primary, so long queries on the replica cause bloat on the primary.

To find the holders, sort pg_stat_activity by age(backend_xmin) and check state and xact_start. Then check xmin and catalog_xmin in pg_replication_slots, and backend_xmin in pg_stat_replication for standbys.

Before you cancel a backend or drop a slot, contact its owner. Terminating a session can abort a batch job or analytics query partway through. Dropping a slot breaks the logical consumer or replica that depends on it, and that consumer usually needs a full resync afterward.

Anti-wraparound vacuums

When a table's oldest unfrozen transaction ID passes autovacuum_freeze_max_age, autovacuum runs an anti-wraparound vacuum. It does this even when autovacuum is disabled for the table. It also does not yield to conflicting locks the way a normal autovacuum does, so DDL and some application operations queue behind it.

On a large, neglected table this vacuum may have to scan most of the heap, which takes a long time and adds I/O load. If routine vacuums keep up, most pages are already marked all-frozen in the visibility map. The forced vacuum then skips them, and it finishes quickly without much effect on the workload.

-- 1. Sessions holding back the xmin horizon, oldest first.
--    Look for state = 'idle in transaction' or an old xact_start.
SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_duration,
       age(backend_xmin)  AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- 2. Replication slots pinning xmin or catalog_xmin.
--    active = false with a large age usually means the consumer is gone.
SELECT slot_name, slot_type, active,
       age(xmin)         AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

-- 3. Standbys reporting xmin via hot_standby_feedback.
SELECT application_name, client_addr, state,
       age(backend_xmin) AS xmin_age
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL;

-- 4. Tables closest to a forced anti-wraparound vacuum.
SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::int AS freeze_max_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;

-- Identify the owner before acting. Only after coordinating:
--   SELECT pg_cancel_backend(<pid>);
--   SELECT pg_drop_replication_slot('<slot_name>');

Queries that list the oldest xmin holders in sessions, replication slots and standbys, plus the tables nearest autovacuum_freeze_max_age, with the destructive commands left commented out until the owners agree.

What are the alternatives to tuning autovacuum harder?

Each of these options reduces the number of dead tuples created, so autovacuum has less work no matter how it is configured.

HOT updates with a lower fillfactor. A heap-only tuple (HOT) update writes the new row version on the same page and adds no index entries. Later page accesses can prune the old version without a full vacuum. This only works when the page has free space. Setting fillfactor to something like 80–90 reserves that space. The tradeoff is a larger table and fewer rows per cached page, so scans read more pages. It only helps tables whose rows are updated in place. Insert-only or delete-heavy tables just get bigger.

Avoid updating indexed columns. HOT is not possible if any indexed column changes. A frequently updated status or updated_at column with an index on it forces a new entry in every index on each update. Remove indexes that queries don't use, or move volatile columns out of indexes.

Partition append-heavy or queue-like tables. If old data expires by time, you can drop or detach a whole partition. That is a metadata operation and leaves no dead tuples to vacuum. The costs are partition maintenance, partition-key constraints on unique indexes, and queries that must prune well. Random updates spread across the whole table still bloat every partition, so partitioning does not help them.

Option Development cost Operational cost Avoid when
Lower fillfactor None Larger table, lower cache density Rows are rarely updated
Unindexed volatile columns Schema and query review Lower Queries need that index
Partitioning High: keys, constraints, queries Partition lifecycle jobs Updates hit random old rows

When should you use VACUUM FULL, REINDEX CONCURRENTLY, or an online rewrite?

Plain VACUUM never returns space to the operating system except for empty pages at the end of the table. It marks dead tuples reusable, so a bloated table stops growing but stays large. Shrinking the files requires rewriting them, and each method trades lock time against disk and I/O.

Method Lock held Extra disk Avoid when
VACUUM Does not block reads or writes None You need the file smaller
VACUUM FULL Access exclusive for the whole run, blocking reads and writes New copy of table plus indexes The table must stay online
REINDEX CONCURRENTLY Short locks, writes continue New copy of each index Heap bloat is the real problem
Online rewrite extension Brief exclusive lock at the final swap About one full copy, plus change capture The table has no primary key or usable unique index, or disk is tight

A failed REINDEX CONCURRENTLY leaves behind an invalid index. You must drop that index and run the reindex again. Online rewrites also add write load from capturing changes while the copy runs.

Reclaiming space is pointless if you haven't fixed the cause. If autovacuum still triggers late, is throttled too hard, or is held back by an old snapshot, the rewritten table bloats back to the same size.

Run a monitoring loop instead of repeated manual rewrites. On a schedule, record these values per table:

  • Dead tuple ratio: n_dead_tup against n_live_tup from pg_stat_user_tables.
  • Autovacuum frequency: changes in autovacuum_count between samples.
  • Autovacuum duration: run times from log_autovacuum_min_duration output.
  • Oldest transaction and snapshot age: taken from pg_stat_activity.

Compare the trends after each tuning change. A dead tuple ratio that stays flat confirms the settings work. A rising ratio points you back to the trigger, throttling, or blocker problems covered in earlier sections.

How can autovacuum tuning save engineering time?

Most of the time cost of bloat comes from reacting to it rather than preventing it. Getting autovacuum to keep up on the hot tables removes several kinds of recurring work:

  • Fewer manual rewrites. A table that autovacuum keeps clean does not need a VACUUM FULL or online rewrite. That removes the planning, maintenance windows, disk-space checks and post-rewrite verification each one requires.
  • Fewer emergency anti-wraparound events. Routine vacuums that keep pages frozen let the forced vacuum finish quickly. Without them, the team may have to deal with a long, lock-holding vacuum on a large table in production.
  • Faster diagnosis. The three-way split into not starting, too slow, or blocked tells you which fix applies. Engineers stop trying worker counts and cost limits against a problem that is actually an idle-in-transaction session or an abandoned slot.
  • Less performance firefighting. Bloated tables and indexes show up as slow scans and cache pressure. Those incidents are often investigated as query or capacity problems before anyone checks dead tuples.
  • Reviewable, repeatable configuration. Per-table settings stored in migrations, plus the pg_class.reloptions query, mean the next engineer can see what was changed and why without reverse-engineering it.

Illustrative estimate. Assume a team currently rewrites two bloated tables once a month, and each rewrite takes an assumed four engineer-hours for planning, execution and verification. Also assume one bloat-related performance investigation per month at an assumed six engineer-hours. That is about 14 engineer-hours a month. If per-table tuning and a monitoring loop take an assumed one or two days to set up and remove most of that recurring work, the effort pays back within the first couple of months. Your own inputs will differ, so plug in your actual rewrite frequency and incident load.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres autovacuum tuning, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.