Dead Tuple Accumulation Detected — Quick Fix Guide

1. Autovacuum Isn't Running Aggressively Enough

This is the number one reason dead tuples pile up. PostgreSQL depends on autovacuum to clean out old row versions (dead tuples). By default, autovacuum waits until 20% of the table is dead tuples before it kicks in. On busy tables, that threshold is way too high.

You'll see the warning in pg_stat_user_tables when n_dead_tup keeps climbing even after autovacuum has run. The real clue? Your query performance drops slowly over hours, not all at once.

What to do: Tune autovacuum for the problem table. Don't change the global settings unless all your tables are misbehaving. Here's a targeted fix:

ALTER TABLE your_table_name SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE your_table_name SET (autovacuum_vacuum_threshold = 50);

This makes autovacuum run when 1% of the table (or 50 rows, whichever comes first) is dead. After applying, run VACUUM your_table_name; manually once to clean the backlog. Then watch n_dead_tup in pg_stat_user_tables — it should stay under 1000 for most tables after a day or two.

If the table is huge (over 10GB), also drop autovacuum_vacuum_cost_limit default. It's set to -1 by default which means it inherits from the global 200. Set it to 1000 so vacuum doesn't throttle:

ALTER TABLE your_table_name SET (autovacuum_vacuum_cost_limit = 1000);

This one change fixes 80% of dead tuple problems. You'll see a difference in 10 minutes.

2. A Long-Running Transaction Is Blocking Cleanup

Sometimes autovacuum is set correctly, but dead tuples still grow. The hidden culprit is a transaction that's been open for hours. PostgreSQL has to keep old row versions around for that transaction to read consistent snapshots. If you have an idle transaction open for 3 hours, no dead tuples in tables it touched can be fully cleaned.

Check for this with:

SELECT pid, state, query_start, state_change, wait_event, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND query_start < now() - interval '10 minutes'
ORDER BY query_start;

If you see queries that started more than 10 minutes ago and they're not doing any real work (just sitting idle in transaction), that's your problem. The application code forgot to commit or rollback.

The fix: Terminate the hanging transaction. First, check if it's safe to kill (read-only queries can be killed without issues):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '30 minutes'
  AND pid <> pg_backend_pid();

After killing it, run VACUUM your_table_name; again. You should see n_dead_tup drop right away. To prevent this from coming back, set idle_in_transaction_session_timeout in postgresql.conf to something like 5 minutes:

idle_in_transaction_session_timeout = '5min'

Then reload with pg_ctl reload or SELECT pg_reload_conf();. This kills any transaction that stays idle too long. It's a lifesaver for busy production systems.

3. Stale Replication Slots Holding Dead Tuples

This one's less common but brutal when it hits. If you're using streaming replication (standby servers) and one replica falls behind or disconnects, PostgreSQL keeps dead tuples alive because the replica might still need them. Even if autovacuum runs, it can't remove dead tuples that the stale slot is holding.

Check your replication slots:

SELECT slot_name, slot_type, active, restart_lsn, catalog_xmin
FROM pg_replication_slots;

If you see a slot where active is false (it's inactive) and catalog_xmin is far behind the current transaction ID, that slot is the reason dead tuples are stuck. The catalog_xmin shows the oldest transaction that slot still thinks is active.

What to do: Remove the stale slot. Only do this if you're sure that replica is gone for good (like a failed server that was replaced):

SELECT pg_drop_replication_slot('your_slot_name_here');

After dropping, run VACUUM FULL your_table_name; if the table is heavily bloated, or just a regular VACUUM if performance is okay. The dead tuples will finally be cleaned.

To prevent this in the future, monitor replication lag with pg_stat_replication and set up alerts when a slot is inactive for more than an hour. Or use max_replication_slots carefully — don't create slots you don't need.

Quick-Reference Summary Table

Dead tuple accumulation is a pain, but these three fixes cover 95% of cases. Start with autovacuum tuning — that's the one people miss most often. If that doesn't work, check for hanging transactions or stale slots. Don't let this warning sit. The longer you wait, the more bloat builds up, and you end up needing a VACUUM FULL that locks the whole table.