Using PostgreSQL BRIN indexes to speed up time‑series queries

The problem: slow range scans on large time‑series tables

Many applications store sensor readings, logs, or metrics in a table where each row is naturally ordered by a timestamp column. As the table grows to millions of rows, a simple WHERE ts BETWEEN '2026-01-01' AND '2026-01-02' can scan the whole table, causing noticeable latency and high I/O.

Thesis: a BRIN index offers a lightweight way to skip whole data blocks when the timestamp column is physically ordered

BRIN (Block Range INdex) stores the minimum and maximum value for each block of table pages. When the column values are monotonic (e.g., ever‑increasing timestamps), the index can quickly determine that an entire block lies outside the query range and skip it, reducing the amount of data read from disk.

Creating and using a BRIN index

Assuming you have a table sensor_data with a ts TIMESTAMPTZ NOT NULL column, the index is created with:

-- Run as a role that has CREATE privilege on the table
CREATE INDEX idx_sensor_data_ts_brin ON sensor_data USING BRIN (ts);

To verify the index is being used, run:

EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE ts >= '2026-01-01 00:00:00+00' AND ts < '2026-01-02 00:00:00+00';

Look for an Index Scan node that mentions idx_sensor_data_ts_brin. The actual execution time shown in the plan reflects the benefit of skipping blocks.

Worked example: size and speed comparison

Consider a test table with 10 million rows of ordered sensor data.

• Create a BRIN index on ts.
• Create a traditional B‑tree index on the same column for comparison.
• Check index sizes with pg_indexes_size().
• Run the same range query and compare execution times.

The following steps illustrate the process (replace yourdb with your database name):

-- 1. Create test table (if not already present)
CREATE TABLE sensor_data (
    id BIGSERIAL PRIMARY KEY,
    ts TIMESTAMPTZ NOT NULL,
    value DOUBLE PRECISION
);

-- 2. Insert ordered data (example using generate_series)
INSERT INTO sensor_data (ts, value)
SELECT
    '2020-01-01 00:00:00+00'::timestamptz + (interval '1 second' * g),
    random() * 100
FROM generate_series(0, 9999999) AS g;

-- 3. Create BRIN index
CREATE INDEX idx_sensor_data_ts_brin ON sensor_data USING BRIN (ts);

-- 4. Create B‑tree index for comparison
CREATE INDEX idx_sensor_data_ts_btree ON sensor_data (ts);

-- 5. Compare sizes
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_indexes_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'sensor_data'::regclass
  AND indexrelid IN ('idx_sensor_data_ts_brin'::regclass, 'idx_sensor_data_ts_btree'::regclass);

-- 6. Test query with BRIN enabled (default)
SET enable_bitmapscan = off; -- ensure plain index scan
EXPLAIN ANALYZE
SELECT count(*) FROM sensor_data
WHERE ts >= '2020-06-01 00:00:00+00' AND ts < '2020-06-02 00:00:00+00';

-- 7. Test query forcing a sequential scan to see baseline
SET enable_indexscan = off;
EXPLAIN ANALYZE
SELECT count(*) FROM sensor_data
WHERE ts >= '2020-06-01 00:00:00+00' AND ts < '2020-06-02 00:00:00+00';

-- 8. Reset planner settings
RESET enable_indexscan;
RESET enable_bitmapscan;

In a typical ordered‑data scenario you will observe:

• The BRIN index occupies only a few hundred kilobytes (often < 1 MB), while the B‑tree index may be tens or hundreds of megabytes.
• The query plan shows an Index Scan using the BRIN index and the execution time drops from several seconds (sequential scan) to a few hundred milliseconds.

Trade‑offs and limitations

BRIN indexes are most effective when the indexed column’s physical order matches its logical order. If rows are inserted out‑of‑order (e.g., due to batch loads from multiple sources or updates that move rows), the min/max ranges per block become less tight, causing the index to scan more blocks than necessary. In such cases you can:

• Periodically run REINDEX INDEX idx_sensor_data_ts_brin; to refresh the stored ranges.
• Increase the pages_per_range storage parameter (default 128) to make each range cover more pages, which reduces index size but may decrease selectivity.

Additionally, BRIN only supports a limited set of operators (<, <=, =, >=, >) and cannot be used for expressions like date_trunc('day', ts) without an appropriate operator class. For complex predicates you may still need a B‑tree or a covering index.

Actionable closing

If your time‑series table receives data in roughly chronological order and you frequently query ranges of timestamps, try adding a BRIN index:

1. Confirm that inserts are mostly append‑only (check pg_stat_user_tables for high n_tup_ins and low n_tup_upd/n_tup_del).
2. Create the index with CREATE INDEX … USING BRIN (ts);.
3. Validate with EXPLAIN ANALYZE on a representative range query.
4. Monitor index size via pg_indexes_size() and query performance over time.
5. Schedule a periodic REINDEX if you notice degraded performance after bulk loads or updates.

By following these steps you can achieve significant I/O savings and faster response times with minimal storage overhead.