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

Deleted does not mean gone

An UPDATE or DELETE leaves old PostgreSQL row versions until they are no longer needed by any relevant snapshot. A long transaction can delay that point while other sessions keep changing rows. The transaction alone does not manufacture new bloat in an otherwise idle table. Reclaiming obsolete versions and making their space reusable is distinct from merely committing the delete.

Ordinary VACUUM usually leaves table size unchanged: it frees space inside existing files for future rows. Empty pages at the end can sometimes be truncated, but a general compaction needs a rewrite such as VACUUM FULL, with additional space and stronger locking. A stable file size after successful vacuum is therefore not evidence that cleanup failed. Distinguish live data, reusable internal space, and space returned to the operating system.

Reclaiming and statistics are separate jobs

Autovacuum has a launcher and multiple workers, not one worker that cleans everything on a fixed schedule. It decides whether tables need VACUUM or ANALYZE using activity estimates and age-related rules. ANALYZE collects planner distributions independently; plain VACUUM is not a substitute for refreshing those statistics. Insert-driven and wraparound-related work also mean dead-tuple count is not the only reason a table needs maintenance.

Cost-based delay throttles vacuum through pauses after estimated I/O work. The cost limit is not a maximum number of tuples one invocation can remove before it stops. Thresholds decide eligibility, worker availability affects scheduling, and storage capacity and throttling affect progress. Diagnose which stage is limiting the table rather than treating every slow cleanup as the same tuning problem.

When it breaks

Churn can outpace cleanup. Dead-tuple estimates and relation sizes then rise across several observation windows. Inspect completed runs, cancellations, worker availability and I/O alongside the write rate. Extra hardware can help an actual resource bottleneck, but restarting the database does not by itself make retained versions reusable. Neither a low threshold nor a high worker count guarantees a better result under the same I/O limit.

Alternatively, cleanup may run but leave particular old versions because a snapshot or retained xmin still needs them. Check transaction and replication state before intervening. An old transaction start time is a clue, not proof that it holds the relevant horizon. Ending an application transaction or removing a replication slot can disrupt work or require rebuilding a consumer; identify its owner and recovery requirements first.

Finally, do not diagnose bloat solely from one n_dead_tup value or a slow plan. These counts are estimates, and page layout, visibility, query selectivity and stale statistics can all change observed reads. Compare trends, vacuum reports and the actual plan. A full-size file with reusable space may be healthy, while a small count can still hide a retention or freezing problem.

Three labeled toy-model containers contrast healthy and overloaded open drains with an entirely pinned blocked drain, followed by invented trigger, cadence and allowance values and evidence limits.
Container levels are qualitative. Model work units, cadence and capacity are invented; they are neither PostgreSQL measurements nor vacuum cost-limit semantics. The model was not run.

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 contrast a trigger, finite cleanup throughput, and an entirely pinned backlog. Work units and check intervals are invented. The base-plus-scale trigger borrows one idea from autovacuum; it omits PostgreSQL 18’s maximum threshold, insert triggers, freezing and scheduling. RECLAIM_CAPACITY is a toy throughput allowance, not vacuum_cost_limit.

"""Illustrative cleanup-backlog model, not a PostgreSQL vacuum simulator."""

BASE_THRESHOLD = 50
SCALE_FACTOR = 0.2
CHECK_INTERVAL = 2
RECLAIM_CAPACITY = 1600  # arbitrary work units per model cleanup opportunity
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 % CHECK_INTERVAL == 0 and needs_vacuum(dead):
            runs += 1
            if not pinned:
                dead = max(0, dead - RECLAIM_CAPACITY)
    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: model threshold, limited throughput, and pinned backlog")


if __name__ == "__main__":
    main()

Keep Python assertions enabled; 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 more often but reclaims nothing. The original article reports both copies passing on Python 3.12.14; this source review did not rerun the model or recover that run’s raw log: healthy ends with 1,600 units, overloaded with 14,000, and pinned with 8,000. These are model outputs, not database measurements. Real vacuum does not stop after reclaiming this invented allowance, and a real old snapshot need not block every dead version. Use the model only to distinguish insufficient throughput from unavailable work, then collect database evidence for either diagnosis.

Further reading

Sources