BRIN Index

Introduction

BRIN (Block Range Index) works by grouping a cluster of pages in the HEAP together into a single range rather than creating an index for every single data row like a B-Tree. The structure of a data row inside a BRIN Index (called an Index Tuple) consists of the following three core components:

blknum (Block Number): The block number starting the range (for example: 0, 128, 256, 512...).
min_value: The minimum value of that column within a block range.
max_value: The maximum value of that column within a block range.

How It Works

When executing a query with a BRIN Index, Postgres scans the index and checks whether the value being searched for falls within any [Min, Max] range

If not, it skips that entire block range, eliminating a huge amount of redundant data.
If yes, Postgres relies on the blknum to load the corresponding Pages in the HEAP up and checks them sequentially to find the exact data row.

Advantages

Super compact: Because it only stores the Min and Max for an entire cluster of data, the size of a BRIN Index is hundreds or thousands of times smaller than a B-Tree. Sometimes a table of several hundred GBs only consumes a few MBs for a BRIN Index.
Extremely fast write speed (Insert/Update): B-Tree must update the index continuously whenever a new row is added. BRIN only updates if the new value exceeds the current Min/Max boundaries of the block range.

Disadvantages

No TID stored for specific rows: BRIN does not point directly to any row in the HEAP but only to the corresponding Pages, so it still incurs additional overhead to search within these Pages
Completely dependent on physical ordering: This is the crucial point. BRIN only works effectively if the data is naturally ordered (supporting both ascending and descending order)

Use Cases

BRIN should be used when the following conditions are met:

The data table is extremely large to the extent that it makes the B-Tree Index too heavy, making it impossible to load entirely into RAM to operate efficiently.
Data possesses a natural sorting characteristic over time
- Such as timestamp data, auto-incrementing numbers...
- Avoid data with random characteristics like emails, names and phone numbers because they will create block ranges with overlapping value ranges, neutralizing the effectiveness of the Index

Comparison between BRIN and GiST

Although both BRIN and GiST share a common ground in utilizing the concept of a Bounding Box to check whether the required data intersects with that region, instead of pointing straight to each row like a B-Tree, these two index types have different storage structures and are created to solve completely different problems

BRIN depends entirely on the physical location on the disk, it does not sort the data itself but only stores the block number according to each range value to load the corresponding Pages from the HEAP.
- Used to save storage space and boost data write speed to the maximum, solving Big Data problems
- Only works with basic data types such as DateTime, Integer and Numeric
GiST builds its own B-Tree and does not care about the physical order of data, because it can group similar data or spatially close data together into Bounding Boxes, creating a hierarchical B-Tree from larger regions to smaller regions.
- Used for spatial searches, geometry and finding overlapping data ranges.
- Can handle data types that B-Tree or BRIN cannot handle such as coordinates, polygons and time intervals

Detail

First, let us create the table and BRIN Index as follows

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_brin_orders_created_at ON orders USING brin (created_at);
CREATE INDEX idx_brin_orders_updated_at ON orders USING brin (updated_at) WITH (pages_per_range = 60);

I have created the Index for two columns which are created_at and updated_at

created_at: By default, the Block Number is 128 blocks
updated_at: Explicitly specified by pages_per_range, so the Index will group each record consisting of 60 blocks

The smaller the pages_per_range value, the more the Index size increases, which is suitable when you want to query small ranges quickly
The larger the pages_per_range value, the smaller the Index size becomes, which is appropriate when your database is already massive and you want to save storage space, used when you need to query data with large ranges like extracting quarterly, monthly or annual reports

When querying data

EXPLAIN ANALYZE SELECT created_at FROM orders WHERE created_at BETWEEN '2026-06-20 08:00:00' AND '2026-06-21 09:00:00';

As you can see, even if it is only querying a single created_at column that already has an Index, after the Index scan to create the Bitmap, a Heap scan must still be executed

The reason is that the BRIN Index does not store specific row data in the Heap, but only stores the range value
The section Heap Block: loosy=185 shows that after scanning the Index, it returns 185 blocks in the Heap without specific TIDs to each row, requiring an additional Recheck step to fetch the correct data according to the query

You can verify to see that storing data for 1,000,000 records results in a very small BRIN Index size

SELECT pg_size_pretty(pg_relation_size('idx_brin_orders_created_at'));
SELECT pg_size_pretty(pg_relation_size('idx_brin_orders_updated_at'));

If you want to view how the data of the BRIN Index is stored, do as follows

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT * FROM brin_page_items(get_raw_page('idx_brin_orders_created_at', 2), 'idx_brin_orders_created_at');
SELECT * FROM brin_page_items(get_raw_page('idx_brin_orders_updated_at', 2), 'idx_brin_orders_updated_at');

You can see that the column blknum which represents the Block number will increase progressively according to pages_per_range
The column value is the range created with [Min, Max] values within a block range