What Is a BRIN Block Range Indexes in PostgreSQL?
BRIN stores summaries for consecutive physical block ranges, often minimum and maximum values. It skips ranges that cannot match and scans candidate pages.
How the BRIN Block Range Indexes Algorithm Works
BRIN stores summaries for consecutive physical block ranges, often minimum and maximum values. It skips ranges that cannot match and scans candidate pages.
When to Use BRIN Block Range Indexes
Very large append-oriented tables whose values correlate with physical order, such as event timestamps.
How to Create a PostgreSQL BRIN Block Range Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX events_created_brin_idx ON events USING brin (created_at) WITH (pages_per_range = 64);BRIN Block Range Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT count(*) FROM events WHERE created_at >= DATE '2026-03-01' AND created_at < DATE '2026-03-02';Queries Supported by BRIN Block Range Indexes
Very large append-oriented tables whose values correlate with physical order, such as event timestamps. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Compact but lossy: candidates need heap rechecks. Poor physical correlation reduces effectiveness. Indexes reduce some search work but require storage and add maintenance to writes.
BRIN Block Range Indexes Limitations and Common Mistakes
BRIN is not a precise row lookup and may not help randomly distributed values.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune BRIN Block Range Indexes
Inspect physical correlation, pages removed by recheck, index size, and pages_per_range.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
BRIN Block Range Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official PostgreSQL Index Documentation
Check PostgreSQL documentation: Index Types for release-specific requirements and supported operators.