Postgres BRIN indexes are surprisingly effective at shrinking table scans for time-series data, but only if you understand the fundamental trade-off they exploit.
Let’s see this in action. Imagine a sensor_readings table:
CREATE TABLE sensor_readings (
id BIGSERIAL PRIMARY KEY,
sensor_id INT,
reading_time TIMESTAMPTZ NOT NULL,
value NUMERIC
);
-- Populate with some time-series data
INSERT INTO sensor_readings (sensor_id, reading_time, value)
SELECT
random() * 100,
NOW() - (random() * '30 days'::interval),
random() * 1000
FROM generate_series(1, 1000000);
-- Add a BRIN index on the timestamp
CREATE INDEX idx_sensor_readings_reading_time_brin
ON sensor_readings USING brin (reading_time);
Now, let’s query for readings within a specific hour, say, from 2023-10-26 14:00:00 to 2023-10-26 15:00:00:
EXPLAIN ANALYZE
SELECT *
FROM sensor_readings
WHERE reading_time BETWEEN '2023-10-26 14:00:00' AND '2023-10-26 15:00:00';
Without the BRIN index, Postgres would likely have to scan a significant portion of the table, especially if it’s large. With the BRIN index, the query plan might look something like this:
Bitmap Heap Scan on sensor_readings (cost=10.00..50000.00 rows=1000 width=32) (actual time=0.050..10.500 rows=500 loops=1)
Recheck Cond: (reading_time >= '2023-10-26 14:00:00'::timestamp with time zone) AND (reading_time <= '2023-10-26 15:00:00'::timestamp with time zone)
Heap Blocks: lossy=150 <-- This is the key!
-> Bitmap Index Scan on idx_sensor_readings_reading_time_brin (cost=0.00..9.75 rows=1000 width=0) (actual time=0.020..0.020 rows=0 loops=1)
Index Cond: (reading_time >= '2023-10-26 14:00:00'::timestamp with time zone) AND (reading_time <= '2023-10-26 15:00:00'::timestamp with time zone)
Notice the Heap Blocks: lossy=150. This tells us that the BRIN index, instead of pointing to individual rows, points to ranges of table blocks (pages). It identified that only about 150 blocks of the table might contain data within our time range. Postgres then performs a "lossy" scan on these blocks, meaning it doesn’t guarantee that every row within those blocks matches, but it dramatically reduces the number of blocks it needs to inspect. This is a massive win for large tables where data is physically ordered by the indexed column.
The problem BRIN indexes solve is the storage overhead and maintenance cost of traditional B-tree indexes on very large tables, particularly when the indexed column has a strong correlation with the physical storage order of the table data. For time-series data, this correlation is almost guaranteed if you insert new data sequentially. A B-tree index on reading_time for a table with billions of rows would be enormous and slow to update. A BRIN index, on the other hand, stores summary information (minimum and maximum values) for a range of table blocks. This makes the index itself much smaller and faster to build and maintain.
The core mechanism is block-level summarization. When you create a BRIN index, Postgres divides your table into "ranges" of pages. For each range, it stores the minimum and maximum value of the indexed column found within those pages. During a query, Postgres uses this summary information to quickly rule out entire ranges of pages that cannot contain matching rows. If your query asks for data between T1 and T2, and a particular block range has a maximum reading_time less than T1, Postgres knows it doesn’t need to look in that range at all. Conversely, if a block range has a minimum reading_time greater than T2, it can also be skipped.
You control the granularity of this summarization with the pages_per_range parameter during index creation. The default is 128 pages. A smaller value means more granular summaries, a larger index, and potentially better selectivity. A larger value means a smaller index and less precise summarization. For time-series data, pages_per_range = 1 is often ideal, effectively creating a summary for each individual page, which is usually the most efficient for ordered data.
The magic of BRIN indexes hinges on the physical ordering of data within the table. If your sensor_readings table is frequently updated with very old timestamps, or if data is scattered due to frequent UPDATE or DELETE operations on the indexed column, the correlation between physical order and the indexed column breaks down. In such cases, BRIN indexes become ineffective because the min/max summaries for block ranges will span the entire range of possible values, rendering them useless for filtering.
The next hurdle you’ll likely encounter is understanding how to tune the pages_per_range parameter effectively for your specific data distribution and query patterns.