Postgres Dead Tuples Not Being Removed: Fixes (2026)

When Postgres dead tuples are not being removed, the usual reason is that VACUUM ran and found nothing it was allowed to delete. It removes only row versions older than the xmin horizon, which four documented things hold back: open transactions, prepared transactions, replication slots, and standby feedback.

TL;DR

VACUUM does not decide what is garbage by looking at your table. It computes a cutoff transaction ID and removes only row versions that were deleted before it. PostgreSQL's own recovery procedure names the things that drag that cutoff backwards, and it names three of them in one place: prepared transactions in pg_prepared_xacts, long-running transactions in pg_stat_activity, and replication slots in pg_replication_slots.¹ A fourth, hot_standby_feedback, is documented separately, and its own description states that it "can cause database bloat on the primary for some workloads."²

So with Postgres dead tuples not being removed, the useful question is not "why won't autovacuum clean my table" but "which transaction ID is my cutoff, and who owns it." One VACUUM (VERBOSE) answers the first half in a single line. Four short catalog queries answer the second. That is the cheapest branch to rule out — but rule it out rather than assume it, because the other branch, where autovacuum is not reaching the table at all, has entirely different fixes and gets three sections of its own below.

This guide works in that order: what the phrase means, whether vacuum ran at all, who is holding the horizon, why autovacuum might not be reaching the table, how to reclaim space, how to prevent it, and what happens if you do not.

Version scope. Everything below is written and verified against the PostgreSQL 18 documentation line, which as of this writing describes 18.6.¹ The mechanism — MVCC, the xmin horizon, and the four holders — is the same on every supported release. Specific parameters, view columns and log-line formats are not: several used here were added in 17 or 18 and are marked inline. If you are on 14, 15, 16 or 17, check the corresponding page of your own version's documentation before copying a query or a setting.

What you'll learn

• What the phrase "dead but not yet removable" in VACUUM output actually means, and how to read it alongside the removable cutoff line
• How to confirm autovacuum ran at all, and what it did, using the table statistics views and pg_stat_progress_vacuum
• The four-query sweep that identifies which session, slot, or standby is holding the xmin horizon
• Why an idle session inside an open transaction bloats tables even when it holds no locks
• How a forgotten replication slot stops cleanup, how to tell whether it is really the culprit, and what dropping the wrong one costs
• Why hot_standby_feedback trades query cancellations on the replica for bloat on the primary
• Why prepared transactions are the one horizon holder that no timeout will clean up for you
• How the autovacuum trigger threshold is computed, and what PostgreSQL 18's new cap changes for large tables
• Why autovacuum can start on a table and never finish, which routine command can prevent it forever, and how index cleanup gets skipped on purpose
• Which tables autovacuum never touches at all
• Why the table file does not shrink even when the dead rows genuinely were removed
• When VACUUM FULL is the right call, and what the alternatives cost
• What the "tuples missed … cleanup lock contention" line means, and why it is a different problem
• Which timeouts reduce the risk, which holder none of them can touch, and why lock_timeout belongs in the list
• What happens if you leave it: the wraparound warnings, the hard stop, and the documented recovery order

What does "dead but not yet removable" mean in Postgres?

It means VACUUM found dead row versions and was not permitted to delete them, because a transaction that might still need to see them has not finished. Those rows stay on disk and keep counting toward n_dead_tup until the horizon advances past them.

This is a direct consequence of MVCC. As the documentation puts it, "an UPDATE or DELETE of a row does not immediately remove the old version of the row … the row version must not be deleted while it is still potentially visible to other transactions."¹ Visibility is decided by transaction ID comparison, so VACUUM computes one cutoff XID for the whole operation and applies it uniformly. Anything deleted by a transaction newer than that cutoff survives the pass, no matter how obviously garbage it looks to you.

PostgreSQL prints both halves of that story. In PostgreSQL 18 the summary block written by VACUUM (VERBOSE) — and by autovacuum, when log_autovacuum_min_duration makes it report¹ — contains a tuple line and a cutoff line:

INFO:  vacuuming "app.public.events"
INFO:  finished vacuuming "app.public.events": index scans: 0
pages: 0 removed, 1842317 remain, 1842317 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 41231334 remain, 8842190 are dead but not yet removable
removable cutoff: 2839471104, which was 41 XIDs old when operation ended

These are PostgreSQL 18's line formats, taken from the source that emits them.³ Earlier releases print a similar but not identical block — the eagerly scanned field, for instance, accompanies 18's eager-freezing work — so compare against your own version's output rather than assuming the shapes match.

Read the two important lines together. 0 removed with a large "dead but not yet removable" count is the signature of a held horizon. The cutoff line names the exact XID that VACUUM used and how far behind the current transaction counter it was when the operation ended — the phrasing comes straight from the vacuum code path that emits it.³ An age of a few dozen XIDs is healthy. An age in the millions means something has been sitting on that cutoff for a long time, and the next section is how you find it.

The wording is worth internalising because it is what you will grep for. Of VERBOSE, the documentation says only that it "Prints a detailed vacuum activity report for each table at INFO level."⁴ At INFO level: "dead but not yet removable" is not an error and not a warning. It is vacuum correctly refusing to destroy data that someone might still read.

Why is n_dead_tup not decreasing after VACUUM?

Because n_dead_tup counts dead row versions that still exist, and a vacuum that removed nothing removes nothing from the counter. Before tuning anything, confirm whether vacuum is running and failing to remove rows, or not running at all — those have completely different fixes.

pg_stat_all_tables, and the pg_stat_user_tables subset of it, answers this. On PostgreSQL 18 the view carries the dead-tuple count, the timestamps of the last manual and automatic passes, the counts of each, and — new in this release — cumulative time totals for both:⁵

SELECT relname,
       n_live_tup,
       n_dead_tup,
       n_ins_since_vacuum,
       last_vacuum,
       last_autovacuum,
       vacuum_count,
       autovacuum_count,
       total_autovacuum_time
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

total_vacuum_time and total_autovacuum_time do not exist in PostgreSQL 17's definition of this view; they were added for 18.⁶ On 14 through 17, drop those two columns from the query.

One scoping note. The documentation describes pg_stat_user_tables as "Same as pg_stat_all_tables, except that only user tables are shown" — so it hides system catalogs and TOAST relations.⁷ A wide table's out-of-line values live in their own TOAST relation, which has its own statistics row. Plain VACUUM processes it along with the parent by default — that is what the PROCESS_TOAST option controls⁴ — but its autovacuum settings are configured separately from the parent's. If the heap looks clean but the total relation size keeps climbing, run the same query against pg_stat_all_tables and select relid::regclass so the schema is visible.

Three readings, three different conclusions:

To see a pass in flight, pg_stat_progress_vacuum has one row per backend currently vacuuming, "including autovacuum worker processes."⁸ It reports the current phase, heap blocks scanned and vacuumed, and how many index vacuum cycles have completed:

SELECT p.pid,
       p.relid::regclass AS table_name,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total,
       p.index_vacuum_count,
       a.query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);

One caveat that surprises people: VACUUM FULL does not appear here at all. Because VACUUM FULL and CLUSTER both rewrite the table rather than modify it in place, their progress is reported through pg_stat_progress_cluster instead, whose command column reads either CLUSTER or VACUUM FULL.⁸

What is the xmin horizon and how do I find what is holding it?

The xmin horizon is the oldest transaction ID that still needs to be able to see old row versions. VACUUM will not remove a row version deleted by a transaction newer than it. Four catalog queries cover every documented holder; run all four, because more than one can be in play.

Run them as a superuser or as a member of pg_read_all_stats. The statistics views are security restricted: "Ordinary users can only see all the information about their own sessions … In rows about other sessions, many columns will be null."⁷ Connected as your application role, you will see a reassuring column of NULLs while the culprit sits in the same table.

PostgreSQL's own procedure for recovering from transaction ID exhaustion is, in effect, a checklist of horizon holders, and it lists them in this order: resolve old prepared transactions found in pg_prepared_xacts; end long-running open transactions found in pg_stat_activity; drop old replication slots found in pg_replication_slots.¹ The fourth, standby feedback, is documented with the replication settings.² Note that this is the docs' ordering for a recovery runbook, not a ranking by how often each one bites you — no source ranks them, and neither will this guide.

1. Open transactions and their snapshots. pg_stat_activity.backend_xmin is documented, tersely and exactly, as "the current backend's xmin horizon"; backend_xid is the "top-level transaction identifier of this backend, if any."⁷ Check both, which is what PostgreSQL's own recovery procedure tells you to do — it says to look for rows "where age(backend_xid) or age(backend_xmin) is large."¹ A read-only session can hold the horizon through its snapshot with no XID at all, and a writing session can hold it through its XID; neither has to be idle. A six-hour report that is actively running counts just as much as a session someone abandoned.

SELECT pid,
       datname,
       usename,
       state,
       age(backend_xid)  AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration,
       left(query, 80)   AS query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL
   OR backend_xmin IS NOT NULL
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC;

Sort by age, not by duration — a session that has been open for an hour but only took its snapshot a minute ago is harmless, while a REPEATABLE READ transaction that grabbed a snapshot at the start of a four-hour report is not. Two filters worth adding in a busy cluster: exclude autovacuum workers and walsenders by checking backend_type, and remember that the query as written spans every database in the cluster, so the oldest row may belong to a database that is not the one you are investigating.

2. Prepared transactions. pg_prepared_xacts holds one row per two-phase-commit transaction awaiting resolution, with the XID, the global ID, and when it was prepared. An entry disappears only when the transaction is committed or rolled back.⁹

SELECT gid, database, owner, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

3. Replication slots. The xmin column of pg_replication_slots is described as "the oldest transaction that this slot needs the database to retain. VACUUM cannot remove tuples deleted by any later transaction." catalog_xmin says the same thing for system catalog tuples.¹⁰

SELECT slot_name, slot_type, database, active, active_pid,
       age(xmin)         AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age,
       restart_lsn,
       wal_status,
       inactive_since,        -- PostgreSQL 17+
       invalidation_reason    -- PostgreSQL 18+
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

Two columns there are version-dependent: PostgreSQL 16's pg_replication_slots has neither inactive_since nor invalidation_reason — it exposes conflicting instead — so drop those lines on 16 and earlier or the query errors out.¹¹

Read the result carefully before you act on it. A slot is only a horizon holder if xmin or catalog_xmin is non-null. catalog_xmin on its own is narrower than it looks: it is the oldest transaction "affecting the system catalogs" that the slot needs retained, and VACUUM "cannot remove catalog tuples deleted by any later transaction" — catalog tuples, not your table's rows.¹⁰ A slot with both columns null is not blocking cleanup at all; if its restart_lsn is far behind, it is retaining WAL, which is a disk-space problem with a different fix.

4. Standby feedback. On the primary, pg_stat_replication.backend_xmin is "this standby's xmin horizon reported by hot_standby_feedback."⁷ A non-null, ageing value here means a query on a replica is holding cleanup on the primary:

SELECT application_name, client_addr, state,
       age(backend_xmin) AS standby_xmin_age
FROM pg_stat_replication
ORDER BY age(backend_xmin) DESC NULLS LAST;

Whichever query returns the largest age, compare it against the removable cutoff age from the vacuum output. They should tell the same story. If the largest holder's age is roughly the cutoff's age, you have found your answer.

Does an idle in transaction session block VACUUM in Postgres?

Yes — and the documentation says so in the description of the timeout that exists to kill such sessions. What matters is that the transaction is open, not that it is doing anything, and not that it holds locks.

The wording is unusually direct: "Even when no significant locks are held, an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat."¹²

This is the failure mode most often produced by application code rather than by a DBA. An ORM that opens a transaction at the start of a web request and holds it across an external HTTP call; a connection pool handing out a session with an uncommitted BEGIN still on it; an interactive psql window somebody left open after typing BEGIN;. None of these look like database problems, and none of them show up as lock contention.

Find them with the pg_stat_activity query above, filtered on state:

SELECT pid, usename, application_name, client_addr, backend_type,
       now() - state_change AS idle_for,
       age(backend_xid)     AS xid_age,
       age(backend_xmin)    AS xmin_age
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC NULLS LAST;

Select both age columns, not just backend_xmin. Depending on isolation level and on whether the transaction has written anything, one of the two can be null while the other is what is actually pinning cleanup — which is why the documented procedure checks age(backend_xid) or age(backend_xmin) rather than either alone.¹

To clear one, SELECT pg_terminate_backend(pid); — the documented remedy, which the wraparound recovery procedure names explicitly for exactly this case.¹ Two things to know before you run it. Terminating the backend rolls back its transaction, so be sure the work in it is genuinely abandoned. And it is not the right tool for every row you found in the sweep: if the standby behind a walsender PID from the fourth query is using a replication slot, terminating that walsender need not release anything, because the slot keeps its own retained xmin in the catalog — which is exactly why the docs describe slots for "servers that no longer exist" as still holding cleanup back.¹¹⁰ The durable fix is a timeout, covered below.

Can a replication slot stop VACUUM from removing dead rows?

Yes, and this version of the problem can persist indefinitely, because a slot needs no session, no process, and no network connection to keep holding the horizon. It just sits in the catalog.

An inactive slot retains its xmin, and VACUUM cannot remove tuples deleted after it.¹⁰ The documentation's recovery guidance is blunt about the usual cause: "In many cases, such slots were created for replication to servers that no longer exist, or that have been down for a long time."¹ A decommissioned replica, a logical subscriber that was torn down without dropping its slot, a change-data-capture pipeline that was switched off — each can leave a slot behind that goes on holding the horizon with nothing on the running system to point at it.

Before dropping anything, work through three checks. Is xmin or catalog_xmin actually non-null — that is, is this slot a horizon holder at all? Is active false and active_pid null? And is the consumer genuinely gone, rather than merely disconnected? The docs attach a real consequence to guessing wrong: "If you drop a slot for a server that still exists and might still try to connect to that slot, that replica may need to be rebuilt."¹ For a logical slot the equivalent loss is the subscriber's position, which usually means a full re-copy. Dropping a slot is not reversible, so treat it as the last step rather than the first. When you are sure:

SELECT pg_drop_replication_slot('slot_name_here');

PostgreSQL 18 can take a slot out of the picture without your dropping it. idle_replication_slot_timeout, added in that release, invalidates — not drops — slots that have been inactive longer than the configured duration; it defaults to 0, meaning disabled. Invalidation happens at checkpoint time rather than the instant the threshold passes, so there is lag between exceeding the timeout and the slot actually being invalidated — force a checkpoint if you need it sooner. The duration is measured from the slot's inactive_since value, and the mechanism does not apply to slots that reserve no WAL or to standby slots synced from a primary.² Once a slot is invalidated, pg_replication_slots.invalidation_reason records why, with idle_timeout being the value for this case.¹⁰

One difference worth knowing if you are chasing multixact rather than transaction ID age: "Unlike transaction ID wraparound, replication slots do not directly hold back multixact cleanup."¹

Does hot_standby_feedback cause bloat on the primary?

It can, and that trade-off is documented rather than incidental. hot_standby_feedback tells the primary about queries running on a standby so that cleanup records do not cancel them. The price is that the primary defers the cleanup instead.

The parameter description states the bargain in one sentence: it "can be used to eliminate query cancels caused by cleanup records, but can cause database bloat on the primary for some workloads." It defaults to off.² If you turned it on to stop replicas throwing conflict cancellations at your analytics queries, you moved the problem rather than removing it, and pg_stat_replication.backend_xmin on the primary is where the new problem shows up.⁷

Three details that shape how it behaves in practice, all from the same page:²

• Feedback is sent no more often than once per wal_receiver_status_interval, which defaults to 10 seconds — so the primary's view of a standby's horizon is always slightly stale.
• Under cascading replication, feedback is passed upstream until it reaches the primary; intermediate standbys "make no other use of feedback they receive other than to pass upstream."
• Combining it with recovery_min_apply_delay compounds the effect: "hot_standby_feedback will be delayed by use of this feature which could lead to bloat on the primary; use both together with care."

There is also a clock-skew failure mode that is easy to misdiagnose as a vacuum bug: "if the clock on standby is moved ahead or backward, the feedback message might not be sent at the required interval. In extreme cases, this can lead to a prolonged risk of not removing dead rows on the primary for extended periods, as the feedback mechanism is based on timestamps."² If your standbys are not on synchronised time, fix that before tuning autovacuum.

The alternatives are to leave feedback off and raise max_standby_streaming_delay and max_standby_archive_delay (both default to 30 seconds, and -1 lets the standby wait forever for conflicting queries), or to move long analytical queries off the replica entirely.² Either way, this is a scheduling decision, not a vacuum setting.

Do prepared transactions block VACUUM?

Yes — the documentation says so directly — and they are the horizon holder that will still be there tomorrow, because none of the session timeouts apply to them.

The PREPARE TRANSACTION page carries a Caution that names the consequence outright: "It is unwise to leave transactions in the prepared state for a long time. This will interfere with the ability of VACUUM to reclaim storage … Keep in mind also that the transaction continues to hold whatever locks it held."¹³

A prepared transaction is a two-phase-commit transaction that has been prepared but not yet committed or rolled back; at that point "the transaction is no longer associated with the current session; instead, its state is fully stored on disk."¹³ pg_prepared_xacts shows one row for each, and the row "is removed when the transaction is committed or rolled back" — there is no other exit.⁹ If a distributed transaction coordinator crashed mid-protocol, or somebody experimented with PREPARE TRANSACTION and never finished, the XID stays pinned indefinitely.

The critical asymmetry is stated as a note on transaction_timeout: "Prepared transactions are not subject to this timeout."¹² Neither idle_in_transaction_session_timeout nor statement_timeout reaches them either, because there is no session left to terminate. pg_terminate_backend has nothing to terminate. The documented exit is to finish the transaction:

-- Inspect first; the gid identifies the transaction to the coordinator.
SELECT gid, database, owner, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

-- Then resolve each one, in agreement with whatever coordinated it.
ROLLBACK PREPARED 'the_gid_here';
-- or
COMMIT PREPARED 'the_gid_here';

Do not roll one back on autopilot. A prepared transaction exists precisely because some coordinator was told it would be honoured; resolving it unilaterally in the wrong direction is how a distributed system loses consistency. If you do not run two-phase commit at all, take the documentation's own advice: "If you have not set up an external transaction manager to track prepared transactions and ensure they get closed out promptly, it is best to keep the prepared-transaction feature disabled by setting max_prepared_transactions to zero. This will prevent accidental creation of prepared transactions that might then be forgotten and eventually cause problems."¹³

Why does autovacuum not run on my table at all?

Because the table has not crossed the trigger threshold, or because autovacuum cannot see it. The threshold is a formula, not a fixed number, and on a large table it can be startlingly high.

PostgreSQL 18 states the calculation as:¹

vacuum threshold = Minimum(vacuum max threshold,
                           vacuum base threshold + vacuum scale factor * number of tuples)

with the defaults being autovacuum_vacuum_threshold = 50 tuples, autovacuum_vacuum_scale_factor = 0.2 (20% of table size), and autovacuum_vacuum_max_threshold = 100,000,000 tuples. Setting the max threshold to -1 removes the cap. The number of tuples term is pg_class.reltuples.¹⁴

Work it through. A 50-million-row table needs 50 + 0.2 × 50,000,000 = 10,000,050 dead tuples before autovacuum considers it. A billion-row table would, without a cap, need 50 + 0.2 × 1,000,000,000 = 200,000,050. The autovacuum_vacuum_max_threshold parameter was added in PostgreSQL 18 and caps that at 100,000,000 — roughly halving the trigger point on that table, and capping it flat for anything larger.¹⁴ On PostgreSQL 14 through 17 there is no cap, which is why per-table storage parameters on big hot tables have been standard advice for years:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000
);

Per-table storage parameters take precedence: "If a setting has been changed via a table's storage parameters, that value is used when processing that table; otherwise the global settings are used."¹ That cuts both ways — check that nobody set autovacuum_enabled = false on the table years ago:

SELECT relnamespace::regnamespace AS schema,
       relname,
       relkind,
       reloptions
FROM pg_class
WHERE reloptions::text ~ 'autovacuum'
  AND relkind IN ('r', 'm', 'p', 't');

That filter keeps fillfactor and other unrelated options out of the way, and including relkind = 't' surfaces TOAST relations, whose autovacuum options are set separately from the parent's.

Also verify the daemon itself. autovacuum is on by default, but it additionally requires track_counts to be enabled, since the trigger logic reads the cumulative statistics system.¹⁴ There is one exception to everything above: "even when this parameter is disabled, the system will launch autovacuum processes if necessary to prevent transaction ID wraparound."¹⁴

Which tables does autovacuum never touch?

Two categories, both documented, and both easy to overlook because nothing about the tables themselves looks unusual.

Temporary tables. "Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via session SQL commands."¹ A long-lived session that creates a temp table and churns it — a batch job, a worker process that reuses its connection for hours — accumulates dead tuples that only an explicit VACUUM in that same session can remove.

Partitioned parent tables. "Partitioned tables do not directly store tuples and consequently are not processed by autovacuum. (Autovacuum does process table partitions just like other tables.)"¹ The partitions themselves are vacuumed normally, so this is not a bloat problem — it is a statistics problem, because autoanalyze does not run on the parent either, and the docs recommend running ANALYZE manually on partitioned tables when first populated and whenever the data distribution shifts.¹ The same applies to inheritance parents: "Tuples changed in partitions and inheritance children do not trigger analyze on the parent table."¹

Foreign tables are a third case for ANALYZE specifically — the daemon does not issue ANALYZE for them "since it has no means of determining how often that might be useful."¹

Why does autovacuum start but never finish?

Because something keeps cancelling it, because a single pass genuinely takes longer than the interval between the events that trigger it, or because it is finishing while deliberately skipping the index work. The first two show up as an autovacuum_count that barely moves while n_dead_tup grows; the third shows a healthy autovacuum_count and dead line pointers that never go away.

Lock cancellation. Autovacuum holds a SHARE UPDATE EXCLUSIVE lock. "If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum."¹ That is normally a good thing — it stops maintenance from blocking DDL. It becomes pathological when the conflicting command runs on a schedule, which the documentation flags as an explicit warning: "Regularly running commands that acquire locks conflicting with a SHARE UPDATE EXCLUSIVE lock (e.g., ANALYZE) can effectively prevent autovacuums from ever completing."¹ A cron job that runs ANALYZE on a big table every fifteen minutes, against a vacuum that needs twenty, will starve that table indefinitely.

There is one autovacuum that does not yield: "if the autovacuum is running to prevent transaction ID wraparound (i.e., the autovacuum query name in the pg_stat_activity view ends with (to prevent wraparound)), the autovacuum is not automatically interrupted."¹ Seeing that suffix means the situation has already escalated.

Repeated index passes. In pg_stat_progress_vacuum, an index_vacuum_count above 1 is the documented signature of a pass that could not hold all its dead item identifiers in memory and had to cycle back. The vacuuming indexes phase "may happen multiple times per vacuum if maintenance_work_mem (or, in the case of autovacuum, autovacuum_work_mem if set) is insufficient to store the number of dead tuples found."⁸ Each cycle rescans every index on the table. The documentation points at the memory setting as the constraint, so raising autovacuum_work_mem is the first thing to try — but check your release's own limits on how much of it vacuum can actually use before setting a very large value.

Index cleanup switched off. VACUUM's INDEX_CLEANUP option can be set to OFF to "force VACUUM to always skip index vacuuming, even when there are many dead tuples in the table," and the same choice is available as a per-table storage parameter. The consequence is spelled out: "If index cleanup is not performed regularly, performance may suffer, because as the table is modified indexes will accumulate dead tuples and the table itself will accumulate dead line pointers that cannot be removed until index cleanup is completed."⁴ A table left in that state accumulates something that looks exactly like the problem this guide is about, and no horizon query will explain it — so check the table's reloptions for it alongside autovacuum_enabled.

The wraparound failsafe does the same thing on purpose. INDEX_CLEANUP "has no effect on the transaction ID wraparound failsafe mechanism. When triggered it will skip index vacuuming, even when INDEX_CLEANUP is set to ON."⁴ So a cluster that has been running near vacuum_failsafe_age — 1.6 billion transactions by default¹⁴ — may be completing vacuums that deliberately do no index work at all.

Throttling. Autovacuum sleeps under cost-based delay. autovacuum_vacuum_cost_delay defaults to 2 milliseconds, and autovacuum_vacuum_cost_limit defaults to -1, which falls back to vacuum_cost_limit (200).¹⁴ The limit is shared: "the value is distributed proportionally among the running autovacuum workers, if there is more than one, so that the sum of the limits for each worker does not exceed the value of this variable."¹⁴ Adding workers therefore does not add throughput unless you raise the cost limit too. On PostgreSQL 18, pg_stat_progress_vacuum.delay_time reports the milliseconds spent sleeping, when track_cost_delay_timing is on.⁸

Worker starvation. autovacuum_max_workers defaults to 3.¹⁴ "If several large tables all become eligible for vacuuming in a short amount of time, all autovacuum workers might become occupied with vacuuming those tables for a long period. This would result in other tables and databases not being vacuumed until a worker becomes available."¹ Your small, hot table can be starved by three big cold ones.

Why is my table still the same size after VACUUM?

Because that is what plain VACUUM is designed to do. It makes space reusable inside the table; it does not hand it back to the operating system.

The wording leaves no room for interpretation: "The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained."¹ That exception is governed by vacuum_truncate, a boolean defaulting to true; when it applies, "the disk space for the truncated pages is returned to the operating system," and the truncation requires an ACCESS EXCLUSIVE lock.¹⁴

This is deliberate, and the docs explain the reasoning: "the idea is not to keep tables at their minimum size, but to maintain steady-state usage of disk space: each table occupies space equivalent to its minimum size plus however much space gets used up between vacuum runs."¹ A table that settles somewhat above its theoretical minimum and then stops growing is a healthy table, not a bloated one.

So if n_dead_tup dropped to near zero and the file size did not change, vacuum worked. The space is inside the table, waiting for new rows. You only need to reclaim it if the table will not grow back into it — after a one-off mass delete, for example.

One boundary on this section: it is about the heap. If you are watching pg_total_relation_size, that figure also includes indexes and any TOAST relation. VACUUM removes dead row versions "in tables and indexes,"¹ but an index that has been through heavy churn can stay physically large even after its entries are gone, and the rewriting commands in the next section are the ones that "build new indexes" from scratch.¹ If the heap shrank and the total did not, look at the indexes rather than at vacuum.

Should I run VACUUM FULL to reclaim the space?

Only when the table genuinely will not reuse the space, and only with a maintenance window. VACUUM FULL "actively compacts tables by writing a complete new version of the table file with no dead space," which minimises size "but can take a long time."¹

Three costs, all documented:¹

1. It "requires an ACCESS EXCLUSIVE lock on the table it is working on, and therefore cannot be done in parallel with other use of the table." Reads are blocked too, not just writes.
2. "It also requires extra disk space for the new copy of the table, until the operation completes." Because the indexes are rebuilt too, budget against pg_total_relation_size, not the heap alone.
3. Autovacuum will never do it for you — the daemon "in fact will never issue VACUUM FULL."

The general guidance is to avoid it: "administrators should strive to use standard VACUUM and avoid VACUUM FULL," and "the usual goal of routine vacuuming is to do standard VACUUMs often enough to avoid needing VACUUM FULL."¹

VACUUM FULL, CLUSTER and the table-rewriting ALTER TABLE variants are documented together as equivalents in this respect: "These commands rewrite an entire new copy of the table and build new indexes for it. All these options require an ACCESS EXCLUSIVE lock."¹ If you routinely empty a table completely, TRUNCATE is the better tool: it "removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space," at the cost of violating strict MVCC semantics.¹

Two operational cautions. First, the extra disk these rewrites need covers the new indexes as well as the new heap, so on a table whose indexes rival the heap, budget against pg_total_relation_size rather than the heap size alone. Second, an ACCESS EXCLUSIVE lock has to be acquired before it can be held: if a long transaction is already on the table, the rewrite queues, and every query that arrives afterwards queues behind it. Set lock_timeout in the same session before you start — it aborts a statement that "waits longer than the specified amount of time while attempting to acquire a lock," and it defaults to disabled.¹²

SET lock_timeout = '5s';
VACUUM FULL events;

One thing to settle before any of this: if the horizon is still held, you are about to pay the full price of a rewrite while the thing that produced the bloat is still running. The table will start growing again the moment you finish. Clear the horizon holder first, run a plain VACUUM, confirm n_dead_tup actually falls, and only then decide whether you still need the space back.

What does "tuples missed … cleanup lock contention" mean?

It means vacuum reached pages it could not get a cleanup lock on — because another backend had them pinned — and skipped the dead tuples there entirely. The line only appears when the count is above zero:³

tuples missed: 812 dead from 47 pages not removed due to cleanup lock contention

This is a separate counter from "dead but not yet removable" and means something different. "Not yet removable" is a visibility decision: the rows are still needed. "Missed … cleanup lock contention" is a concurrency accident: vacuum was allowed to remove them and could not get at the page. The two lines can appear in the same summary and should be diagnosed independently.

Small counts here are unremarkable on a busy table, and the next pass has another chance at those pages. Persistently large counts suggest pages under near-constant pin — a very hot single row, or a query pattern that keeps a cursor open on one. The lever here is the query pattern rather than a vacuum parameter: the constraint is another backend's pin on the page, not anything vacuum is configured to do.

How do I stop dead tuples piling up again?

With timeouts, with the per-table autovacuum storage parameters from the threshold section above, and with monitoring that alerts on horizon age rather than on dead-tuple count. Every timeout below defaults to disabled, so none of this is on unless you turn it on.

No single setting here prevents the whole problem, and the table's right-hand column is the reason. Prepared transactions escape all of the session timeouts. Slots are not sessions. And an actively running long query is reachable only by statement_timeout or transaction_timeout, both of which have to be set low enough to bite — which is also low enough to kill the legitimate reports you meant to keep.

transaction_timeout, added in PostgreSQL 17, is the broadest of the session-level three: it terminates "any session that spans longer than the specified amount of time in a transaction," and "the limit applies both to explicit transactions (started with BEGIN) and to an implicitly started transaction corresponding to a single statement."¹² Note the interaction, which is easy to get backwards: "If transaction_timeout is shorter or equal to idle_in_transaction_session_timeout or statement_timeout then the longer timeout is ignored."¹²

For statement_timeout, transaction_timeout and lock_timeout the docs say plainly that setting them in postgresql.conf "is not recommended because it would affect all sessions."¹² Apply those per role or per application instead. (idle_replication_slot_timeout is the exception in the other direction: it "can only be set in the postgresql.conf file or on the server command line."²)

ALTER ROLE web_app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE reporting SET statement_timeout = '30min';

-- PostgreSQL 17 and later only:
ALTER ROLE web_app SET transaction_timeout = '5min';

idle_session_timeout needs extra care in a pooled environment: "Be wary of enforcing this timeout on connections made through connection-pooling software or other middleware, as such a layer may not react well to unexpected connection closure."¹² If you front Postgres with a pooler, see our guide to production Postgres connection pooling with PgBouncer and Supavisor before you set it.

For monitoring, alert on numbers that lead the problem rather than trail it: the maximum of age(backend_xid) and age(backend_xmin) in pg_stat_activity, age(xmin) and age(catalog_xmin) in pg_replication_slots, age(backend_xmin) in pg_stat_replication, age(transaction) in pg_prepared_xacts — that last one especially, since it is the holder no timeout will ever clear for you — and age(relfrozenxid) per table. Dead-tuple counts tell you a problem already happened. Set log_autovacuum_min_duration low enough to capture the passes you care about — it is what makes autovacuum write the same summary block that VACUUM (VERBOSE) prints, so the cutoff line lands in your server log on every pass that qualifies.¹ Check the value your release ships with before assuming it is off.

What happens if I ignore it?

Cleanup stops being a disk-space question and becomes an availability one. When rows cannot be removed or frozen, relfrozenxid stops advancing, and PostgreSQL escalates on a documented schedule.

The requirement is absolute: "it is necessary to vacuum every table in every database at least once every two billion transactions."¹ Track how close you are with the query the docs themselves supply:¹

SELECT c.oid::regclass as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

SELECT datname, age(datfrozenxid) FROM pg_database;

The first escalation arrives when "the database's oldest XIDs reach forty million transactions from the wraparound point," at which point the server starts logging:¹

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

Ignore that and "the system will refuse to assign new XIDs once there are fewer than three million transactions left until wraparound":¹

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"
HINT:  Execute a database-wide VACUUM in that database.

In that state, transactions already in progress continue and read-only transactions can start, but anything that modifies records or truncates relations fails. VACUUM still runs normally. The documented recovery order is the same horizon sweep this guide has been describing, applied under pressure: resolve prepared transactions, end long-running transactions, drop stale replication slots, then run a database-wide VACUUM.¹

Two instructions in that procedure are easy to get wrong. "Do not use VACUUM FULL in this scenario, because it requires an XID and will therefore fail, except in super-user mode, where it will instead consume an XID and thus increase the risk of transaction ID wraparound. Do not use VACUUM FREEZE either, because it will do more than the minimum amount of work required to restore normal operation."¹ And contrary to older advice still circulating, stopping the postmaster is no longer part of the answer: "In typical scenarios, this is no longer necessary, and should be avoided whenever possible, since it involves taking the system down."¹

Bottom line

Postgres dead tuples not being removed is often not a vacuum configuration problem at all. It is a visibility problem wearing a vacuum costume, and the horizon is worth ruling out first because it is the cheapest thing to check.

Do it in this order. Confirm from pg_stat_user_tables whether vacuum is running on that table at all — if last_autovacuum is null or stale, start with the threshold and cancellation sections, because a horizon holder is not what is stopping a vacuum that never ran. If it is running, run VACUUM (VERBOSE), read the removable cutoff line, and compare its age against all four horizon queries as a superuser or pg_read_all_stats member. In many cases one of them will be obviously, embarrassingly old — a psql window, a slot for a replica that was retired last quarter, a coordinator that crashed mid-two-phase-commit.

Fix the holder, run a plain VACUUM, and confirm n_dead_tup falls. Only then decide whether you also need the disk space back, and remember that plain VACUUM was never going to give it to you. Finally, set idle_in_transaction_session_timeout per role — plus transaction_timeout if you are on PostgreSQL 17 or later — so the same session cannot do it again, and alert on horizon age rather than dead-tuple count.

For related reading: high-churn queue tables are the classic bloat generator, and pg-boss on Postgres shows what that workload looks like in practice. If mass deletes are what pushed you here, automating partition management with pg_partman and pg_cron replaces them with partition drops that need no vacuum at all. And if the slot holding your horizon belongs to a logical replication setup, our walkthrough of zero-downtime upgrades with pg_createsubscriber covers how those slots get created and cleaned up.