Detecting GiST Index Bloat from Bulk Inserts

A sequential bulk load is the single most reliable way to leave a GiST index in worse shape than the row count suggests. When millions of features arrive already sorted — a nightly COPY ordered by a Z-curve, an ETL job that appends a spatially-contiguous tile at a time — the R-tree’s page-split heuristic feeds nearly every new entry into the same corner of the tree, producing internal nodes whose bounding boxes overlap heavily. The load reports success, the row count is exactly right, and no query has run yet, so every physical check passes. Then the morning read traffic arrives and a bounding-box overlap that should touch one subtree descends several, and latency multiplies. This page is the detection playbook for that specific failure: distinguishing structural fragmentation from ordinary dead-tuple bloat, catching it with pgstattuple and pg_stat_user_indexes immediately after a load, and alerting on gis.spatial.index_fragmentation_ratio before the first query storm exposes it. It sits under Spatial Index Health Monitoring and within Geospatial Observability Architecture & Fundamentals.

Healthy versus bulk-load-fragmented GiST tree descent On the left, a healthy GiST tree: a root node fans out to three internal nodes whose bounding boxes are disjoint, so a single overlap query follows one highlighted path to one leaf. On the right, a fragmented tree after a sorted bulk load: the same root fans out to three internal nodes whose bounding boxes overlap heavily, so the identical query must follow three highlighted paths to three leaves, reading far more of the tree. A label notes selectivity collapse and rising index_fragmentation_ratio. Healthy tree · disjoint boxes root bbox A bbox B bbox C leaf query descends 1 subtree After sorted bulk load · overlap root overlapping boxes leaves query descends 3 subtrees · selectivity collapses

The contrast above is the fingerprint of this failure. A well-built tree keeps sibling bounding boxes disjoint, so an overlap query prunes to one subtree; a tree built from pre-sorted input has siblings whose boxes overlap, so the same query fans out and reads a large fraction of the index. Critically, both trees hold the same tuples, occupy nearly the same disk, and show identical dead-tuple counts — which is why dead-tuple bloat detection misses this entirely and you need the fragmentation signal defined in the geospatial metric taxonomy for ETL.

Problem Framing

There are two independent ways a GiST index goes bad, and conflating them sends you to the wrong fix. Dead-tuple bloat is physical: updates and deletes leave reclaimable entries, the index grows on disk, and VACUUM fixes it. Structural fragmentation is logical: page splits arranged the bounding boxes badly, the index is the right size but the tree is unbalanced, and only a REINDEX fixes it. A sorted bulk insert produces the second kind almost exclusively — there are no updates or deletes in a fresh append, so dead tuples stay near zero while fragmentation climbs.

The reason sorting is the trigger lies in the split heuristic. When a leaf page fills, GiST splits its entries into two pages and computes a bounding box for each. If the incoming entries are spatially sorted, consecutive inserts all land at the growing edge of the same page, so split after split carves thin, adjacent slices whose parent boxes end up long and overlapping. Random-order inserts, by contrast, scatter across the tree and let the heuristic build compact, disjoint boxes. This is also why a one-shot CREATE INDEX on an already-loaded table produces a far better tree than incrementally inserting the same sorted rows — the bulk build sees all entries at once and packs them well. The distinction between fixing this with a rebuild versus with vacuuming is exactly the boundary drawn against scheduling GiST index vacuum for spatial tables, which owns the dead-tuple half of the problem.

The signal that this is biting: index-scan latency on a && or ST_Intersects predicate steps up right after a load, EXPLAIN (ANALYZE, BUFFERS) shows the index scan reading many more buffers than rows returned, and gis.spatial.index_bloat_ratio stays flat while gis.spatial.index_fragmentation_ratio jumps. Whether to fix this reactively or to avoid it by choosing a more append-tolerant access method is part of the trade-off analysed in GiST vs BRIN indexes for spatial observability.

Implementation

Detection has to run at load boundaries, not on the periodic health cadence, because the whole point is to catch the tree before the first query storm. Capture a fragmentation reading immediately after every bulk load. The cheap, always-on proxy is read amplification from pg_stat_user_indexes — but on a freshly loaded index no user queries have run yet, so seed it with a controlled probe query, then measure:

-- Run a representative overlap probe against the freshly loaded index,
-- then read how much of the tree it had to descend.
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT count(*)
FROM parcels_bulk p
WHERE p.geom && ST_MakeEnvelope(-73.99, 40.70, -73.95, 40.75, 4326);
-- Watch "Shared Hit Blocks" vs "Actual Rows": a high ratio is fragmentation.

For the direct structural reading, pgstatindex reports leaf density and leaf fragmentation. A sorted-load tree shows normal density (it is packed, just badly partitioned) with elevated leaf_fragmentation, which is the exact signature that separates it from dead-tuple bloat:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Immediately after a bulk load: high leaf_fragmentation with normal density
-- and ~zero dead tuples == structural fragmentation, not vacuum debt.
SELECT
  i.indexname                                   AS index,
  stat.leaf_fragmentation                        AS leaf_fragmentation_pct,
  stat.avg_leaf_density                          AS leaf_density_pct,
  s.n_dead_tup                                   AS dead_tuples,
  -- normalize leaf_fragmentation into the gis.spatial.* gauge
  stat.leaf_fragmentation / 100.0                AS index_fragmentation_ratio
FROM pg_indexes i
JOIN LATERAL pgstatindex(i.indexname) AS stat ON true
JOIN pg_stat_user_tables s ON s.relname = i.tablename
WHERE i.tablename = 'parcels_bulk'
  AND i.indexdef ILIKE '%gist%';

Publish index_fragmentation_ratio from that query as a labelled gauge and have the ETL job emit it as the final step of every load, so the value on the dashboard is always the post-load state. Then alert on the jump. Because a load is a discrete event, alert on the level immediately after it rather than on a slow rolling trend:

groups:
  - name: spatial-bulk-load-fragmentation
    rules:
      # Fragmentation high while bloat is low == sorted-load tree imbalance.
      - alert: GistFragmentationAfterBulkLoad
        expr: |
          gis_spatial_index_fragmentation_ratio{access_method="gist"} > 0.35
          and gis_spatial_index_bloat_ratio < 0.15
        for: 5m
        labels: { severity: warning, team: gis-platform }
        annotations:
          summary: "GiST index {{ $labels.index }} fragmented after load (bloat is low)"
          description: "Sorted bulk insert produced overlapping node boxes. REINDEX, or rebuild post-load."

      # The consequence: read amplification climbs on the overlap predicate.
      - alert: GistReadAmplificationPostLoad
        expr: gis_spatial_index_tuples_read_ratio{access_method="gist"} > 10
        for: 10m
        labels: { severity: warning, team: gis-platform }

The pairing fragmentation high AND bloat low is what makes this alert specific — it fires for the sorted-load case and stays quiet for the update-churn case that the vacuum-scheduling guide handles, so the page lands on the right runbook.

Verification & Testing

Prove the detector works by manufacturing the failure. Load the same rows two ways — pre-sorted versus shuffled — and confirm the fragmentation gauge separates them. A Z-order sort is the realistic worst case because it is exactly what many spatial ETL jobs emit:

-- Fragmented on purpose: insert in spatial (Hilbert/Z) order into an existing index.
CREATE TABLE parcels_sorted (id bigserial, geom geometry(Polygon, 4326));
CREATE INDEX parcels_sorted_gist ON parcels_sorted USING gist (geom);
INSERT INTO parcels_sorted (geom)
SELECT geom FROM parcels_source ORDER BY ST_GeoHash(ST_Centroid(geom));

-- Healthy control: same rows, random order, index built after the load.
CREATE TABLE parcels_random (id bigserial, geom geometry(Polygon, 4326));
INSERT INTO parcels_random (geom)
SELECT geom FROM parcels_source ORDER BY random();
CREATE INDEX parcels_random_gist ON parcels_random USING gist (geom);

-- Compare leaf_fragmentation: the sorted+incremental case reads markedly higher.
SELECT 'sorted' AS load, leaf_fragmentation FROM pgstatindex('parcels_sorted_gist')
UNION ALL
SELECT 'random', leaf_fragmentation FROM pgstatindex('parcels_random_gist');

A green verification is three things together: the sorted table’s leaf_fragmentation is materially higher than the random control, the GistFragmentationAfterBulkLoad rule fires only for the sorted table, and a REINDEX INDEX CONCURRENTLY parcels_sorted_gist drops the gauge back to the control’s level. If the alert also fires on the random control, the threshold is too tight; if it misses the sorted case, the ETL job is not emitting the gauge at load time.

Gotchas & Failure Modes

Building the index after the load hides the problem — and that is the fix. The cleanest remedy for a bulk pipeline is to drop the GiST index, COPY the data, then CREATE INDEX once. A bulk build packs all entries with global knowledge and produces disjoint boxes even from sorted input, whereas incremental inserts into a live index do not. If you cannot drop the index (an online append-only table), schedule a post-load REINDEX CONCURRENTLY instead.

Dead-tuple checks report the index as healthy. A monitoring setup that only tracks index_bloat_ratio or n_dead_tup will pronounce a freshly loaded, badly fragmented index perfectly fine, because those numbers really are fine — the damage is structural. This is the most common blind spot; the fragmentation gauge is non-optional for any pipeline that bulk-loads spatial data.

Sampling cadence misses the window. If index_fragmentation_ratio is only sampled hourly, a load that finishes at the top of the hour can degrade morning queries for nearly an hour before the gauge updates. Emit the reading as the final ETL step so it is tied to the load event, not to a wall-clock schedule — the same reason the periodic collection in Spatial Index Health Monitoring is supplemented, not replaced, here.

fillfactor set too high on a load-heavy index. A GiST index at fillfactor = 100 leaves no room on leaf pages, so the very first inserts split, compounding sorted-load fragmentation. For tables that continue to receive appends after the initial build, a lower fillfactor (around 80–90) gives splits room to produce tighter boxes.

FAQ

Why does fragmentation appear even though I never update or delete rows?

Because fragmentation is not caused by dead tuples. It is caused by how page splits partitioned the bounding boxes during insertion. A pure append of sorted geometries never creates a dead tuple, yet it can produce a badly overlapping tree — the two failure modes are independent, which is exactly why a dead-tuple-only dashboard misses it.

How is this different from ordinary index bloat?

Ordinary bloat is dead space: the index is physically larger than its live contents, and VACUUM reclaims it. Bulk-load fragmentation is a bad tree shape: the index is the right size but its internal boxes overlap, so queries descend too much of it, and only a REINDEX (or a fresh bulk build) repairs it. The bloat_ratio low, fragmentation_ratio high combination is the tell.

Should I build the GiST index before or after a bulk load?

After, whenever you can. Dropping the index, loading the data, and running CREATE INDEX once yields a far better-packed tree than inserting the same rows into a live index, because the bulk build partitions all entries with full knowledge. For online tables that cannot drop the index, load into the live index and follow with a REINDEX CONCURRENTLY.

What fragmentation ratio should trigger a rebuild?

Start alerting around 0.35 and treat 0.60 as a strong rebuild signal, but calibrate against a freshly built control index on the same data — the “good” baseline is not zero, it is whatever a clean bulk build produces for your geometry distribution. Alert on the delta from that baseline rather than an absolute number.

Can I detect this without the expensive pgstatindex scan?

Partially. Read amplification from pg_stat_user_indexes (tuples read per tuple returned) is a cheap always-on proxy, and EXPLAIN (ANALYZE, BUFFERS) on a representative overlap query shows buffers-read versus rows-returned without touching pgstatindex. Use those for continuous signal and reserve pgstatindex for confirmation at load boundaries or on a replica.