What causes table bloat on high-update Postgres tables?

If you run Postgres tables with heavy UPDATE or DELETE traffic, such as job queues, session stores, counters, or status columns that change on every request, table bloat will eventually show up as a performance problem. The cause is how Postgres implements MVCC (multi-version concurrency control).

Postgres never overwrites a row in place. An UPDATE writes a complete new row version and marks the old one as expired. A DELETE only marks the row as expired. Old versions have to stay on disk while any running transaction might still need to see them. Once no transaction can see them, they become dead tuples: space that holds no live data but still occupies pages in the table and its indexes.

Plain VACUUM finds dead tuples and marks their space as reusable for future inserts and updates in the same table. It generally does not give that space back to the operating system. The one exception is that it can truncate completely empty pages at the end of the file. So if vacuum falls behind and the table grows, the table usually stays large after vacuum catches up.

That extra size has real costs:

  • Sequential scans read more pages.
  • Index scans touch more heap pages.
  • The working set no longer fits in shared buffers or the OS page cache.

Queries slow down even though the row count hasn't changed.

Autovacuum exists to handle this cleanup continuously. It has a second, non-negotiable job: freezing old rows to prevent transaction ID wraparound.

That second job is why setting autovacuum_enabled = false on a busy table is dangerous. Postgres still forces an anti-wraparound vacuum when the table's oldest transaction ID gets too old. That vacuum runs whether or not autovacuum is disabled on the table, and by that point it has to scan a large, bloated table, usually during peak load.

+------------------+     +-------------------------------------------+
| UPDATE / DELETE  |---->| Heap page                                 |
| heavy traffic    |     | [live v2] [dead v1] [dead v1] [live v3]   |
+------------------+     +-------------------------------------------+
                                            |
                                            v
                   +----------------------------------------------+
                   | VACUUM (plain or autovacuum)                 |
                   |  - marks dead space reusable in same table   |
                   |  - truncates only empty pages at end of file |
                   |  - space generally NOT returned to the OS    |
                   +----------------------------------------------+
   vacuum falls behind    |                               | freeze old rows
                          v                               v
          +-------------------------------+  +--------------------------+
          | Bloat: table stays large      |  | Protects against XID     |
          |  - more pages to scan         |  | wraparound               |
          |  - index scans hit more heap  |  +--------------------------+
          |  - working set exceeds cache  |               |
          |  => slower queries            |               | XID gets too old
          +-------------------------------+               v
                                             +------------------------------+
                                             | autovacuum_enabled = false   |
                                             | does not stop it: forced     |
                                             | anti-wraparound vacuum runs  |
                                             | later on a large, bloated    |
                                             | table, at a worse time       |
                                             +------------------------------+

UPDATEs and DELETEs leave dead tuples that plain VACUUM makes reusable but generally does not return to the OS, so a table that falls behind stays bloated, while disabling autovacuum still leads to a forced anti-wraparound vacuum later.

How does autovacuum decide when to run?

Autovacuum checks each table against a simple trigger. A vacuum is queued once the table's dead tuple count exceeds:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

reltuples is the planner's estimated row count. The threshold is a fixed floor, commonly 50 rows. The scale factor is a fraction of the table, commonly 0.2. On large tables the scale factor dominates the result.

Illustrative estimate. Assume a 100M-row table, a threshold of 50, and a scale factor of 0.2. Autovacuum waits until about 20,000,050 dead tuples exist. On a hot table, those 20M dead row versions take up heap pages and index entries. Queries have to skip past them, and the space can only be reused after vacuum runs. Because the trigger grows with the table, the largest and busiest tables wait the longest.

Assumed rows (reltuples) Dead tuples before trigger (threshold 50, scale 0.2)
1M ~200,050
10M ~2,000,050
100M ~20,000,050
1B ~200,000,050

All values illustrative.

Once vacuum starts, a cost-based throttle controls its speed. Each page read, page found dirty, or page written adds to a running cost. When the cost reaches autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay and then continues.

That limit is a single budget shared by all active workers. Raising autovacuum_max_workers without raising the cost limit does not speed things up. It spreads the same budget across more workers, so each individual vacuum runs slower.

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

Run these checks before changing any setting. Each cause has a different fix, and the wrong one costs a deploy cycle.

Check 1: Is autovacuum firing on the table?

Start with SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;. Compare n_dead_tup against the trigger threshold from the previous section. If dead tuples are far above it and last_autovacuum is old or null, vacuum is not being triggered. Sample autovacuum_count twice, a few minutes apart. A rising count means vacuum runs often, so look at the next two checks.

Check 2: Is a running worker throttled?

Use SELECT pid, query, wait_event_type, wait_event, xact_start FROM pg_stat_activity WHERE backend_type = 'autovacuum worker'; to see which workers are active. Join on pid to pg_stat_progress_vacuum and track phase, heap_blks_scanned and heap_blks_total. A worker that shows a VacuumDelay wait most of the time and scans blocks slowly is being held back by the cost limit.

Check 3: Does vacuum finish but leave dead tuples behind?

If autovacuum_count rises and n_dead_tup stays high right after each run, something is holding back the xmin horizon. Section 4 covers the causes.

Symptom Likely cause Where to look next
High n_dead_tup, stale last_autovacuum Not triggering Scale factor and threshold
Workers active for hours, mostly VacuumDelay Throttled Cost limit and cost delay
Frequent runs, n_dead_tup barely drops Cannot remove tuples Old transactions, replication slots
-- Check 1: is autovacuum firing? Compare dead tuples to the global trigger point.
-- (Ignores per-table reloptions; check pg_class.reloptions if you set any.)
SELECT s.relname, s.n_live_tup, s.n_dead_tup,
       round(current_setting('autovacuum_vacuum_threshold')::int
         + current_setting('autovacuum_vacuum_scale_factor')::float8
           * greatest(c.reltuples, 0)) AS trigger_at,
       s.last_autovacuum, s.autovacuum_count
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC
LIMIT 20;

-- Check 2: is a running worker throttled? Re-run with \watch 10 in psql
-- and see whether heap_blks_scanned moves while wait_event is VacuumDelay.
SELECT a.pid, p.relid::regclass AS table_name, p.phase,
       p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct_scanned,
       a.wait_event_type, a.wait_event,
       now() - a.xact_start AS running_for
FROM pg_stat_activity a
JOIN pg_stat_progress_vacuum p USING (pid)
WHERE a.backend_type = 'autovacuum worker';

-- Check 3: does vacuum finish but leave dead tuples behind?
-- List whatever is pinning the xmin horizon, oldest first.
SELECT 'backend' AS source, pid::text AS holder, age(backend_xmin) AS xmin_age
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'replication slot', slot_name::text, age(xmin)
FROM pg_replication_slots WHERE xmin IS NOT NULL
UNION ALL
SELECT 'slot catalog_xmin', slot_name::text, age(catalog_xmin)
FROM pg_replication_slots WHERE catalog_xmin IS NOT NULL
UNION ALL
SELECT 'prepared xact', gid, age(transaction)
FROM pg_prepared_xacts
ORDER BY xmin_age DESC
LIMIT 10;

Three diagnostic queries, one per cause: dead tuples versus the autovacuum trigger point, progress and wait events of running workers, and the sessions, replication slots or prepared transactions holding back the xmin horizon.

What blocks dead tuple removal, and why should you fix it before tuning?

VACUUM can only remove a dead tuple if no transaction could still see it. Postgres tracks the oldest snapshot still in use across the cluster, called the xmin horizon. Any tuple deleted or updated after that point must stay on disk. A single old snapshot therefore pins dead tuples in every table, however often autovacuum runs.

This is why you should check the horizon before you tune. When it is held back, lowering autovacuum_vacuum_scale_factor or raising cost limits only makes vacuum run more often and accomplish nothing. Engineers see busy workers and a flat n_dead_tup and conclude autovacuum is broken. More aggressive settings cannot fix this. You have to find what is holding the horizon.

The common blockers, and where to find them:

  • Long-running or idle-in-transaction sessions. In pg_stat_activity, sort by age(backend_xmin) and check state = 'idle in transaction'. Fix the application code that leaves transactions open. pg_terminate_backend(pid) clears an individual session, and idle_in_transaction_session_timeout prevents repeats.
  • Abandoned replication slots. In pg_replication_slots, look for active = false with an old xmin or catalog_xmin. Confirm that no consumer still needs the slot, then run pg_drop_replication_slot('name').
  • Orphaned prepared transactions. Check pg_prepared_xacts for old prepared timestamps. Ask the transaction manager that owns each one before you run ROLLBACK PREPARED 'gid'.
  • hot_standby_feedback. A replica running long queries reports its xmin to the primary. That value appears as backend_xmin in pg_stat_replication. Move the long queries elsewhere, or accept query cancellations on the replica.
-- 1. Sessions holding an old snapshot (long-running or idle in transaction)
SELECT pid, usename, state, age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- 2. Replication slots pinning the horizon (inactive slots are the usual culprit)
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. Prepared transactions nobody committed or rolled back
SELECT gid, owner, database, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

-- 4. Standbys reporting an old xmin via hot_standby_feedback
SELECT application_name, client_addr, age(backend_xmin) AS xmin_age
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

-- Clearing, only after confirming the owner no longer needs it:
-- SELECT pg_terminate_backend(12345);              -- stuck session
-- SELECT pg_drop_replication_slot('old_cdc_slot'); -- abandoned slot
-- ROLLBACK PREPARED 'txn_gid_example';             -- orphaned 2PC
-- Prevent repeats of idle-in-transaction sessions:
-- ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
-- SELECT pg_reload_conf();

Four queries that rank each type of xmin horizon blocker by transaction age, followed by the commented-out commands used to clear each one after you confirm it is safe.

How do you tune autovacuum per table with thresholds and cost limits?

Once nothing is holding back the xmin horizon, change the trigger for the hot tables only. Use ALTER TABLE ... SET to set autovacuum_vacuum_scale_factor to zero or close to it, and set autovacuum_vacuum_threshold to a fixed number. Autovacuum starts when dead tuples exceed threshold + scale_factor × reltuples. A proportional trigger lets large tables collect millions of dead rows before vacuum runs. A fixed trigger caps that number. Keep the global defaults for the rest of the database.

Illustrative estimate (assumed numbers, not a benchmark): take a table with 50,000,000 rows. With a scale factor of 0.2 and a threshold of 50, vacuum waits for about 10,000,050 dead tuples. With a scale factor of 0 and a threshold of 100,000, it starts at 100,000.

Vacuuming more often only helps if each run can finish. Raise autovacuum_vacuum_cost_limit for the hot table or globally. Then set autovacuum_max_workers to roughly the number of hot tables. The global cost budget is shared among the workers that are running. If you add workers without raising the limit, each worker gets a smaller share and runs slower.

These changes have costs:

  • Vacuum uses more I/O and CPU.
  • On storage that is already saturated, foreground queries get slower. Raise the settings in steps and check latency after each step.
  • A table with constant churn may end up being vacuumed almost all the time.
  • Each run scans every index on the table. On a table with large indexes, frequent runs can mean repeated full index scans that remove few rows.

Per-table settings also need maintenance. They do not appear in postgresql.conf, and they drift as workloads change. To list them, query relname, reloptions from pg_class where reloptions IS NOT NULL. Record why each override exists, and review the list on a regular schedule.

-- 1. Hot table only: trigger on an absolute dead-tuple count, not a fraction of the table.
--    Per-table cost limit lets this table's vacuum do more work before each sleep.
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold    = 100000,
  autovacuum_vacuum_cost_limit   = 2000
);

-- Record why the override exists next to the table itself.
COMMENT ON TABLE public.orders IS
  'autovacuum override: fixed 100k dead-tuple trigger for high update churn; review quarterly';

-- 2. Optional global changes. Raise the shared cost budget in steps and watch latency.
--    The budget is split across running workers, so raise it before adding workers.
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 800;
ALTER SYSTEM SET autovacuum_max_workers = 5;  -- about the number of hot tables; may need a restart, check your version
SELECT pg_reload_conf();

-- 3. Audit every per-table override. These do not show up in postgresql.conf.
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;

-- 4. Check that the overrides are doing their job.
SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 5. Drop an override once the workload no longer needs it.
ALTER TABLE public.orders RESET (autovacuum_vacuum_cost_limit);

Sets a fixed dead-tuple trigger and cost limit on one hot table, adjusts the global budget and worker count, then lists all per-table overrides from pg_class.reloptions so they can be reviewed.

What are the alternatives to more aggressive autovacuum?

Tuning makes vacuum keep up with dead tuples. The options below either produce fewer dead tuples or reclaim space that has already bloated.

Make more updates HOT

A heap-only tuple (HOT) update writes the new row version on the same page and skips creating new index entries. Pruning can then reclaim the old version without a full vacuum pass. Two conditions must hold: no indexed column changes, and the page has free space.

  • Lower fillfactor on the table so each page keeps room for new row versions.
  • Drop or avoid indexes on frequently updated columns, such as updated_at or status counters, so those updates stay HOT-eligible.

To check the effect, compare n_tup_hot_upd with n_tup_upd in pg_stat_user_tables. A rising ratio means fewer dead index entries.

Lowering fillfactor has costs. The table starts larger, and sequential scans read more pages. It only helps when updates leave indexed columns unchanged. If updates rewrite indexed columns, you pay for the extra space and gain nothing.

Reclaim existing bloat

Plain vacuum makes freed space reusable but rarely returns it to the operating system. To shrink a bloated table, you have to rewrite it.

Option Lock taken Disk needed When it fits
VACUUM FULL ACCESS EXCLUSIVE (blocks reads and writes) About a second copy of the table plus its indexes Severe bloat, and you have a maintenance window
CLUSTER ACCESS EXCLUSIVE About a second copy Same as above, when you also want rows ordered by an index
REINDEX CONCURRENTLY Does not block reads or writes A new copy of each index being rebuilt Index bloat while the table itself is healthy
Third-party online repack Mostly online, with short exclusive locks About a second copy You can't take a window and can accept the tool's added operational risk (triggers, failed runs, cleanup)

How do you measure bloat and verify that tuning worked?

Bloat estimation queries combine pg_class.relpages and reltuples with average column widths from pg_stats to compute how many pages a table should need, then compare that with how many it uses. They are cheap because they read only catalogs, but they are approximate. avg_width comes from a sample. Alignment padding is guessed. Values stored out of line in the TOAST table are not modeled well. On tables with wide text, jsonb, or bytea columns, a 30% "bloat" figure can be mostly estimation error. Use these queries to rank candidates, not to make decisions.

When you need a real number, the pgstattuple extension's pgstattuple('table_name') reports dead_tuple_percent and free_percent exactly. It reads every page, so run it on a replica or off-peak, and only on tables the estimate has flagged.

A tuning change has worked when three things hold under steady load:

  • Dead tuples stay bounded. n_dead_tup in pg_stat_user_tables rises and falls in a sawtooth rather than trending upward.
  • Vacuum runs at an acceptable rate. autovacuum_count and last_autovacuum show runs at the frequency you expect. With log_autovacuum_min_duration set, the logs show each run finishing well before the next one is due.
  • Size stays flat. pg_relation_size and pg_total_relation_size level off. Free space is being reused instead of the table growing.

Save these checks as a scheduled query or dashboard, and include pg_class.reloptions in it. Then a dropped per-table override after a migration, or a new write-heavy table on default settings, shows up as a trend line within days. Otherwise you find it during an incident.

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 route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

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.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • 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.