BRIN Indexes

A BRIN (Block Range INdex) stores one summary per range of adjacent heap pages, 128 pages (1 MB) by default: with the minmax operator class, the smallest and largest value. It is the zone map of Zone Maps and Pruning, built on demand for a row store, and it helps only when the column follows the rows' physical order:

CREATE INDEX orders_ts_btree ON orders (order_ts);
CREATE INDEX orders_ts_brin ON orders USING brin (order_ts);
CREATE INDEX sales_date_brin ON mart.sales USING brin (order_date);
SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class
WHERE relname IN ('orders_ts_btree', 'orders_ts_brin', 'sales_date_brin');
SELECT tablename, attname, correlation FROM pg_stats
WHERE attname IN ('order_ts', 'order_date') AND tablename IN ('orders', 'sales');
DROP INDEX orders_ts_btree;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE order_ts >= '2026-06-30' AND order_ts < '2026-07-01';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM mart.sales WHERE order_date = '2026-06-30';
DROP INDEX orders_ts_brin, mart.sales_date_brin;

 orders_ts_btree | 2208 kB
 orders_ts_brin  | 24 kB
...
 orders    | order_ts   |           1
 sales     | order_date |   0.4983874
...
   ->  Bitmap Heap Scan on orders (actual rows=195.00 loops=1)
         Rows Removed by Index Recheck: 1493
         Heap Blocks: lossy=24
...
   ->  Bitmap Heap Scan on sales (actual rows=265.00 loops=1)
         Rows Removed by Index Recheck: 11026
         Heap Blocks: lossy=256

The BRIN is 92 times smaller than the B-tree. Its bitmap is lossy (whole pages), so every row in a matching range is rechecked. orders is in perfect time order (correlation 1), and the last day sat in the final, partial range of 24 pages. mart.sales was written in hash-join order (correlation 0.50), so the same lookup read 256 pages and discarded 11,026 rows to find 265.

New pages stay unsummarized, and always scanned, until VACUUM summarizes them (or autosummarize = on). minmax_multi classes (PostgreSQL 14 1,289 ) tolerate outliers; otherwise load facts in time order, as Partitioning the Sales Mart does.