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

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

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

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.