Alerting on GiST Index Bloat and Autovacuum Lag

← Back to Observability for Spatial Endpoints

This page covers how to measure bloat in a spatial index honestly, why the usual bloat queries mislead on GiST, and which signal to actually alert on.

# Context & When to Use

A spatial index degrades quietly. Updates and deletes leave dead entries; GiST pages split in ways that leave them half full; the index grows while the number of live rows does not. Nothing errors. Query plans do not change. The index simply gets bigger until it no longer fits in shared buffers, and then a 7 ms index scan becomes a 700 ms one over the course of a week — the pattern charted on the observability topic page.

The trap is that the popular bloat-estimation queries circulating in operations runbooks were written for B-trees. They assume fixed-width keys, a known fill factor and a predictable page layout. GiST satisfies none of those: it stores bounding boxes whose overlap changes how pages split, so the estimate can be badly wrong in either direction. Acting on it means either rebuilding an index that was fine or ignoring one that is not.

Use pgstattuple for the truth, use the buffer cache-hit ratio as the alert, and treat the bloat figure as the explanation you reach for once the alert has fired.

# Runnable Implementation

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Honest measurement of every spatial index. Costs a full scan per index,
-- so run it off-peak and store the result rather than querying it live.
CREATE TABLE index_health_history (
    measured_at   timestamptz NOT NULL DEFAULT now(),
    index_name    text        NOT NULL,
    size_bytes    bigint      NOT NULL,
    free_percent  numeric     NOT NULL,
    hit_ratio     numeric,
    PRIMARY KEY (index_name, measured_at)
);

INSERT INTO index_health_history (index_name, size_bytes, free_percent, hit_ratio)
SELECT i.indexrelname,
       pg_relation_size(i.indexrelid),
       -- pgstattuple reads the pages: slow, but correct for GiST
       (pgstattuple(i.indexrelid)).free_percent,
       round(s.idx_blks_hit::numeric
             / NULLIF(s.idx_blks_hit + s.idx_blks_read, 0), 4)
FROM   pg_stat_user_indexes i
JOIN   pg_statio_user_indexes s USING (indexrelid)
JOIN   pg_class c   ON c.oid = i.indexrelid
JOIN   pg_am   am   ON am.oid = c.relam
WHERE  am.amname = 'gist';

The rebuild, when it is warranted, is one statement — and the CONCURRENTLY form is the only one that belongs on a live system:

REINDEX INDEX CONCURRENTLY features_geom_gix;

-- After any interruption, check for the leftover invalid copy
SELECT c.relname
FROM   pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE  NOT i.indisvalid AND c.relname LIKE '%ccnew%';

# Key Parameters & Options

The default autovacuum_vacuum_scale_factor of 0.2 means a 400 million row table waits for 80 million dead tuples before autovacuum runs. On a spatial table that is far too late — set it per table:

ALTER TABLE features SET (autovacuum_vacuum_scale_factor = 0.05,
                          autovacuum_analyze_scale_factor = 0.02);

# Bloat only matters when it crosses the cache

Which fix to choose follows directly from the chart. If the index is bloated and would fit comfortably once rebuilt, reindex. If it is dense and simply larger than the cache, the answer is more memory or partitioning so only the recent partitions need to stay resident.

# Gotchas & Failure Modes

• pgstattuple on a huge index during business hours. It reads every page and evicts other data from the cache while doing so — the measurement causes the symptom. Schedule it off-peak.
• Copy-pasted bloat estimates. As charted above, they are unrelated to reality for GiST. If a runbook has one, label it “B-tree only”.
• REINDEX without CONCURRENTLY. Takes an ACCESS EXCLUSIVE lock for the whole rebuild — minutes of downtime on a large spatial index.
• Interrupted REINDEX CONCURRENTLY. Leaves an invalid _ccnew index consuming disk and write bandwidth without serving reads. Check for it after any failure and drop it explicitly.
• Autovacuum blocked by a long transaction. An idle-in-transaction connection prevents cleanup no matter how the thresholds are tuned. Watch pg_stat_activity for state = 'idle in transaction' older than a few minutes.
• Rebuilding the symptom, not the cause. An index that bloats again within a fortnight is being fed by an update pattern that rewrites geometry constantly. Consider whether those writes need to touch the geometry column at all.

# Why spatial tables bloat faster than the rest

It is worth understanding the mechanism, because the fix depends on which of three causes is dominant, and they call for different responses.

The first is ordinary dead tuples. Any update or delete leaves the old index entry in place until vacuum reclaims it, and a table whose geometry is rewritten on every position report generates them continuously. The second is page splits: GiST chooses a split by minimising bounding-box overlap, and when new geometry arrives in an area already densely covered, the resulting pages are often left well under half full. The third is the one people miss — an update that does not touch the geometry column still writes a new row version, and unless the table qualifies for a heap-only tuple update, that means a new entry in every index including the spatial one.

That third case has a cheap fix that is easy to miss. Lowering the table’s fillfactor leaves free space on each heap page, which lets more updates stay heap-only and therefore never touch the spatial index at all:

ALTER TABLE features SET (fillfactor = 85);
VACUUM FULL features;   -- or pg_repack, to apply it to existing pages

# Verification Snippet

-- Before and after a rebuild
SELECT pg_size_pretty(pg_relation_size('features_geom_gix')) AS size,
       (pgstattuple('features_geom_gix')).free_percent       AS free_pct;
--  size   | free_pct
-- --------+----------
--  14 GB  |    38.10
-- after REINDEX INDEX CONCURRENTLY:
--  8.9 GB |     4.20

-- Confirm the hit ratio recovered
SELECT indexrelname,
       round(idx_blks_hit::numeric / NULLIF(idx_blks_hit + idx_blks_read, 0), 4)
FROM   pg_statio_user_indexes WHERE indexrelname = 'features_geom_gix';

# Prometheus rule: alert on the leading signal, not on bloat directly
# - alert: SpatialIndexLeavingCache
#   expr: pg_statio_user_indexes_hit_ratio{index=~".*_gix"} < 0.98
#   for: 15m

# Related

• Observability for Spatial Endpoints — where this signal sits among the others
• Query Plan Analysis & Index Tuning — reading the plans a bloated index produces
• Table Partitioning for Large Spatial Datasets — keeping only the hot index resident

← Back to Observability for Spatial Endpoints