Tuning GiST and BRIN Indexes for Lineage

Part of: Spatial Index Tuning for Provenance Queries

Spatial-temporal audit queries filter a lineage table by both footprint and time, and the cheapest plan usually pairs a GiST geometry index with a BRIN timestamp index — but you only know the pairing works, and which fillfactor and pages_per_range to pick, by measuring it with EXPLAIN ANALYZE. This how-to builds both indexes on a lineage table, compares plans before and after, and tunes the parameters, extending the spatial index tuning for provenance queries overview into a concrete measurement exercise.

Prerequisites

  • PostgreSQL 15+ with PostGIS 3.4+ and the postgis extension enabled.
  • A lineage_audit table with geom geometry(Geometry, 4326), valid_from timestamptz, and enough rows (hundreds of thousands or more) for index choice to matter — the column layout from PostGIS lineage schema design works directly.
  • Rows inserted in valid_from order, or the ability to run CLUSTER, since BRIN depends on physical ordering.
  • Permission to run CREATE INDEX, CLUSTER, ANALYZE, and EXPLAIN ANALYZE.
pages_per_range trades size against precision A large pages_per_range gives a tiny index that prunes coarsely; a small one prunes tightly at a larger index size. pages_per_range = 128 (default) — coarse blocks actually scanned pruned tiny index pages_per_range = 32 — finer scanned pruned bigger BRIN only works while physical order correlates with the indexed column. Check pg_stats.correlation for the column — near 1.0 means BRIN is effective; near 0 means it is decoration. An out-of-order backfill is the usual thing that destroys it.

Check the correlation before tuning anything else, because a BRIN index on a poorly correlated column cannot be rescued by any pages_per_range. pg_stats.correlation for an append-only ingestion timestamp should sit very close to 1.0; anything materially below that means rows have been inserted out of chronological order, and the index is storing min/max ranges so wide that every range matches every query.

When that happens the fix is upstream rather than in the index. Route backfills to their own partition so they do not interleave with live ingestion, or accept a B-tree on that column instead. Reducing pages_per_range on a badly correlated column makes the index larger without making it selective, which is the worst of both outcomes and a common response to the symptom.

Implementation

Run the target query once to capture the baseline plan, build the two indexes with tuned parameters, refresh statistics, and re-check. The block below does all of it in sequence; execute it statement by statement so you can read each plan.

-- 1. Baseline: expect a Seq Scan with high actual rows and buffer reads.
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, dataset_uuid, valid_from
FROM lineage_audit
WHERE geom && ST_MakeEnvelope(-71.2, 42.2, -70.9, 42.5, 4326)
  AND valid_from >= '2026-01-01' AND valid_from < '2026-04-01';

-- 2. Check the physical correlation of valid_from BEFORE trusting BRIN.
--    Values near 1.0 (or -1.0) mean rows are physically ordered by time.
SELECT correlation
FROM pg_stats
WHERE tablename = 'lineage_audit' AND attname = 'valid_from';

-- 3. If correlation is weak, force physical order once via a B-tree + CLUSTER.
CREATE INDEX IF NOT EXISTS idx_lineage_ts_btree
    ON lineage_audit (valid_from);
CLUSTER lineage_audit USING idx_lineage_ts_btree;

-- 4. Build the GiST spatial index. fillfactor 90 leaves room on leaf pages;
--    for an append-only, rarely-updated audit table you can pack tighter.
CREATE INDEX idx_lineage_geom_gist
    ON lineage_audit USING gist (geom) WITH (fillfactor = 95);

-- 5. Build the BRIN temporal index. Smaller pages_per_range = tighter pruning
--    at the cost of a marginally larger index. 32 suits selective date ranges.
CREATE INDEX idx_lineage_ts_brin
    ON lineage_audit USING brin (valid_from) WITH (pages_per_range = 32);

-- 6. Refresh statistics so the planner will cost the new indexes correctly.
ANALYZE lineage_audit;

-- 7. Re-run the exact baseline query and compare the plan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, dataset_uuid, valid_from
FROM lineage_audit
WHERE geom && ST_MakeEnvelope(-71.2, 42.2, -70.9, 42.5, 4326)
  AND valid_from >= '2026-01-01' AND valid_from < '2026-04-01';

Two parameter choices drive the outcome. fillfactor = 95 on the GiST index packs leaf pages more densely because an immutable audit table sees few in-place updates, so reserving free space would only waste it. pages_per_range = 32 on the BRIN index makes each summarized block range narrower, which tightens min/max pruning for selective quarter-long date filters; widen it toward 128 if your ranges span years and you want the smallest possible index.

Partial Indexes Are the Cheapest Win

Excluding null extents shrinks the index substantially Non-spatial lineage rows carry null extents; a partial index skips them, and in a typical estate that is a large share of all rows. FULL INDEX — every row, including those with no extent indexed: spatial rows + checksum steps + validations + notifications PARTIAL — WHERE extent IS NOT NULL indexed: spatial rows only not indexed — and never matched a spatial query anyway The excluded rows could never satisfy a geometry predicate, so nothing is lost. Smaller index, faster writes, and it stays proportional to spatial activity rather than total activity.

The proportion of lineage rows carrying no extent is usually higher than people expect. Checksum verifications, schema validations, notification steps and metadata injections are all legitimate process steps with nothing spatial about them, and in a mature pipeline they can outnumber the geometry-bearing steps. Excluding them costs nothing in query capability and shrinks the index by whatever that share happens to be.

The same reasoning extends to status. If queries essentially always filter to successful steps, a partial index with WHERE status = 'success' keeps failures out of the hot index while leaving them fully queryable through a sequential scan on the rare occasions someone wants them. Take care that the query predicate matches the index predicate exactly, or the planner will not use it — the most common way a partial index quietly does nothing.

Verification

The proof is in the plan diff. A correctly tuned pair replaces the baseline Seq Scan with a BitmapAnd that combines both indexes:

Bitmap Heap Scan on lineage_audit
  Recheck Cond: ((geom && ...) AND (valid_from >= ... AND valid_from < ...))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_lineage_geom_gist
              Index Cond: (geom && ...)
        ->  Bitmap Index Scan on idx_lineage_ts_brin
              Index Cond: (valid_from >= ... AND valid_from < ...)

Compare the actual time and Buffers: shared read figures between the two EXPLAIN ANALYZE runs. A successful tune shows a large drop in both — the spatial and temporal prefilters together discard most of the table before any heap page is touched. If only one index appears in the plan, the other filter is either non-selective for this query or its statistics are stale; re-run ANALYZE and confirm the query uses && and a range predicate the indexes support.

GiST Fillfactor and Rebuild Cadence

GiST bloat and what controls it Index size grows faster than the table on an insert-heavy workload; a lower fillfactor slows the growth and REINDEX CONCURRENTLY resets it. GiST index size relative to table, over months of inserts fillfactor 90 (default) fillfactor 70 REINDEX CONCURRENTLY here Lower fillfactor leaves room for page splits, trading a larger initial index for a slower-growing one.

A lower fillfactor is the right default for a write-heavy lineage table, and the reasoning is the opposite of the usual advice. On a read-mostly table you pack pages tightly because space is the constraint; on an append-heavy spatial index the constraint is page splits, and leaving room for them avoids the churn that produces bloat. Seventy is a reasonable starting point for tables receiving continuous inserts.

REINDEX CONCURRENTLY is what resets accumulated bloat without taking a lock that stops ingestion, and it is worth scheduling rather than waiting for a symptom. Monitor index size against table size and rebuild when the ratio drifts materially from its post-build value — an index that has grown to several times the size it was after a fresh build is doing more I/O per lookup than it needs to, and the rebuild takes minutes.

A Measurement Loop You Can Repeat

Index tuning is not a one-off exercise, because the workload moves. New pipelines change the write ratio, new dashboards introduce query shapes nobody planned for, and data volume shifts which plans the planner considers viable. A short repeatable loop keeps the indexes matched to reality without turning tuning into a standing project.

Start each cycle from pg_stat_user_indexes, which tells you unambiguously which indexes have been scanned since the last reset and how often. Anything with zero scans over a full quarter is a candidate for removal — it is costing write throughput and providing nothing. Reset the statistics after each review so the next cycle measures a clean period rather than the cumulative history.

Then take the slowest queries from pg_stat_statements and run each through EXPLAIN (ANALYZE, BUFFERS). Look for the three signatures described in the parent guide — a sequential scan under a geometry predicate, a large gap between estimated and actual rows, and high buffer counts despite an index scan — and fix the cause rather than adding an index reflexively. Two of those three are not index problems at all.

Finally, check pg_stats.correlation on every BRIN-indexed column and the size ratio of every GiST index against its table. Both drift slowly and neither produces a symptom until it is quite bad, which is exactly why they belong in a scheduled check rather than in incident response. The whole loop takes under an hour and is worth doing quarterly on any lineage store receiving continuous writes.

Gotchas & edge cases

  • BRIN needs physical ordering. BRIN summarizes each block’s min/max, so if rows are scattered by time across the heap, every range overlaps your filter and the index reads the whole table. Check pg_stats.correlation first; if it is far from ±1, CLUSTER on a timestamp B-tree once to reorder the heap, as shown in step 3. Note that CLUSTER takes an exclusive lock and does not maintain order for future inserts — append-only ingestion in time order preserves it naturally.
  • GiST fillfactor cuts both ways. Packing to 95 shrinks the index and speeds scans on a static table, but if you ever backfill or update geometries heavily, dense pages cause splits and bloat. Keep the default 90 for tables that still receive corrections.
  • BRIN summaries go stale on new blocks. Rows inserted after the index is built land in unsummarized ranges until autovacuum or a manual brin_summarize_new_values('idx_lineage_ts_brin') runs, so a freshly loaded partition may temporarily fall back to scanning. Schedule summarization after bulk loads.