Reclaiming Space with pg_repack
Rebuild bloated tables and indexes online without the exclusive locks that VACUUM FULL requires.
Why Bloat Needs an Online Rebuild
PostgreSQL uses MVCC, so an UPDATE or DELETE does not overwrite a row in place. It marks the old tuple dead and writes a new version. Over time, dead tuples accumulate as bloat in tables and indexes.
Regular VACUUM reclaims dead tuples for reuse but does not shrink the file on disk. To physically return space to the OS, you traditionally run VACUUM FULL — but that takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for the entire rebuild.
On a busy production table, that lock is unacceptable. This is where pg_repack comes in: it rebuilds the table and reclaims space with almost no blocking.
Measuring the Bloat First
Never repack blindly. First quantify the bloat so you know the rebuild is worthwhile. The simplest signal is dead tuples versus live tuples from pg_stat_user_tables.
n_live_tup— estimated live rowsn_dead_tup— estimated dead rows awaiting cleanuplast_autovacuum— when autovacuum last ran
A high dead-to-live ratio plus a large on-disk size means a repack will reclaim real space.
SELECT relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / 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 10;All lessons in this course
- Measuring Table and Index Bloat Accurately
- Reclaiming Space with pg_repack
- Tuning Fillfactor for Update-Heavy Tables
- TOAST Internals and Large Value Storage