Introduction PostgreSQL's MVCC model creates a new row version for every UPDATE, marking the old version as dead. When UPDATE frequency is high, dead tuples accumulate faster than autovacuum can clean them, causing table bloat. Bloated tables waste disk space, slow sequential scans, and increase buffer cache pressure.
Symptoms - Table size growing much larger than the actual row count suggests - `pg_stat_user_tables` shows `n_dead_tup` consistently high - Sequential scan performance degrading over time - `pg_stat_bgwriter` shows increasing `buffers_alloc` relative to actual data growth - `SELECT pg_size_pretty(pg_total_relation_size('my_table'))` shows unexpected size
Common Causes - Frequent UPDATEs on tables with many columns (full row rewrite for each update) - Autovacuum not running frequently enough on high-churn tables - Long-running transactions preventing dead tuple cleanup - TOAST table bloat from updating large text/bytea columns - Tables with no primary key causing inefficient vacuum operations
Step-by-Step Fix 1. **Estimate table bloat": ```sql -- Simple bloat estimate SELECT schemaname, relname, pg_size_pretty(pg_relation_size(oid)) AS table_size, n_live_tup, n_dead_tup, CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 1) ELSE 0 END AS dead_pct FROM pg_stat_user_tables JOIN pg_class ON pg_class.relname = pg_stat_user_tables.relname WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC LIMIT 20; ```
- 1.**Use pgstattuple extension for accurate bloat measurement":
- 2.```sql
- 3.CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders'); -- Returns: -- table_len, tuple_count, dead_tuple_count, free_space, free_percent, dead_tuple_percent ```
- 1.**Reclaim space with pg_repack (online, no exclusive lock)":
- 2.```bash
- 3.# Install pg_repack
- 4.sudo apt install postgresql-16-repack
# Enable extension psql -d mydb -c "CREATE EXTENSION pg_repack;"
# Repack the bloated table pg_repack -d mydb -t orders ```
- 1.**For extreme bloat, use VACUUM FULL (requires exclusive lock)":
- 2.```sql
- 3.-- Schedule during maintenance window
- 4.VACUUM FULL orders;
- 5.ANALYZE orders;
- 6.-- Rebuild indexes
- 7.REINDEX TABLE orders;
- 8.
` - 9.**Optimize the schema to reduce UPDATE bloat":
- 10.```sql
- 11.-- Split frequently-updated columns into a separate table
- 12.CREATE TABLE order_status (
- 13.order_id INT PRIMARY KEY REFERENCES orders(id),
- 14.status VARCHAR(20),
- 15.updated_at TIMESTAMPTZ DEFAULT NOW()
- 16.);
-- Move status updates to the smaller table -- This way, updating status only rewrites the small table, not the full orders row ```