BRIN Indexes for Append-Only Spatial Tables

Problem Statement

A radar-ingestion pipeline appends reflectivity samples to radar_sweeps at a rate that pushes the table past a billion rows within weeks, and the spatial index type you choose determines whether that table stays affordable or becomes an operational liability. A GiST index on the geometry(Point, 4326) column would be technically correct but grows to tens of gigabytes, refuses to stay cache-resident, and taxes every insert with tree-maintenance overhead. Because sweeps arrive in acquisition order and each sweep covers a spatially compact footprint, the table has a property GiST cannot exploit but BRIN was built for: the physical row order tracks the map. This page shows how to index that table with a BRIN index measured in megabytes, tune pages_per_range to the ingest pattern, keep the summaries current, and recognise the precise limits where BRIN stops being the right answer.

Why the Naive Approach Fails

The reflexive move is a GiST index, because that is what every PostGIS tutorial reaches for:

sql
-- Naive: a GiST R-tree on a billion-row append-only firehose
CREATE INDEX idx_radar_sweeps_gist ON radar_sweeps USING gist (geom);

On a billion rows this index balloons past 30 GB. It cannot live in shared_buffers, so every probe pays random I/O, and — worse for an append workload — each INSERT must descend and split GiST pages, throttling ingest throughput and generating write amplification that autovacuum then has to chase. The tree’s precision buys nothing here, because analysts never query a single radar sample by identity; they query a bounding box for a time-and-region slice that maps to a contiguous physical span of the table. Paying for per-row tree precision on data that is only ever read in physical-order swaths is the core mismatch.

python
# The symptom in Python: inserts that should be instant crawl under GiST maintenance
import psycopg
from psycopg.rows import dict_row

with psycopg.connect("host=localhost dbname=weather user=ingest") as conn:
    with conn.cursor() as cur:
        # Each batch of appended sweeps forces GiST page splits — throughput collapses
        cur.executemany(
            "INSERT INTO radar_sweeps (sweep_id, geom, reflectivity, observed_at) "
            "VALUES (%s, ST_SetSRID(ST_MakePoint(%s, %s), 4326), %s, %s)",
            batch,  # 50k rows; wall-clock time dominated by index upkeep, not the write
        )
    conn.commit()

Production-Ready Implementation

BRIN inverts the trade-off. It stores one bounding box per block range instead of one entry per row, so the index is tiny and adds almost nothing to insert cost. The single requirement is correlation between physical order and spatial position — which an append-only sweep firehose satisfies by construction. Below is a complete, copy-paste setup and query module.

sql
-- 1. Verify the correlation BRIN depends on BEFORE building anything.
--    Values near 1.0 (or -1.0) mean physical order tracks the spatial dimension.
ANALYZE radar_sweeps;
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'radar_sweeps'
  AND attname IN ('geom', 'observed_at');

-- 2. Build the BRIN index on the geometry column.
--    pages_per_range sized so one range ~ one spatially compact sweep burst.
CREATE INDEX CONCURRENTLY idx_radar_sweeps_geom_brin
ON radar_sweeps
USING brin (geom) WITH (pages_per_range = 64);

-- 3. Summarize immediately so existing data is prunable.
SELECT brin_summarize_new_values('idx_radar_sweeps_geom_brin');
python
"""
radar_brin_query.py
-------------------
Query the BRIN-indexed radar_sweeps table by bounding box + time slice.
BRIN pruning works only when the WHERE clause bounds the geometry column,
and streaming keeps a wide range recheck from exhausting client memory.
Requires: psycopg[binary]>=3.1, shapely>=2.0
"""
from __future__ import annotations

import psycopg
from psycopg.rows import dict_row
from shapely import wkb as shapely_wkb

DSN = "host=localhost dbname=weather user=app_user"

def stream_sweeps_in_window(
    min_lon: float, min_lat: float, max_lon: float, max_lat: float,
    start_ts: str, end_ts: str, batch: int = 2000,
):
    """
    Server-side cursor so a wide block-range recheck never materialises in RAM.
    The bounding-box predicate lets BRIN skip ranges outside the query envelope;
    the time predicate narrows the physical span further.
    """
    sql = """
        SELECT sweep_id,
               ST_AsBinary(geom) AS geom_wkb,
               reflectivity,
               observed_at
        FROM radar_sweeps
        WHERE geom && ST_SetSRID(ST_MakeEnvelope(%s, %s, %s, %s), 4326)
          AND observed_at BETWEEN %s AND %s;
    """
    with psycopg.connect(DSN, row_factory=dict_row) as conn:
        # Named server-side cursor streams rows in FETCH batches of `batch`.
        with conn.cursor(name="radar_stream") as cur:
            cur.itersize = batch
            cur.execute(sql, (min_lon, min_lat, max_lon, max_lat, start_ts, end_ts))
            for row in cur:
                row["geom"] = shapely_wkb.loads(row.pop("geom_wkb"))
                yield row

The && bounding-box operator is what BRIN understands via the brin_geometry_inclusion_ops_2d operator class. A BRIN scan cannot answer <-> nearest-neighbour ordering and does not accelerate exact-distance predicates on its own, so keep the predicate a bounding-box overlap and let the recheck refine it.

Configuration and Tuning Knobs

pages_per_range is the one knob that matters most. It sets how many consecutive 8 KB heap pages each summary covers, trading index size against pruning sharpness.

Setting Recommended value Why it matters
pages_per_range 32–128 Lower packs tighter, more selective boxes (bigger index, sharper pruning); higher yields a smaller index with coarser boxes. Match one range to one spatially compact append burst.
maintenance_work_mem 256MB+ BRIN builds are light, but a generous value speeds the initial summarize pass over a billion-row table.
autovacuum_vacuum_scale_factor 0.0 + threshold Append-only tables never trigger the default scale-based autovacuum; use a fixed threshold so summarize/vacuum still runs.
random_page_cost 1.1 (SSD) BRIN is a sequential access method, so it is less sensitive here than GiST, but a sane value keeps the planner honest.

Choosing pages_per_range empirically beats guessing. Build two candidates and compare index size against the number of block ranges a representative query must recheck:

sql
-- Compare a tight vs. coarse summary granularity
CREATE INDEX idx_sweeps_brin_32  ON radar_sweeps USING brin (geom) WITH (pages_per_range = 32);
CREATE INDEX idx_sweeps_brin_128 ON radar_sweeps USING brin (geom) WITH (pages_per_range = 128);

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE 'idx_sweeps_brin_%';

Keeping summaries current is the operational half of BRIN. Rows appended beyond the last summarized range are found only by a sequential recheck until they are summarized, so on a firehose you schedule the summarize call rather than waiting for autovacuum:

python
# Run on a short interval (e.g. every few minutes) so recent sweeps stay prunable
def refresh_brin_summaries(dsn: str = DSN) -> None:
    with psycopg.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT brin_summarize_new_values('idx_radar_sweeps_geom_brin');")
        conn.commit()

Verification Steps

Confirm BRIN is chosen, that it prunes block ranges, and that its footprint is the fraction of GiST you expected:

sql
-- 1. The plan should show a Bitmap Heap Scan fed by a Bitmap Index Scan on the BRIN index.
EXPLAIN (ANALYZE, BUFFERS)
SELECT sweep_id, reflectivity
FROM radar_sweeps
WHERE geom && ST_SetSRID(ST_MakeEnvelope(-105.4, 39.4, -104.5, 40.2), 4326)
  AND observed_at BETWEEN '2026-06-01' AND '2026-06-02';

-- 2. Index size sanity check — should be megabytes, not gigabytes.
SELECT pg_size_pretty(pg_relation_size('idx_radar_sweeps_geom_brin')) AS brin_size;

-- 3. Confirm correlation is still high enough for pruning to work.
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'radar_sweeps' AND attname = 'geom';

A healthy plan shows Bitmap Index Scan on idx_radar_sweeps_geom_brin with Buffers counts far below the table’s full page count, proving whole block ranges were skipped. If the plan rechecks nearly every block, correlation has decayed and the next section applies.

Gotchas Checklist

  • BRIN needs correlation, not just append-only. Append order alone is not enough; the appended geometries must also be spatially compact per burst. If sweeps interleave across the whole map, the summaries overlap and prune nothing. Verify pg_stats.correlation before trusting the index.

  • Out-of-order backfill silently ruins pruning. A historical load that inserts old sweeps after new ones scatters spatial extents across block ranges. Load in spatial or time order, or run CLUSTER radar_sweeps USING <a temporary gist index> to restore locality, then resummarize.

  • Unsummarized ranges fall back to sequential recheck. Freshly appended rows past the last summary are not pruned until brin_summarize_new_values runs. Schedule it; do not rely on autovacuum timing for a high-velocity table.

  • BRIN cannot do KNN or exact-distance ordering. There is no <-> support. If any endpoint needs nearest-neighbour results on this table, keep a separate SP-GiST or GiST index for that access path, or accept BRIN pre-filtering plus an in-query distance sort over the recheck set.

  • The recheck reads real rows. BRIN returns candidate block ranges, and PostgreSQL rechecks every tuple in them against the exact predicate. On a wide query envelope that recheck can touch millions of rows, so always pair the bounding box with a selective secondary predicate such as the time window, and stream results with a server-side cursor.