If your table keeps growing while the row count stays flat, autovacuum is probably running fine and still removing nothing — because something is holding an old snapshot open. Check pg_stat_activity and pg_replication_slots before you touch a single autovacuum setting. Only after you've ruled out blockers does tuning thresholds and cost limits make any difference.
I lost most of a day to this once. A table with a steady ~2 million rows had grown well past what its data should occupy, sequential scans were creeping, and pg_stat_user_tables showed last_autovacuum updating every few minutes. Autovacuum was doing its job on schedule and accomplishing nothing, because a reporting connection had been sitting idle in transaction since the previous deploy.
Why "autovacuum ran" and "dead rows were removed" are different things
Postgres uses MVCC: an UPDATE writes a new row version and leaves the old one in place, and a DELETE just marks the old one dead. Vacuum reclaims those dead versions — but only the ones no live snapshot could still need. The cutoff is the oldest transaction anywhere in the system that might still look backwards.
That means vacuum's effectiveness is capped by the oldest snapshot on the instance, not by how often it runs. A vacuum that runs every minute against a table protected by a two-hour-old transaction will remove nothing for two hours, and it will report success every time.
Since Postgres 16, VACUUM VERBOSE and the autovacuum log line tell you this directly — the output includes how many dead tuples were left behind because they weren't yet removable. That line is the fastest honest signal you have.
Takeaway: autovacuum frequency is irrelevant if the removable cutoff isn't advancing.
How do I tell if autovacuum is falling behind?
Start with the tables themselves:
SELECT relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;
A dead ratio that climbs across repeated samples is the actual symptom. A high ratio that holds steady on a write-heavy table is often normal.
Then check whether anything is pinning the cutoff:
-- Oldest transactions and the snapshot they hold
SELECT pid, state, backend_xmin,
now() - xact_start AS xact_age,
left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_age DESC NULLS LAST
LIMIT 10;
-- Replication slots pinning old rows (including forgotten ones)
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
-- Prepared (two-phase) transactions nobody committed
SELECT gid, prepared, owner FROM pg_prepared_xacts;
An inactive replication slot is the sneakiest of the three: it holds the cutoff indefinitely, produces no query to blame in pg_stat_activity, and also grows WAL until the disk fills. A slot left behind by a decommissioned read replica or an abandoned CDC pipeline will quietly do both.
Takeaway: three queries — activity, slots, prepared transactions — explain the large majority of "vacuum runs but bloat grows" cases.
What actually blocks dead tuple removal?
| Blocker | How you spot it | Fix |
|---|---|---|
| Long-running query |
xact_age large, state = 'active'
|
Optimize or cap it; set statement_timeout
|
idle in transaction |
state = 'idle in transaction' |
idle_in_transaction_session_timeout; fix the client's commit path |
| Inactive replication slot | pg_replication_slots.active = false |
Drop the slot if the consumer is gone |
hot_standby_feedback = on on a replica |
Replica running long reports | Trade-off: query cancels vs. primary bloat |
| Prepared transaction | Row in pg_prepared_xacts
|
ROLLBACK PREPARED; audit the XA client |
| Autovacuum genuinely too slow | Blockers clean, n_dead_tup still climbing |
Tune thresholds and cost limits (below) |
Only the last row is a tuning problem. The rest are application or topology problems that tuning will not fix.
Which autovacuum settings are worth changing?
The defaults are deliberately conservative and scale-based, and that's exactly where large tables lose. With autovacuum_vacuum_scale_factor at 0.2, a 50-million-row table waits for roughly 10 million dead rows before autovacuum even considers it. That threshold is fine for a 10,000-row table and absurd for a large one.
Set it per table rather than globally:
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 5000,
autovacuum_analyze_scale_factor = 0.02
);
If autovacuum starts often enough but never finishes before more garbage arrives, the constraint is throughput, not eligibility. Autovacuum sleeps according to autovacuum_vacuum_cost_delay (2ms by default since Postgres 12) once it has burned through its cost budget. On modern NVMe storage that pacing is usually far more cautious than the hardware needs:
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '1ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;
ALTER SYSTEM SET autovacuum_max_workers = 5; -- requires restart
SELECT pg_reload_conf();
Raise the cost limit gradually and watch I/O. Autovacuum competing with peak traffic for the same disk is a real failure mode, just a less common one than autovacuum being throttled into irrelevance.
Two more worth knowing: maintenance_work_mem bounds how much dead-tuple state a vacuum can hold, and running out of it forces extra index scan passes over the same table. Postgres 17 reworked that storage so the old 1 GB effective ceiling no longer applies, which is a genuine reason to prioritize that upgrade on bloat-prone databases. And autovacuum_vacuum_insert_scale_factor (Postgres 13+) covers insert-only tables, which otherwise accumulate unfrozen pages and never get vacuumed on the dead-tuple path at all.
Takeaway: per-table thresholds for eligibility, cost delay and limit for throughput — they fix different failures and are not interchangeable.
What do I do about "database must be vacuumed within N transactions"?
This warning means transaction ID age is approaching the wraparound limit. If it keeps climbing, Postgres eventually refuses writes with database is not accepting commands to avoid wraparound data loss, and recovery requires single-user mode. Treat the warning as a page, not a log line.
Find the offenders by age:
SELECT c.relname,
age(c.relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY xid_age DESC
LIMIT 10;
Compare against autovacuum_freeze_max_age (200 million by default). Then do the same blocker hunt — an anti-wraparound autovacuum is subject to the same cutoff as any other vacuum, so an open transaction stalls the thing keeping you online. Anti-wraparound vacuums also refuse to yield to lock requests the way ordinary autovacuum does, so a VACUUM (FREEZE) you kick off manually during a quiet window is often the calmer path than letting one start during peak traffic.
Takeaway: wraparound warnings are a deadline, and the fix is almost always removing the snapshot holder, not vacuuming harder.
The bloat is already there — now what?
Vacuum makes space reusable inside the table; it rarely returns it to the filesystem. To actually shrink files you need a rewrite.
VACUUM FULL rewrites the table compactly but takes an ACCESS EXCLUSIVE lock for the entire operation, so every reader and writer blocks until it finishes. It's the right call for a table you can take offline and the wrong call for anything on the request path.
If you need the rewrite without the outage, pg_repack does it online by maintaining a shadow copy and swapping at the end, requiring only a brief exclusive lock — at the cost of needing roughly double the table's disk space during the operation and a superuser-installed extension, which some managed providers don't offer. For measuring bloat honestly rather than estimating it from statistics, the pgstattuple extension scans the table and reports real free space, and that full scan is heavy enough that you should run it off-peak.
FAQ
How do I know if autovacuum is running right now?
Query pg_stat_progress_vacuum, which shows each active vacuum's phase, heap blocks scanned, and index vacuum count. If a vacuum has a high index_vacuum_count, it's making multiple index passes and needs more maintenance_work_mem.
Does VACUUM lock the table?
Ordinary VACUUM and autovacuum take a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue normally; they only conflict with schema changes and other vacuums. VACUUM FULL is different — it takes ACCESS EXCLUSIVE and blocks everything for the duration.
Why is n_dead_tup high right after a vacuum finished?
Either the dead rows weren't removable yet because an older snapshot still exists, or the counter is simply stale — pg_stat_user_tables values are estimates updated by the stats collector. Check the vacuum's own log output for how many tuples it reported as not yet removable.
Bottom line
Diagnose before you tune: look at pg_stat_activity, pg_replication_slots, and pg_prepared_xacts first, because a held snapshot makes every autovacuum setting irrelevant. If those are clean and dead tuples still accumulate, lower the scale factor per table for eligibility and lower the cost delay for throughput. Treat wraparound warnings as an incident with a deadline. And if you're already carrying bloat you can't afford an outage to remove, pg_repack is the standard answer — assuming your provider lets you install it.
Top comments (1)
The "autovacuum ran" vs "dead rows were removed" distinction is the one I wish more runbooks had. The oldest-snapshot cutoff framing explains a lot of confusing dashboards — vacuum graphs look healthy while the table keeps growing, and nobody thinks to check what's pinning xmin because vacuum "is fine".
From an ops standpoint, the two timeout settings have done more for me than any threshold tuning:
idle_in_transaction_session_timeoutcatches the worker that died mid-batch still holding its transaction open, andstatement_timeoutkeeps the ad-hoc reporting query from becoming the next incident. Both are cheap to set defensively, and they remove entire classes of blockers instead of tuning around them.Your table lists
hot_standby_feedback = onas a blocker but doesn't say much about the trade-off. In your experience, is the realistic answer for a reporting workload a dedicated replica with feedback off (accepting the query cancellations), or do people get decent results tuningmax_standby_streaming_delayand living with the occasional bloat contribution on the primary? Asking because it's the one row of that table where the fix has a visible user-facing cost.