When to Choose a BRIN Index in PostgreSQL for Time‑Series Data

The problem: slow range scans on a growing log table

Imagine a table that stores sensor readings, with a timestamp column that always increases as new rows are appended. Over months the table grows to hundreds of millions of rows. A simple query like SELECT * FROM readings WHERE ts BETWEEN '2024-06-01' AND '2024-06-02' starts to take seconds or minutes because PostgreSQL must scan large portions of the table to find the matching rows.

What a BRIN index actually stores

BRIN stands for Block Range INdex. Instead of indexing each row, PostgreSQL divides the table into ranges of consecutive pages (by default 128 pages per range) and stores the minimum and maximum value of the indexed column for each range. During a query, the planner can skip entire ranges whose min/max do not satisfy the predicate, reading only the relevant page ranges.

Because the index holds only one summary per range, its size is tiny compared to a traditional B‑tree, often just a few megabytes even for tables that are tens of gigabytes.

Creating and tuning a BRIN index

To create a BRIN index on a timestamp column you run:

CREATE INDEX ON readings USING BRIN (ts);

The pages_per_range parameter controls how many table pages are grouped into each summary. A smaller value gives more precise summaries (fewer false positives) but makes the index larger; a larger value does the opposite. You can set it at creation time:

CREATE INDEX ON readings USING BRIN (ts) WITH (pages_per_range = 64);

After significant inserts or updates, the stored min/max values can become stale. Refreshing them requires a VACUUM (which updates statistics) or a REINDEX if you want to rebuild the summaries from scratch.

Worked example: comparing plans

Below is a step‑by‑step illustration you can run in a test database. Adjust the table name, schema, and connection details to match your environment.

1. Create a test table and populate it with sequentially increasing timestamps:

   CREATE TABLE sensor_log (
       id BIGSERIAL PRIMARY KEY,
       ts TIMESTAMPTZ NOT NULL,
       value DOUBLE PRECISION
   );
   
   INSERT INTO sensor_log (ts, value)
   SELECT
       '2020-01-01 00:00:00'::timestamptz + (interval '1 second' * generate_series) AS ts,
       random() * 100 AS value
   FROM generate_series(0, 200000000);  -- 200 million rows, adjust as needed
   

2. Check the table size (run as a role with SELECT privileges on pg_total_relation_size):

   SELECT pg_size_pretty(pg_total_relation_size('sensor_log')) AS total_size;

3. Create a BRIN index on ts:

   CREATE INDEX ON sensor_log USING BRIN (ts);

4. Explain a range query without forcing an index scan (to see the planner’s choice):

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT COUNT(*) FROM sensor_log
   WHERE ts BETWEEN '2021-07-01' AND '2021-07-02';
   

   Look for an Index Scan using sensor_log_ts_brin_idx node and note the Rows Removed by Filter value; it should be low if the BRIN index is effective.

5. For comparison, drop the BRIN index and create a B‑tree index, then repeat the EXPLAIN ANALYZE:

   DROP INDEX IF EXISTS sensor_log_ts_brin_idx;
   CREATE INDEX ON sensor_log (ts);
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT COUNT(*) FROM sensor_log
   WHERE ts BETWEEN '2021-07-01' AND '2021-07-02';
   

   Observe the index size difference (you can check with pg_relation_size('sensor_log_ts_brin_idx') vs. the B‑tree index).

These steps let you verify that the BRIN index yields a smaller footprint and that the query plan skips large portions of the table. Remember not to treat the output as a guarantee of performance in your production workload; run similar tests with your actual data distribution.

Trade‑offs and limitations

• Correlation matters: BRIN works best when the column’s value order matches the physical storage order (e.g., ever‑increasing timestamps or serial IDs). If the column is randomly ordered, the min/max ranges overlap heavily and the index offers little benefit.
• Stale summaries: Heavy updates or deletes can make the stored min/max values inaccurate, causing the planner to scan unnecessary ranges. Periodic VACUUM or REINDEX mitigates this.
• Equality predicates: A query like WHERE ts = '2024-06-01 12:34:56' may still need to examine many ranges because the equality value could fall inside a wide range’s min/max.

When to reach for a B‑tree instead

If your workload includes many point lookups, frequent updates on the indexed column, or you need ordering guarantees for MIN/MAX aggregates without scanning ranges, a traditional B‑tree index is usually the safer choice despite its larger size.

Actionable closing

1. Identify tables with append‑only, sequentially growing columns (timestamps, serial IDs, monotonic counters).
2. Create a BRIN index with a modest pages_per_range (start with the default 128, adjust down if you see high rows‑removed‑by‑filter).
3. Monitor index size via pg_relation_size and query performance with EXPLAIN ANALYZE.
4. Schedule a periodic VACUUM (or REINDEX during low‑traffic windows) to keep summaries fresh.
5. Re‑evaluate if the column update pattern changes; switch to a B‑tree if correlation degrades.

By following these steps you can achieve significant query speed‑ups for range predicates while keeping the index footprint minimal—a practical win for large, time‑series‑oriented PostgreSQL workloads.