Table Bloat Dead Tuples
pg_relation_size / \dt+ shows a table far larger than the live data justifies (e.g. 40 MB for 200,000 small rows). Sequential scans and VACUUM take longer...
In 10 seconds
A table's on-disk file is several times larger than the live data it holds, and it keeps growing even though the row count is roughly stable. Disk usage and sequential-scan cost climb, but nothing is obviously broken. Under MVCC, every UPDATE and DELETE leaves a dead row version behind, and that dead space stays in the heap until VACUUM reclaims it. When churn outruns vacuuming -- or autovacuum is switched off -- the heap fills with dead tuples: it is bloated. You need to measure the bloat exactly, clear it, and understand why a plain VACUUM makes the dead tuples disappear but does not make the file smaller.
Why This Happens
PostgreSQL never updates a row in place. An UPDATE writes a new row version and marks the old one dead; a DELETE just marks the row dead. The dead versions stay in the heap so that concurrent transactions with older snapshots can still see them -- that is how MVCC gives you non-blocking reads. VACUUM is the janitor: once no snapshot can see a dead tuple, vacuum removes it and records the freed slot in the table's free space map (FSM) so future inserts and updates can reuse it. Crucially, plain VACUUM reclaims space FOR REUSE WITHIN THE TABLE; it does not hand the space back to the operating system and does not shrink the file, except for the narrow case of trailing all-empty pages, which it can truncate.
So a heavily churned table reaches a steady 'high-water mark' size: vacuum keeps dead_tuple_percent low and the free space is recycled in place, but the file stays at its largest extent. That is correct and usually desirable -- the table has the room it needs and avoids constant file extension. You only need to physically shrink the file when the high-water mark is genuinely too big (a one-off mass delete, an archival, a runaway batch).
For that you must REWRITE the table: VACUUM FULL rebuilds it compactly under an ACCESS EXCLUSIVE lock (blocking all reads and writes, needing room for a second copy), or pg_repack does the same rewrite online with only a brief lock. The decision is therefore two-step: is dead_tuple_percent high (run VACUUM, reclaim to FSM), or is free_percent high after vacuuming and you truly need the disk back (rewrite with VACUUM FULL / pg_repack)? The decision is therefore two-step: is dead_tuple_percent high (run VACUUM, reclaim to FSM), or is free_percent high after vacuuming and you truly need the disk back (rewrite with VACUUM FULL / pg_repack)?
Diagnose
Free Tier -- Basic Approach
The cheap smoke alarm: estimate the dead ratio from catalog statistics, no extension and no table scan required.
relname | n_live_tup | n_dead_tup | est_dead_pct | heap_size ----------+------------+------------+--------------+----------- accounts | 200000 | 300000 | 60.0 | 40 MB (1 row)
pg_stat_user_tables gives n_live_tup 200000 and n_dead_tup 300000, so the estimated dead ratio is 60.0% against a 40 MB heap. This is the metric to graph and alert on -- it costs nothing and updates continuously. Note it counts ROWS, so it reads 60% here, whereas the byte-accurate pgstattuple reads 51.86%; the free estimate is meant to flag the problem, not size the fix.
Fix
Run a plain VACUUM. It removes the 300,000 dead tuples and marks that space reusable -- online, with no exclusive lock -- but it deliberately does NOT shrink the file.
INFO: vacuuming "appdb.public.accounts"
INFO: finished vacuuming "appdb.public.accounts": index scans: 1
pages: 0 removed, 5155 remain, 5155 scanned (100.00% of total)
tuples: 300000 removed, 200000 remain, 0 are dead but not yet removable
removable cutoff: 701143, which was 0 XIDs old when operation ended
new relfrozenxid: 701140, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 5155 pages from table (100.00% of total) had 300000 dead item identifiers removed
index "accounts_pkey": pages: 1374 in total, 0 newly deleted, 0 currently deleted, 0 reusable
I/O timings: read: 0.000 ms, write: 0.389 ms
avg read rate: 0.000 MB/s, avg write rate: 0.809 MB/s
buffer usage: 16895 hits, 0 misses, 5 dirtied
WAL usage: 16834 records, 1 full page images, 2712082 bytes
system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.04 s
INFO: vacuuming "appdb.pg_toast.pg_toast_18415"
INFO: finished vacuuming "appdb.pg_toast.pg_toast_18415": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 701143, which was 0 XIDs old when operation ended
new relfrozenxid: 701143, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
I/O timings: read: 0.076 ms, write: 0.000 ms
avg read rate: 11.290 MB/s, avg write rate: 0.000 MB/s
buffer usage: 32 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pg_sleep
----------
(1 row)
n_live_tup | n_dead_tup
------------+------------
200000 | 0
(1 row)
table_len | heap_size | tuple_count | dead_tuple_count | dead_pct | free_pct
-----------+-----------+-------------+------------------+----------+----------
42229760 | 40 MB | 200000 | 0 | 0.00 | 57.09
(1 row)
VACUUM (VERBOSE) accounts reports removing the dead tuples; afterwards n_dead_tup is 0 and pgstattuple shows dead_pct 0.00 with free_pct jumping to 57.09 -- at the SAME table_len 42229760. The dead space is gone and the slots are now free in the FSM, ready to be reused, but the file did not get smaller because plain VACUUM does not rewrite the heap or return disk to the OS. The lock taken is only SHARE UPDATE EXCLUSIVE, so reads and writes continued throughout.
Verify
Prove the vacuumed space is genuinely reused: re-insert the rows we deleted and watch the file NOT grow.
table_len_before_reinsert | heap_size_before
---------------------------+------------------
42229760 | 40 MB
(1 row)
INSERT 0 300000
pg_sleep
----------
(1 row)
table_len_after_reinsert | heap_size_after | tuple_count | dead_pct | free_pct
--------------------------+-----------------+-------------+----------+----------
42229760 | 40 MB | 500000 | 0.00 | 0.20
(1 row)
ALTER TABLE
reloptions
---------------------------
{autovacuum_enabled=true}
(1 row)
Before the re-insert table_len is 42229760 (40 MB). Inserting 300,000 fresh rows brings the table back to 500,000 live rows -- and table_len is STILL 42229760, with free_pct back down to 0.20 and dead_pct 0.00. The inserts filled the space the vacuum freed instead of extending the file, which is the whole point: for steady-state churn, plain VACUUM is the complete fix, no rewrite or lock required. We then re-enable autovacuum (reloptions {autovacuum_enabled=true}) so the table stays healthy on its own.
⏪ Rollback
Plain VACUUM is safe and online -- it takes only a SHARE UPDATE EXCLUSIVE lock (autovacuum-grade), never blocks reads or writes, and there is nothing to roll back. The real hazard is the heavy hammer: VACUUM FULL takes an ACCESS EXCLUSIVE lock for its entire duration, so the table is completely unavailable while it rewrites, and it needs free disk equal to the new copy. If a VACUUM FULL is running and causing an outage you cannot wait out, cancel it (pg_cancel_backend on its pid); the original table is untouched until the rewrite commits, so cancelling simply leaves the bloated-but-working table in place. To avoid the outage entirely, use pg_repack, which performs the same compaction online and only needs a short lock to swap the rebuilt table in. Never 'fix' bloat by dropping and recreating a busy table by hand.
📦 Version Matrix (PG 14--18)
The dead-tuple mechanism and the reclaim-vs-shrink behaviour are identical on every supported major version. Each one deletes 60%, runs a plain VACUUM, then a VACUUM FULL, and reports the file size at each step.
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why didn't my table get smaller after I ran VACUUM?
Because plain VACUUM is not supposed to. It removes dead tuples and marks that space free in the free space map so it can be reused, but it leaves the file at its current size (only trailing empty pages are truncated). In this recipe the table stayed at 42229760 bytes after VACUUM while dead_tuple_percent dropped to 0 and free_percent rose to 57%. To physically shrink the file you must rewrite it with VACUUM FULL or pg_repack.
How do I measure table bloat accurately?
pgstattuple('table') scans the heap and returns table_len, tuple_count (live), dead_tuple_count, dead_tuple_percent and free_percent -- the ground truth. For a cheap, non-scanning estimate use pg_stat_user_tables.n_dead_tup vs n_live_tup; for a sampled approximation of a huge table use pgstattuple_approx.
When should I use VACUUM FULL versus plain VACUUM?
Use plain VACUUM (or just let autovacuum run) for ongoing churn -- it is online, lock-light, and recycles space in place. Use VACUUM FULL or pg_repack only when the table's high-water mark is genuinely too large (after a one-off mass delete or archival) and you must return disk to the OS. VACUUM FULL takes an ACCESS EXCLUSIVE lock and needs room for a second copy; pg_repack does the same rewrite online.
Will the freed space ever be reused, or is it wasted?
It is reused. After VACUUM the freed slots are recorded in the free space map, and the next inserts/updates fill them instead of extending the file. This recipe proves it: after vacuuming, re-inserting 300,000 rows kept table_len at exactly 42229760 -- zero file growth -- because the inserts reused the vacuumed space.
How do I stop the table from bloating again?
Keep autovacuum enabled and tune it for the table's write rate (lower autovacuum_vacuum_scale_factor on hot tables so vacuum runs sooner). Avoid long-running or idle-in-transaction sessions that hold back the xmin horizon and prevent dead tuples from being removed. Monitor n_dead_tup or pgstattuple_approx and alert on a rising dead ratio.