Detecting GiST Index Bloat

A GiST index that quietly doubles in size while the table it serves stays flat is one of the most under-diagnosed regressions in a spatial stack, and it belongs squarely in the remit of Spatial Performance Monitoring & Observability. On a fleet telemetry platform the symptom is textbook: a vehicle_tracks table receiving a position ping every few seconds per vehicle, a geometry(Point,4326) column named position under an UPDATE-in-place workload, and a spatial index whose on-disk footprint keeps climbing even though the live row count barely moves. Queries that once returned in single-digit milliseconds start reading dozens of extra buffer pages per scan, cache hit ratios sag, and eventually the planner’s cost model shifts away from the index because it now looks expensive. This page is the workflow for catching that decay early — measuring it precisely instead of guessing — and reversing it without downtime.

The reason GiST is especially prone to this under high write churn is structural. PostgreSQL is a copy-on-write MVCC engine: every UPDATE to the position column produces a brand-new heap tuple and a brand-new index entry, while the previous versions linger as dead entries until autovacuum reclaims them. GiST leaf pages are not automatically re-packed when entries die. Over millions of position updates the tree accumulates half-empty leaf pages, split remnants, and internal-node overhead that never fully collapses back. The result is bloat: physical pages that hold far less useful data than they could. Left alone, an index at 50 percent leaf density is doing twice the I/O it needs to for the same logical answer.

Prerequisites and Infrastructure Validation

Bloat measurement depends on the pgstattuple contrib module, which is not installed by default. Install it once per database with a superuser or a role holding CREATE on the database:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Confirm the module and the PostGIS version you are running, since GiST internals and the granularity of pgstatindex have improved across releases:

SELECT name, installed_version
FROM pg_available_extensions
WHERE name IN ('pgstattuple', 'postgis');

SELECT PostGIS_Full_Version();

• PostgreSQL 13 or later is assumed; REINDEX INDEX CONCURRENTLY requires PostgreSQL 12+.
• PostGIS 3.0 or later for stable geometry operator classes.
• The measuring role needs SELECT on the target table and the ability to execute the pgstattuple functions (grant EXECUTE explicitly if you are not a superuser).

Confirm the GiST index exists on the geometry columns. You cannot measure what you have not identified. List every GiST index attached to the telemetry tables and capture its current physical size:

SELECT
    i.indexrelid::regclass          AS index_name,
    t.relname                       AS table_name,
    am.amname                       AS access_method,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    pg_get_indexdef(i.indexrelid)   AS definition
FROM pg_index i
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_class t  ON t.oid  = i.indrelid
JOIN pg_am   am  ON am.oid = ic.relam
WHERE t.relname IN ('vehicle_tracks', 'trip_segments')
  AND am.amname = 'gist'
ORDER BY pg_relation_size(i.indexrelid) DESC;

For a fleet database you should expect at least idx_vehicle_tracks_position_gist on vehicle_tracks (position) and idx_trip_segments_route_gist on trip_segments (route). Record the reported index_size now — it is the baseline you will compare against after a rebuild.

Confirm the SRID of each geometry column so that later validation queries and any spatial filters you run against the same tables use matching coordinate systems:

SELECT
    Find_SRID('public', 'vehicle_tracks', 'position') AS position_srid,
    Find_SRID('public', 'trip_segments', 'route')     AS route_srid;

Both should return 4326. A mismatch here is unrelated to bloat but will corrupt any comparison query you write against these tables, so resolve it first.

Core Execution Workflow

Step 1 — Read Structural Density with pgstatindex

pgstatindex() is the correct first probe for a GiST index because it reports the tree’s structural health cheaply. The two fields that matter most for spatial bloat are avg_leaf_density (the percentage of usable leaf-page space actually occupied) and leaf_fragmentation (the percentage of leaf pages that are physically out of logical order, a proxy for split churn):

SELECT
    version,
    tree_level,
    index_size,
    root_block_no,
    internal_pages,
    leaf_pages,
    empty_pages,
    deleted_pages,
    avg_leaf_density,
    leaf_fragmentation
FROM pgstatindex('idx_vehicle_tracks_position_gist');

A freshly built GiST index on point data typically reports avg_leaf_density near 90 percent and leaf_fragmentation in the low single digits. After weeks of position churn you might instead see avg_leaf_density around 48 percent and leaf_fragmentation above 35. That gap is the bloat. A high empty_pages or deleted_pages count reinforces the diagnosis: those are pages autovacuum emptied but that a rebuild has not yet physically removed.

Step 2 — Quantify Dead Space with pgstattuple

Where pgstatindex describes the shape of the tree, pgstattuple() measures how much of the object is genuinely free versus used. It scans the whole index, so treat it as the heavier, more authoritative probe:

SELECT
    table_len,
    tuple_count,
    tuple_len,
    tuple_percent,
    dead_tuple_count,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('idx_vehicle_tracks_position_gist');

For an index, free_percent is the headline number: it is the share of the object that holds no live tuple data and would be reclaimed by a rebuild. A healthy point index sits under 15 percent free; a bloated one under sustained UPDATE load routinely shows 40 percent or more. Multiply free_percent by index_size from Step 0 to get a rough byte figure for reclaimable space.

Because a full pgstattuple scan is expensive on multi-gigabyte indexes, restrict it to indexes that pgstatindex has already flagged as suspicious. The narrower, scriptable version of exactly this measurement — including a Python routine that flags indexes over a leaf_fragmentation threshold and emits a rebuild plan — is covered in measuring GiST index bloat with pgstattuple.

Step 3 — Correlate Bloat With Write Churn

Bloat is a symptom of write patterns, so tie the index metrics back to activity counters. pg_stat_user_indexes shows how the index is used, and pg_stat_user_tables shows the churn producing dead tuples:

SELECT
    s.indexrelname,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
WHERE s.relname = 'vehicle_tracks';

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    n_tup_upd,
    n_tup_hot_upd,
    round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_update_pct,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'vehicle_tracks';

Two signals confirm churn-driven index bloat. First, a very high n_tup_upd relative to n_live_tup means the position column is being rewritten constantly. Second, a low hot_update_pct means those updates are not Heap-Only Tuple updates — because the position column is indexed, every position change forces a fresh index entry rather than a cheap in-page update. That combination is the exact engine of GiST leaf-page turnover. Keeping autovacuum aggressive enough to clear dead tuples promptly is the preventive half of this story, covered in autovacuum tuning for geometry tables.

Step 4 — Estimate Reclaimable Space and Set a Threshold

Turn the raw metrics into a decision. A compact query joins the size and density figures so you can rank indexes by how much space a rebuild would return:

WITH idx AS (
    SELECT
        i.indexrelid::regclass AS index_name,
        pg_relation_size(i.indexrelid) AS bytes
    FROM pg_index i
    JOIN pg_class t ON t.oid = i.indrelid
    JOIN pg_class ic ON ic.oid = i.indexrelid
    JOIN pg_am am ON am.oid = ic.relam
    WHERE am.amname = 'gist'
      AND t.relname IN ('vehicle_tracks', 'trip_segments')
)
SELECT
    idx.index_name,
    pg_size_pretty(idx.bytes)                          AS current_size,
    ps.avg_leaf_density,
    ps.leaf_fragmentation,
    pg_size_pretty((idx.bytes * (100 - ps.avg_leaf_density) / 100)::bigint)
        AS approx_reclaimable
FROM idx
CROSS JOIN LATERAL pgstatindex(idx.index_name) ps
ORDER BY idx.bytes * (100 - ps.avg_leaf_density) DESC;

approx_reclaimable is an estimate, not a guarantee, because a rebuild targets a fill factor rather than 100 percent packing — but it is a sound way to prioritize. A defensible policy for point-geometry indexes: flag for review at leaf_fragmentation > 20, and rebuild when leaf_fragmentation > 30 and avg_leaf_density < 55. Weight the decision by size, since reclaiming 40 percent of a 30 GB index is worth a maintenance window while reclaiming 40 percent of a 30 MB index is not.

Step 5 — Rebuild Online With REINDEX CONCURRENTLY

The only operation that physically re-packs a bloated GiST index is a rebuild. Use REINDEX INDEX CONCURRENTLY so writes to vehicle_tracks continue during the rebuild — critical when position pings never stop:

REINDEX INDEX CONCURRENTLY idx_vehicle_tracks_position_gist;

CONCURRENTLY builds a new index alongside the old one, holding only a brief lock at swap time rather than blocking writes for the full build. It costs more total work and disk (both indexes coexist transiently) and cannot run inside a transaction block. If a concurrent rebuild is interrupted it can leave an INVALID index behind; detect and clean those up:

SELECT indexrelid::regclass AS invalid_index
FROM pg_index
WHERE indisvalid = false
  AND indrelid = 'vehicle_tracks'::regclass;

For teams that already run online index maintenance as part of schema evolution, the same non-blocking build mechanics are detailed in concurrent index builds, which covers CREATE INDEX CONCURRENTLY and the invalid-index recovery path in depth.

Performance Considerations

Reading the EXPLAIN Difference Bloat Makes

Bloat rarely changes which plan you get until it is severe, but it changes the cost of that plan by inflating buffer reads. Capture a representative spatial query before and after a rebuild with buffers enabled:

EXPLAIN (ANALYZE, BUFFERS)
SELECT vehicle_id, recorded_at
FROM vehicle_tracks
WHERE position && ST_MakeEnvelope(-73.99, 40.72, -73.95, 40.76, 4326);

On a bloated index the Index Scan node reports a large Buffers: shared hit=... read=... count relative to the number of rows returned, because the scan must walk many sparsely populated leaf pages. After REINDEX CONCURRENTLY, the same query touches far fewer buffers for the identical result set. If you want a deeper method for interpreting these plans and cost figures, see query plan analysis with EXPLAIN.

GUCs That Influence Bloat Accumulation

You can set a GiST index fill factor at creation or rebuild time to give point updates breathing room:

ALTER INDEX idx_vehicle_tracks_position_gist SET (fillfactor = 80);
REINDEX INDEX CONCURRENTLY idx_vehicle_tracks_position_gist;

Keeping Statistics Honest

A bloated index skews the planner’s page-count estimates. After any rebuild, refresh statistics so cost estimates reflect the new compact index:

ANALYZE vehicle_tracks;

Common Failure Modes and Fixes

pgstatindex Returns an Error on the GiST Index

Diagnosis: Older pgstattuple versions could not introspect certain index access methods, or the extension was installed in a different schema than your search_path.

Fix:

ALTER EXTENSION pgstattuple UPDATE;
SELECT * FROM public.pgstatindex('idx_vehicle_tracks_position_gist');

Qualify the function with the schema where the extension lives, and ensure it is current. On supported PostgreSQL and PostGIS versions pgstatindex works on GiST without issue.

Index Size Keeps Growing Right After a Rebuild

Diagnosis: The rebuild reclaimed space, but sustained UPDATE churn plus lagging autovacuum re-bloats the index within days. A rebuild treats the symptom, not the cause.

Fix: Tighten per-table autovacuum so dead entries are removed continuously rather than in large bursts:

ALTER TABLE vehicle_tracks SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay   = 2,
    autovacuum_analyze_scale_factor = 0.02
);

REINDEX CONCURRENTLY Fails and Leaves an INVALID Index

Diagnosis: The concurrent rebuild was interrupted (deadlock, cancellation, or a crash), leaving a leftover invalid index that still consumes disk.

Fix:

-- Identify the invalid leftover
SELECT indexrelid::regclass
FROM pg_index
WHERE indisvalid = false;

-- Drop it without locking readers, then retry the rebuild
DROP INDEX CONCURRENTLY idx_vehicle_tracks_position_gist_ccnew;
REINDEX INDEX CONCURRENTLY idx_vehicle_tracks_position_gist;

The Planner Abandons the Index After Bloat

Diagnosis: When an index grows large enough relative to the table, the planner may estimate a sequential scan as cheaper and stop using it, masking the bloat as a general slowdown.

Fix: Rebuild the index to shrink it, then re-run ANALYZE. Verify the index is chosen again with EXPLAIN, and confirm usage is climbing via idx_scan in pg_stat_user_indexes.

Verification

After a rebuild, confirm three things: density restored, size reduced, and queries cheaper.

-- 1. Structural health restored
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('idx_vehicle_tracks_position_gist');
-- expect avg_leaf_density back near 90, leaf_fragmentation in single digits

-- 2. Physical size shrunk versus the baseline recorded in Step 0
SELECT pg_size_pretty(pg_relation_size('idx_vehicle_tracks_position_gist')) AS new_size;

-- 3. Dead space collapsed
SELECT free_percent, dead_tuple_percent
FROM pgstattuple('idx_vehicle_tracks_position_gist');

A successful rebuild returns avg_leaf_density to the high 80s or 90s, drops free_percent below 15, and reduces pg_relation_size toward the freshly built baseline. Schedule the pgstatindex probe from Step 1 as a recurring check — daily on the busiest indexes — so the next bloat cycle is caught by measurement rather than by a latency alert.

Frequently Asked Questions

Does VACUUM remove GiST index bloat on its own?

No. VACUUM marks dead index entries as reusable and returns entirely empty pages to the free space map, but it never physically compacts partially filled leaf pages or merges them. A GiST index that has been through heavy UPDATE churn keeps its low leaf density until you rebuild it with REINDEX. VACUUM keeps the table healthy; only a rebuild restores index packing.

What leaf_fragmentation value should trigger a rebuild?

There is no universal number, but a practical rule for point-geometry indexes is to investigate when leaf_fragmentation exceeds 20 and to schedule a rebuild when it exceeds 30 while avg_leaf_density drops below roughly 55 percent. Always weigh the metric against index size: a 20 percent fragmentation figure matters far more on a 40 GB index than on a 40 MB one.

Is it safe to run pgstattuple on a large production index?

pgstattuple performs a full scan of the index and takes an ACCESS SHARE lock, so it competes for I/O and buffer cache on very large objects. Prefer pgstatindex, which is far cheaper for GiST because it reads structural metadata, or use the sampling function pgstattuple_approx on the underlying table. Run full pgstattuple scans during off-peak windows on multi-gigabyte indexes.

Why does the position column bloat the index faster than the trip geometry?

The position point is updated on nearly every telemetry ping, so each UPDATE writes a new index tuple and leaves the old one dead, driving continuous leaf-page turnover. The trip_segments linestring is written once when a trip closes and rarely changed, so its GiST index stays densely packed. Update frequency, not geometry type, is the dominant driver of GiST bloat.

Related Topics

• Spatial Performance Monitoring & Observability — parent reference for the full monitoring and observability toolkit
• Measuring GiST Index Bloat With pgstattuple — exact SQL and a Python threshold flagger that emits a REINDEX plan
• Autovacuum Tuning for Geometry Tables — the preventive half: stop dead tuples from driving leaf-page turnover
• Concurrent Index Builds — non-blocking CREATE and REINDEX CONCURRENTLY mechanics and invalid-index recovery
• Query Plan Analysis with EXPLAIN — read buffer counts and cost estimates to see bloat’s effect on scans