Table Bloat After a Mass Delete: What PostgreSQL Does with Your Disk Space

A GDPR deletion script ran on a Saturday — 8 million rows deleted from the user_events table across a 4-hour window. By Monday, queries against user_events were running 3x slower than before the deletion. The table had fewer rows but was taking longer to scan. The deleted rows were still there, physically, as dead tuples autovacuum had not cleared because the deletion rate had overwhelmed the autovacuum trigger thresholds.

The Alert

Application monitoring: user_events queries p95 at 4.2 seconds, up from 1.4 seconds on Friday. The table had 8 million fewer rows. Slower after deletion was counterintuitive and delayed the diagnosis by 6 hours.

The Discovery

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
  last_autovacuum,
  last_autoanalyze,
  n_mod_since_analyze
FROM   pg_stat_user_tables
WHERE  relname = 'user_events';

n_dead_tup: 7,840,000. Dead percentage: 62%. The table had 5 million live rows and 7.8 million dead rows. Autovacuum had triggered and run but had not finished — the deletion rate during the GDPR script was faster than autovacuum could reclaim pages. The table was 62% dead space that sequential scans had to read and discard on every query.

Incident Timeline

Root Cause

PostgreSQL's MVCC model marks deleted rows as dead but does not physically remove them until vacuum reclaims the space. Autovacuum is triggered by thresholds (default: 20% of table rows changed). A bulk deletion that exceeds the reclaim rate leaves dead rows in place. Sequential scans must read and filter every dead row, which is as expensive as reading a live row.

The Fix

-- Run after any bulk delete over 1M rows:
VACUUM (VERBOSE, ANALYZE) user_events;

-- Do NOT use VACUUM FULL unless absolutely necessary:
-- VACUUM FULL locks the table exclusively and rewrites it entirely
-- Regular VACUUM is concurrent and usually sufficient

-- Monitor progress:
SELECT phase, heap_blks_scanned, heap_blks_total, num_dead_tuples
FROM   pg_stat_progress_vacuum
WHERE  relid = 'user_events'::regclass;

Prevention

Any bulk delete over 500K rows now runs in batches of 100K with a VACUUM between batches. For the GDPR deletion script specifically, we added a post-deletion vacuum to the script itself. Autovacuum thresholds on the user_events table were adjusted to trigger earlier on this high-churn table.

Measuring Bloat Before Vacuuming

Running VACUUM FULL when a standard VACUUM would do is a serious mistake — VACUUM FULL locks the table exclusively and rewrites the entire file, causing downtime on any table being actively used. Before taking action, measure the actual bloat and decide whether standard VACUUM or VACUUM FULL is warranted.

-- pgstattuple extension gives exact bloat (requires superuser and brief lock):
-- SELECT * FROM pgstattuple('user_events');  -- if extension available

-- Estimate from pg_stat_user_tables without a lock:
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  pg_size_pretty(pg_relation_size(relname::regclass))  AS table_size,
  pg_size_pretty(
    pg_relation_size(relname::regclass) *
    n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0)
  )                                                      AS estimated_bloat,
  last_autovacuum,
  last_vacuum
FROM   pg_stat_user_tables
WHERE  n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT  20;

-- Standard VACUUM: concurrent, marks pages as reusable, no lock
-- Use when: dead_pct < 40%, table is actively used by application
VACUUM (VERBOSE, ANALYZE) user_events;

-- VACUUM FULL: rewrites entire table, exclusive lock, returns space to OS
-- Use when: dead_pct > 50% AND the space is needed AND a lock window exists
-- VACUUM FULL user_events;  -- DO NOT run on live high-traffic tables

-- Check VACUUM progress in real time:
SELECT phase, heap_blks_scanned, heap_blks_total,
       num_dead_tuples, num_deleted_tuples
FROM   pg_stat_progress_vacuum
WHERE  relid = 'user_events'::regclass;