Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 31
Measure retained space before choosing a rewrite
Diagnose PostgreSQL table and index bloat through tuple churn, page density, HOT behavior, and workload-aware remediation.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
A relation can be larger than its live rows because pages contain reusable free space, alignment, tuple metadata, fillfactor reserves, or retained dead versions. Bloat is excess physical cost relative to the workload, not one size number.
02
Updates and deletes create obsolete heap tuples and index entries. Vacuum makes eligible space reusable, but long snapshots, insufficient maintenance capacity, and update patterns can let debt accumulate.
03
A heap-only tuple update avoids new index entries when indexed values do not change and the page has room. Low HOT rates on frequently updated tables multiply heap churn into every index.
04
Catalog estimates, pgstattuple, pg_freespacemap, pageinspect, and index statistics expose different parts of the physical picture. Sampling and extension overhead must match the incident risk.
05
If normal churn will reuse space, no shrink is needed. REINDEX rebuilds one index; pg_repack or an online migration pattern can rewrite with reduced blocking; VACUUM FULL rewrites under an exclusive lock.
06
Autovacuum thresholds, transaction hygiene, HOT-friendly schema design, bounded batches, and right-sized indexes keep physical growth aligned with useful work.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Start with table, indexes, estimated live/dead tuples, and maintenance history. Size alone cannot identify removable bloat.
SELECT relid::regclass, n_live_tup, n_dead_tup, last_autovacuum,
pg_size_pretty(pg_table_size(relid)) table_size,
pg_size_pretty(pg_indexes_size(relid)) indexes_size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;A low HOT ratio on an update-heavy table points to indexed-column changes or insufficient page-local free space, both of which amplify index growth.
SELECT relname, n_tup_upd, n_tup_hot_upd, n_dead_tup,
round(100.0*n_tup_hot_upd/nullif(n_tup_upd,0),1) hot_pct
FROM pg_stat_user_tables ORDER BY n_tup_upd DESC;pgstattuple can measure tuple, dead-tuple, and free-space percentages but exact scans consume I/O. Use it on a bounded candidate after catalog evidence identifies impact.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('public.orders');Reindexing or rewriting needs temporary disk, WAL capacity, replica headroom, lock analysis, and a rollback path. Reclaim only after fixing the growth mechanism.
SELECT pg_size_pretty(pg_total_relation_size('public.orders')) current_size;
-- Estimate new heap and indexes, then budget both copies plus WAL.
-- Validate lock behavior and replica lag in rehearsal before pg_repack,
-- REINDEX CONCURRENTLY, or a controlled table migration.08
Optional deep dives with the complete experiments and product-version context behind this guide.