Table Bloat Detection | PG Pilot PostgreSQL Monitoring

What is this issue?

PostgreSQL's MVCC (Multi-Version Concurrency Control) doesn't immediately remove
deleted or updated rows. Instead, it marks them as "dead" and leaves them in place
until VACUUM cleans them up.

Table bloat occurs when:
- VACUUM isn't running frequently enough
- Long-running transactions prevent VACUUM from cleaning dead tuples
- High UPDATE/DELETE activity outpaces VACUUM's cleanup

A table with 60%+ dead tuples is significantly bloated and needs attention.

Why it matters

Wasted Disk Space

Dead tuples consume storage that can't be reclaimed without VACUUM

Slower Sequential Scans

Queries must read through dead tuples even though they're not returned

Larger Backups

Bloated tables increase backup size and restore time

Index Bloat

Table bloat often accompanies index bloat, compounding the problem

How PG Pilot detects it

```sql
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
  AND n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) > 0.3
ORDER BY n_dead_tup DESC;
```

How to fix it

1

Run VACUUM

First, try a regular VACUUM to clean up dead tuples:

VACUUM VERBOSE your_table;

This will reclaim space for reuse within the table but won't
return space to the OS.

2

Check for blockers

If VACUUM doesn't help, check for long-running transactions:

SELECT pid, age(backend_xmin), query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Also check for replication slots holding back cleanup.

3

Consider VACUUM FULL

If regular VACUUM isn't enough, VACUUM FULL rewrites the entire table:

VACUUM FULL your_table;

Warning: This locks the table exclusively for the entire duration.
For large tables, consider pg_repack instead.

4

Tune autovacuum

For high-write tables, make autovacuum more aggressive:

ALTER TABLE your_table SET (
  autovacuum_vacuum_threshold = 50,
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_threshold = 50,
  autovacuum_analyze_scale_factor = 0.05
);

Table Bloat