Choosing GiST vs BRIN Indexes for Time-Series Sensor Geometry #

For a spatial-temporal sensor table in PostGIS, the index choice comes down to what you query and how the rows are physically ordered: use a GiST index for spatial proximity questions (“which sensors are within 5 km of this point?”) and a BRIN index for time-range scans over append-only telemetry (“every reading in July”). GiST is a per-row tree that excels at arbitrary spatial lookups but costs real disk and write time; BRIN is a tiny per-block summary that is nearly free to store and maintain but only prunes when rows land on disk in the same order they are queried by. Most sensor archives built on the PostGIS storage and spatial indexing schema end up wanting both — GiST on the geometry, BRIN on the timestamp — but knowing which does what prevents you from paying for an index the planner will never use.

Why the Two Indexes Behave So Differently #

GiST (Generalized Search Tree) builds a balanced tree of bounding boxes. Every row contributes one leaf entry holding that geometry’s minimum bounding rectangle, and interior nodes hold the union of their children’s boxes. A spatial predicate like ST_DWithin walks the tree, descending only into branches whose bounding box overlaps the query region. Because each row is indexed individually, GiST answers arbitrary spatial questions regardless of how the data is laid out on disk — but that per-row structure is exactly why it grows large and why every insert must update the tree.

BRIN (Block Range Index) stores no per-row data at all. It divides the table into ranges of physical pages (128 pages per range by default) and records only a summary — for a timestamp, the min and max observed_at in that block range. A range scan checks each summary and skips any block range whose min/max cannot contain the queried values. This makes BRIN astonishingly small and almost free to maintain, but it only works when physical order correlates with the indexed column. Sensor telemetry is naturally append-only in event-time order, so consecutive observed_at values land in consecutive blocks and each range summarizes a tight time band. That correlation is the whole game: break it and BRIN degrades to a full scan while still reporting as “used.”

The comparison below summarizes the trade-off across the criteria that actually decide the choice for environmental sensor data.

Production-Ready Implementation #

The helper below inspects a table’s query needs and row-order correlation, then emits the CREATE INDEX statements that fit. It uses PostgreSQL’s pg_stats.correlation to check whether observed_at is physically ordered enough for BRIN to be worthwhile before recommending it.

# python 3.11 · psycopg2-binary==2.9.9
from dataclasses import dataclass

import psycopg2


@dataclass
class IndexPlan:
    """A recommended index set for a sensor readings partition."""
    statements: list[str]
    rationale: list[str]


def recommend_indexes(
    dsn: str,
    table: str,
    geom_col: str = "geom",
    time_col: str = "observed_at",
    spatial_queries: bool = True,
    time_range_queries: bool = True,
    brin_correlation_floor: float = 0.9,
    pages_per_range: int = 32,
) -> IndexPlan:
    """
    Recommend GiST and/or BRIN indexes for a spatial-temporal sensor table.

    Args:
        spatial_queries:       True if the table serves ST_DWithin / && lookups.
        time_range_queries:    True if the table serves observed_at range scans.
        brin_correlation_floor: Minimum pg_stats correlation on time_col to
                                justify BRIN; below this, physical order is too
                                scrambled for BRIN pruning to help.
        pages_per_range:       BRIN granularity; smaller is more selective.

    Returns an IndexPlan with CREATE INDEX statements and human-readable reasons.
    Time: O(1) — one catalog lookup.
    """
    statements: list[str] = []
    rationale: list[str] = []

    if spatial_queries:
        statements.append(
            f"CREATE INDEX ON {table} USING GIST ({geom_col});"
        )
        rationale.append(
            "Spatial proximity queries need a per-row tree; GiST handles "
            "arbitrary point locations regardless of on-disk order."
        )

    if time_range_queries:
        # Only recommend BRIN if physical order correlates with the timestamp.
        with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
            cur.execute(
                """
                SELECT abs(correlation)
                FROM pg_stats
                WHERE tablename = %s AND attname = %s
                """,
                (table.split(".")[-1], time_col),
            )
            row = cur.fetchone()
        correlation = float(row[0]) if row and row[0] is not None else 0.0

        if correlation >= brin_correlation_floor:
            statements.append(
                f"CREATE INDEX ON {table} USING BRIN ({time_col}) "
                f"WITH (pages_per_range = {pages_per_range});"
            )
            rationale.append(
                f"observed_at correlation {correlation:.2f} >= "
                f"{brin_correlation_floor}: rows are append-ordered, so BRIN "
                "prunes time ranges at almost no storage cost."
            )
        else:
            statements.append(
                f"CREATE INDEX ON {table} USING BTREE ({time_col});"
            )
            rationale.append(
                f"observed_at correlation {correlation:.2f} < "
                f"{brin_correlation_floor}: rows are out of order (likely a "
                "backfill); BRIN would not prune, so fall back to a b-tree."
            )

    return IndexPlan(statements=statements, rationale=rationale)

The correlation gate is the important part: it encodes the rule that BRIN is only worth building when the table’s physical order tracks the timestamp. A freshly bulk-loaded, timestamp-ordered partition scores near 1.0; a partition polluted by an out-of-order backfill scores low, and the function correctly recommends a b-tree instead.

Parameter Tuning Guide #

The right index and its settings vary by how each sensor class is written and read. Query pattern decides the index type; write pattern decides the BRIN granularity and build timing.

pages_per_range trades index size for selectivity: smaller ranges prune more precisely but store more summaries. For busy month-sized partitions, 16–32 gives near-b-tree selectivity at a fraction of a percent of the size; low-volume tables are fine at the default 128.

Build timing differs by index. Build GiST once after a bulk COPY load rather than maintaining it row-by-row; BRIN is cheap enough to create at any time and refresh with brin_summarize_new_values after appends.

Verification and Testing #

Confirm each index is actually used by the query it was built for. The test below creates both indexes on a populated partition and asserts, via EXPLAIN, that a spatial predicate hits GiST and a time-range predicate hits BRIN.

# python 3.11 · psycopg2-binary==2.9.9
import os
import psycopg2


TEST_DSN = os.environ.get("TEST_DSN", "dbname=eiot_test")


def _plan(cur, query: str) -> str:
    cur.execute("EXPLAIN (FORMAT TEXT) " + query)
    return "\n".join(r[0] for r in cur.fetchall())


def test_planner_uses_gist_for_spatial_and_brin_for_time():
    spatial_q = """
        SELECT count(*) FROM sensor_readings
        WHERE ST_DWithin(
            geom::geography,
            ST_SetSRID(ST_MakePoint(-122.4, 37.77), 4326)::geography,
            5000
        )
    """
    time_q = """
        SELECT count(*) FROM sensor_readings
        WHERE observed_at >= '2026-07-10' AND observed_at < '2026-07-11'
    """
    with psycopg2.connect(TEST_DSN) as conn, conn.cursor() as cur:
        cur.execute("ANALYZE sensor_readings")           # refresh stats first
        spatial_plan = _plan(cur, spatial_q)
        time_plan = _plan(cur, time_q)

    # Spatial predicate should ride the GiST index, not a sequential scan.
    assert "Index Scan" in spatial_plan or "Bitmap Index Scan" in spatial_plan
    assert "gist" in spatial_plan.lower() or "geom" in spatial_plan.lower()

    # Time-range predicate should ride the BRIN index.
    assert "Bitmap Index Scan" in time_plan
    assert "brin" in time_plan.lower() or "observed_at" in time_plan.lower()

If the spatial plan shows a Seq Scan, the SRID on the query literal does not match the column or statistics are stale — run ANALYZE and confirm both sides are SRID 4326. If the time plan shows a full scan despite the BRIN index, the physical order is scrambled; check pg_stats.correlation on observed_at.

Gotchas #

A “used” BRIN index can still scan everything. BRIN always reports as used in the plan, but if block-range min/max values overlap the query it recheck-scans every block anyway. Measure Buffers in EXPLAIN (ANALYZE, BUFFERS), not just the index name — an effective BRIN reads a small fraction of the table’s pages.

Geometry BRIN rarely helps scattered points. BRIN on geom only prunes when nearby points sit in nearby blocks, which requires loading in spatial (for example Hilbert-curve) order. Randomly scattered or mobile sensor points have no such correlation, so a geometry BRIN summarizes ranges spanning the whole coverage area and prunes nothing — use GiST for spatial reads.

Backfills silently kill BRIN pruning. Inserting historical timestamps into a live partition scatters old values across new blocks, widening every range summary until it overlaps all queries. After any out-of-order load, run brin_summarize_new_values or, if correlation is badly broken, CLUSTER the partition on the timestamp and reindex.

Forgetting to ANALYZE after a bulk load. Both indexes depend on fresh planner statistics. Immediately after a large load the planner may still believe the table is tiny and choose a sequential scan over either index. Always ANALYZE the partition before serving reads.

Related #

• PostGIS Storage & Spatial Indexing for Sensor Networks — the schema and partitioning design these indexes sit on
• Bulk-Loading Sensor Readings into PostGIS with COPY and ST_MakePoint — load rows in timestamp order so BRIN stays correlated, then build GiST once at the end