BRIN Indexes in PostgreSQL: A Guide to Hidden Performance Gems

When we talk about PostgreSQL and query optimization, most people immediately think of B-Tree indexes, GIN, GiST, and so on. But BRIN indexes often remain in the shadows, despite being incredibly useful in certain scenarios. They can dramatically boost performance, especially when dealing with massive tables and limited disk space. Today, I’ll explain how BRIN works and when it shines.

What Is BRIN?

BRIN stands for Block Range Index. While regular indexes aim to store detailed information about every row, BRIN takes a different approach. It stores summary information for groups of pages, called “zones,” instead of indexing every row individually.

To simplify, BRIN divides a table into blocks—each zone typically covering 32 pages by default—and stores the minimum and maximum values for a column (or other generalized characteristics depending on the operator class) within that zone. When you query the data, BRIN identifies the relevant zones and skips the rest.

Is it 100% precise? Not quite—you’ll still need to scan rows within the selected zones. However, this filtering is significantly faster than scanning the entire table.

When Is BRIN a Good Choice?

Here are a few scenarios where BRIN excels:

1. Huge Tables
   When dealing with tables containing millions or billions of rows, traditional B-Tree indexes can become bloated and take up substantial space. BRIN, on the other hand, remains lightweight and compact.

2. Natural Data Correlation
   If your data has a natural order, BRIN can be particularly effective. For instance, if you have an order_date column in an orders table, it’s likely that older orders are stored at the beginning of the table and newer ones at the end. For queries targeting a specific month, BRIN quickly narrows down the relevant zones.

3. Disk Space Savings
   BRIN indexes are remarkably compact. Instead of storing all individual values, they use aggregated metadata per zone, which reduces storage requirements.

Limitations of BRIN

Like any tool, BRIN has its trade-offs:

• Inaccuracy: BRIN doesn’t pinpoint data locations. It only indicates zones where the data might be, requiring further filtering within those zones.
• Scattered Data: If column values are scattered randomly, BRIN won’t be able to screen out irrelevant zones effectively, making it less beneficial.

Creating a BRIN Index

Here’s a quick example of how to create a BRIN index:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);

The pages_per_range parameter determines the number of pages in each zone. The default is 128 pages, but you can adjust it. A smaller value increases index accuracy but also enlarges the index size. Experimenting with this parameter can help find the best balance for your data.

Automating Summaries

You can use extensions like autosummarize to automate the updating of summary data, removing the need to manually call brin_summarize_new_values.

BRIN Operator Classes: Minmax and Inclusion

BRIN uses operator classes to define how it summarizes data. The two primary approaches are:

• Minmax Classes: Store the minimum and maximum values in each zone. Ideal for ordered types like numbers, dates, or strings.
• Inclusion Classes: Handle more complex types, such as geometries, IP networks, or ranges. These classes store “bulk” representations like bounding boxes or unions of ranges, enabling operations on complex data structures.

Why Inclusion Classes Matter

Inclusion classes can process complex data efficiently. For instance, they might identify zones containing objects that intersect a specific area, making them useful for specialized use cases like spatial data.

BRIN indexes are a powerful yet underutilized feature in PostgreSQL. While they’re not suitable for every situation, they can significantly improve performance when working with large datasets and naturally ordered data. Experiment with BRIN to unlock its potential and optimize your database queries.

There are minmax-operations for most types, and inclusion-operators for more exotic structures. That is, you can index not only simple numbers and strings, but also complex types. For example, box_inclusion_ops will allow you to search for objects in a certain geographical area, quickly cutting off the zones where there are exactly no suitable objects.

Sample optimization example

Suppose there is an order table with a bunch of rows:

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    order_date    DATE NOT NULL,
    customer_id   BIGINT NOT NULL,
    total_amount  NUMERIC(10, 2) NOT NULL
);

Inserting a bunch of data:

INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
    (DATE '2023-01-01' + (RANDOM()*365)::INT),
    (RANDOM()*1000000)::BIGINT,
    (RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;

Now we create the BRIN index:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);

date_minmax_ops says: we will store the minimum and maximum date for each 64-page zone.

When querying:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';

The plan will show that PostgreSQL will first consult the BRIN index, determine which zones may contain the desired dates, and skip the rest. If the data is roughly sorted by date (e.g., as inserted), the number of extra zones will be minimized.

Parameter Subtleties

pages_per_range

• Smaller values: Provide more accurate filtering (each zone is smaller, so less garbage) but result in a larger index size.
• Larger values: Reduce index size but worsen accuracy.
• Recommendation: Test on real data to find the optimal balance.

autovacuum and brin_summarize_new_values

Custom Operator Classes

If no standard operator class fits your needs, you can write your own. This is a more advanced approach, requiring you to:

• Implement C-functions to describe how to:
  • Aggregate values,
  • Merge zones, and
  • Check intersections with queries.
• This allows you to index unique or exotic data structures, but it’s a complex, highly technical task.

Conclusion

BRIN indexes will not replace B-Tree indexes everywhere. However, in cases where data is organized or has a natural correlation with its physical location, BRIN can deliver amazing performance with minimal index size.