VACUUM FREEZE and Geometry TOAST Tables

Problem Statement

A parcel table has been tuned: aggressive scale factors, a raised cost limit, healthy dead-tuple counts. Then one Tuesday afternoon an unthrottled anti-wraparound vacuum starts on a table nobody was watching — the TOAST table holding the geometry — and saturates the disk for three hours. This page covers the freezing side of autovacuum tuning for geometry tables, which is the part that geometry storage makes different.

Why the Naive Approach Fails

Tuning the visible table does nothing for the invisible one. On a polygon table, the main heap holds narrow rows and the TOAST table holds nearly all the bytes:

Where the bytes are A parcels table of 41 million rows occupies 6.2 gigabytes in the main heap and 148 gigabytes in its TOAST table, because the polygon geometry is over the TOAST threshold. Autovacuum settings applied to the main table govern 4 percent of the storage. Where a geometry table actually keeps its data parcels (heap) 6.2 GB — ids, names, pointers pg_toast_16418 148 GB — the geometry itself autovacuum settings on "parcels" govern the top bar only Every ALTER TABLE parcels SET (autovacuum_…) you have written applies to four percent of the storage. The other ninety-six percent is governed by defaults you never chose.

The second problem is bunching. Tables created together, loaded together and left at default settings all reach autovacuum_freeze_max_age at approximately the same time, so the anti-wraparound vacuums arrive as a group rather than spread out.

Production-Ready Implementation

Measure both ages, per table, in one query:

sql
SELECT
    c.relname                                            AS relation,
    pg_size_pretty(pg_relation_size(c.oid))              AS size,
    age(c.relfrozenxid)                                  AS xid_age,
    round(100 * age(c.relfrozenxid)::numeric
          / current_setting('autovacuum_freeze_max_age')::numeric, 1) AS pct_to_forced,
    CASE WHEN t.relname IS NOT NULL THEN 'toast' ELSE 'heap' END AS kind
FROM pg_class c
LEFT JOIN pg_class t ON t.reltoastrelid = c.oid
WHERE c.relkind IN ('r', 't')
  AND c.relnamespace = 'public'::regnamespace
     OR c.relname LIKE 'pg_toast%'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

Then set parameters on both halves. The toast. prefix is the piece almost everyone misses:

sql
-- the main table
ALTER TABLE parcels SET (
    autovacuum_vacuum_scale_factor  = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_freeze_max_age       = 150000000,
    autovacuum_vacuum_cost_limit    = 2000
);

-- and the TOAST table, where the geometry lives
ALTER TABLE parcels SET (
    toast.autovacuum_vacuum_scale_factor = 0.05,
    toast.autovacuum_freeze_max_age      = 150000000,
    toast.autovacuum_vacuum_cost_limit   = 2000
);

Lowering autovacuum_freeze_max_age below the 200-million default sounds backwards — it makes forced vacuums happen sooner — but it makes them happen smaller and more often, which is the whole objective. A freeze that runs at 150 million transactions has less to do than one deferred to 200 million.

Spreading the schedule prevents the group arrival:

python
import random
import psycopg

BASE_AGE = 150_000_000
JITTER = 30_000_000        # ±20% so tables do not freeze together


def stagger_freeze_ages(dsn: str, tables: list[str]) -> dict[str, int]:
    """Give each table a slightly different freeze threshold."""
    assigned = {}
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        for name in tables:
            age = BASE_AGE + random.randint(-JITTER, JITTER)
            cur.execute(
                f"ALTER TABLE {name} SET ("
                f"  autovacuum_freeze_max_age = {age},"
                f"  toast.autovacuum_freeze_max_age = {age})"
            )
            assigned[name] = age
        conn.commit()
    return assigned
Bunched freezes and staggered ones With identical default thresholds, six large tables cross the forced-vacuum line within the same week and their anti-wraparound vacuums overlap. With staggered thresholds the same six are spread across two months and never overlap. Six tables, two schedules default thresholds — all six freeze in the same week unthrottled I/O, all at once staggered thresholds — spread over two months Same total work either way. One version is a background hum; the other is an incident.

Configuration and Tuning Knobs

vacuum_freeze_min_age decides how old a row must be before a normal vacuum freezes it. Lowering it means ordinary vacuums do freezing work incrementally, which is exactly what keeps the forced ones small. Fifty million is a reasonable value on an active table.

vacuum_freeze_table_age triggers a whole-table scan during a normal autovacuum rather than waiting for the anti-wraparound threshold. Setting it comfortably below autovacuum_freeze_max_age means the expensive scan happens under the cost limiter instead of outside it.

autovacuum_vacuum_cost_limit applies to normal vacuums only — an anti-wraparound vacuum ignores it entirely. That asymmetry is the argument for never letting one become necessary.

toast_tuple_target changes when values get TOASTed. It is rarely worth touching, but on a table of small polygons that sit just over the threshold, raising it can keep geometry inline and eliminate the TOAST table’s role entirely.

Anti-wraparound vacuum duration by table Duration of a forced anti-wraparound vacuum on four relations: the parcels heap takes 4 minutes, its TOAST table 3 hours 10 minutes, an ordinary lookup table 8 seconds and the audit table 40 seconds. Where a forced vacuum actually spends its time parcels (heap) 4 min pg_toast for parcels 3 h 10 min · unthrottled, uncancellable regions 8 s parcel_validity 40 s The second row is why the toast. settings matter more than everything else on this page.

Verification Steps

sql
-- the ten oldest relations, main and TOAST together
SELECT c.relname, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;

-- is anything running an anti-wraparound vacuum right now?
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

-- and confirm the toast parameters actually took
SELECT relname, reloptions FROM pg_class
WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE relname = 'parcels');

The third query is the one that catches the common mistake: if reloptions is null on the TOAST relation, the toast. settings were never applied and the largest object in the database is still running on defaults.

Gotchas Checklist

  • Settings on the main table do not reach the TOAST table. Only the toast. prefixed parameters do, and forgetting them is the single most common cause of surprise vacuums on spatial schemas.
  • Anti-wraparound vacuums cannot be cancelled safely. Killing one just means it starts again, and repeatedly killing it eventually risks the database refusing writes entirely.
  • A long-running transaction blocks freezing. The same open snapshot that stops dead-tuple reclamation also stops relfrozenxid advancing, so the age keeps climbing while vacuums appear to run normally.
  • Replicas do not freeze independently. Freezing happens on the primary and replicates; a hot standby with hot_standby_feedback on can hold back the primary’s horizon and cause exactly this problem from a distance.
  • Monitor age, not vacuum counts. A table being vacuumed frequently tells you nothing about whether its transaction age is advancing.

Watching the Freeze Debt Build

Freeze work is unusual among maintenance tasks in that ignoring it is free until suddenly it is not. There is no gradual degradation to notice — the table behaves normally until transaction age crosses autovacuum_freeze_max_age, at which point PostgreSQL starts an anti-wraparound vacuum that cannot be cancelled and will not yield. On a geometry table with a large TOAST relation, that vacuum can run for hours.

The query that makes the debt visible is short, and it belongs on a dashboard rather than in someone’s history:

sql
SELECT c.relname,
       age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       t.relname AS toast_rel,
       age(t.relfrozenxid) AS toast_xid_age
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relkind = 'r' AND age(c.relfrozenxid) > 100000000
ORDER BY age(c.relfrozenxid) DESC;

Two things about this query matter. It reports the TOAST relation’s age separately, because a TOAST table has its own relfrozenxid and can fall behind the heap it belongs to — and on a geometry table, the TOAST relation is usually the larger of the two. And it filters on a threshold well below the default 200-million wraparound trigger, so the dashboard shows the debt accumulating rather than the emergency arriving.

The remedy is to make the work happen in windows you choose. A manual VACUUM (FREEZE, VERBOSE) on the largest geometry table during a quiet period costs the same total effort as the emergency vacuum but happens when someone is watching. Scheduling it whenever a table crosses a hundred million transactions of age keeps every table permanently outside the anti-wraparound path, which is the only reliable way to ensure the forced vacuum never starts during a traffic peak.