Spatial Index Health Monitoring

A spatial index degrades quietly. A GiST tree that answered bounding-box overlaps in single-digit milliseconds when the table was loaded will, months of inserts and updates later, return the same rows an order of magnitude slower while every dashboard reports the query as “successful.” The planner still chooses the index, the query still completes, and nothing raises an exception — yet page splits have unbalanced the tree, dead tuples have inflated its physical size, and expanded bounding boxes have wrecked its selectivity. This page is the operational reference for treating GiST, BRIN, and SP-GiST index health as a first-class observability signal on PostGIS: what to measure, how to instrument it, and when to schedule the REINDEX and VACUUM work that keeps spatial queries honest. It sits under Geospatial Observability Architecture & Fundamentals and turns the index-fragmentation gauges that reference only names into a monitored, alertable subsystem.

The loop above is the whole discipline in one picture: physical statistics are read from the index, condensed into a small set of health gauges, compared against thresholds, and resolved by scheduled maintenance that feeds back onto the index. Everything below fills in each stage — the access-method-specific failure surface, the exact gis.spatial.* gauges, the SQL that populates them from pgstattuple and pg_stat_user_indexes, the PromQL that pages on them, and the recovery playbook. The metric names used here are governed by the shared geospatial metric taxonomy for ETL, and how aggressively you sample per-index statistics on very large tables follows the observability scoping rules for vector data.

Architecture

Spatial index health is not one number, because the three access methods PostGIS uses fail in different ways and reward different maintenance. Treating them as interchangeable is the first mistake teams make when they port a relational index-bloat dashboard onto a spatial workload.

• GiST (R-tree over bounding boxes). The workhorse for polygons and lines. It stores each geometry’s envelope in a balanced tree and answers overlap and containment by descending the boxes. Its failure mode is tree imbalance from page splits: sequential or spatially-sorted inserts push new entries into the same subtree, splitting pages in a way that leaves internal node bounding boxes overlapping heavily. Once internal boxes overlap, a single query descends multiple subtrees and the effective selectivity collapses. GiST also accumulates dead index tuples from updates and deletes exactly like a heap, so it bloats physically as well.
• BRIN (block-range summaries). Cheap and tiny, it stores the bounding box of each block range rather than each geometry. It only performs when the physical row order correlates with spatial location. Its failure mode is summary widening: out-of-order inserts stretch each block range’s summary box until it overlaps most of the table, and the index degenerates into a sequential scan with extra steps. BRIN does not bloat in the dead-tuple sense; it decays in selectivity.
• SP-GiST (space-partitioning trees). Quadtrees and k-d trees for point-heavy data. It partitions space rather than data, so it is resilient to skew but sensitive to partition depth blowup when points cluster densely in a small region.

The decision of which method to monitor most closely follows the decision of which method to deploy, and the trade-offs between GiST and BRIN for an observability workload are worked through in GiST vs BRIN indexes for spatial observability. For the rest of this page the emphasis is GiST, because it is the default, the most churn-sensitive, and the one whose health most directly determines query latency.

Three physical phenomena underlie every gauge below. Page splits are the structural event: when a GiST page fills, PostgreSQL splits it, and the split heuristic chooses how to divide the entries’ bounding boxes. Good splits produce tight, disjoint child boxes; bad splits (common under sorted bulk loads) produce overlapping ones. Dead-tuple bloat is the physical event: updated and deleted index entries are not reclaimed until VACUUM runs, so the index grows on disk and every scan touches more pages. Bounding-box expansion is the selectivity event: as internal node boxes grow to cover their children, the fraction of the tree a query must visit rises, and the planner’s row estimate for an index scan drifts away from reality.

Metric Specification

The gauges below are point-in-time samples of index state, collected on a schedule (typically every few minutes for hot tables, hourly for large cold ones) rather than on every query, because computing them touches physical statistics that are too expensive to gather inline. Each carries the dimensions index, table, and access_method so a single dashboard can compare a GiST index on a parcels layer against a BRIN index on an AIS-track archive.

Two of these deserve a formal definition because thresholds are set against the derived ratio, not the raw byte counts. The index bloat ratio compares the index’s live logical size against its physical on-disk size:

$$B_{\text{idx}} = \frac{\text{size}_{\text{physical}} - \text{size}_{\text{live}}}{\text{size}_{\text{physical}}}$$

where $\text{size}_{\text{live}}$ is the space the same tuples would occupy in a freshly built index. A value of $0.5$ means half the index is dead space or free fragments — a REINDEX would roughly halve it. The fragmentation ratio captures GiST-specific tree quality by measuring how much of the summed internal-node bounding-box area is wasted on overlap between siblings:

$$F_{\text{gist}} = 1 - \frac{\text{Area}\!\left(\bigcup_i b_i\right)}{\sum_i \text{Area}(b_i)}$$

for sibling node boxes $b_1 \dots b_n$. Disjoint boxes give $F = 0$; heavily overlapping boxes (a badly split tree) push $F$ toward $1$. Unlike bloat, fragmentation is not fixed by VACUUM — only a rebuild re-partitions the boxes. The bbox_expansion_ratio reuses the envelope-efficiency idea from the parent architecture reference: sprawling geometries with mostly-empty envelopes force wide index boxes regardless of tree quality, so a persistently high value signals a data problem (a multipart geometry spanning a continent) rather than a maintenance one.

Configuration & Collection

The gauges are populated from two PostgreSQL sources. pg_stat_user_indexes (and its sibling pg_statio_user_indexes) give scan counts and tuple-read ratios cheaply on every sample. The pgstattuple extension gives physical density and free-space fractions, but its pgstatindex and pgstattuple functions perform a full index scan, so they are gathered on a slower cadence and never inline with user queries.

Enable the extension once, then sample scan efficiency from the always-on catalog view:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Cheap, always-on: scan efficiency and read amplification per spatial index.
SELECT
  s.relname                                   AS table,
  s.indexrelname                              AS index,
  am.amname                                   AS access_method,
  s.idx_scan                                  AS scans,
  s.idx_tup_read                              AS tuples_read,
  s.idx_tup_fetch                             AS tuples_fetched,
  CASE WHEN s.idx_tup_fetch > 0
       THEN s.idx_tup_read::float8 / s.idx_tup_fetch
       ELSE 0 END                             AS tuples_read_ratio
FROM pg_stat_user_indexes s
JOIN pg_class c   ON c.oid = s.indexrelid
JOIN pg_am    am  ON am.oid = c.relam
WHERE am.amname IN ('gist', 'brin', 'spgist')
ORDER BY tuples_read_ratio DESC;

For the physical bloat and page-density signals, call pgstatindex on a schedule. On a large table this is I/O heavy, so gate it behind a statement_timeout and run it against a replica when one exists:

-- Expensive: full index scan. Run on a slow cadence / on a replica.
SET statement_timeout = '90s';

SELECT
  i.indexname                                   AS index,
  i.tablename                                    AS table,
  stat.leaf_fragmentation                        AS leaf_fragmentation_pct,
  stat.avg_leaf_density                          AS avg_leaf_density_pct,
  pg_relation_size(i.indexname::regclass)        AS physical_bytes,
  -- bloat ratio: how much of the index is NOT live leaf density
  (100 - stat.avg_leaf_density) / 100.0          AS index_bloat_ratio
FROM pg_indexes i
JOIN LATERAL pgstatindex(i.indexname) AS stat ON true
WHERE i.indexdef ILIKE '%gist%'
  AND i.schemaname = 'public';

The bbox_expansion_ratio is computed straight from the indexed geometries themselves — it is a property of the data, not the index structure — using the same envelope-versus-area comparison the taxonomy defines:

-- Envelope inefficiency drives wide GiST boxes and poor selectivity.
SELECT
  'parcels'::text AS table,
  avg(
    ST_Area(ST_Envelope(geom)) /
    GREATEST(ST_Area(geom), 1e-9)          -- guard zero-area lines/points
  ) FILTER (WHERE GeometryType(geom) IN ('POLYGON','MULTIPOLYGON'))
    AS bbox_expansion_ratio
FROM public.parcels
TABLESAMPLE SYSTEM (2);                    -- 2% sample keeps the scan cheap

A small exporter (a Python psycopg job or the postgres_exporter with custom queries) runs these on their respective cadences and publishes the results as gis_spatial_index_fragmentation_ratio, gis_spatial_index_bloat_ratio, and gis_spatial_bbox_expansion_ratio, labelled by index, table, and access_method. Keep the label set bounded — one series per index, not per query — so a table with hundreds of partitions does not blow up cardinality; that budget is the province of the observability scoping rules for vector data.

Threshold Design & Alerting

Alert on the health gauges, not on raw query latency, because latency is a lagging signal — by the time a spatial join is visibly slow, the index has been degrading for days. Tier the rules so that a REINDEX-worthy structural problem pages while a routine bloat threshold opens a ticket for the next maintenance window.

# prometheus-rules.yaml
groups:
  - name: spatial-index-health
    rules:
      # Structural imbalance — only a rebuild fixes it. Page if it is severe and persistent.
      - alert: SpatialIndexFragmentationCritical
        expr: gis_spatial_index_fragmentation_ratio{access_method="gist"} > 0.60
        for: 1h
        labels: { severity: critical, team: gis-platform }
        annotations:
          summary: "GiST index {{ $labels.index }} is structurally fragmented (>0.60)"
          description: "Sibling bbox overlap has collapsed selectivity. Schedule REINDEX CONCURRENTLY."

      - alert: SpatialIndexFragmentationWarning
        expr: gis_spatial_index_fragmentation_ratio{access_method="gist"} > 0.35
        for: 6h
        labels: { severity: warning, team: gis-platform }

      # Physical bloat — VACUUM reclaims most of it; REINDEX reclaims the rest.
      - alert: SpatialIndexBloatHigh
        expr: gis_spatial_index_bloat_ratio > 0.50
        for: 2h
        labels: { severity: warning, team: gis-platform }
        annotations:
          summary: "Index {{ $labels.index }} is >50% dead/free space"

      # Read amplification rising — the planner is descending too much of the tree.
      - alert: SpatialIndexReadAmplification
        expr: |
          gis_spatial_index_tuples_read_ratio > 10
          and on(index) rate(gis_spatial_index_dead_tuples[30m]) > 0
        for: 30m
        labels: { severity: warning, team: gis-platform }

      # Selectivity decay from sprawling geometries — a data problem, route to data-eng.
      - alert: SpatialBboxExpansionExtreme
        expr: gis_spatial_bbox_expansion_ratio > 25
        for: 1h
        labels: { severity: warning, team: data-engineering }
        annotations:
          summary: "Envelope inefficiency on {{ $labels.table }} degrading index selectivity"

The pairing in SpatialIndexReadAmplification is deliberate: a high read ratio with dead tuples still accumulating is a maintenance problem, whereas a high read ratio with zero dead-tuple growth points at query patterns or data sprawl instead. Encoding that distinction in the rule keeps the page routed to the team that can actually act on it, mirroring the severity-and-ownership split used throughout the geospatial metric taxonomy for ETL.

Failure Modes

• REINDEX masking a chronic data problem. A REINDEX drops index_fragmentation_ratio to zero overnight, but if the underlying churn pattern (sorted bulk loads, wide multipart geometries) is unchanged, it climbs back within days. Rebuilding on a schedule without fixing the load pattern turns a maintenance job into a treadmill. Correlate the post-rebuild decay rate with insert patterns before assuming the rebuild cadence is the answer.
• VACUUM starved by a long-running transaction. VACUUM cannot remove dead index tuples newer than the oldest open snapshot. A forgotten analytics transaction or a stuck replication slot pins xmin, and index_bloat_ratio climbs no matter how often autovacuum runs. Check pg_stat_activity and pg_replication_slots for the oldest xmin before scaling autovacuum.
• BRIN summaries silently widened by out-of-order inserts. A BRIN index shows near-zero bloat and passes every physical check while its selectivity has collapsed, because appending rows out of spatial order stretches each block-range summary. The tell is a rising index_tuples_read_ratio with a flat index_bloat_ratio — physical health is fine, logical selectivity is gone. The remedy is a brin_summarize_new_values or a re-cluster, not a VACUUM.
• Autovacuum threshold set for a heap, not an index. Default autovacuum scale factors are tuned for heap dead-tuple fractions. A GiST index on a high-update table can bloat well past a healthy point while the heap is still under the autovacuum trigger, so the index never gets vacuumed on time. Per-table autovacuum tuning for churny spatial tables is the subject of scheduling GiST index vacuum for spatial tables.
• Bulk load fragmentation invisible until first query storm. A nightly sequential COPY of millions of rows can leave a GiST tree badly split, but because the load itself does not query the index, fragmentation_ratio is only sampled — and only alarms — after the morning read traffic arrives. Sampling the gauge immediately after every bulk load closes the gap; detecting exactly this pattern is covered in detecting GiST index bloat from bulk inserts.

Troubleshooting Checklist

When spatial queries slow down and you suspect the index, work these in order — the sequence moves from cheap always-on signals to expensive rebuilds.

1. Confirm the index is still chosen. Run EXPLAIN (ANALYZE, BUFFERS) on the slow spatial predicate and verify an Index Scan or Bitmap Index Scan on the GiST index, not a Seq Scan. A planner that abandoned the index points at collapsed selectivity or a stale ANALYZE, not bloat.
2. Check read amplification first. Query pg_stat_user_indexes for idx_tup_read / idx_tup_fetch. A high ratio means the tree is over-descended — either fragmentation or a widened BRIN summary — and tells you whether to rebuild or re-summarize before you pay for pgstatindex.
3. Rule out a pinned snapshot. Before blaming autovacuum, check pg_stat_activity for the oldest xact_start and pg_replication_slots for a lagging xmin. A pinned snapshot makes every VACUUM a no-op and no amount of tuning helps until it clears.
4. Measure physical bloat. Run pgstatindex (on a replica if possible) and compute index_bloat_ratio. Above ~0.5, a VACUUM then REINDEX CONCURRENTLY reclaims the space without taking a heavy lock.
5. Measure structural fragmentation. If bloat is low but reads are amplified, compute the sibling-overlap fragmentation. High fragmentation with low bloat means the tree is balanced badly, not full of dead tuples — go straight to REINDEX CONCURRENTLY.
6. Inspect the geometries themselves. Sample bbox_expansion_ratio. A handful of continent-spanning multipart features can wreck selectivity that no rebuild will fix; the remedy is splitting or subsetting the geometry, not touching the index.
7. Rebuild, then re-baseline. After REINDEX, resample every gauge and record the fresh baseline, then watch the decay rate. If fragmentation returns within days, the load pattern — not the schedule — is the root cause, and the fix belongs upstream at ingestion.

For canonical index-method internals and the maintenance commands referenced here, consult the PostGIS spatial indexing documentation and the PostgreSQL pgstattuple and routine reindexing references.

Related

• Scheduling GiST Index Vacuum for Spatial Tables — autovacuum tuning and scheduled VACUUM/REINDEX for high-churn spatial tables.
• Detecting GiST Index Bloat from Bulk Inserts — catching tree imbalance from sequential loads before the first query storm.
• Geospatial Metric Taxonomy for ETL — the canonical gis.spatial.* names and instrument types these gauges follow.
• Observability Scoping Rules for Vector Data — bounding per-index statistics cardinality on large partitioned tables.
• GiST vs BRIN Indexes for Spatial Observability — choosing the access method whose health you monitor here.
• Geospatial Observability Architecture & Fundamentals — the parent reference this index-health subsystem plugs into.