BRIN, Hash, and Choosing the Right Index Type
500,000 rows in physical time order. A B-tree index on created_at costs 11MB. A BRIN index costs 24kB — for the same query support.
A B-tree stores one entry per row, which is precise but expensive at real scale — a 500-million-row table's B-tree index can genuinely run into tens of gigabytes. BRIN (Block Range Index) takes a fundamentally different, much cheaper approach: instead of one entry per row, it stores one summary — just a min and max value — per contiguous range of physical table pages. For a naturally-ordered column like an append-only created_at timestamp, that summary is nearly as useful as a full B-tree for range queries, at a tiny fraction of the storage and maintenance cost, because rows inserted close together in time also live close together on disk.\n\nThat last clause is the whole story, and also the catch: BRIN's usefulness depends entirely on the data's physical order matching the column being indexed. When that correlation breaks — rows updated out of order, or a column with no natural relationship to insertion order — a page range's min/max summary stops being narrow and starts covering almost the entire value domain, and the index stops eliminating anything useful. This lab measures both the real win and the real limit, plus a second, differently-shaped index type: Hash, built for equality alone, and genuinely unable to serve a range query at all.
BRIN vs B-tree, Measured
CREATE INDEX idx_ts_brin ON idx_timeseries USING brin(created_at) WITH (pages_per_range=128);
CREATE INDEX idx_ts_btree ON idx_timeseries(created_at);
SELECT pg_size_pretty(pg_relation_size('idx_ts_brin')), pg_size_pretty(pg_relation_size('idx_ts_btree'));
brin_size | btree_size
-----------+------------
24 kB | 11 MB
Roughly 458 times smaller, on 500,000 physically time-ordered rows. pages_per_range=128 means each BRIN summary entry covers 128 table pages at once — one min/max pair standing in for a whole block of rows, instead of the one-entry-per-row cost a B-tree pays.
BRIN, Genuinely Used for a Range Query
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM idx_timeseries WHERE created_at BETWEEN '2025-01-02' AND '2025-01-03';
Bitmap Heap Scan on idx_timeseries (actual rows=86401.00 loops=1)
Recheck Cond: (...)
Rows Removed by Index Recheck: 26027
Heap Blocks: lossy=768
-> Bitmap Index Scan on idx_ts_brin (actual rows=7680.00 loops=1)
Execution Time: 433.628 ms
"lossy" is the key word — BRIN's bitmap is not exact per row, only per page range, so PostgreSQL fetches every row in every flagged range and rechecks the real condition, discarding the 26,027 rows that fell inside a matching range's min/max but didn't actually qualify. This is genuinely more rechecking than a B-tree needs, but the index itself cost 458 times less to build and maintain.
The Same Column, Out of Physical Order
Insert the identical 500,000 rows with created_at values assigned randomly instead of sequentially, then build the identical kind of BRIN index and run the identical query:
Seq Scan on idx_ts_shuffled (actual rows=86260.00 loops=1)
Filter: (...)
Rows Removed by Filter: 413740
The planner does not even choose the BRIN index this time — with created_at values scattered randomly across every physical page, each page range's min/max summary now spans nearly the entire table's value domain, so almost every range would need to be checked anyway. BRIN did not give a wrong answer; it simply stopped being able to eliminate anything, and the planner correctly recognized a Seq Scan was cheaper than a bitmap scan that would have to check almost every page regardless.
Hash: Equality Only, By Design
CREATE INDEX idx_hashonly_idx ON idx_hashonly USING hash(id);
EXPLAIN SELECT * FROM idx_hashonly WHERE id = 12345; -- Index Scan using idx_hashonly_idx
EXPLAIN SELECT * FROM idx_hashonly WHERE id > 12345 AND id < 12400; -- Seq Scan
A Hash index stores entries by hash value, with no relationship between a row's hash and its neighbors' — perfect for "find this exact value" and structurally unable to answer "find values in this range" at all, unlike a B-tree, which supports both.
BRIN (Block Range Index)
An index storing one summary value (typically a min/max pair) per contiguous range of physical table pages, rather than one entry per row. Dramatically smaller and cheaper to maintain than a B-tree, but only useful when the indexed column's values correlate strongly with physical row order — an append-only timestamp being the canonical case. A BRIN scan is inherently "lossy": it identifies candidate page ranges, then rechecks every row in them against the real condition.
Hash index
An index storing entries by the hash of the indexed value, supporting equality lookups only — there is no ordering relationship between nearby hash values and nearby original values, so range queries, sorting, and pattern matching are all structurally impossible with a Hash index. Since PostgreSQL 10, Hash indexes are WAL-logged and crash-safe, making them a legitimate (if narrow) choice specifically for high-volume equality-only lookups.
📏 Build BRIN and B-tree on the Same Column, Measure Both
Create a BRIN index and a B-tree index on the identical created_at column, and compare their real sizes.
psql -U postgres -d beer_db -c "CREATE INDEX idx_ts_brin ON idx_timeseries USING brin(created_at) WITH (pages_per_range=128);" -c "CREATE INDEX idx_ts_btree ON idx_timeseries(created_at);" -c "SELECT pg_size_pretty(pg_relation_size('idx_ts_brin')) AS brin_size, pg_size_pretty(pg_relation_size('idx_ts_btree')) AS btree_size;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_ts_brin ON idx_timeseries USING brin(created_at) WITH (pages_per_range=128);" -c "CREATE INDEX idx_ts_btree ON idx_timeseries(created_at);" -c "SELECT pg_size_pretty(pg_relation_size('idx_ts_brin')) AS brin_size, pg_size_pretty(pg_relation_size('idx_ts_btree')) AS btree_size;" SET CREATE INDEX CREATE INDEX brin_size | btree_size -----------+------------ 24 kB | 11 MB (1 row)
🎯 Confirm BRIN Genuinely Answers a Range Query
Drop the B-tree so only BRIN remains, then run a real range query and read the "lossy" recheck behavior directly.
psql -U postgres -d beer_db -c "DROP INDEX idx_ts_btree;"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM idx_timeseries WHERE created_at BETWEEN '2025-01-02' AND '2025-01-03';"student@lab:~$ psql -U postgres -d beer_db -c "DROP INDEX idx_ts_btree;" SET DROP INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM idx_timeseries WHERE created_at BETWEEN '2025-01-02' AND '2025-01-03';" SET QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on idx_timeseries (cost=33.80..4837.70 rows=86580 width=24) (actual rows=86401.00 loops=1) Recheck Cond: ((created_at >= '2025-01-02 00:00:00'::timestamp without time zone) AND (created_at <= '2025-01-03 00:00:00'::timestamp without time zone)) Rows Removed by Index Recheck: 26027 Heap Blocks: lossy=768 Buffers: shared hit=777 -> Bitmap Index Scan on idx_ts_brin (cost=0.00..12.16 rows=92593 width=0) (actual rows=7680.00 loops=1) Index Cond: ((created_at >= '2025-01-02 00:00:00'::timestamp without time zone) AND (created_at <= '2025-01-03 00:00:00'::timestamp without time zone)) Index Searches: 1 Buffers: shared hit=9 Planning: Buffers: shared hit=77 read=7 Planning Time: 8.148 ms Execution Time: 433.628 ms (13 rows)
🔀 Confirm Row Order — Not the Index — Is What Actually Matters
Build the same shape of table and BRIN index, but with created_at values inserted in random order instead of sequential, and see what happens to the same query.
psql -U postgres -d beer_db -c "CREATE TABLE idx_ts_shuffled (id int PRIMARY KEY, created_at timestamp);" -c "INSERT INTO idx_ts_shuffled SELECT g, TIMESTAMP '2025-01-01' + ((random()*500000)::int||' seconds')::interval FROM generate_series(1,500000) g;" -c "CREATE INDEX idx_shuf_brin ON idx_ts_shuffled USING brin(created_at) WITH (pages_per_range=128);" -c "ANALYZE idx_ts_shuffled;"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM idx_ts_shuffled WHERE created_at BETWEEN '2025-01-02' AND '2025-01-03';"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE idx_ts_shuffled (id int PRIMARY KEY, created_at timestamp);" -c "INSERT INTO idx_ts_shuffled SELECT g, TIMESTAMP '2025-01-01' + ((random()*500000)::int||' seconds')::interval FROM generate_series(1,500000) g;" -c "CREATE INDEX idx_shuf_brin ON idx_ts_shuffled USING brin(created_at) WITH (pages_per_range=128);" -c "ANALYZE idx_ts_shuffled;" SET CREATE TABLE INSERT 0 500000 CREATE INDEX ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM idx_ts_shuffled WHERE created_at BETWEEN '2025-01-02' AND '2025-01-03';" SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on idx_ts_shuffled (cost=0.00..9951.00 rows=87589 width=12) (actual rows=86260.00 loops=1) Filter: ((created_at >= '2025-01-02 00:00:00'::timestamp without time zone) AND (created_at <= '2025-01-03 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 413740 Buffers: shared hit=2451 Planning: Buffers: shared hit=75 read=6 Planning Time: 8.660 ms Execution Time: 844.010 ms (8 rows)
#️⃣ Build a Hash Index and Confirm Its Real Limitation
Create a Hash index for equality lookups only, then confirm directly that it cannot be used for a range query at all.
psql -U postgres -d beer_db -c "CREATE TABLE idx_hashonly (id int, val int);" -c "INSERT INTO idx_hashonly SELECT g, g FROM generate_series(1,200000) g;" -c "CREATE INDEX idx_hashonly_idx ON idx_hashonly USING hash(id);" -c "ANALYZE idx_hashonly;"psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_hashonly WHERE id = 12345;"psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_hashonly WHERE id > 12345 AND id < 12400;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE idx_hashonly (id int, val int);" -c "INSERT INTO idx_hashonly SELECT g, g FROM generate_series(1,200000) g;" -c "CREATE INDEX idx_hashonly_idx ON idx_hashonly USING hash(id);" -c "ANALYZE idx_hashonly;" SET CREATE TABLE INSERT 0 200000 CREATE INDEX ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_hashonly WHERE id = 12345;" SET QUERY PLAN ------------------------------------------------------------------------------------- Index Scan using idx_hashonly_idx on idx_hashonly (cost=0.00..8.02 rows=1 width=8) Index Cond: (id = 12345) (2 rows) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM idx_hashonly WHERE id > 12345 AND id < 12400;" SET QUERY PLAN ---------------------------------------------------------------- Seq Scan on idx_hashonly (cost=0.00..3885.00 rows=52 width=8) Filter: ((id > 12345) AND (id < 12400)) (2 rows)
Lab 3.2.5 complete. Choosing the right index type, measured against real data shapes:\n\n\n BRIN vs B-tree size, ordered data : ✅ 24 kB vs 11 MB — ~458x smaller\n BRIN genuinely answers a range query: ✅ lossy recheck, real tradeoff shown\n BRIN on shuffled data : ✅ not even chosen — ineffective, not wrong\n Hash: equality works, range fails : ✅ structural, not data-dependent\n
Enable JavaScript to run the live terminal and track your progress.