Intermediate LevelPostgreSQL
How do you measure table/index bloat and rebuild bloat using pg_repack?
❓ Interview Prompt: Explain Table/Index Bloat, `VACUUM FULL` exclusive locks, and `pg_repack` zero-downtime online table rebuilding.
While standard 'VACUUM' marks dead tuples reusable, it does NOT shrink physical table files on disk. 'VACUUM FULL' reclaims disk space but requires an exclusive table lock blocking reads and writes!
Zero-Downtime Solution ('pg_repack'):
'pg_repack' rebuilds bloated tables and indexes online without exclusive locks:
1
Creates a shadow table and logs changes using triggers.
2
Swaps table references atomically in metadata catalogs!
Code Implementation Example
bash
# Execute online zero-downtime table and index repack via pg_repack CLI
pg_repack -h localhost -U postgres -d appdb -t orders💡Rebuilding bloated tables online without locks using pg_repack.
🎯 Interview Key Takeaway
Use pg_repack instead of VACUUM FULL to eliminate table bloat online without locking production databases.
#pg_repack#Bloat#VACUUM FULL#Zero-Downtime#Intermediate
Back to all PostgreSQL Questions→