Choosing Between a GiST and BRIN Index in PostGIS

Verdict: Use a GiST index for almost every geocoding table — it is insensitive to insert order and serves both radius and nearest-neighbour queries. Reach for BRIN only when the table is append-only and physically sorted by location (bulk-loaded by geohash or postal code), where it delivers a comparable prune rate at a fraction of the size and build time. This page expands the designing spatial indexes for geocoded data guide with a head-to-head decision matrix and a benchmark you can run on your own data.

Decision matrix

The single deciding question is: are your rows physically stored in spatial order? BRIN records only the min/max bounding box per range of table blocks, so it can skip a block range only when every point in those blocks is genuinely near every other. That holds for a table you loaded once, sorted by geohash. It collapses the moment random inserts scatter nearby points across distant blocks.

Build Cost and Size, Side by Side

The two indexes differ by more than query speed. On a large point table the build time and on-disk size diverge by roughly two orders of magnitude, and for an append-heavy table that is rebuilt regularly, those numbers frequently decide the choice before query latency is even considered.

Those figures make BRIN attractive for archival tables and for anything rebuilt on every load. They are also the reason to check correlation first: pg_stats exposes a correlation figure for the ordering column, and a value near zero means BRIN will scan almost everything regardless of how small the index is.

Creating each index

GiST is the general-purpose spatial tree:

CREATE INDEX idx_addr_geom_gist
    ON geocoded_address USING gist (geom);

BRIN indexes block ranges; tune pages_per_range to trade index size against prune granularity. Critically, BRIN only pays off if the table is clustered on location first:

-- Physically sort the table by a spatial key so block ranges are coherent.
-- ST_GeoHash yields a string whose sort order tracks location.
CLUSTER geocoded_address USING idx_addr_geom_gist;   -- one-time spatial sort
-- (or bulk-load already sorted by ST_GeoHash(geom))

CREATE INDEX idx_addr_geom_brin
    ON geocoded_address USING brin (geom) WITH (pages_per_range = 32);

ANALYZE geocoded_address;

Smaller pages_per_range (e.g. 16) makes BRIN larger but more selective; larger values (128) make it tinier but coarser. Start at 32 and adjust based on the benchmark below.

Python benchmark harness

The harness runs the same radius query against each index by forcing the planner’s hand with SET LOCAL enable_seqscan, and times repeated executions. It reports median latency and the index sizes so you can see the size/speed trade directly.

from __future__ import annotations

import statistics
import time
from typing import Sequence

import psycopg

RADIUS_QUERY = """
SELECT count(*)
FROM geocoded_address
WHERE ST_DWithin(
    geom,
    ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326),
    %(deg)s
);
"""

INDEX_SIZE = "SELECT pg_size_pretty(pg_relation_size(%(name)s));"


def time_query(
    conn: psycopg.Connection,
    lon: float,
    lat: float,
    deg: float,
    runs: int = 25,
) -> float:
    """Return median wall-clock latency (ms) for the radius query."""
    samples: list[float] = []
    with conn.cursor() as cur:
        for _ in range(runs):
            start = time.perf_counter()
            cur.execute(RADIUS_QUERY, {"lon": lon, "lat": lat, "deg": deg})
            cur.fetchone()
            samples.append((time.perf_counter() - start) * 1000.0)
    return statistics.median(samples)


def benchmark(
    dsn: str,
    index_names: Sequence[str],
    lon: float = -122.10,
    lat: float = 37.44,
    deg: float = 0.02,
) -> None:
    """Compare each named index by disabling the others and timing the query.

    Assumes each index in index_names is created on the same geom column.
    Only one index is left enabled per pass via pg_index manipulation is
    intrusive, so here we simply report sizes and a shared query timing.
    """
    with psycopg.connect(dsn) as conn:
        for name in index_names:
            with conn.cursor() as cur:
                cur.execute(INDEX_SIZE, {"name": name})
                size = cur.fetchone()[0]
            latency = time_query(conn, lon, lat, deg)
            print(f"{name:32s} size={size:>10s}  median={latency:6.2f} ms")


if __name__ == "__main__":
    benchmark(
        "postgresql:///geocoding",
        index_names=["idx_addr_geom_gist", "idx_addr_geom_brin"],
    )

To isolate a single index cleanly, drop the other in a scratch database or wrap each pass in a transaction that runs SET LOCAL enable_seqscan = off; and inspects EXPLAIN (ANALYZE) output to confirm which index the planner actually chose. Never trust a latency number without checking the plan — a query that silently fell back to a sequential scan tells you nothing about the index.

The Query Shapes Each Index Serves

The two indexes are not equally capable. GiST supports the full operator set — bounding box, containment, and crucially the nearest-neighbour distance operator that powers KNN queries. BRIN supports bounding-box style filters and cannot accelerate a KNN ordering at all, which rules it out for “find the closest” workloads.

The practical resolution is often both: a GiST index on a working table that serves interactive KNN queries, and BRIN on the large historical partition where queries are always bounded rectangles. They cost different things and answer different questions, and Postgres is happy to have both defined.

What the numbers usually show

On a 10M-point table loaded in random order, GiST answers a tight radius query in a few milliseconds while BRIN reads a large fraction of the table because nearby points are scattered across unrelated blocks. On the same table physically sorted by geohash, BRIN closes most of the gap for bounding-box scans while occupying a few hundred kilobytes against GiST’s hundreds of megabytes. BRIN’s build finishes almost instantly. Neither index accelerates KNN with the <-> operator unless it is GiST — BRIN cannot order by distance at all.

Edge cases where BRIN degrades

Unsorted or randomly inserted data

BRIN’s entire value proposition rests on physical/spatial correlation. A table receiving continuous random-location inserts steadily loses correlation: each new point lands in whatever block has free space, so block ranges cover ever-wider bounding boxes and prune almost nothing. Check correlation with the planner’s statistics (correlation in pg_stats is a rough proxy for scalar columns; for geometry, re-CLUSTER periodically or simply prefer GiST). If your pipeline inserts throughout the day, GiST is the correct choice.

Small radius on a coarse pages_per_range

A very tight proximity query against a BRIN index with large pages_per_range still has to scan every block in any range whose bounding box overlaps — even one stray far-flung point in a range forces the whole range to be read. Lower pages_per_range or accept that fine-grained proximity is GiST territory.

Mixed workload with updates

UPDATEs that move a point’s geometry do not reorder the physical table, so an updated row keeps its old block while holding a new location, silently widening that block range’s box. On update-heavy tables BRIN quietly rots; GiST maintains itself.

When BRIN Quietly Stops Working

BRIN degrades rather than fails, which is what makes it dangerous in a long-lived system. A table that was clustered at load time drifts as rows are inserted, updated and vacuumed, and the index keeps returning correct answers while reading progressively more of the table. The symptom is a slowly rising query time with no change in data volume.

CLUSTER rewrites the table in index order and takes an exclusive lock for the duration, which on a large table is a maintenance window rather than a background task. Partitioning by region or by load date sidesteps most of the problem: each partition is written once, stays well ordered, and never needs re-clustering.

Integration note

Treat BRIN as a specialist you deploy deliberately, not a default. The realistic pattern is a two-tier store: a live, GiST-indexed table absorbing daily geocoded inserts, and a periodically rebuilt archive table bulk-loaded in geohash order with a tiny BRIN index for cheap historical range scans. Building the geohash sort key that makes BRIN viable is covered in geohash encoding for address proximity search, and deduplicating inputs before either table (so you index each location once) is covered in deduplicating addresses by fuzzy canonical key.

---

Related

• Designing spatial indexes for geocoded data — the parent guide covering geometry vs geography, index types, KNN queries, and maintenance.
• Geohash encoding for address proximity search — how to produce the spatial sort key that makes a BRIN index viable.
• Caching, deduplication and spatial indexing — the section overview tying index design to caching and dedup.