BRIN and Hash Indexes in PostgreSQL #

B-trees answer nearly every indexing question well, which is why the other access methods get so little attention. Two of them are worth knowing precisely because they are cheap: BRIN indexes a 2 TB append-only table in a few megabytes, and hash indexes equality on long keys in less space than a B-tree — each at the cost of assumptions that, when violated, make them useless.

This page covers what each one stores, the plans they produce, the conditions they depend on, and how to decide between them and a B-tree. It complements B-tree index optimization and the GIN and GiST guidance in specialized index types.

When the Optimizer Chooses These Paths #

BRIN — Block Range INdex. Instead of one entry per row, BRIN stores a summary for each range of heap pages — by default 128 pages, 1 MB. For the default minmax operator class, the summary is the smallest and largest value in that range. A query with a range or equality condition reads the summaries, collects the page ranges whose summary could contain matches, and produces a bitmap of those pages. Every row in them is then rechecked.

That makes BRIN worthwhile when the indexed column’s values follow physical row order — an insert timestamp on an append-only table, a monotonically increasing id, a sensor reading appended per device in batches. The dependency is the correlation statistic described in index correlation and physical row order. With correlation near 1, a one-day range touches a handful of page ranges; with correlation near 0, every range’s summary spans the whole value domain and the index matches everything.

Hash indexes. A hash index stores the 32-bit hash of each value in buckets. It supports exactly one operator: =. No ordering, no range scans, no index-only scans, no unique constraints, no multicolumn indexes. Since PostgreSQL 10 they are WAL-logged and crash-safe, which made them usable in production. Their advantage over a B-tree is size when keys are long — a 200-character URL hashes to four bytes — and slightly cheaper equality lookups on such keys.

The planner treats both as ordinary access paths and costs them from statistics: BRIN from correlation and range count, hash from the equality’s estimated selectivity.

What each access method supports A table comparing B-tree, BRIN and hash. B-tree supports equality, ranges, ordering, uniqueness and index-only scans, at full size. BRIN supports equality and ranges only through page-range summaries, is tiny, and needs physical correlation. Hash supports equality only, is medium-sized, and works regardless of correlation. B-tree BRIN hash equality yes via ranges yes range scans yes yes no ORDER BY support yes no no unique constraints yes no no size full tiny medium needs correlation no yes no BRIN and hash trade capability for size

Annotated EXPLAIN Node Breakdown #

A BRIN index on an append-only events table:

CREATE INDEX events_occurred_brin ON events USING brin (occurred_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE occurred_at >= '2026-09-16' AND occurred_at < '2026-09-17';
Aggregate  (actual time=812.4..812.4 rows=1 loops=1)
  ->  Bitmap Heap Scan on events  (actual time=2.1..742.6 rows=8412004 loops=1)
        Recheck Cond: ((occurred_at >= '2026-09-16'::date) AND (occurred_at < '2026-09-17'::date))
        Rows Removed by Index Recheck: 402118
        Heap Blocks: lossy=68224
        Buffers: shared hit=2104 read=66120
        ->  Bitmap Index Scan on events_occurred_brin  (actual time=2.0..2.0 rows=682240 loops=1)
              Index Cond: ((occurred_at >= '2026-09-16'::date) AND (occurred_at < '2026-09-17'::date))
              Buffers: shared hit=18
Execution Time: 814.1 ms
-- index scan touched 18 pages: the whole BRIN index is 2 MB for a 1.4 TB table
-- Heap Blocks: lossy — BRIN always produces page-level bitmaps
-- 402,118 rows rechecked and rejected: the edge ranges of the day

A hash index on a long key:

CREATE INDEX pages_url_hash ON pages USING hash (url);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM pages WHERE url = 'https://example.com/a/very/long/path?with=params&more=1';
Index Scan using pages_url_hash on pages  (actual time=0.03..0.03 rows=1 loops=1)
  Index Cond: (url = 'https://example.com/a/very/long/path?with=params&more=1'::text)
  Buffers: shared hit=4
Execution Time: 0.05 ms
-- index size: 1.1 GB vs 4.8 GB for the equivalent B-tree

Breakdown of the BRIN plan:

Reading a BRIN plan Four fields are annotated. A tiny Bitmap Index Scan buffer count shows the index's size. Heap Blocks lossy shows BRIN's page-level granularity. Rows Removed by Index Recheck measures how much of each matching page range did not qualify. The heap buffers show the real cost of the query. Bitmap Index Scan … Buffers: shared hit=18 the whole index is megabytes Heap Blocks: lossy=68224 page ranges, never row pointers Rows Removed by Index Recheck: 402118 cost of range granularity Buffers: read=66120 the heap scan is the real work BRIN narrows a scan; it does not turn it into a point lookup

Algorithm Internals #

Summarization. BRIN summaries are built when the index is created, and for new data either by VACUUM, by brin_summarize_new_values(), or automatically when the index has autosummarize = on. Until a range is summarized, it is treated as possibly matching everything, so a freshly loaded table with an unsummarized tail does not benefit until it is summarized. On append-only tables, enabling autosummarize keeps the index current without waiting for vacuum.

Range size. pages_per_range (default 128) trades index size against precision: fewer pages per range means more summaries, a larger index and fewer rows rechecked. Tuning it is covered in BRIN pages_per_range tuning.

Operator classes. minmax stores the minimum and maximum. From PostgreSQL 14, minmax_multi stores several disjoint intervals per range, which survives occasional out-of-order values far better, and bloom stores a Bloom filter per range, supporting equality on columns with no ordering correlation at all. Both must be requested explicitly:

CREATE INDEX events_occurred_brin ON events USING brin (occurred_at timestamptz_minmax_multi_ops);
CREATE INDEX events_device_brin  ON events USING brin (device_id int8_bloom_ops);

Updates and deletes degrade BRIN summaries: a value written into an old page range widens that range’s summary permanently until the index is rebuilt or the range desummarized. BRIN suits append-only and append-mostly data.

Hash internals. Buckets are split as the index grows. Because only the hash is stored, the index cannot answer ordering, ranges or index-only scans, and equality is verified against the heap row. Hash indexes do not support unique constraints; enforcing uniqueness still needs a B-tree.

Index size for the same column A B-tree on occurred_at occupies 84 gigabytes. A BRIN index with the default 128 pages per range occupies 2 megabytes. A BRIN index with 8 pages per range occupies 32 megabytes. A hash index is not applicable for range queries on this column. B-tree on occurred_at 84 GB BRIN, 128 pages/range 2 MB BRIN, 8 pages/range 32 MB sizes in megabytes; BRIN is four orders of magnitude smaller

Memory, I/O and Maintenance Behaviour #

BRIN reads little and rechecks much. Its index scan is trivial; the cost is the heap pages of matching ranges plus the recheck. That makes it excellent for analytical range scans over large fractions of a table and poor for selective point lookups, where a B-tree’s precise pointers win.

BRIN write cost is minimal. Inserts do not add index entries; they only widen the summary of the range they land in, which is why ingest-heavy tables like BRIN so much. Deletes and updates do not shrink summaries.

Vacuum interaction. Summarization of new ranges happens during vacuum unless autosummarize is enabled. On tables that are never vacuumed because they are append-only, that is exactly the case where autosummarize matters — and in PostgreSQL 13+, the insert-based autovacuum threshold makes those tables get vacuumed anyway, as covered in insert-only tables and the autovacuum insert threshold.

Hash index maintenance is ordinary: entries are inserted and deleted like a B-tree’s, and bucket splits happen as the index grows. There is no ordering to maintain, so inserts are cheap, but the index still grows with row count — it is not a small-index solution the way BRIN is.

Memory is unremarkable for both: BRIN builds need little maintenance_work_mem because there is little to sort; hash builds sort by hash value and behave like B-tree builds. BRIN’s build is also unusually fast — it reads the table once and writes a few megabytes — which makes experimenting with different range sizes and operator classes cheap enough to do on production-sized copies rather than reasoning about it.

Combining BRIN with other structures #

BRIN rarely stands alone. On a time-series table, the usual arrangement is a B-tree on the primary key for point lookups, a BRIN index on the timestamp for analytical ranges, and — if the table is partitioned by time — pruning handling the coarse selection while BRIN narrows the scan inside each partition. The three do not conflict: the planner costs each path and picks per query, so an id lookup uses the B-tree, a one-day aggregate uses BRIN, and a one-month report may prune to one partition and then scan it sequentially because BRIN offers nothing over reading the whole partition.

The same layering applies to storage planning. A B-tree on a timestamp column of a multi-terabyte table can itself run to tens of gigabytes, competing for cache with the table. Replacing it with BRIN frees that cache for data pages, which often improves unrelated queries more than the indexed query itself. That indirect effect is easy to miss when comparing two plans in isolation, and it is one reason to measure cache hit ratios before and after such a change rather than only query time.

Hash indexes compose less readily, because their single capability overlaps entirely with what a B-tree already provides. They make sense as a replacement, not as an addition: keeping both a hash and a B-tree on the same column doubles write cost for no capability the B-tree lacks.

Step-by-Step Tuning Workflow #

  1. Check correlation before considering BRIN:

    SELECT attname, correlation FROM pg_stats
WHERE tablename = 'events' AND attname IN ('occurred_at', 'device_id');

    Above 0.9 is a good candidate; below 0.5 is not, unless you use the bloom operator class for equality.

  2. Create BRIN and measure the recheck ratio:

    CREATE INDEX CONCURRENTLY events_occurred_brin ON events USING brin (occurred_at) WITH (autosummarize = on);

    Compare Rows Removed by Index Recheck with rows returned; a ratio above roughly 1:1 suggests smaller ranges.

  3. Tune pages_per_range downward if rechecks dominate, accepting a larger index.

  4. Use minmax_multi when the column is mostly ordered but has occasional stragglers — late-arriving events, backfills.

  5. For hash indexes, confirm the workload is equality-only and the keys are long enough for the size saving to matter. Measure both indexes’ sizes before choosing.

  6. Never use hash for uniqueness or ordering — it cannot enforce or provide them.

  7. Re-check after bulk loads and backfills, which can break BRIN correlation. Rebuilding the index restores summaries.

Checking whether a BRIN index is earning its place #

Because BRIN indexes are so small, they are easy to add and easy to forget. Two checks keep them honest. First, pg_stat_user_indexes.idx_scan tells you whether the planner chooses the index at all; a BRIN index with no scans is either redundant against a partition scheme or unusable because correlation has decayed. Second, comparing Rows Removed by Index Recheck with rows returned, on the queries that matter, tells you whether the index is narrowing the scan or merely adding a step before reading most of the table anyway.

A BRIN index whose bitmap covers 90% of the table’s pages is worse than no index: the planner pays for the bitmap and then reads almost everything, and it may also mislead cost estimates for nodes above the scan. When the ratio reaches that point, the honest response is to drop the index and either partition the table or accept the sequential scan, whose cost the planner models accurately.

Common Pitfalls #

BRIN on an uncorrelated column. Every range matches everything. Diagnostic signal: Heap Blocks: lossy close to the table’s page count. Fix: check correlation first, or use the bloom operator class for equality.

Expecting BRIN to serve point lookups. It cannot; it returns page ranges. Diagnostic signal: single-row queries reading thousands of pages. Fix: a B-tree for selective lookups, BRIN alongside it for analytical ranges.

Unsummarized new data. The tail of an append-only table is not covered until summarized. Diagnostic signal: queries for recent rows scanning more than expected. Fix: autosummarize = on or periodic brin_summarize_new_values().

Updates scattering values. Old ranges widen and never recover. Diagnostic signal: recheck ratios worsening over time. Fix: rebuild the index, or reconsider BRIN for that table.

Hash index for a unique key. It cannot enforce uniqueness. Diagnostic signal: attempting CREATE UNIQUE INDEX … USING hash fails. Fix: B-tree for the constraint.

Hash index on short keys. The size saving disappears. Diagnostic signal: hash index no smaller than the B-tree. Fix: keep the B-tree, which also serves ranges and ordering.

Frequently Asked Questions #

When should I use a BRIN index? #

When the indexed column’s values follow the table’s physical order — typically an insert timestamp or serial id on an append-only table — and queries select ranges of it. BRIN is then thousands of times smaller than a B-tree while narrowing the scan effectively.

Why is my BRIN index not helping? #

Most often the column is not physically correlated, so every block range’s summary overlaps the queried range. Check pg_stats.correlation, and check that recent data has been summarized, either by vacuum or with autosummarize enabled.

Are hash indexes safe to use in PostgreSQL? #

Yes since PostgreSQL 10, when they became WAL-logged and crash-safe. They support only equality, cannot enforce uniqueness and cannot provide ordering, so they are useful mainly for equality lookups on long keys.

Can BRIN replace a B-tree for my time-series table? #

For range queries over large spans, often yes, at a fraction of the size. For selective lookups of individual rows, no: BRIN returns page ranges, so those queries read far more than a B-tree would. Many time-series tables keep both.

Up: Index Tuning & Strategy