When a table keeps growing, queries slow down and n_dead_tup (the count of dead tuples, old row versions left by updates and deletes) stays high, the first reaction is to tune autovacuum: more workers, a lower scale factor, a higher cost limit. Often autovacuum is already running. It is just not allowed to remove anything, because some transaction might still need the old row versions. No setting fixes that. You have to find the transaction.

The lab for this page used PostgreSQL 18.6 in Docker with generated data. The script and full output are public.

What the lab showed

A 200,000-row accounts table, 27 MB. One session, named report-export, opened a REPEATABLE READ transaction, ran one SELECT and then did nothing. Every row was then updated three times. Twenty seconds later:

 relname  | n_live_tup | n_dead_tup | autovacuum_count | autovacuumed | table_size
----------+------------+------------+------------------+--------------+------------
 accounts |     200000 |     600000 |                4 | t            | 108 MB

Autovacuum had run four times. The table had quadrupled. The autovacuum log (with log_autovacuum_min_duration = 0) says why:

LOG:  automatic vacuum of table "postgres.public.accounts": index scans: 0
	pages: 0 removed, 13794 remain, 13794 scanned (100.00% of total), 0 eagerly scanned
	tuples: 0 removed, 800000 remain, 600000 are dead but not yet removable
	removable cutoff: 756, which was 15 XIDs old when operation ended

"dead but not yet removable" is the line to look for. Vacuum scanned every page, did the I/O, and could remove nothing, because the oldest snapshot still open in the cluster (the xmin horizon) was older than all 600,000 dead versions. A manual VACUUM printed the same line. In production this is worse than in the lab: autovacuum keeps returning to the same table, spending I/O each time, while the table and its indexes keep growing.

Find what holds the horizon

Four things can hold the xmin horizon back. Check all four, oldest first:

-- 1. Open transactions and sessions idle in transaction
SELECT pid, usename, application_name, state, age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_open_for, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY age(backend_xmin) DESC;

-- 2. Forgotten prepared transactions
SELECT gid, owner, prepared, age(transaction) AS xmin_age FROM pg_prepared_xacts;

-- 3. Replication slots (physical slots with hot_standby_feedback, logical slots for CDC)
SELECT slot_name, slot_type, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

-- 4. Standbys sending hot_standby_feedback
SELECT application_name, client_addr, age(backend_xmin) AS xmin_age FROM pg_stat_replication;

In the lab, the first query found it straight away:

 pid | application_name |        state        | xmin_age |  xact_open_for  |           last_query
-----+------------------+---------------------+----------+-----------------+--------------------------------
  71 | report-export    | idle in transaction |       15 | 00:00:24.102925 | SELECT count(*) FROM accounts;

The xmin_age is small in a quiet lab. On a busy system it is the number of transactions since that snapshot was taken, and xact_open_for is often hours. last_query is the last statement the session ran, not what it is doing now; for an idle session that is usually enough to find the owning code path. The usual culprits:

  • Idle in transaction: an application that opens a transaction, then waits on an HTTP call, a queue or a user, or an ORM session that is never closed. Also a person with a SQL client in manual-commit mode.
  • Long reports and exports on the primary, especially with REPEATABLE READ for a consistent export.
  • Replicas with hot_standby_feedback = on: a long query on the replica holds back vacuum on the primary. It appears in pg_stat_replication.backend_xmin, not in the primary's pg_stat_activity.
  • Abandoned slots: a logical slot for a CDC tool that was turned off holds catalog_xmin, which blocks cleanup of the system catalogs.

End it, then let vacuum work

Confirm with the owner first; terminating a session rolls back its open transaction. Then:

SELECT pg_terminate_backend(71);

On RDS and Aurora, rds_superuser members can terminate other users' sessions. The next autovacuum run in the lab removed everything:

	tuples: 600000 removed, 200000 remain, 0 are dead but not yet removable
	removable cutoff: 771, which was 0 XIDs old when operation ended

To stop it happening again, set a timeout for the roles that should never sit idle in a transaction, rather than for the whole cluster:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

Choose the value from the application's real transaction lengths, and tell the team first: when it fires, the session is terminated and the application sees a closed connection.

Why the table is still 108 MB

After the dead rows were removed, the table was the same size:

 n_live_tup | n_dead_tup | table_size
------------+------------+------------
     200000 |          0 | 108 MB

 tuple_count | dead_tuple_count | free_percent
-------------+------------------+--------------
      200000 |                0 |         74.9

That is normal. VACUUM marks the space as free for new rows in the same table; it returns space to the operating system only when pages at the end of the table become completely empty. Three more full-table updates, each followed by a vacuum, stayed at 108 MB: the free space was reused. So a bloated table is not always a problem. If the workload refills it, the free space is working space.

It becomes a problem when the bloat is permanent and large: sequential scans read 4 times the pages, fewer useful rows fit in cache, and backups and replicas carry the empty space. Then the options are:

  • VACUUM FULL rewrote the lab table back to 27 MB in 0.9 seconds. It holds an ACCESS EXCLUSIVE lock for the whole rewrite, blocking reads as well as writes, and needs disk space for a full copy. On a large production table that is an outage.
  • pg_repack rebuilds the table online and takes the exclusive lock only briefly at the start and end. It is available on RDS and Aurora. It still needs space for a full copy and generates WAL for the whole table, which replicas and CDC consumers have to process.

Measure before choosing. pgstattuple gives exact free space but reads the whole table; pgstattuple_approx is cheaper on large tables.

When autovacuum is just too slow

If nothing is holding the horizon and n_dead_tup still climbs, autovacuum is not starting often enough or not finishing fast enough. By default a table is vacuumed after 50 rows plus 20% of its rows change:

 relname  | reltuples | dead_rows_to_trigger
----------+-----------+----------------------
 accounts |    200000 |                40050

For a 500-million-row table that is 100 million dead rows before autovacuum starts. PostgreSQL 18 caps the trigger at autovacuum_vacuum_max_threshold, 100 million by default; earlier versions have no cap. For large, busy tables set the threshold per table instead of changing it everywhere:

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

If autovacuum starts on time but runs for hours, look at cost throttling (autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay) and at autovacuum_work_mem for tables with many indexes. Check what is running now in pg_stat_progress_vacuum.

Checklist

  1. Turn on autovacuum logging: log_autovacuum_min_duration, in a custom parameter group on RDS and Aurora.
  2. Look for "dead but not yet removable" in the log or in VACUUM (VERBOSE).
  3. If present, run the four horizon queries and find the owner of the oldest entry.
  4. If absent, compare the trigger point with the table's size and the time each autovacuum takes.
  5. Rewrite a table only if the bloat stays and you have measured what it costs.

The same blockers also stop vacuum from freezing old rows, which is how a database gets close to transaction ID wraparound. To see which statements suffer from bloat, rank them with pg_stat_statements.