A table with one million rows. We update every row three times. The row count never changes, but the table grows from 50 MB to 199 MB and every full scan reads four times as many pages. That is table bloat, and it is the reason PostgreSQL needs VACUUM.
In this post we measure it on PostgreSQL 16: how bloat builds up, what VACUUM and VACUUM FULL really do (and what they lock), how one idle session can stop all cleanup, how fillfactor makes updates cheaper, and how to tune autovacuum. Every number below comes from a real run.
01Why tables bloat
PostgreSQL uses MVCC: readers never wait for writers. To make that work, an UPDATE never overwrites a row in place. It writes a new version of the row and marks the old one as expired. A DELETE only marks the row as expired. The old versions stay in the table until nothing can still see them, and only then can VACUUM turn them into reusable space.
02The test: 1 million rows, 3 updates
CREATE EXTENSION pgstattuple; -- exact bloat measurement (contrib) CREATE TABLE accounts (id int PRIMARY KEY, balance numeric(12,2), status text, updated_at timestamptz) WITH (autovacuum_enabled = off); -- off ONLY for the demo, to see bloat build up INSERT INTO accounts SELECT g, 1000, 'ACTIVE', now() FROM generate_series(1, 1000000) g; VACUUM ANALYZE accounts; UPDATE accounts SET balance = balance + 1, updated_at = now(); -- run 3 times
| State | Table | Index | Live rows | Dead rows | Pages read by a full scan |
|---|---|---|---|---|---|
| Fresh | 50 MB | 21 MB | 1,000,000 | 0 | 6,370 |
| After 1 update | 100 MB | 43 MB | 1,000,000 | 1,000,000 | – |
| After 2 updates | 149 MB | 50 MB | 1,000,000 | 2,000,000 | – |
| After 3 updates | 199 MB | 57 MB | 1,000,000 | 2,999,939 | 25,477 |
Four times the pages for the same answer. In our test the data was cached, so the full scan only went from 137 ms to 165 ms; on a server where those pages come from disk, or where the table no longer fits in memory, the difference is far larger. The index more than doubled too, because every new row version needed a new index entry.
03VACUUM vs VACUUM FULL
VACUUM (VERBOSE) accounts;
pages: 0 removed, 25477 remain, 25477 scanned (100.00% of total) tuples: 84 removed, 1000000 remain, 0 are dead but not yet removable index scan needed: 19108 pages from table (75.00% of total) had 2999855 dead item identifiers removed Time: 459.875 ms
| Plain VACUUM | VACUUM FULL | |
|---|---|---|
| Time on our table | 0.46 s | 1.02 s |
| Table size after | 199 MB (unchanged) | 50 MB |
| Index size after | 57 MB (unchanged) | 21 MB |
| Dead rows after | 0 | 0 |
| Lock | SHARE UPDATE EXCLUSIVE: reads and writes continue | ACCESS EXCLUSIVE: nothing can read or write the table |
| Disk space needed | None extra | A full new copy of the table and indexes while it runs |
| Use it | Routinely (autovacuum does it for you) | Rarely, in a maintenance window, after a one-off mass update or delete |
Plain VACUUM did not shrink the file, but the space is not wasted: we ran a fourth full update and the table stayed at 199 MB, because the new row versions went into the space VACUUM had freed.
While VACUUM FULL ran, this is what other sessions saw:
query | mode | granted ----------------------+---------------------+--------- VACUUM FULL accounts | AccessExclusiveLock | t
SET lock_timeout = '200ms'; SELECT count(*) FROM accounts; ERROR: canceling statement due to lock timeout
04When VACUUM can't clean
VACUUM can only remove a row version that no running transaction can still see. One forgotten session is enough to stop all cleanup. We opened a REPEATABLE READ transaction in a second session, left it idle, and updated the table again:
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM accounts; -- ...and then the session just sits there
UPDATE accounts SET balance = balance + 1; VACUUM (VERBOSE) accounts;
tuples: 0 removed, 2000000 remain, 1000000 are dead but not yet removable
VACUUM ran, but removed nothing. Find the session holding the oldest snapshot:
SELECT pid, application_name, state, age(backend_xmin) AS xmin_age,
date_trunc('second', now() - xact_start) AS xact_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY age(backend_xmin) DESC;
pid | application_name | state | xmin_age | xact_age -----+------------------+---------------------+----------+---------- 357 | report_export | idle in transaction | 1 | 00:00:05
tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable
idle_in_transaction_session_timeout (for example 5min) for application roles, and alert on old transactions. Replication slots and prepared transactions hold back cleanup in the same way (query in section 07).05HOT updates and fillfactor
If an update does not change any indexed column and the new version fits on the same page, PostgreSQL makes a HOT (heap-only tuple) update: no new index entries, and the old version is cleaned up cheaply during normal reads. The catch: a freshly loaded table has full pages (fillfactor 100), so there is no room. Same data, same updates (10% of rows per round, only balance changes):
CREATE TABLE acc_ff100 (...) WITH (fillfactor = 100); -- the default
CREATE TABLE acc_ff90 (...) WITH (fillfactor = 90); -- leave 10% free in every page
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 IN ('acc_ff100', 'acc_ff90');
| Table | Size after load | HOT updates, round 1 | HOT updates after 4 rounds |
|---|---|---|---|
| fillfactor 100 | 42 MB | 0% | 68% |
| fillfactor 90 | 47 MB | 100% | 96% |
With fillfactor 100 the first round could not use HOT at all; later rounds improved only because pruning freed space. With fillfactor 90 almost every update was HOT from the start, at the cost of a table about 11% larger. Use it for tables that are updated often, not for append-only tables. For an existing table, ALTER TABLE … SET (fillfactor = 90) affects new pages only until the table is rewritten.
06Tuning autovacuum
Autovacuum vacuums a table when its dead rows exceed threshold + scale_factor × rows. The PostgreSQL 16 defaults on our server:
| Setting | Default | Meaning |
|---|---|---|
autovacuum_vacuum_threshold | 50 | Base number of dead rows |
autovacuum_vacuum_scale_factor | 0.2 | Plus 20% of the table |
autovacuum_vacuum_insert_scale_factor | 0.2 | Also vacuum after inserts (helps index-only scans and freezing) |
autovacuum_analyze_scale_factor | 0.1 | Re-analyze after 10% changes |
autovacuum_naptime | 60 s | How often each database is checked |
autovacuum_max_workers | 3 | Tables vacuumed at the same time |
vacuum_cost_limit / autovacuum_vacuum_cost_limit | 200 / -1 (inherit) | How much work before autovacuum sleeps: the main speed brake |
For our 1-million-row table that means autovacuum waits for 200,050 dead rows. For a 100-million-row table it waits for 20 million. Big, busy tables need a per-table setting:
ALTER TABLE accounts SET (autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 1000);
-- then: UPDATE 10% of the rows (100,000 dead rows > 1,000 + 5% of 1M)
relname | n_dead_tup | last_autovacuum | autovacuum_count ----------+------------+-------------------------------+------------------ accounts | 0 | 2026-10-31 08:15:49.870217+00 | 1
autovacuum_vacuum_cost_limit (for example 1000–2000 on fast storage) if autovacuum can't keep up; add workers last. Never switch autovacuum off: it also prevents transaction ID wraparound.07Monitoring queries
Tables with the most dead rows (the trigger column uses the global defaults):
SELECT s.schemaname, s.relname, s.n_live_tup, s.n_dead_tup,
round(100.0 * s.n_dead_tup / nullif(s.n_live_tup + s.n_dead_tup, 0), 1) AS dead_pct,
(current_setting('autovacuum_vacuum_threshold')::int
+ current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)::bigint AS default_trigger_at,
s.last_autovacuum, s.last_vacuum
FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC LIMIT 10;
Everything that holds back cleanup (sessions, replication slots, prepared transactions):
SELECT 'session' AS holder, pid::text AS id, application_name AS detail, age(backend_xmin) AS xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL UNION ALL SELECT 'replication slot', slot_name, slot_type, greatest(age(xmin), age(catalog_xmin)) FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL UNION ALL SELECT 'prepared xact', gid, owner::text, age(transaction) FROM pg_prepared_xacts ORDER BY xmin_age DESC;
Transaction ID age (autovacuum must freeze old rows long before the ~2 billion limit) and vacuums running right now:
SELECT datname, age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
FROM pg_database ORDER BY 2 DESC;
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;
For exact numbers on one table, SELECT * FROM pgstattuple('accounts'); reads the whole table, so run it off-peak.
08Checklist
| Check | Why |
|---|---|
| Autovacuum is on, and never disabled on real tables | It also prevents transaction ID wraparound |
| Large, busy tables have their own scale factor | The 20% default waits too long on big tables |
idle_in_transaction_session_timeout is set for app roles | One idle transaction blocks cleanup everywhere |
| Replication slots and prepared transactions are monitored | Both hold back cleanup silently |
| Hot update-heavy tables use fillfactor 80–90 | More HOT updates, less index bloat |
| VACUUM FULL only in a maintenance window | ACCESS EXCLUSIVE lock and a full copy on disk |
| Dead-row and XID-age queries are in your monitoring | Catch bloat before users feel it |
Originally published at techstaradmin.blogspot.com.
