In multi-version storage, deleting a row records the change without immediately freeing the space. Reclaiming that space is a background job, and when it is skipped the cost shows up slowly.

Deleted does not mean gone

Deleted rows do not vanish from a PostgreSQL table at once; they are only marked dead, and the storage they used, along with older versions still needed by other transactions, stays until someone reclaims and reuses it. That is why a table can keep growing in size even when its live row count stays flat, and why a long-running transaction can pin old versions and balloon a database that is quiet on writes. The insight to take away is that reclaiming is not a side effect of deleting; it is a separate, recurring job.

Reclaiming is a scheduled skill

PostgreSQL reclaims that space with a maintenance worker, autovacuum, that wakes up on its own schedule, scans tables whose bloat passes a threshold, and reuses the freed tuples while also refreshing the planner’s statistics. Because it is periodic rather than continuous, a table can drift away from its healthy size if the schedule never quite meets the churn. The fix is not to disable it but to keep it running on a regular cadence, tune how quickly it reacts after a large delete, and watch the signals rather than hoping a manual VACUUM is enough.

What good maintenance keys on

Good maintenance keys on the signals instead of the symptom. Watch the ratio of dead tuples to live ones, the age of the oldest transaction, and how long it has been since the last automatic vacuum actually ran, and set thresholds so a burst of deletes is reclaimed within a reasonable window. A cluster that gets cleaned on a schedule stays at a predictable size, keeps its indexes useful, and turns routine bloat into something you schedule instead of an emergency you respond to.

When it breaks

Autovacuum falling behind write churn is the most common failure. On a write-heavy table each update leaves a dead version, so a burst of updates can create dead tuples faster than the throttled worker reclaims them. Cost-based throttling, a long naptime, or too few workers stretch the gap: n_dead_tup climbs, the heap and indexes bloat, scans read extra dead pages, and statistics go stale. Adding hardware or restarting does not drain the backlog; only more vacuum throughput or less churn does.

A second failure looks identical in monitoring but has the opposite cause: vacuum runs yet reclaims nothing because an old snapshot pins the visibility horizon. A forgotten REPEATABLE READ transaction, an idle-in-transaction session, or a replication slot holding xmin keeps dead versions visible. Lowering thresholds or running VACUUM manually cannot free them; n_dead_tup stays high and the table keeps growing. Find the holder in pg_stat_activity or pg_replication_slots and release it instead of tuning autovacuum harder.

The third failure is misdiagnosis: bloat is treated as a slow query or a missing index. The team adds indexes or rewrites SQL while EXPLAIN ANALYZE BUFFERS keeps showing inflated heap fetches and counts that disagree with pg_stat_user_tables. Ordinary VACUUM only marks space reusable and refreshes visibility information, so skipping it leaves every query paying the bloat tax. Check the dead-tuple ratio and last-autovacuum age before reaching for the query tuner.

Check it quickly

Save the following as vacuum_model.py and run it with python3 vacuum_model.py. It uses only the standard library to model a threshold that triggers cleanup, a per-run cost budget that caps reclaiming, and a pinned horizon that blocks it. The numbers are illustrative, but the threshold formula mirrors the documented base plus scale-factor rule.

"""Simplified autovacuum threshold and cost-limit model (stdlib only)."""

BASE_THRESHOLD = 50
SCALE_FACTOR = 0.2
NAPTIME_TICKS = 2
CLEAN_BUDGET = 1600  # max dead tuples one run reclaims (simplified cost limit)
LIVE_TUPLES = 10_000
THRESHOLD = BASE_THRESHOLD + SCALE_FACTOR * LIVE_TUPLES


def needs_vacuum(dead):
    return dead > THRESHOLD


def run_workload(*, churn_per_tick, ticks, pinned=False):
    dead = 0
    runs = 0
    max_dead = 0
    for tick in range(1, ticks + 1):
        dead += churn_per_tick  # each update leaves one dead version
        max_dead = max(max_dead, dead)
        if tick % NAPTIME_TICKS == 0 and needs_vacuum(dead):
            runs += 1
            if not pinned:
                dead = max(0, dead - CLEAN_BUDGET)
    return {"dead": dead, "runs": runs, "max_dead": max_dead}


def main():
    healthy = run_workload(churn_per_tick=400, ticks=20)
    assert healthy["runs"] >= 1, "healthy workload should trigger vacuum"
    assert healthy["dead"] < THRESHOLD, healthy
    assert healthy["max_dead"] < 3000, healthy

    overloaded = run_workload(churn_per_tick=1500, ticks=20)
    assert overloaded["runs"] >= 1, "overloaded workload still triggers vacuum"
    assert overloaded["dead"] > THRESHOLD, overloaded

    pinned = run_workload(churn_per_tick=400, ticks=20, pinned=True)
    assert pinned["runs"] >= healthy["runs"], (pinned, healthy)
    assert pinned["dead"] == 400 * 20, pinned
    assert pinned["dead"] > healthy["dead"], (pinned, healthy)

    print(f"healthy: runs={healthy['runs']} dead={healthy['dead']} max={healthy['max_dead']}")
    print(f"overloaded: runs={overloaded['runs']} dead={overloaded['dead']} max={overloaded['max_dead']}")
    print(f"pinned: runs={pinned['runs']} dead={pinned['dead']}")
    print("PASS: threshold triggers, cost limit falls behind, pinned xmin reclaims nothing")


if __name__ == "__main__":
    main()

Expect passing assertions and a PASS line. The healthy workload triggers several runs and ends below the threshold. The overloaded workload triggers on every check yet ends far above it, which is the falling-behind signature. The pinned workload runs just as often but reclaims nothing. The script was verified with the system Python 3 on macOS; it does not connect to PostgreSQL.

Further reading

Sources