Why is my table bloated when autovacuum runs?
Quick answer: Measure before you tune. The dead row counters are estimates and can read backwards, so decompose the size first, then read
backend_xminand thexminof 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.
What was measured, and where
Section titled “What was measured, and where”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.
| server | PostgreSQL 18.6, image postgres:18.6 |
| container | dbg-pgbloat, Docker on an 8 vCPU host |
| settings | defaults, except wal_level = logical so logical slots could be tested |
| standby | dbg-pgbloat-r, same image, streaming from that primary, hot_standby_feedback = on |
| date | 2026-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.
What is the problem?
Section titled “What is the problem?”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:
- Dead rows vacuum is not allowed to remove. Something is holding the cleanup horizon, and no vacuum setting overrides that.
- 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.
- 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.
Vacuum does sometimes shrink the file
Section titled “Vacuum does sometimes shrink the file”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:
| table | what was deleted | heap before | heap after plain VACUUM |
|---|---|---|---|
t_tail | the newest 200,000 rows | 99,303,424 | 49,651,712 |
t_mid | every second row | 99,303,424 | 99,303,424 |
t_notrunc, vacuum_truncate = off | the newest 200,000 rows | 99,303,424 | 99,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.
How do I confirm it?
Section titled “How do I confirm it?”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.
1. Decompose the size
Section titled “1. Decompose the size”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 totalFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')ORDER BY pg_total_relation_size(c.oid) DESCLIMIT 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_countFROM pg_stat_user_tablesORDER BY n_dead_tup DESCLIMIT 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:
| table | DELETE and VACUUM | pg_stat_user_tables | pgstattuple |
|---|---|---|---|
ca | same session | 0 live, 200,000 dead | 200,000 live, 0 dead |
cb | separate sessions | 198,413 live, 0 dead | 200,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.
3. Measure the table itself
Section titled “3. Measure the table itself”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_percentFROM 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.
4. Watch the vacuum that is running
Section titled “4. Watch the vacuum that is running”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 queryFROM pg_stat_progress_vacuum pJOIN 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.
5. Find what is holding the horizon
Section titled “5. Find what is holding the horizon”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:
| session | isolation | statements run so far | backend_xmin |
|---|---|---|---|
| A | read committed | BEGIN only | null |
| B | read committed | BEGIN, one SELECT | null |
| C | repeatable read | BEGIN, one SELECT | 782 |
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 queryFROM pg_stat_activityWHERE backend_xmin IS NOT NULLORDER 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_statusFROM pg_replication_slotsORDER 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_slotsto find slots whereage(xmin)orage(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 slot | xmin | catalog_xmin | what it blocked |
|---|---|---|---|
| physical, created and never used | null | null | nothing: 50,000 dead rows removed normally |
| logical, created and never read | null | 756 | 2,000 dead rows in pg_class, none in the user table |
physical, left behind by a standby that had used hot_standby_feedback | 831 | null | 20,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.
7. Check the standbys
Section titled “7. Check the standbys”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 configuration | pg_stat_replication.backend_xmin | pg_replication_slots.xmin | dead rows vacuum could remove |
|---|---|---|---|
streaming through slot standby1 | null | 830 | 0 of 50,000 |
| streaming with no slot | 831 | not applicable | 50,000 of 50,000, and 0 of 10,000 newer ones |
Read both:
SELECT application_name, state, backend_xmin, age(backend_xmin) AS xmin_ageFROM pg_stat_replicationORDER 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_ageFROM pg_prepared_xactsORDER BY prepared;Autovacuum runs and does not finish
Section titled “Autovacuum runs and does not finish”Everything above is about vacuum not being allowed to remove rows. This section is about vacuum not getting to them.
It is being cancelled
Section titled “It is being cancelled”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 EXCLUSIVElock (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 taskCONTEXT: 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.
It is not triggered when you think
Section titled “It is not triggered when you think”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_autovacuumFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind = 'r' AND n.nspname = 'public'ORDER BY c.reltuples DESCLIMIT 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: 0Its 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_ageFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind = 'r' AND n.nspname = 'public'ORDER BY age(c.relfrozenxid) DESCLIMIT 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, sourceFROM pg_settingsWHERE name IN ('autovacuum_vacuum_cost_limit', 'autovacuum_vacuum_cost_delay', 'autovacuum_max_workers', 'autovacuum_naptime', 'log_autovacuum_min_duration')ORDER BY name;When it is not a vacuum problem at all
Section titled “When it is not a vacuum problem at all”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:
| table | column updated | HOT updates | heap after | indexes before | indexes after |
|---|---|---|---|---|---|
hot_unindexed_col | unindexed | 62,384 of 200,000 | 10,010,624 | 622,592 | 2,981,888 |
hot_indexed_col | indexed | 0 of 200,000 | 10,010,624 | 622,592 | 4,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_pctFROM pg_stat_user_tablesWHERE n_tup_upd > 0ORDER BY n_tup_upd DESCLIMIT 20;Autovacuum cannot see some of your tables
Section titled “Autovacuum cannot see some of your tables”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
ANALYZEon 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.
How do I get the space back?
Section titled “How do I get the space back?”Match the remedy to what the steps above told you.
| what you found | what to do |
|---|---|
| dead rows with a horizon holder | remove the holder. Nothing else helps. |
| dead rows, no holder, autovacuum cancelled in the log | stop the conflicting command, or schedule it |
| dead rows, no holder, threshold not reached | lower the scale factor on that table |
| free space, table at steady state | nothing. It will be reused. |
| heap capacity you need returned to the disk | VACUUM FULL or CLUSTER in a window, or pg_repack |
| index bloat with a healthy heap | REINDEX CONCURRENTLY that index |
| the whole table is emptied periodically | TRUNCATE 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
TRUNCATErather than usingDELETEfollowed byVACUUM.
That sentence is from Routine Vacuuming in the PostgreSQL 18 documentation, read 2026-09-20.
What VACUUM FULL costs
Section titled “What VACUUM FULL costs”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:
| heap | indexes | total | index relfilenode | |
|---|---|---|---|---|
| before | 10,010,624 | 4,595,712 | 14,647,296 | 16470 |
after VACUUM FULL | 1,277,952 | 622,592 | 1,908,736 | 16477 |
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.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”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.
How do I stop it coming back?
Section titled “How do I stop it coming back?”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.