What Causes Table Bloat and How Do You Fix It?
Learn what causes PostgreSQL table bloat and how to fix it with autovacuum tuning, VACUUM FULL, or pg_repack.
Expected Interview Answer
Table bloat is the accumulation of dead or wasted space inside a table's data pages — usually because updates and deletes leave dead tuples faster than autovacuum reclaims them — and it is fixed either by letting a properly tuned autovacuum keep up, or by actively rewriting the table with VACUUM FULL or pg_repack when bloat has already grown large.
Bloat happens for a few root causes: a high-churn table where autovacuum's default thresholds are too lax, long-running transactions that hold back the oldest visible row and prevent VACUUM from reclaiming anything newer, or bulk update/delete jobs that generate a spike of dead tuples faster than a single vacuum cycle can process. Left unchecked, bloat wastes disk space, makes sequential scans slower because more empty pages must be read, and can even push the query planner toward worse plans. The fix is twofold: tune autovacuum (or run manual VACUUM) so dead tuples never accumulate faster than cleanup, and for tables that are already badly bloated, use VACUUM FULL (which locks the table and rewrites it) or the online pg_repack extension (which rewrites without a long-held lock) to physically shrink the file.
- Reclaims wasted disk space
- Restores sequential scan and index scan speed
- Keeps the query planner’s row-count estimates accurate
- Avoids emergency downtime from severely bloated tables
AI Mentor Explanation
Think of a scorebook where every crossed-out correction is never actually removed, only ever added to, so after a long tournament the book is triple its needed thickness even though most pages are corrections nobody reads anymore. If the groundstaff clerk who recopies the book falls behind because matches run back-to-back, the book keeps swelling. Table bloat is this same swelling of a database file with dead, unreclaimed row versions, and the fix is either keeping the recopying clerk caught up or doing one big recopy to shrink the book back down.
Step-by-Step Explanation
Step 1
Identify bloated tables
Query pg_stat_user_tables or use a bloat-estimation query/extension to find tables with a large dead-to-live tuple ratio.
Step 2
Diagnose the root cause
Check for long-running transactions holding back the visibility horizon, or a recent bulk update/delete job as the trigger.
Step 3
Tune ongoing cleanup
Lower autovacuum thresholds on high-churn tables so dead tuples get reclaimed as fast as they are created.
Step 4
Reclaim existing bloat
Run VACUUM FULL during a maintenance window, or use pg_repack for an online rewrite without a long exclusive lock.
What Interviewer Expects
- Understanding that bloat comes from dead tuples outpacing cleanup, not from data corruption
- Awareness that long-running transactions can block VACUUM from reclaiming space
- Knowledge of the trade-off between VACUUM FULL (locking) and pg_repack (online)
- Ability to name a diagnostic approach for finding bloated tables
Common Mistakes
- Assuming bloat means data is corrupted or lost
- Running VACUUM FULL on a large production table during peak hours without considering the lock
- Not checking for long-running transactions as the underlying cause
- Treating bloat as a one-time fix rather than an ongoing tuning problem
Best Answer (HR Friendly)
“Table bloat happens when updates and deletes leave behind dead space faster than PostgreSQL's cleanup process can reclaim it, so the table takes up more disk space than the actual data needs. I would fix ongoing bloat by tuning autovacuum to run more aggressively on busy tables, and fix existing bloat with a full rewrite using VACUUM FULL or a tool like pg_repack that avoids a long lock.”
Code Example
-- Rough dead-tuple ratio per table
SELECT relname,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / GREATEST(n_live_tup, 1), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY dead_ratio DESC
LIMIT 10;
-- Reclaim space with a lock (maintenance window)
VACUUM FULL orders;
-- pg_repack offers an online alternative without a long lock:
-- pg_repack --table=orders -d mydbFollow-up Questions
- How does a long-running transaction cause bloat to accumulate?
- What is the difference between VACUUM FULL and pg_repack?
- How would you monitor bloat trends over time in production?
- Can index bloat happen independently of table bloat?
MCQ Practice
1. What is the most common root cause of table bloat?
Bloat arises when dead tuples pile up faster than VACUUM or autovacuum can reclaim their space.
2. What can prevent VACUUM from reclaiming recently dead tuples?
A long-running transaction keeps an old snapshot visible, so VACUUM cannot reclaim tuples that transaction might still need.
3. What is a key advantage of pg_repack over VACUUM FULL?
pg_repack rewrites the table in the background and swaps it in with only a brief lock, unlike VACUUM FULL’s long exclusive lock.
Flash Cards
What causes table bloat? — Dead tuples from updates/deletes accumulating faster than VACUUM reclaims them.
What can block VACUUM from cleaning up? — A long-running transaction still holding an old snapshot of the data.
How do you reclaim bloat with a lock? — VACUUM FULL, which rewrites the table but locks it for the duration.
How do you reclaim bloat without a long lock? — The pg_repack extension, which rewrites the table online.