Automating VACUUM ANALYZE for Geometry-Heavy Tables

This page addresses one specific gap left open by Autovacuum Tuning for Geometry Tables: even a perfectly tuned autovacuum reacts to bulk operations only after the damage is done. When a nightly job backfills or replaces the geometry(Point,4326) position of millions of rows in a fleet’s vehicle_tracks table, or bulk-loads a new batch of trip_segments linestrings, it creates a sudden spike of dead tuples and instantly stale statistics. Autovacuum will get to it eventually, but “eventually” can mean the next hour of queries runs against a bloated table with a planner that thinks the data looks nothing like it now does. The fix is a targeted, scheduled VACUUM ANALYZE that runs immediately after the load, tunes the affected table’s thresholds, and verifies that statistics are actually fresh before it declares success.

Why the Naive Approach Fails

A nightly job versus a continuous one Two day-long timelines. The nightly VACUUM FULL leaves dead tuples climbing all day and then takes an exclusive lock for forty minutes at three in the morning. Tuned autovacuum keeps dead tuples in a narrow band all day and never takes a blocking lock. Dead tuples through one day, two strategies nightly VACUUM FULL 40 min ACCESS EXCLUSIVE tuned autovacuum no blocking lock, ever VACUUM FULL rewrites the table and its indexes — on a geometry table that is hours of I/O for space you will refill.

The first instinct is to lean entirely on autovacuum, or to fire a blanket database-wide VACUUM from a shell script. Both fail for a high-throughput geometry table.

Relying on autovacuum after a bulk backfill means the planner keeps using pre-load statistics until the analyze scale factor is crossed and a worker happens to be free. In the meantime a spatial query can pick a sequential scan because the optimizer’s row estimate is wrong.

The blanket approach fails differently — it tries to run VACUUM from inside a transaction and errors out:

python
# Broken: psycopg2 opens a transaction by default, and VACUUM cannot run in one
import psycopg2
conn = psycopg2.connect(dsn)
cur = conn.cursor()
cur.execute("VACUUM ANALYZE vehicle_tracks;")
# psycopg2.errors.ActiveSqlTransaction:
#   VACUUM cannot run inside a transaction block

Even when corrected, a database-wide VACUUM wastes enormous effort re-scanning cold tables the batch never touched, and offers no verification that the one table you care about actually got fresh statistics.

Production-Ready Implementation

One autovacuum policy per table class Three table classes with their settings. High-churn tracking tables get an aggressive scale factor and a raised cost limit. Append-only history tables need analyze but barely any vacuum. Static reference tables need almost nothing, and their default settings are already correct. Three table shapes, three policies High churn vehicle_positions vacuum_scale_factor = 0.01 vacuum_cost_limit = 2000 updates every row hourly Append only radar_sweeps analyze_scale_factor = 0.005 vacuum_scale_factor = 0.2 (default) no dead rows, drifting stats Mostly static admin_boundaries defaults are fine Run ANALYZE explicitly after each quarterly boundary import two loads a year Global settings are a compromise across all three. Per-table storage parameters cost nothing and never expire.

The maintenance routine below runs in autocommit mode, vacuums and analyzes only the tables a load touched, and then verifies statistics freshness by reading pg_stat_user_tables. It is designed to be invoked at the end of a batch load or from a scheduler.

python
from __future__ import annotations

import logging
import sys
from datetime import datetime, timedelta, timezone

import psycopg
from psycopg.rows import dict_row

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("geom_vacuum")

# Tables whose geometry columns are rewritten by bulk loads.
TARGET_TABLES = ["vehicle_tracks", "trip_segments"]

# A load is considered "settled" only if statistics were refreshed within this
# window and the dead-tuple ratio is back under the ceiling.
FRESHNESS_WINDOW = timedelta(minutes=5)
DEAD_PCT_CEILING = 5.0


def vacuum_analyze_table(conn: psycopg.Connection, table: str) -> None:
    """Run VACUUM (ANALYZE) on one table. Requires autocommit mode."""
    # psycopg.sql.Identifier safely quotes the table name; VACUUM does not
    # accept a bound parameter for the target, so we must build the statement.
    from psycopg import sql
    stmt = sql.SQL("VACUUM (ANALYZE, VERBOSE) {}").format(sql.Identifier(table))
    log.info("Running VACUUM (ANALYZE) on %s", table)
    conn.execute(stmt)


def tune_thresholds(conn: psycopg.Connection, table: str) -> None:
    """Ensure aggressive per-table autovacuum settings are in place."""
    from psycopg import sql
    stmt = sql.SQL(
        "ALTER TABLE {} SET ("
        "  autovacuum_vacuum_scale_factor = 0.02,"
        "  autovacuum_analyze_scale_factor = 0.02,"
        "  autovacuum_vacuum_threshold = 5000,"
        "  autovacuum_vacuum_cost_limit = 2000"
        ")"
    ).format(sql.Identifier(table))
    conn.execute(stmt)


def verify_freshness(conn: psycopg.Connection, table: str) -> bool:
    """Confirm ANALYZE ran recently and dead tuples are back under control."""
    row = conn.execute(
        """
        SELECT
            greatest(last_analyze, last_autoanalyze) AS last_stats,
            n_dead_tup,
            n_live_tup,
            round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
        FROM pg_stat_user_tables
        WHERE relname = %s
        """,
        (table,),
    ).fetchone()

    if row is None or row["last_stats"] is None:
        log.error("%s: no statistics timestamp found", table)
        return False

    age = datetime.now(timezone.utc) - row["last_stats"]
    dead_pct = float(row["dead_pct"] or 0.0)
    fresh = age <= FRESHNESS_WINDOW and dead_pct <= DEAD_PCT_CEILING
    log.info(
        "%s: stats age=%s, dead_pct=%.2f%% -> %s",
        table, age, dead_pct, "OK" if fresh else "STALE",
    )
    return fresh


def run(dsn: str, tables: list[str]) -> int:
    failures = 0
    # autocommit is mandatory: VACUUM cannot run inside a transaction block.
    with psycopg.connect(dsn, autocommit=True, row_factory=dict_row) as conn:
        # A generous statement timeout guards against a runaway vacuum, but must
        # be large enough for a big table. 0 disables it if you prefer.
        conn.execute("SET statement_timeout = '1800s'")
        for table in tables:
            tune_thresholds(conn, table)
            vacuum_analyze_table(conn, table)
            if not verify_freshness(conn, table):
                failures += 1
    return failures


if __name__ == "__main__":
    dsn = "postgresql://maint:secret@primary.internal:5432/fleet"
    failed = run(dsn, TARGET_TABLES)
    if failed:
        log.error("%d table(s) failed freshness verification", failed)
        sys.exit(1)
    log.info("All target tables vacuumed, analyzed, and verified fresh")

Scheduling with cron

To trigger the job on a fixed schedule from an external host, add a crontab entry that runs after the nightly load window:

cron
# Run 20 minutes past 02:00, after the 02:00 bulk position backfill completes
20 2 * * *  /opt/fleet/.venv/bin/python /opt/fleet/maintenance/geom_vacuum.py >> /var/log/geom_vacuum.log 2>&1

Scheduling with pg_cron

If you prefer to keep the schedule inside the database, pg_cron can call VACUUM (ANALYZE) directly without any external host. Install and schedule it against the primary:

sql
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Nightly targeted maintenance, 02:20 server time
SELECT cron.schedule(
    'vacuum_vehicle_tracks',
    '20 2 * * *',
    'VACUUM (ANALYZE) vehicle_tracks'
);

SELECT cron.schedule(
    'vacuum_trip_segments',
    '25 2 * * *',
    'VACUUM (ANALYZE) trip_segments'
);

The Python job is preferable when the vacuum must be triggered by a batch-completion event rather than a wall-clock time, or when you want the freshness verification and non-zero exit code to feed an alerting pipeline. pg_cron is preferable for pure time-based maintenance with no external moving parts.

Configuration and Tuning Knobs

Autocommit is non-negotiable. The psycopg.connect(..., autocommit=True) flag is what allows VACUUM to run at all; without it the driver wraps the statement in a transaction and PostgreSQL rejects it.

Statement timeout bounds a runaway vacuum but must be generous for large tables:

Knob Value Reason
autocommit True Required for VACUUM to execute
statement_timeout 1800s or 0 Cap the job, or disable for very large tables
FRESHNESS_WINDOW 5 min How recent last_analyze must be to pass
DEAD_PCT_CEILING 5.0 Maximum acceptable dead-tuple ratio after the run

VACUUM ANALYZE versus ANALYZE. If the bulk step only appended rows via COPY, swap the verb for a cheaper ANALYZE — no dead tuples were created, so there is nothing to reclaim. Reserve the full VACUUM (ANALYZE) for loads that update or replace existing geometry, which is the case for an in-place position backfill.

Parallelism. For a very large vehicle_tracks table, let vacuum use parallel workers on its indexes:

sql
VACUUM (ANALYZE, PARALLEL 4) vehicle_tracks;

Verification Steps

Reading the proof out of pg_stat_user_tables A single statistics row with four callouts. n_live_tup and n_dead_tup give the current ratio. n_mod_since_analyze shows whether statistics are drifting. last_autovacuum and last_autoanalyze show the policy is actually firing rather than merely being configured. Four columns tell you whether the policy works relname | vehicle_positions n_live_tup | 41,882,104 n_dead_tup | 212,447 n_mod_since_analyze| 88,120 last_autovacuum | 14 min ago last_autoanalyze | 9 min ago dead ratio 0.5% — the scale factor is holding 0.2% modified — estimates fresh both firing on their own A NULL last_autovacuum on a busy table means the policy exists on paper only — check for a blocked xmin horizon.

The job verifies itself, but confirm the mechanism by hand once. Immediately after a load, check the statistics timestamp moved and the dead ratio dropped:

sql
SELECT
    greatest(last_analyze, last_autoanalyze) AS last_stats,
    now() - greatest(last_analyze, last_autoanalyze) AS stats_age,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'vehicle_tracks';

A stats_age under five minutes and a dead_pct in the low single digits confirm the run took effect. Then verify the planner benefits by checking that a bounding-box query still resolves to a GiST index scan on fresh statistics:

sql
EXPLAIN
SELECT vehicle_id, recorded_at
FROM vehicle_tracks
WHERE position && ST_MakeEnvelope(-87.70, 41.85, -87.60, 41.95, 4326);

The plan should read Index Scan using idx_vehicle_tracks_position_gist. If it shows a Seq Scan, the statistics did not refresh — investigate whether the job actually reached the ANALYZE stage.

Gotchas Checklist

  • Forgetting autocommit is the most common failure: without it, psycopg reports “VACUUM cannot run inside a transaction block”. Always connect with autocommit=True for this job.
  • Never build the VACUUM target with string formatting from untrusted input. VACUUM cannot take a bound parameter for the table name, so use psycopg.sql.Identifier to quote it safely rather than an f-string.
  • A blanket database-wide VACUUM wastes hours re-scanning cold tables the load never touched. Target only the tables the batch modified.
  • ANALYZE is enough for append-only COPY loads. Running a full VACUUM after a pure insert wastes I/O reclaiming space that was never freed; reserve VACUUM ANALYZE for update-style backfills.
  • A long-running transaction elsewhere blocks reclamation. VACUUM cannot remove tuples still visible to an older snapshot, so ensure batch loaders commit promptly and no reporting query holds a transaction open across the maintenance window.