GiST vs SP-GiST vs BRIN for Spatial Columns

Problem Statement

Three access methods can index a PostGIS geometry column, and the documentation describes each one accurately without saying which to use. This page turns the descriptions into a decision: what the three structures actually do differently, which properties of your data make one of them clearly right, and how to measure the choice rather than argue about it. It is the comparison behind choosing a spatial index type.

Why the Naive Approach Fails

The default advice — “use GiST” — is right most of the time and wrong in two situations that both come up in production: a billion-row append-only sensor table where the GiST index is larger than the machine’s memory, and a uniformly distributed point table where SP-GiST’s non-overlapping partitions genuinely index better. Applying the default without checking those cases costs either an unaffordable index or a slower one.

The opposite failure is worse. Reading that BRIN is “1000× smaller” and applying it to a table with scattered writes produces an index the planner trusts and that prunes nothing.

What each method stores Three panels over the same points. GiST stores overlapping bounding rectangles arranged in a balanced tree. SP-GiST recursively divides the extent into non-overlapping quadrants. BRIN stores one bounding box per range of physical heap pages, which is tight only when storage order follows location. Three ways to summarise the same points GiST boxes may overlap SP-GiST partitions never overlap BRIN pages 0–127 pages 128–255 pages 256–383 one box per page range The first two index the data. The third indexes the storage — which is why physical order decides whether it works.

Production-Ready Implementation

Build all three and measure. On a real table this takes an afternoon and settles the question permanently:

sql
-- 10 million sensor points, append-ordered by acquisition time
CREATE INDEX CONCURRENTLY obs_geom_gist   ON observations USING gist   (geom);
CREATE INDEX CONCURRENTLY obs_geom_spgist ON observations USING spgist (geom);
CREATE INDEX CONCURRENTLY obs_geom_brin   ON observations USING brin   (geom)
    WITH (pages_per_range = 64);

SELECT indexrelid::regclass                  AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'observations'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
--         index         |  size
-- ----------------------+---------
--  obs_geom_gist        | 683 MB
--  obs_geom_spgist      | 521 MB
--  obs_geom_brin        | 2544 kB

Then force each in turn and compare the plans. Disabling the others is the reliable way to make the planner use the one you want to measure:

sql
BEGIN;
SET LOCAL enable_seqscan = off;
DROP INDEX obs_geom_spgist, obs_geom_brin;    -- inside a rolled-back transaction
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM observations
WHERE geom && ST_MakeEnvelope(-122.5, 47.4, -122.2, 47.8, 4326);
ROLLBACK;

Dropping indexes inside a transaction that you roll back is a safe way to isolate one — the drops never commit, and the plan you measured is the one that index produces.

The three methods measured on one table A comparison table. GiST is 683 megabytes, builds in 210 seconds and answers a viewport query in 3.1 milliseconds. SP-GiST is 521 megabytes, 180 seconds and 2.8 milliseconds. BRIN is 2.5 megabytes, 12 seconds and 46 milliseconds — two orders of magnitude smaller, an order of magnitude slower per query. 10M points, one viewport query, three indexes size build query GiST 683 MB 210 s 3.1 ms SP-GiST 521 MB 180 s 2.8 ms BRIN 2.5 MB 12 s 46 ms BRIN is not competitive on latency and is not trying to be: it is the option when 683 MB of index is the problem and 46 ms is an acceptable answer for a query that runs once a minute rather than a thousand times a second.

Configuration and Tuning Knobs

GiST fillfactor defaults to 90 and is worth lowering to 70 on a table with heavy updates: leaving space in leaf pages reduces page splits and therefore bloat, at the cost of a larger index from the start.

SP-GiST works on points, boxes and polygons through different operator classes, but its polygon support is newer and less exercised than GiST’s. For anything other than points, treat SP-GiST as the option to benchmark rather than the one to assume.

BRIN pages_per_range is the whole tuning surface: smaller means more, tighter summaries and sharper pruning; larger means a smaller index and coarser boxes. Match it to the size of one spatially compact insert burst.

autosummarize = on on a BRIN index makes newly appended ranges summarised automatically instead of waiting for a vacuum, which matters on a high-velocity ingest where the newest rows are the ones people query.

What each index costs on the write path Per-insert overhead: GiST adds about 41 microseconds because it may split pages, SP-GiST about 34, and BRIN under one microsecond because most inserts only extend the current summary range. Write overhead per inserted row GiST 41 µs SP-GiST 34 µs BRIN < 1 µs — most inserts just widen the current range On a firehose ingest this axis matters as much as query latency, and it is the one people forget to measure.

Verification Steps

sql
-- which index is the planner actually choosing?
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'observations'
ORDER BY idx_scan DESC;

-- and for BRIN specifically, is the correlation still there?
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'observations' AND attname = 'recorded_at';

A BRIN index on a table whose correlation has fallen below about 0.9 is no longer pruning meaningfully. That number moves as the table ages, which is why it belongs in monitoring rather than in a one-off assessment.

Three data shapes, three clear answers

Most tables do not need this analysis because their shape settles the question outright.

Mixed polygons queried by viewport — GiST. Parcels, buildings, administrative areas, anything with varied extents queried by a map. Overlapping bounding boxes are inherent to the data, which is precisely what an R-tree handles, and the exact-predicate recheck needs the heap pointer GiST leaf entries carry.

Uniform points with no nearest-neighbour requirement — benchmark SP-GiST. Grid samples, regularly spaced observations, synthetic data. The non-overlapping quadtree partitions produce a smaller structure and slightly faster descents. Check first that no query uses the distance operator, because losing ordered nearest-neighbour scans is a large price for a small gain.

Append-only, spatially ordered, enormous — BRIN. Radar sweeps, vehicle tracks, tiled imports written in tile order. When the physical order already encodes location, the summaries are tight and the index is three orders of magnitude smaller than the alternative. Verify the correlation rather than assuming it, and re-verify after any bulk backfill.

Everything outside those three shapes is a GiST table until measurement says otherwise.

Gotchas Checklist

  • Only GiST supports the <-> distance operator for ordered nearest-neighbour scans. Choosing SP-GiST on a table that serves KNN queries silently removes that capability.
  • BRIN indexes are cheap to create and cheap to be wrong about. Because they build in seconds, people add them speculatively; because they prune nothing on uncorrelated data, they then make plans worse. Always check correlation first.
  • An unused index still costs writes. Benchmarking three indexes is fine; leaving all three in place afterwards is not — drop the losers.
  • pages_per_range cannot be changed in place. Altering it requires rebuilding the index, so pick deliberately or plan for a rebuild.
  • SP-GiST does not support INCLUDE. If a covering index is part of the plan, GiST is the only option.

Changing your mind later

None of these choices is permanent. Because all three access methods can coexist on the same column, migrating from one to another is a matter of building the replacement concurrently, confirming from pg_stat_user_indexes that the planner has moved to it, and dropping the old one. The whole sequence is online, reversible at every step, and takes an afternoon. That is worth knowing before the decision, because it lowers the stakes considerably: pick the one the evidence supports today, measure it in production, and change it if the data shape changes.