BRIN vs GiST Indexes on Partitioned Geometry

← Back to Table Partitioning for Large Spatial Datasets

This page covers when to replace a GiST index with a BRIN index on the older partitions of a spatial table, and how to tell in advance whether it will help or quietly cost you a full scan.

# Context & When to Use

The problem partitioning solves for indexes is partly a size problem. A GiST index over 780 million geometries is 44 GB; even split into monthly partitions it is still 3.4 GB per month, and thirty-six of those never fit in shared buffers at once. The recent partitions are read constantly and deserve the memory; the two-year-old ones are read once a quarter by an analyst and are pure ballast.

BRIN offers a different bargain. Instead of an entry per row, it stores a summarising bounding box per range of table blocks — by default 128 pages, about 1 MB of heap. Query planning then works by elimination: skip any block range whose summary box does not intersect the query envelope, and scan what remains. The index for a 21 GB partition is around 90 KB.

The whole thing hinges on physical correlation: whether rows that are near each other in space are also near each other on disk. Data appended in survey order, or by a sensor sweeping a route, or bulk-loaded region by region, correlates well. Data appended by 4 000 vehicles reporting simultaneously from across a country does not correlate at all, and BRIN degrades to a sequential scan with extra steps. Measure before choosing — the plan analysis techniques in Query Plan Analysis & Index Tuning apply directly.

# Runnable Implementation

-- 1. Measure correlation FIRST. Compare the area of the whole partition's
--    extent against the average area of a per-block-range extent.
WITH ranges AS (
    SELECT (ctid::text::point)[0]::bigint / 128 AS block_range,
           ST_Extent(geom)                      AS range_extent
    FROM   positions_2025_04
    GROUP  BY 1
)
SELECT count(*)                                              AS block_ranges,
       round(avg(ST_Area(range_extent))::numeric, 6)         AS avg_range_area,
       round(ST_Area(ST_Extent(range_extent))::numeric, 6)   AS whole_area,
       round((avg(ST_Area(range_extent))
              / NULLIF(ST_Area(ST_Extent(range_extent)), 0) * 100)::numeric, 2)
                                                             AS pct_of_whole
FROM   ranges;
-- pct_of_whole under ~5 %  → BRIN will prune well
-- pct_of_whole above ~40 % → BRIN will prune almost nothing

-- 2. Archival partitions: BRIN instead of GiST
DROP INDEX IF EXISTS positions_2025_04_geom_gix;
CREATE INDEX positions_2025_04_geom_brin
    ON positions_2025_04 USING BRIN (geom) WITH (pages_per_range = 32);

-- 3. Keep GiST where interactive queries land
CREATE INDEX IF NOT EXISTS positions_2026_08_geom_gix
    ON positions_2026_08 USING GIST (geom);

Because indexes declared on the parent are cloned to every partition, a mixed strategy means not declaring the geometry index on the parent and managing it per partition instead — usually in the same scheduled function that creates and detaches partitions.

# Key Parameters & Options

BRIN loses KNN ordering entirely, so any partition that serves a nearest-neighbour endpoint keeps GiST regardless of age. That constraint often decides the split point on its own.

# Choosing the boundary between hot and cold

# Creating correlation deliberately

If a partition would benefit from BRIN but its insert order is random, the correlation can be manufactured. Once a partition is detached and no longer receiving writes, rewriting it in spatial order costs one pass over the data and permanently changes what BRIN can do with it.

The ordering key needs to be a one-dimensional value that keeps nearby geometries adjacent. A geohash or a Hilbert curve index both work; PostGIS ships ST_GeoHash for points and ST_Hexagon-style grids for coarser bucketing. Sorting by geohash prefix is the simplest version and gets most of the benefit:

-- On a DETACHED partition only: rewrite in spatial order, then index
CREATE TABLE positions_2025_04_sorted AS
SELECT * FROM positions_2025_04
ORDER  BY ST_GeoHash(ST_Transform(geom, 4326), 8);

CREATE INDEX ON positions_2025_04_sorted USING BRIN (geom) WITH (pages_per_range = 32);

After the rewrite, the correlation query from earlier typically drops from 60–80 % of the whole extent per block range to under 3 %, which is the difference between BRIN pruning nothing and pruning almost everything. The cost is one full rewrite of the partition, so it belongs in the same maintenance pass that detaches and archives — never on a live partition.

# Gotchas & Failure Modes

• BRIN on an uncorrelated partition. The plan still says Bitmap Index Scan, so it looks indexed while reading every block. Compare actual rows against rows removed by filter in EXPLAIN ANALYZE — a huge removal count is the tell.
• Summaries going stale after inserts. BRIN does not summarise new pages until VACUUM runs or brin_summarize_new_values() is called. On an append-heavy archival partition that is rarely vacuumed, the tail of the table is effectively unindexed.
• CLUSTER as a prerequisite. Rewriting a partition in spatial order — CLUSTER … USING <gist index> — creates the correlation BRIN needs, but takes an ACCESS EXCLUSIVE lock for the duration. Do it once, on an already-detached partition, before attaching it.
• Parent-level index blocking the mix. An index declared on the parent is cloned to every child and cannot be dropped from one child alone. Manage geometry indexes per partition from the start if a mixed strategy is planned.
• KNN queries silently losing their index. ORDER BY geom <-> point cannot use BRIN. If an analyst runs a nearest-neighbour query across all partitions, the archival ones sort in memory. Bound such queries by time so they only touch GiST partitions.
• pages_per_range set too low. At 1 page per range the index approaches the size of a btree while still lacking its precision. Between 32 and 128 is the useful band.

# Verification Snippet

-- Size comparison, per partition
SELECT c.relname,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       am.amname                                      AS index_type
FROM   pg_class c
JOIN   pg_index i  ON i.indrelid = c.oid
JOIN   pg_class ic ON ic.oid = i.indexrelid
JOIN   pg_am am    ON am.oid = ic.relam
WHERE  c.relname LIKE 'positions_20%'
ORDER  BY c.relname;

-- Does BRIN actually eliminate anything here?
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM positions_2025_04
WHERE geom && ST_MakeEnvelope(-0.2, 51.4, 0.0, 51.6, 4326);
-- Healthy:   Bitmap Heap Scan … Rows Removed by Index Recheck: 4 012
-- Unhealthy: Bitmap Heap Scan … Rows Removed by Index Recheck: 9 940 118

# Related

• Table Partitioning for Large Spatial Datasets — the partition layout this indexes
• Reading EXPLAIN ANALYZE for Spatial Query Optimization — telling a working index scan from a decorative one
• Observability for Spatial Endpoints — watching the cache-hit ratio this trade is meant to protect

← Back to Table Partitioning for Large Spatial Datasets