BRIN vs BTREE in PostgreSQL: Massive Space Savings and Performance Trade‑offs

Introduction

PostgreSQL 9.5 introduced Block Range Indexes (BRIN) for columns whose values correlate with their physical location in large tables. The article evaluates how much performance gain BRIN provides under different data distributions and how its storage footprint compares to the traditional BTREE index.

Experimental Setup

Two identical tables (t1 and t2) were created with the same schema, row count (10 million), and data distribution. The only difference is that t1 uses BRIN indexes on each column, while t2 uses BTREE indexes.

Data Insertion

The data were generated with the following statements (identical for both tables):

insert into t1
select id, mod(id, 100), round(id/100), mod(id, 1000), round(id/1000), mod(id,30000), round(id/30000)
from generate_series(1, 10000000) id;

insert into t2
select id, mod(id, 100), round(id/100), mod(id, 1000), round(id/1000), mod(id,30000), round(id/30000)
from generate_series(1, 10000000) id;

The columns represent:

id – sequential primary key

id1 – id % 100

id2 – floor(id/100)

id3 – id % 1000

id4 – floor(id/1000)

id5 – id % 30000

id6 – floor(id/30000)

Index Size Comparison

Both tables occupy the same 574 MB of heap space. However, BRIN indexes total only about 32 KB, whereas BTREE indexes consume roughly 214 MB – a 6,848× difference.

Query Performance by Column

id (unique, sequential) : BTREE scans 4 buffers, BRIN scans 143 buffers; BTREE is markedly faster.

id1 (mod 100, many duplicates) : Both indexes must visit almost every heap page. BTREE still accesses fewer buffers (by ~270) and runs faster because BRIN’s lossy nature adds CPU filtering overhead.

id2 (floor(id/100), moderate repeats) : BTREE uses 5 buffers, BRIN uses 131 buffers; BTREE remains faster.

id3 (mod 1000) : BTREE accesses far fewer buffers; BRIN still scans all heap pages due to its 128‑page range, resulting in a 21× speed gap.

id4 (floor(id/1000), higher repeat density) : BRIN still needs 131 buffers; BTREE’s buffer count rises to 14, but BTREE stays noticeably faster.

id5 (mod 30000, sparse values) : BRIN can skip many pages, using 42,755 buffers; BTREE drops to 338 buffers, still about 265× faster.

id6 (floor(id/30000), very high repeat) : Performance gap narrows dramatically; BRIN accesses 387 buffers, BTREE 306 buffers, making them almost equal.

Conclusions

BRIN indexes achieve extraordinary storage savings (up to 6,848× less) and are ideal for data‑warehouse or VLDB scenarios where space is critical.

Across all seven tested distributions, BTREE consistently outperforms BRIN in query speed; the advantage ranges from negligible to 265× depending on data repeatability.

When query performance differences are small, BRIN may be the more economical choice due to its tiny footprint.

BRIN’s lossy nature incurs extra CPU time for precise matching.

The results suggest that similar “storage index” concepts could benefit other database platforms.