What is table bloat in PostgreSQL and how do you detect it?
Bloat is space in a table or index taken by dead row versions that vacuum has marked reusable but not returned to the operating system. It makes scans slower and files larger. Check n_dead_tup in pg_stat_user_tables or use pgstattuple.
Explain it simply
A cupboard where you keep adding new shirts and only pretend to throw out old ones fills with clutter. Because of MVCC, every update leaves an old copy behind. VACUUM marks that space reusable for future rows, but the file does not shrink, so a table that once held a lot of churn stays big with holes inside.
Sequential scans must read all those half-empty pages, so queries slow down even though the row count is the same.
Example
SELECT relname, n_live_tup, n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;
-- exact measurement
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');Fixes: make autovacuum more aggressive on busy tables, avoid long transactions that block cleanup, and for severe bloat use pg_repack or VACUUM FULL during a maintenance window.
What to say in the interview
"Bloat comes from dead tuples that are reusable but never given back. I watch n_dead_tup and last_autovacuum, measure with pgstattuple, and prevent it with tuned autovacuum and short transactions. Heavy bloat needs pg_repack or VACUUM FULL."
Common mistakes
- Expecting plain VACUUM to shrink the file on disk.