Skip to content

Why is my table bloated when autovacuum runs?

Last updated

View as Markdown

Quick answer: Measure before you tune. The dead row counters are estimates and can read backwards, so decompose the size first, then read backend_xmin and the xmin of every replication slot. A held horizon and a cancelled autovacuum both look like “autovacuum is not keeping up” and neither is fixed by a scale factor.

Every number on this page was run, and nearly all of it came from one container, so you can tell which claims are about PostgreSQL and which are about a particular table.

serverPostgreSQL 18.6, image postgres:18.6
containerdbg-pgbloat, Docker on an 8 vCPU host
settingsdefaults, except wal_level = logical so logical slots could be tested
standbydbg-pgbloat-r, same image, streaming from that primary, hot_standby_feedback = on
date2026-09-20

Some measurements needed settings no default server has, and they are labelled where they appear. They ran in a second container, dbg-pgwrap, same PostgreSQL 18.6 image, same day, 2026-09-20, with autovacuum_naptime = 5s so a run could be watched, and autovacuum_freeze_max_age = 100000 so wraparound could be reached in a minute rather than in months.

One re-run is labelled dbg-pgbloat2, a third container on the same PostgreSQL 18.6 image on the same day, 2026-09-20. It is the count of rows released when the logical slot was dropped, in the replication slot section below.

PostgreSQL never updates a row in place. An UPDATE writes a new version and marks the old one dead; a DELETE just marks it dead. Vacuum removes them.

Three different things get called bloat, and confusing them is why tuning often changes nothing:

  1. Dead rows vacuum is not allowed to remove. Something is holding the cleanup horizon, and no vacuum setting overrides that.
  2. Free space inside the file. Vacuum made it reusable and left it there. If the table has reached a steady state this is not a problem to fix.
  3. Space that is not in the heap. Indexes and TOAST are usually most of a large table, and the usual diagnostic query does not look at them.

Sort out which one you have before changing anything.

The common shorthand is that vacuum never returns space to the operating system. That is nearly always true. The exception matters because it makes disk usage look non-deterministic.

The PostgreSQL 18 documentation states the exception directly:

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 sentence is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

Deleting the newest rows is one way to trigger the exception. Three 400,000 row tables, identical except for what was deleted and one storage setting, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20, in bytes as pg_relation_size reported them:

tablewhat was deletedheap beforeheap after plain VACUUM
t_tailthe newest 200,000 rows99,303,42449,651,712
t_midevery second row99,303,42499,303,424
t_notrunc, vacuum_truncate = offthe newest 200,000 rows99,303,42499,303,424

Half the file went back to the operating system in the first row and none did in the second, from the same command on the same server. The third row is the same deletion as the first with truncation turned off at the table level, which shows that the difference was truncation rather than luck.

The index did not move in any of the three: 9,003,008 bytes before and after, same tables, same PostgreSQL 18.6 container, 2026-09-20. Vacuum removes index entries; it does not give index space back.

In this order. Each step is cheaper and less certain than the one after it, and the common mistake is to stop at step two and start tuning.

Before anything else, find out what the space is:

SELECT c.relname,
pg_size_pretty(pg_relation_size(c.oid)) AS heap,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
pg_size_pretty(coalesce(pg_total_relation_size(c.reltoastrelid), 0)) AS toast,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

A 20,000 row table with one incompressible text column per row, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20, measured 1,048,576 bytes of heap, 466,944 bytes of indexes and 165,724,160 bytes of TOAST. The heap is under one percent of it. Deleting half the rows and running VACUUM left all three numbers unchanged on that same server and day, and pg_stat_user_tables reported n_dead_tup of 0 throughout.

If the heap is small and the total is large, no amount of autovacuum tuning is the answer, and the question becomes which index or which TOAST table.

2. Use the counters to choose where to look, not to conclude

Section titled “2. Use the counters to choose where to look, not to conclude”
SELECT relname,
n_live_tup,
n_dead_tup,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

The PostgreSQL 18 documentation describes n_live_tup as:

Estimated number of live rows

That is from The Cumulative Statistics System in the PostgreSQL 18 documentation, read 2026-09-20. The word that matters is “estimated”.

On PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20, two tables named ca and cb were each built with 400,000 rows and each had its newest 200,000 rows deleted and then a plain VACUUM run, the only difference being where the two statements ran. Both tables were polled every 500 ms for eight seconds afterwards on that same PostgreSQL 18.6 container on 2026-09-20, and both were read with pgstattuple at the end:

tableDELETE and VACUUMpg_stat_user_tablespgstattuple
casame session0 live, 200,000 dead200,000 live, 0 dead
cbseparate sessions198,413 live, 0 dead200,000 live, 0 dead

Both files measured 23,412,736 bytes on that same PostgreSQL 18.6 container, dbg-pgbloat, on 2026-09-20. Physically they are the same table twice.

In the ca row, pg_stat_user_tables reported zero live rows and two hundred thousand dead ones for a table that had just been vacuumed clean. The reading was stable: every one of the seventeen samples across eight seconds gave the same answer. That is the reading the usual advice interprets as “vacuum runs and cannot win”.

What decides it is the session, not timing. A backend accumulates its statistics and flushes them, so when the DELETE and the VACUUM run in one session the delete’s tally can be applied after the vacuum’s, leaving the counters describing a table state that no longer exists. Run them from separate sessions and the order comes out right. The obvious explanation, that the counters are briefly inconsistent while the two statements race each other, is wrong: polling every 500 ms on that PostgreSQL 18.6 container, dbg-pgbloat, on 2026-09-20 showed the reading stable rather than transient.

Treat these counters as a way to pick which table to investigate and never as evidence about that table.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT tuple_count,
dead_tuple_count,
round(dead_tuple_percent::numeric, 2) AS dead_tuple_percent,
round(free_percent::numeric, 2) AS free_percent
FROM pgstattuple('orders');

It reads every page, so it is the slow and correct answer. Run it on the tables step 2 pointed at, off-peak or on a replica.

Free space is what it separates out. A 200,000 row table with every second row deleted and vacuumed, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20, reported 0 dead tuples and 44.58 percent free, with the heap still at 7,249,920 bytes. Nothing is wrong with that table and nothing needs tuning, because the space will be reused.

For indexes, pgstatindex answers the same question. The primary key of that table, same server and day, reported an avg_leaf_density of 45.10 with its size unchanged at 4,513,792 bytes.

SELECT p.pid,
p.phase,
p.heap_blks_scanned,
p.heap_blks_total,
p.dead_tuple_bytes,
p.max_dead_tuple_bytes,
left(a.query, 40) AS query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);

For runs that have finished, set log_autovacuum_min_duration and read the log. At 0 it logs every autovacuum. A cancelled autovacuum is logged as an ERROR whatever this is set to, which is how the cancellation below is caught.

This step is often skipped, and it decides whether tuning can help at all. Vacuum may remove a dead row only when no transaction could still need it. Whatever sets that limit blocks cleanup in other tables too, not only its own: a session’s snapshot holds back every table in its database.

xact_start is not the field. Age alone is not either. Three sessions, all open, all reported as idle in transaction with the same transaction age, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20:

sessionisolationstatements run so farbackend_xmin
Aread committedBEGIN onlynull
Bread committedBEGIN, one SELECTnull
Crepeatable readBEGIN, one SELECT782

With all three open, VACUUM removed none of the 50,000 dead rows and reported removable cutoff: 782. Closing only session C and repeating the same command on the same server removed all 50,000, while A and B stayed open. The field that predicted it was backend_xmin, not xact_start and not the state string.

So read the horizon itself:

SELECT pid,
state,
backend_xmin,
age(backend_xmin) AS xmin_age,
now() - xact_start AS xact_age,
left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

A long active query holds the horizon exactly as an idle transaction does. An idle in transaction session with a null backend_xmin is a lock problem and a connection problem, but it is not this problem.

6. Read the slots by their xmin, not their active flag

Section titled “6. Read the slots by their xmin, not their active flag”
SELECT slot_name,
slot_type,
active,
xmin,
age(xmin) AS xmin_age,
catalog_xmin,
age(catalog_xmin) AS catalog_xmin_age,
restart_lsn,
wal_status
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

The PostgreSQL 18 documentation names those two fields for this:

Drop any old replication slots. Use pg_replication_slots to find slots where age(xmin) or age(catalog_xmin) is large.

That is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

“Inactive slot” is not a diagnosis on its own. Three inactive slots gave three different answers, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20:

inactive slotxmincatalog_xminwhat it blocked
physical, created and never usednullnullnothing: 50,000 dead rows removed normally
logical, created and never readnull7562,000 dead rows in pg_class, none in the user table
physical, left behind by a standby that had used hot_standby_feedback831null20,000 dead rows in the user table

The logical slot pinned the removable cutoff at 756 for pg_class and dropping it released almost all of those rows immediately, same server and day.

The shortfall looks like rows left behind, but it is not. Re-run on PostgreSQL 18.6 in container dbg-pgbloat2, 2026-09-20, with the slot holding 2,000 dead rows in pg_class: the vacuum after the drop removed 1,997 and reported none still dead, and a third vacuum with nothing else changed removed none. Three rows are unaccounted for, and this page does not know where they went: a vacuum that had left work behind would have found it on the third pass, and the third pass found nothing. Expect a catalog vacuum to remove slightly fewer rows than were dead when you counted them, and do not read the difference as a held horizon. The third slot held the user table’s cutoff at 831 with the standby shut down and no walsender connected, and dropping it released all 20,000.

Check the fields rather than the active flag: a slot whose xmin or catalog_xmin is set holds a horizon whether or not anything is connected to it, and a slot with both null does not.

A replica running long reports can hold the primary’s horizon. The PostgreSQL 18 documentation says so about hot_standby_feedback:

If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat.

That sentence is from Hot Standby in the PostgreSQL 18 documentation, read 2026-09-20.

Where it shows up on the primary depends on whether the standby uses a slot. One standby, one repeatable read query held open on it, hot_standby_feedback = on throughout, PostgreSQL 18.6 in containers dbg-pgbloat and dbg-pgbloat-r, 2026-09-20:

standby configurationpg_stat_replication.backend_xminpg_replication_slots.xmindead rows vacuum could remove
streaming through slot standby1null8300 of 50,000
streaming with no slot831not applicable50,000 of 50,000, and 0 of 10,000 newer ones

Read both:

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

Prepared transactions are the last holder, and they survive restarts:

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

Everything above is about vacuum not being allowed to remove rows. This section is about vacuum not getting to them.

Autovacuum takes a SHARE UPDATE EXCLUSIVE lock, and unless it is running to prevent wraparound it yields to anything that conflicts. The PostgreSQL 18 documentation states the consequence:

Regularly running commands that acquire locks conflicting with a SHARE UPDATE EXCLUSIVE lock (e.g., ANALYZE) can effectively prevent autovacuums from ever completing.

That is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

What that looks like on the server, measured in container dbg-pgwrap, PostgreSQL 18.6 with autovacuum_naptime = 5s, 2026-09-20: a 300,000 row table with 150,000 dead rows, autovacuum running, and one LOCK TABLE ... IN ACCESS EXCLUSIVE MODE from another session. The lock was granted at once. On that same dbg-pgwrap container, PostgreSQL 18.6, 2026-09-20, pg_stat_progress_vacuum was sampled just before taking the lock and read 376 of 12,500 blocks scanned, while the log line below names block 596, which is where it had reached by the time it was killed. The two numbers are a before and an after rather than a disagreement:

ERROR: canceling autovacuum task
CONTEXT: while scanning block 596 of relation "public.churn"
automatic vacuum of table "postgres.public.churn"

Afterwards, on that same server and day, pg_stat_all_tables reported n_dead_tup of 150,000, autovacuum_count of 0 and last_autovacuum null.

That combination is misleading. A null last_autovacuum usually gets read as never triggered, so lower the scale factor, and here autovacuum had been triggered, had started, and had been thrown off. Only the log distinguishes them, and the cancellation is logged as an ERROR at the default settings.

The update and delete path in PostgreSQL 18 is a capped threshold, not an uncapped one:

SELECT c.relname,
c.reltuples::bigint AS estimated_rows,
least(current_setting('autovacuum_vacuum_max_threshold')::bigint,
current_setting('autovacuum_vacuum_threshold')::bigint
+ current_setting('autovacuum_vacuum_scale_factor')::numeric * c.reltuples
)::bigint AS dead_rows_before_autovacuum
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY c.reltuples DESC
LIMIT 20;

Read out of a default PostgreSQL 18.6 server in container dbg-pgbloat on 2026-09-20: autovacuum_vacuum_threshold 50, autovacuum_vacuum_scale_factor 0.2, autovacuum_vacuum_max_threshold 100000000. The cap only changes the answer once the threshold and the scale factor together would exceed it. A 100,000,000 row table is below that line and waits for 20,000,050 dead rows, which is the threshold of 50 on top of the scale factor’s 20,000,000. A 1,000,000,000 row table is above it and waits for 100,000,000 rather than the 200,000,050 the threshold and scale factor would otherwise give between them, and that is the difference the cap makes. reltuples is itself an estimate, so treat the trigger point as approximate in both directions.

There are two other ways in, and neither counts dead rows.

Inserts trigger vacuum too. autovacuum_vacuum_insert_threshold is 1000 and autovacuum_vacuum_insert_scale_factor is 0.2 on a default PostgreSQL 18.6 server, read from container dbg-pgbloat on 2026-09-20. A table that had 5,000 rows inserted and nothing updated or deleted was autovacuumed within 25 seconds in container dbg-pgwrap, same PostgreSQL 18.6 and day, with n_dead_tup of 0 the whole time.

Freezing is a separate job with its own trigger. In container dbg-pgwrap, PostgreSQL 18.6 with autovacuum_freeze_max_age set to 100000, 2026-09-20, a table created with autovacuum_enabled = off reached a relfrozenxid age of 150,002 and was vacuumed anyway:

LOG: automatic aggressive vacuum to prevent wraparound of table "postgres.public.frozen_off": index scans: 0

Its age went to 1 on that same server and day. Aggressive vacuum activity on a table nobody deletes from is this, not a cleanup problem:

SELECT c.relname,
age(c.relfrozenxid) AS xid_age,
current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

It is throttled, or there are not enough workers

Section titled “It is throttled, or there are not enough workers”

Autovacuum pauses to stay out of the way, governed by autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay. Raising the cost limit lets each run do more work before it pauses. Do it deliberately and watch the input and output load, because the throttle exists for a reason.

Throttling is one cause of a slow run and not the only one. Lock cancellation, index cleanup, aggressive freezing and the rate the workload creates dead rows all bound it too, which is why the log is worth more than the setting.

The worker limit is per cluster, not per database. The PostgreSQL 18 documentation says:

A maximum of autovacuum_max_workers worker processes are allowed to run at the same time.

That sentence is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

On a default PostgreSQL 18.6 server in container dbg-pgbloat, read 2026-09-20, autovacuum_max_workers is 3 and autovacuum_worker_slots is 16. The second one is new in PostgreSQL 18 and is the pool the first draws from, so raising autovacuum_max_workers above autovacuum_worker_slots does not get you more workers. A large table count is not by itself evidence of a backlog: eligibility, worker saturation and the durations in the log are.

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('autovacuum_vacuum_cost_limit', 'autovacuum_vacuum_cost_delay',
'autovacuum_max_workers', 'autovacuum_naptime',
'log_autovacuum_min_duration')
ORDER BY name;

The workload creates more dead rows than it needs to

Section titled “The workload creates more dead rows than it needs to”

An update that changes no indexed column and finds room on its own page is a HOT update: it leaves no new index entry and its dead version can be cleaned up without touching an index. Two tables, identical columns, identical indexes, identical fillfactor = 70, 20,000 rows each, 200,000 updates each, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20. The only difference is whether the updated column has an index on it:

tablecolumn updatedHOT updatesheap afterindexes beforeindexes after
hot_unindexed_colunindexed62,384 of 200,00010,010,624622,5922,981,888
hot_indexed_colindexed0 of 200,00010,010,624622,5924,595,712

The heap came out the same. The index side did not, on that same PostgreSQL 18.6 container on 2026-09-20: 2,981,888 bytes against 4,595,712 bytes. Dropping an index nothing queries, or not updating a column that is indexed, is a durable fix where a lower scale factor is a workaround.

fillfactor is the other half of it. On that same PostgreSQL 18.6 container on 2026-09-20, a table packed at the default 100 and then updated 200,000 times recorded 0 HOT updates, against 62,384 for the identical workload at fillfactor = 70. Free space on the page is what makes a HOT update possible, which is also why the first pass over a freshly loaded table is the worst case.

SELECT relname,
n_tup_upd,
n_tup_hot_upd,
round(n_tup_hot_upd * 100.0 / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 20;

Temporary tables. The PostgreSQL 18 documentation states it:

Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze operations should be performed via session SQL commands.

That is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

Measured on PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20: a temporary table with 100,000 rows and half of them deleted still reported 50,000 dead rows and a null last_autovacuum several minutes later, holding 3,629,056 bytes. A session that churns temporary tables has to vacuum them itself.

Partitioned parents, for ANALYZE. The documentation again:

Unfortunately, this means that autovacuum does not run ANALYZE on partitioned tables, and this can cause suboptimal plans for queries that reference partitioned table statistics.

That sentence is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

On PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20, both partitions of a two-partition table were autovacuumed and autoanalyzed within three naptimes, and the parent had a null last_autoanalyze and a reltuples of -1, meaning it had never been analyzed. The partitions are vacuumed; the parent’s statistics are your job.

Match the remedy to what the steps above told you.

what you foundwhat to do
dead rows with a horizon holderremove the holder. Nothing else helps.
dead rows, no holder, autovacuum cancelled in the logstop the conflicting command, or schedule it
dead rows, no holder, threshold not reachedlower the scale factor on that table
free space, table at steady statenothing. It will be reused.
heap capacity you need returned to the diskVACUUM FULL or CLUSTER in a window, or pg_repack
index bloat with a healthy heapREINDEX CONCURRENTLY that index
the whole table is emptied periodicallyTRUNCATE instead of DELETE

That last row is the documentation’s own advice:

If you have a table whose entire contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM.

That sentence is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.

VACUUM FULL orders;

It rewrites the table and rebuilds every index, under an ACCESS EXCLUSIVE lock for the whole operation. One table, PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20:

heapindexestotalindex relfilenode
before10,010,6244,595,71214,647,29616470
after VACUUM FULL1,277,952622,5921,908,73616477

The changed relfilenode is the index rebuild, and the new copy exists alongside the old one until the command finishes, which is where the disk requirement comes from. On a large table that is both an outage and a free-space requirement, which is why it is a poor emergency response.

Plain VACUUM takes a much weaker lock. On that same PostgreSQL 18.6 container on 2026-09-20, a vacuum of a 2,000,000 row table held only a ShareUpdateExclusiveLock, and while it ran another session read all 2,000,000 rows and updated one of them. pg_repack reaches the VACUUM FULL result through that weaker lock, taking a brief exclusive lock only at setup and at the swap, at the cost of an extension and disk room for the second copy.

The pattern is the long-lived transaction. An agent asked to process records in batches will open one transaction, loop over everything, and commit at the end, because that is atomic and correct. It is also a transaction that holds a snapshot for the length of the job.

The related pattern is opening a transaction and then waiting on something outside the database: an HTTP call, a queue, a rate limiter. The transaction lasts as long as the slowest external thing.

The third is a generated schema that indexes every column. Measured above on PostgreSQL 18.6 in container dbg-pgbloat, 2026-09-20, updating an indexed column instead of an unindexed one took the HOT update count from 62,384 to 0 over 200,000 updates. An index nobody queries still costs that on every write.

None of the three shows up in development, where the job finishes in a second and the table has a thousand rows.

Set idle_in_transaction_session_timeout so an abandoned transaction ends itself:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

Read the log, and set log_autovacuum_min_duration. A cancelled autovacuum and an autovacuum that never ran look identical in pg_stat_user_tables, which was measured above; the log’s canceling autovacuum task line tells them apart.

Monitor backend_xmin age and slot xmin age, not dead tuple counts. Those two predict bloat before it appears, and they are not estimates.

Tune autovacuum per table on your largest tables. The global defaults are chosen for small tables and are not a statement about what yours need. 0.01 is a starting point to validate against your churn rate, not a cure.

Partition tables that age out. Dropping a partition removes the rows and the file together, with no row-by-row delete, though it takes an ACCESS EXCLUSIVE lock on the parent table.

Drop replication slots whose xmin or catalog_xmin is set and whose consumer is gone. Check what consumes a slot before dropping it: a subscriber that comes back to a dropped slot has to be rebuilt.

  • Why is my table still bloated when autovacuum runs?

    Three different things wear that name, and they have different fixes: dead rows vacuum is not allowed to remove, free space vacuum made reusable and left in the file, and space that is not in the heap at all. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgbloat, a 20,000 row table of incompressible text measured 1,048,576 bytes of heap and 165,724,160 bytes of TOAST, so a query that reads only pg_relation_size sees under one percent of it. Decompose the size first, then look for what is holding the cleanup horizon.

  • Can I trust n_live_tup and n_dead_tup?

    Only as a place to start looking. They are estimates applied asynchronously, and they can read backwards. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgbloat, a table whose DELETE and VACUUM ran in one session reported 0 live rows and 200,000 dead in pg_stat_user_tables, stable across seventeen samples over eight seconds, while pgstattuple read the same 23,412,736 byte file as 200,000 live and 0 dead. A second table given the identical treatment from separate sessions reported 198,413 live and 0 dead. Physical counts came from pgstattuple both times.

  • Which field tells me what is holding the cleanup horizon?

    backend_xmin in pg_stat_activity, and xmin or catalog_xmin in pg_replication_slots. Not xact_start and not the slot's active flag. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgbloat, two sessions sitting idle in transaction with a null backend_xmin did not stop vacuum removing 50,000 dead rows, and one repeatable read session with backend_xmin set blocked every one of them.

  • Does an inactive replication slot cause bloat?

    Only if its xmin is set. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgbloat, a freshly created inactive physical slot had a null xmin and blocked nothing, and an inactive logical slot held catalog_xmin, which blocked 2,000 dead rows in pg_class while a user table cleaned up normally. An inactive slot left behind by a standby that had used hot_standby_feedback held xmin and blocked 20,000 dead rows until it was dropped.

  • Does VACUUM FULL fix bloat?

    It does, and it takes an ACCESS EXCLUSIVE lock for the whole rewrite, rebuilds every index, and needs room for the new copy while the old one still exists. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-pgbloat, one table went from 10,010,624 bytes of heap and 4,595,712 bytes of indexes to 1,277,952 and 622,592, and the index relfilenode changed, which is the rebuild. Plain VACUUM on the same server held only a SHARE UPDATE EXCLUSIVE lock and a concurrent update succeeded while it ran.