BRIN index for large sequential data in PostgreSQL

Introduction

A BRIN index helps speed up searches on very large tables where data is stored in order. It uses less space and works well when data is naturally sorted.

When you have a huge table with millions of rows sorted by date or ID.

When you want to save disk space on indexes for large datasets.

When queries often filter on ranges of values, like dates or numbers.

When you want faster query times but can accept slightly less precise indexing.

When your data is mostly inserted in order, like logs or sensor readings.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING BRIN (column_name);

BRIN stands for Block Range INdex, which summarizes ranges of data blocks.

It works best on columns where values are naturally ordered or clustered.

Examples

Creates a BRIN index on the sale_date column of the sales table.

PostgreSQL

CREATE INDEX brin_idx_date ON sales USING BRIN (sale_date);

Creates a BRIN index on the log_id column of the logs table, useful if log_id increases sequentially.

PostgreSQL

CREATE INDEX brin_idx_id ON logs USING BRIN (log_id);

Sample Program

This example creates a table with 10,000 sensor readings ordered by time, adds a BRIN index on the reading_time column, and runs a query filtering by reading_time to show how the index helps.

PostgreSQL

CREATE TABLE sensor_data (
  id SERIAL PRIMARY KEY,
  reading_time TIMESTAMP NOT NULL,
  value FLOAT
);

-- Insert sample data in order
INSERT INTO sensor_data (reading_time, value)
SELECT NOW() + (i || ' seconds')::interval, random() * 100
FROM generate_series(1, 10000) AS s(i);

-- Create BRIN index on reading_time
CREATE INDEX brin_idx_reading_time ON sensor_data USING BRIN (reading_time);

-- Query using the index
EXPLAIN ANALYZE
SELECT * FROM sensor_data WHERE reading_time >= NOW() + '5000 seconds'::interval;

Important Notes

BRIN indexes are very small compared to regular indexes, saving disk space.

They work best when data is inserted in order or naturally clustered.

BRIN indexes may not speed up queries well if data is random or scattered.

Summary

BRIN indexes are great for very large, ordered tables to speed up range queries.

They use less space by summarizing data in block ranges instead of indexing every row.

Use BRIN when your data is mostly sequential and you want efficient, lightweight indexing.