大規模なパーティションテーブル向けBRINインデックス

On tables with billions of rows, a B-tree index can grow huge and consume a lot of memory. BRIN (Block Range Index) offers a tiny alternative for naturally ordered data.

BRIN stores the min and max value per block range instead of one entry per row. A query checks which ranges could contain matching rows and scans only those blocks.

BRIN shines when the column's physical order correlates with its value order, such as an inserted_at timestamp on an append-only table. Random ordering makes BRIN nearly useless.

You declare it with USING brin. It is dramatically smaller than a B-tree on the same column.

The pages_per_range storage parameter controls granularity. Smaller values give more precise (but larger) indexes.

A B-tree might be many gigabytes on a billion-row table; the equivalent BRIN can be just a few megabytes. The trade-off is BRIN does coarser filtering.

On a range-partitioned table, create the BRIN on the parent; it propagates to each partition. Within a partition the data is usually well-correlated, so BRIN works great.

New blocks must be summarized for BRIN to cover them. Autovacuum does this, or you can force it.

Look for a Bitmap Index Scan on the BRIN index followed by a recheck. The recheck confirms candidate blocks really contain matches.

Avoid BRIN for:

• Columns with random physical order
• Point lookups needing exact, fast access (use B-tree)
• Uniqueness enforcement (BRIN cannot)

At scale, a common pattern is: range-partition by time, BRIN on the time column for cheap range scans, and a small B-tree on a frequently filtered foreign key.

You learned BRIN indexes: tiny block-range summaries ideal for huge, well-correlated partitioned tables. Tune pages_per_range, keep summaries fresh, verify with EXPLAIN, and combine BRIN with selective B-trees.