"Bloat" and "dead tuples" get used interchangeably, and that's the first wrong turn. Autovacuum can be running exactly as configured, n_dead_tup can be near zero right after it finishes, and the table file on disk can still be twice the size it needs to be for the live data in it. Those are two different problems, and VACUUM (the plain, non-FULL kind autovacuum runs) only ever fixes one of them.
What VACUUM actually does to the table file
Ordinary VACUUM scans the table, finds tuples no transaction can still see (dead rows from updates and deletes), and marks that space as reusable — it adds the freed space to the table's free space map (FSM) so future inserts and updates can claim it. What it does not do is move live rows together and shrink the file. If dead space is scattered through a 10GB table, after VACUUM you still have a 10GB file; you just have free space inside it, available for reuse, not returned to the filesystem.
SELECT relname, pg_size_pretty(pg_relation_size(relid)) AS size,
n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
n_dead_tup near zero right after autovacuum ran tells you the dead rows are marked reusable. It tells you nothing about whether the file itself has shrunk, because it can't — plain VACUUM was explicitly designed to avoid taking the heavyweight lock a full rewrite would need, in exchange for never giving space back to the OS. The only way the file shrinks is VACUUM FULL (which rewrites the whole table into a new file and takes AccessExclusiveLock for the duration — the same heavyweight problem as the table rewrites in the ADD COLUMN case) or an online-rebuild extension like pg_repack, which builds a new copy and swaps it in with only a brief lock at the end.
Why free space doesn't mean the problem is solved
Reusable free space inside the file only helps if future writes land on those pages. If your workload's inserts happen to append (a growing id, a time-series created_at), new rows go to new pages at the end of the file regardless of how much free space autovacuum reclaimed earlier in the table — the reclaimed space sits unused, and the file keeps growing from both ends: new pages at the tail, scattered reusable gaps in the body. That's bloat that n_dead_tup alone will never show you, because by the time you check, the dead tuples that created those gaps have already been marked clean.
The more common cause: updates that can't be HOT
Postgres has an optimization called a Heap-Only Tuple (HOT) update: if an UPDATE doesn't touch any indexed column, and there's free space left on the same page as the old row version, Postgres can write the new version on that page and link it from the old slot, without touching any index at all. No HOT update means every index on the table gets a new entry too — more write amplification, more index bloat, not just table bloat.
The second condition is the one people miss: free space on the same page. By default, fillfactor is 100 — every page is packed as full as possible when rows are inserted, leaving no room for a same-page HOT update later. The very first update to a freshly-loaded table is statistically unlikely to be HOT, because there's nowhere on the page to put the new version; it goes to a different page, and now you've updated an index too, for a table whose indexed columns you never touched.
-- Leaves 10% of each page empty at insert time, specifically so
-- later UPDATEs have somewhere to put the new row version on the
-- same page and qualify as HOT.
ALTER TABLE orders SET (fillfactor = 90);
Lowering fillfactor doesn't help existing data until the table is rewritten with the new setting in effect (a VACUUM FULL or pg_repack again) — it changes how future pages are filled, not the packing of pages that already exist. Check your actual HOT rate before guessing at a value:
SELECT relname,
n_tup_upd,
n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';
A table with heavy UPDATE traffic and a hot_pct well under 50 is a strong candidate for a lower fillfactor — assuming the updated columns genuinely aren't indexed; if they are, no fillfactor setting will make those particular updates HOT, because the index entry has to change regardless of where on the page the new tuple lands.
The bloat source autovacuum literally cannot fix
VACUUM can only remove a dead tuple once no active transaction's snapshot could still need to see it — specifically, once it's older than every backend's xmin horizon. A single long-running transaction (an analytics query left open, an ORM that opens a transaction and forgets to close it, a replication slot holding back the horizon) blocks that cleanup for every table in the database, not just the one the long transaction touches. Autovacuum will dutifully run on schedule the whole time, scan the table, and remove nothing, because nothing is eligible yet.
SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 5;
If this turns up a transaction open for hours, that — not autovacuum's settings — is usually the actual bloat cause. No tuning of autovacuum_vacuum_scale_factor fixes a horizon that a stuck transaction is holding back.
Rule of thumb:
n_dead_tupnear zero after autovacuum tells you dead space is marked reusable, not that the file has shrunk — those are different claims. Checkhot_pctbefore blaming autovacuum's schedule; a low HOT rate from afillfactorof 100 causes both table and index bloat that routineVACUUMcan never fully undo. And before changing any autovacuum setting, checkpg_stat_activityfor a transaction old enough to be holding the cleanup horizon back — that's a different fix entirely.