Scheduling GiST Index Vacuum for Spatial Tables

Autovacuum defaults were written for heap tables with predictable row turnover, and they consistently arrive too late for a GiST index sitting on a high-churn spatial table. A moving-asset layer that updates every vehicle position every few seconds, or a parcels table re-tiled nightly, generates dead index tuples far faster than the default scale factor expects, so the index bloats, page density falls, and bounding-box overlap creeps upward while the heap is still comfortably below its autovacuum trigger. This page is the focused operational guide to fixing that: tuning per-table autovacuum thresholds for the dead-tuple pattern a GiST index actually produces, adding scheduled VACUUM and REINDEX passes with pg_cron for the churn autovacuum cannot keep up with, and confirming the result with the same health gauges you already collect. It sits under Spatial Index Health Monitoring and within Geospatial Observability Architecture & Fundamentals.

Dead-tuple bloat between default autovacuum and a tuned schedule A time axis runs left to right over one day. A rising sawtooth shows the index dead-tuple fraction climbing as churn adds dead tuples. Under default autovacuum settings the trigger fires late, so the sawtooth overshoots a dashed healthy-ceiling line before each vacuum drops it. A second, lower sawtooth shows a tuned per-table threshold plus a scheduled nightly vacuum firing earlier and more often, keeping the fraction below the ceiling throughout. dead-tuple fraction time (one day of churn) healthy ceiling · bloat_ratio 0.30 default · triggers late tuned threshold + nightly pg_cron VACUUM default autovacuum tuned + scheduled

The two sawtooths above are the whole argument. Default autovacuum lets the dead-tuple fraction overshoot the healthy ceiling before each pass because its trigger is proportional to heap row count, not index dead-tuple accumulation; a lower per-table threshold plus a scheduled pass keeps the fraction flat under the ceiling. Getting there means three changes — a tuned autovacuum policy, a scheduled maintenance job, and a post-vacuum verification step — each of which reads from the gis.spatial.* gauges defined by the shared geospatial metric taxonomy for ETL.

Problem Framing

The default autovacuum_vacuum_scale_factor of 0.2 means a table is vacuumed once 20% of its rows are dead, plus a small fixed threshold. That fraction is measured against the heap, and it is far too loose for the index on a spatial table that churns. Two distinct patterns break it.

The first is high-frequency update. A fleet-tracking table where each UPDATE geom = ... rewrites the row produces one dead heap tuple and one dead GiST index entry per update. The heap’s dead fraction may sit at a few percent because the table is large and the live set dominates, yet the index has already turned over most of its recent pages. The second is delete-heavy expiry: a rolling window that deletes yesterday’s observations leaves a band of dead index tuples that autovacuum, watching the heap, is slow to reclaim.

Either way the symptom on the health dashboard is the same — gis.spatial.index_bloat_ratio rising steadily while the heap looks fine — and the consequence is measurable: every index scan reads more pages, so a && bounding-box overlap that took 3 ms creeps to 15 ms. Because the geometry query never fails, the regression is invisible without the gauge. Whether the right long-term answer is a different access method entirely, rather than more aggressive vacuuming, is a question weighed in GiST vs BRIN indexes for spatial observability; this page assumes GiST is the correct choice and the job is keeping it healthy.

Before changing anything, capture the current dead-tuple state so the new threshold is grounded in the table’s real turnover rather than a guess:

-- Baseline: dead-tuple pressure and last autovacuum time for spatial tables.
SELECT
  relname                                   AS table,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric
        / GREATEST(n_live_tup, 1), 4)       AS dead_fraction,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
WHERE relname IN ('vehicle_positions', 'parcels')
ORDER BY dead_fraction DESC;

Implementation

Start with per-table autovacuum settings, because storage-level defaults are the wrong altitude for a churny spatial table. Lower the scale factor so the trigger fires on a smaller dead fraction, and raise the cost limit so the vacuum worker is not throttled into irrelevance on a large table:

-- Tune autovacuum for the dead-tuple pattern a GiST index actually produces.
ALTER TABLE vehicle_positions SET (
  autovacuum_vacuum_scale_factor = 0.02,     -- trigger at 2% dead, not 20%
  autovacuum_vacuum_threshold    = 5000,     -- plus a fixed floor for small bursts
  autovacuum_vacuum_cost_limit   = 2000,     -- let the worker do real work per round
  autovacuum_vacuum_cost_delay   = 2         -- ms; keep it responsive, not throttled
);

-- Delete-heavy expiry tables benefit from insert-triggered vacuum too (PG13+).
ALTER TABLE observations SET (
  autovacuum_vacuum_insert_scale_factor = 0.05
);

Tuned autovacuum handles the steady state, but it will not reclaim a large burst promptly, and it never rebuilds the tree structure that page splits degrade. For that, add an explicit scheduled pass with pg_cron. A nightly VACUUM during the quiet window keeps dead tuples from carrying into peak hours, and a less frequent REINDEX CONCURRENTLY repairs the structural fragmentation that VACUUM alone cannot touch:

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Nightly targeted VACUUM of the high-churn table at 02:15.
SELECT cron.schedule(
  'vacuum-vehicle-positions',
  '15 2 * * *',
  $$ VACUUM (ANALYZE, VERBOSE) vehicle_positions $$
);

-- Weekly REINDEX CONCURRENTLY of the GiST index — no heavy lock, off-peak.
SELECT cron.schedule(
  'reindex-vehicle-positions-geom',
  '45 3 * * 0',
  $$ REINDEX INDEX CONCURRENTLY vehicle_positions_geom_gist $$
);

REINDEX CONCURRENTLY builds a replacement index alongside the live one and swaps it in, so read and write traffic continues throughout — essential for a table that must stay online. Reserve a plain REINDEX (which takes an ACCESS EXCLUSIVE lock) for a true maintenance window only. If you run outside pg_cron, the same two commands drop cleanly into a systemd timer or an Airflow maintenance task with a wall-clock guard, following the scheduling patterns established for spatial pipeline health checks across the taxonomy.

Verification & Testing

A scheduled vacuum job is only trustworthy if you can see it working. After each pass, resample the health gauges and confirm the dead-tuple fraction and bloat ratio dropped — a job that “ran successfully” but did not move the numbers is a job vacuuming the wrong table or blocked by a pinned snapshot.

-- Post-vacuum check: did the bloat actually come down?
SELECT
  now()                                        AS checked_at,
  s.relname                                    AS table,
  s.n_dead_tup,
  stat.avg_leaf_density                        AS leaf_density_pct,
  (100 - stat.avg_leaf_density) / 100.0        AS index_bloat_ratio
FROM pg_stat_user_tables s
JOIN pg_indexes i ON i.tablename = s.relname AND i.indexdef ILIKE '%gist%'
JOIN LATERAL pgstatindex(i.indexname) AS stat ON true
WHERE s.relname = 'vehicle_positions';

Wire an alert that fires when the scheduled job runs but bloat does not fall — the maintenance equivalent of a smoke detector — so a silently ineffective vacuum surfaces instead of hiding:

groups:
  - name: spatial-vacuum-effectiveness
    rules:
      # Bloat still high some minutes after the nightly job should have finished.
      - alert: ScheduledVacuumIneffective
        expr: |
          gis_spatial_index_bloat_ratio{table="vehicle_positions"} > 0.30
          and hour() == 3
        for: 15m
        labels: { severity: warning, team: gis-platform }
        annotations:
          summary: "Nightly VACUUM did not reduce bloat on {{ $labels.table }}"
          description: "Check for a pinned xmin (long transaction or lagging replication slot)."

A healthy result is three things at once: last_autovacuum (or the pg_cron run) updated within the expected window, n_dead_tup and index_bloat_ratio visibly lower afterward, and index-scan latency on the && predicate back at baseline. If bloat stays high while the job reports success, the near-certain cause is a snapshot pinning xmin, covered below.

Gotchas & Failure Modes

A pinned snapshot makes every vacuum a no-op. VACUUM can only remove dead tuples older than the oldest live snapshot. A long-running analytics query, an idle-in-transaction session, or a lagging logical replication slot pins xmin, and the dead-tuple count refuses to fall no matter how aggressively you tune autovacuum. Always check pg_stat_activity for the oldest xact_start and pg_replication_slots for a stale xmin before touching thresholds — the fix is killing the transaction or advancing the slot, not more vacuuming.

Cost-throttling silently starves the worker on large tables. With the default autovacuum_vacuum_cost_delay, a vacuum on a multi-hundred-gigabyte spatial table can take longer than the interval between triggers, so it never finishes before the next round is due and bloat outruns it. Raising the cost limit and lowering the delay (as above) is what lets the worker keep pace; monitor the vacuum duration so you notice when a table has outgrown the setting.

REINDEX without CONCURRENTLY locks out the map. A plain REINDEX INDEX takes an ACCESS EXCLUSIVE lock that blocks every read of the table — a tile server or feature API querying that layer stalls for the duration. On any online spatial table use REINDEX INDEX CONCURRENTLY, and only fall back to the locking form inside a declared maintenance window.

Autovacuum tuned, but the index still fragments. Vacuuming reclaims dead space; it does not re-balance the tree. If dead-tuple bloat is under control yet gis.spatial.index_fragmentation_ratio keeps climbing, the cause is page-split imbalance from the load pattern, not vacuum debt — the remedy is the scheduled REINDEX, and the diagnosis belongs to detecting GiST index bloat from bulk inserts, which separates the two failure surfaces.

FAQ

How aggressive should autovacuum_vacuum_scale_factor be for a spatial table?

Set it low enough that the trigger fires before gis.spatial.index_bloat_ratio crosses your healthy ceiling, then verify against the gauge. For a high-update layer, 0.01–0.05 is typical; the fixed autovacuum_vacuum_threshold matters more on smaller tables where a percentage of the row count is only a handful of tuples. Tune to the observed dead-tuple accumulation rate, not to a blanket rule.

Do I still need a scheduled VACUUM if autovacuum is tuned well?

For dead-tuple bloat alone, well-tuned autovacuum is usually enough. The scheduled pass earns its place for two things autovacuum will not reliably do: reclaiming a large burst promptly during the quiet window, and pairing with the periodic REINDEX that repairs structural fragmentation. Keep the scheduled job even with good autovacuum, but let it be a backstop, not the primary mechanism.

Does VACUUM fix GiST tree imbalance?

No. VACUUM reclaims dead tuples and free space, lowering index_bloat_ratio, but it does not re-partition the bounding boxes that page splits arranged badly. Structural fragmentation — high index_fragmentation_ratio with low bloat — is only fixed by a rebuild. That is why the schedule pairs a frequent VACUUM with a less frequent REINDEX CONCURRENTLY.

Can I run REINDEX CONCURRENTLY on a live production table?

Yes — that is exactly what it is for. It builds the new index alongside the old one and swaps atomically, so queries and writes continue throughout. It costs more total I/O and disk than a locking REINDEX and can fail partway (leaving an invalid index to drop and retry), so schedule it off-peak and monitor the run, but it does not require downtime.

Why is the heap’s dead fraction low while the index is bloated?

Because they measure different things. Autovacuum’s default trigger watches the heap’s dead-tuple fraction against total live rows, which a large table dilutes. The GiST index turns over on the churned subset, so its recent pages fill with dead entries long before the whole-heap fraction moves. This mismatch is the entire reason per-table autovacuum tuning — not the storage default — is required for spatial tables.