Autovacuum Tuning for Geometry Tables

Autovacuum is the quiet subsystem that decides whether a high-write PostGIS table stays fast or slowly rots, which makes it a first-class concern for Spatial Performance Monitoring & Observability. A fleet telemetry platform is the sharpest illustration: the vehicle_tracks table takes a geometry(Point,4326) position update from every vehicle every few seconds, and each of those updates is an MVCC dead tuple in waiting. PostgreSQL ships with autovacuum defaults calibrated for a general OLTP mix — a 20 percent dead-tuple scale factor, a modest cost limit, throttled workers — and those defaults are wrong by an order of magnitude for a table whose hot column is a GiST-indexed geometry. Left on defaults, dead tuples pile up, the GiST index bloats, planner statistics drift, and the optimizer eventually abandons the spatial index for a sequential scan. This page is the tuning workflow that keeps a geometry-heavy table lean and its query plans stable.

The mechanism that makes geometry tables special is the interaction between updates and the spatial index. PostgreSQL’s Heap-Only Tuple (HOT) optimization avoids new index entries when an UPDATE touches only unindexed columns. But the position column is the GiST index key, so every position ping is a non-HOT update: a new heap tuple, a new index entry, and a dead version of each left behind. Dead tuples and dead index entries therefore accumulate in lockstep with the write rate. Autovacuum must both reclaim that dead weight (VACUUM) and refresh the statistics the planner relies on (ANALYZE), and on a busy fleet table it must do both far more often than the defaults allow. Getting the scale factors, thresholds, and cost limits right is the difference between a table that self-maintains and one that needs emergency REINDEX runs, the subject of detecting GiST index bloat.

Prerequisites and Infrastructure Validation

Confirm autovacuum is globally enabled before tuning any single table — per-table settings do nothing if the daemon is off:

SHOW autovacuum;                 -- must be 'on'
SHOW autovacuum_max_workers;     -- ensure enough workers for your table count
SELECT PostGIS_Full_Version();

Verify the geometry column and its SRID, and confirm the GiST index that tuning is meant to protect actually exists:

SELECT Find_SRID('public', 'vehicle_tracks', 'position') AS position_srid;

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'vehicle_tracks'
  AND indexdef ILIKE '%gist%';

position_srid should be 4326. You should see idx_vehicle_tracks_position_gist defined USING gist (position). Python clients that will later drive verification queries need psycopg (v3) or psycopg2; nothing else is required for tuning itself, which is pure DDL and catalog reads.

Core Execution Workflow

Step 1 — Baseline the Dead-Tuple and Autovacuum State

You cannot tune what you have not measured. Capture the current churn profile of vehicle_tracks from pg_stat_user_tables:

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    n_tup_upd,
    n_tup_hot_upd,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'vehicle_tracks';

Two readings tell you whether the defaults are failing. A dead_pct that regularly sits in double digits means vacuum is not keeping up. A last_autovacuum timestamp that is hours old on a table taking thousands of updates per minute means the 20 percent trigger has not even been reached yet — dead tuples are simply piling up. Note the current autovacuum_count; you will watch it climb after tuning.

Step 2 — Set an Aggressive Vacuum Scale Factor and Threshold

Autovacuum fires when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup. On a ten-million-row table the default 0.2 scale factor waits for two million dead tuples. Override it per table so vacuum fires at roughly 2 percent instead:

ALTER TABLE vehicle_tracks SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- fire at ~2% dead (default 0.20)
    autovacuum_vacuum_threshold    = 5000    -- floor so small tables still vacuum
);

The threshold acts as a fixed floor added to the scale-factor calculation; keeping it modest ensures a table that temporarily shrinks still gets vacuumed. For an extremely hot table you can push the scale factor to 0.01. The goal is many small, cheap vacuum sweeps rather than rare, expensive ones — the sawtooth pattern in the diagram above.

Step 3 — Keep Planner Statistics Fresh With autoanalyze

Reclaiming dead tuples is only half the job. The planner chooses the GiST index over a sequential scan using row-count and selectivity estimates that go stale under churn. Tie autoanalyze to a similarly aggressive scale factor:

ALTER TABLE vehicle_tracks SET (
    autovacuum_analyze_scale_factor = 0.02,  -- re-analyze at ~2% change
    autovacuum_analyze_threshold    = 5000
);

Fresh statistics keep reltuples and the geometry column’s histogram accurate, so the optimizer continues to estimate spatial filter selectivity correctly and keeps picking the index. When those estimates drift, the planner can wrongly conclude a bounding-box filter returns most of the table and switch to a sequential scan — a regression you can confirm with query plan analysis with EXPLAIN.

Step 4 — Raise the Cost Limit So Vacuum Keeps Pace

Autovacuum throttles itself using a cost budget: it pauses for autovacuum_vacuum_cost_delay after accumulating autovacuum_vacuum_cost_limit worth of page-access cost. The defaults are conservative and, on a table producing dead tuples faster than a throttled worker can clear them, vacuum falls permanently behind. Give the worker more room per cycle:

ALTER TABLE vehicle_tracks SET (
    autovacuum_vacuum_cost_delay = 2,        -- ms; small pause, not zero
    autovacuum_vacuum_cost_limit = 2000      -- default is 200
);

Raising the cost limit rather than dropping the delay to zero preserves a measure of I/O politeness while letting the worker do ten times the work between pauses. On NVMe-backed storage this lets vacuum keep pace with a busy vehicle_tracks table without saturating the disks that also serve live queries.

Step 5 — Verify the Settings Landed

Confirm the per-table overrides are recorded in the catalog:

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'vehicle_tracks';

reloptions should list every override you set. A NULL here means the ALTER TABLE did not apply — check for typos in the option names, which PostgreSQL silently ignores only if malformed at parse time.

Performance Considerations

Watching the Effect With pg_stat_user_tables

The single most useful ongoing query watches dead-tuple ratio and autovacuum recency together. Trend it, do not just spot-check it:

SELECT
    now() - last_autovacuum   AS since_autovacuum,
    now() - last_autoanalyze  AS since_autoanalyze,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'vehicle_tracks';

After tuning, since_autovacuum should be minutes not hours, dead_pct should hover in the low single digits, and autovacuum_count should climb steadily. If dead_pct still trends upward, the scale factor is still too high or the cost limit still too low for the write rate.

To fold this into an application health check, poll the same counters from Python with psycopg and raise an alert when the dead ratio breaches a ceiling or autovacuum has gone quiet for too long:

import psycopg
from psycopg.rows import dict_row

DEAD_PCT_CEILING = 8.0        # percent dead tuples before alerting
STALE_VACUUM_SECONDS = 1800   # alert if no autovacuum within 30 minutes

def check_geometry_table_health(dsn: str, table: str = "vehicle_tracks") -> list[str]:
    alerts: list[str] = []
    with psycopg.connect(dsn, row_factory=dict_row) as conn:
        row = conn.execute(
            """
            SELECT
                n_dead_tup,
                round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
                extract(epoch FROM now() - last_autovacuum)  AS vac_age_s,
                extract(epoch FROM now() - last_autoanalyze) AS ana_age_s
            FROM pg_stat_user_tables
            WHERE relname = %s
            """,
            (table,),
        ).fetchone()

    if row is None:
        return [f"{table}: not found in pg_stat_user_tables"]
    if (row["dead_pct"] or 0) > DEAD_PCT_CEILING:
        alerts.append(f"{table}: dead_pct={row['dead_pct']}% exceeds {DEAD_PCT_CEILING}%")
    if row["vac_age_s"] is None or row["vac_age_s"] > STALE_VACUUM_SECONDS:
        alerts.append(f"{table}: autovacuum stale (age={row['vac_age_s']}s)")
    return alerts

Wire the returned list into whatever alerting channel the platform already uses; an empty list means the tuning is holding.

GUCs and Their Roles

Statistics Target for Geometry Selectivity

For a geometry column feeding a GiST index, a higher statistics target sharpens the selectivity estimates the planner uses:

ALTER TABLE vehicle_tracks ALTER COLUMN position SET STATISTICS 500;
ANALYZE vehicle_tracks;

A larger sample makes bounding-box selectivity estimates more accurate on skewed spatial distributions — dense downtown clusters versus sparse rural tracks — which further stabilizes index selection.

Common Failure Modes and Fixes

Dead Tuples Keep Climbing Despite Tuning

Diagnosis: Vacuum is firing but cannot finish before the next flood of updates, or a long-running transaction is holding back the dead-tuple horizon so vacuum cannot remove anything.

Fix:

-- Find the oldest transaction pinning the vacuum horizon
SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start
LIMIT 5;

Terminate or fix the offending long transaction, then raise autovacuum_vacuum_cost_limit further so the worker can drain the backlog.

Autovacuum Never Runs on the Table

Diagnosis: Autovacuum is globally disabled, all workers are perpetually busy on other tables, or the table has autovacuum_enabled = false set.

Fix:

SELECT reloptions FROM pg_class WHERE relname = 'vehicle_tracks';
-- If it shows autovacuum_enabled=false, re-enable it:
ALTER TABLE vehicle_tracks SET (autovacuum_enabled = true);
-- And raise the global worker count if all workers are saturated:
--   autovacuum_max_workers in postgresql.conf, then reload.

The Planner Switched to a Sequential Scan After Heavy Writes

Diagnosis: Statistics went stale between autoanalyze runs, so the optimizer’s selectivity estimate for the bounding-box filter is wrong and it now prefers a sequential scan.

Fix:

ANALYZE vehicle_tracks;
EXPLAIN (ANALYZE, BUFFERS)
SELECT vehicle_id, recorded_at
FROM vehicle_tracks
WHERE position && ST_MakeEnvelope(-118.30, 33.95, -118.20, 34.05, 4326);

Confirm the plan shows an Index Scan on the GiST index. If manual ANALYZE fixes it repeatedly, lower autovacuum_analyze_scale_factor further.

Vacuum Saturates I/O and Slows Live Queries

Diagnosis: The cost limit was raised too far or the delay dropped to zero, so vacuum now competes aggressively with the query workload.

Fix: Restore a small non-zero delay and a moderate limit:

ALTER TABLE vehicle_tracks SET (
    autovacuum_vacuum_cost_delay = 2,
    autovacuum_vacuum_cost_limit = 1200
);

Verification

Confirm the tuning holds under real load with a short checklist:

-- 1. Overrides are recorded
SELECT reloptions FROM pg_class WHERE relname = 'vehicle_tracks';

-- 2. Dead-tuple ratio is low and autovacuum is recent
SELECT n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       now() - last_autovacuum AS since_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'vehicle_tracks';

-- 3. The GiST index is still chosen
EXPLAIN
SELECT vehicle_id, recorded_at
FROM vehicle_tracks
WHERE position && ST_MakeEnvelope(-118.30, 33.95, -118.20, 34.05, 4326);

A healthy result is dead_pct in the low single digits, since_autovacuum measured in minutes, and an Index Scan (not Seq Scan) in the plan. For batch scenarios where autovacuum reacts too slowly on its own — bulk position backfills and mass loads — pair this tuning with the scheduled maintenance job described in automating VACUUM ANALYZE for geometry-heavy tables.

Frequently Asked Questions

Why do geometry tables need more aggressive autovacuum than ordinary tables?

Because updating an indexed geometry column defeats Heap-Only Tuple optimization. Every position UPDATE writes a new heap tuple and a new GiST index entry, so dead tuples and dead index entries accumulate much faster than on a table whose hot columns are unindexed. The default 20 percent scale factor lets far too much dead weight build up before vacuum fires on a high-ping fleet table.

Does a lower autovacuum_analyze_scale_factor really affect index choice?

Yes. The planner decides between a GiST index scan and a sequential scan using column statistics and row-count estimates. After heavy churn those estimates drift, and a stale reltuples can make the planner think the table is smaller or the filter less selective than it is, tipping it toward a sequential scan. Frequent autoanalyze keeps the estimates honest so the GiST path stays chosen.

Will setting autovacuum_vacuum_cost_delay to zero overload my disks?

It can, if applied globally. The safer pattern is to keep a small per-table cost delay and instead raise autovacuum_vacuum_cost_limit, which lets a worker do more work per cycle without removing throttling entirely. On fast NVMe storage a cost delay of 2 milliseconds with a cost limit of 2000 lets vacuum keep pace with a busy vehicle_tracks table without saturating I/O.

Should I still run manual VACUUM if autovacuum is well tuned?

For steady-state churn, well-tuned autovacuum is enough. The exception is bulk operations: a large batch load or mass position backfill creates a spike of dead tuples that autovacuum reacts to only after the fact. Running a targeted VACUUM ANALYZE immediately after such a job restores stats and reclaims space without waiting for the next autovacuum cycle.

Related Topics

• Spatial Performance Monitoring & Observability — parent reference for the full monitoring and observability toolkit
• Automating VACUUM ANALYZE for Geometry-Heavy Tables — scheduled maintenance for bulk loads that autovacuum reacts to too slowly
• Detecting GiST Index Bloat — what happens when autovacuum falls behind and the index needs a rebuild
• Query Plan Analysis with EXPLAIN — confirm the GiST index is still chosen after statistics change