Concurrency & IsolationdebuggingAdvanced

A table keeps growing though its row count is flat

Symptoms

  • A frequently-updated table is 40 GB on disk but holds only ~2 million live rows.
  • Sequential scans and even index scans have gotten slower over months.
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'sessions';
  n_live_tup: 2,100,000
  n_dead_tup: 58,000,000     ← 28× more dead than live
  last_autovacuum: 6 days ago

Investigate

Inspect areas in any order (0/4 inspected). When you think you know the root cause, commit to it.

Live vs dead tuples
Long-running transactions
Autovacuum settings
Update rate