PostgreSQL BRIN Indexes for Time-Ordered Tables

On a large, append-only PostgreSQL table, the B-tree on created_at often becomes one of the biggest objects in the database, and every insert pays to maintain it. A BRIN (block range) index stores one small summary per group of pages instead of one entry per row. The catch is that it only works when the physical row order follows the column. When it does not, EXPLAIN ANALYZE shows a Bitmap Heap Scan with a large Rows Removed by Index Recheck count and far more Heap Blocks: lossy than the result needs. This guide covers how BRIN works in PostgreSQL 14 to 18, how to test a table, and how to roll it out safely.

Scope and review date: November 2026. This post covers PostgreSQL 14 to 18, with version notes where behavior changed (14 for the new operator classes, 16 for HOT updates, 17 for parallel builds, and 18 for the EXPLAIN output format). PostgreSQL 14 reaches end of life on 12 November 2026. Commands, defaults, and plan shapes were checked against the PostgreSQL documentation and the PostgreSQL 18 source on 9 November 2026. They were not run in a lab, so rehearse them on a copy of your data.

How does a BRIN index work?

The BRIN chapter of the PostgreSQL documentation splits the table into block ranges of pages_per_range physically adjacent pages. For each range, the index stores a summary, which for sortable types is the minimum and maximum value. The number of index entries is the table's page count divided by pages_per_range.

At query time, PostgreSQL compares the WHERE clause with each summary and skips ranges that cannot match. Every page in a matching range goes into a lossy bitmap, and a Bitmap Heap Scan reads those pages and rechecks every row. The index never points at individual rows.

To see the stored summaries themselves, the pageinspect function brin_page_items() lists each range's first block and its summary. It needs superuser rights, so use it on a staging copy.

Is your table a good fit for BRIN?

BRIN suits large tables where a column follows the insert order, such as event logs and metrics. The index types overview says it is most effective for columns well correlated with the physical row order. Start with the statistic PostgreSQL already keeps.

ANALYZE events;

SELECT attname, correlation, n_distinct
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'events'
  AND attname IN ('created_at', 'device_id');

Interpretation: the pg_stats documentation defines correlation as the statistical correlation between physical row order and the logical order of the column, from -1 to +1. A created_at on an insert-only table usually sits close to 1, and a random UUID near 0. A value close to -1 also works.

The planner uses the same number. In the PostgreSQL 18 source, the BRIN cost estimate (brincostestimate in selfuncs.c) divides the minimum number of ranges a query needs by the absolute correlation. A lower correlation makes BRIN look more expensive, and the planner may ignore the index you just built.

Correlation is a sampled, whole-table figure, so it can hide outliers. If a few late-arriving rows land in almost every range, correlation still looks high, but each range's minimum is pulled back to the oldest outlier. Sample the real ranges:

-- Sample about 1% of pages and show the real min/max per 128-page range
SELECT (ctid::text::point)[0]::bigint / 128 AS range_no,
       min(created_at) AS min_ts,
       max(created_at) AS max_ts,
       max(created_at) - min(created_at) AS span
FROM events TABLESAMPLE SYSTEM (1)
GROUP BY 1
ORDER BY 1
LIMIT 20;

Interpretation: the first column is the range number. The span should be roughly the time it takes to fill 1 MB of table. Spans of days on a table that fills a range in minutes mean outliers are spread through the table, which the minmax-multi operator classes handle better.

How do you create a BRIN index and choose pages_per_range?

The CREATE INDEX reference lists two BRIN storage parameters: pages_per_range, default 128, and autosummarize, default off. Build the index concurrently on a live table so writes are not blocked.

CREATE INDEX CONCURRENTLY events_brin
  ON events USING brin (created_at)
  WITH (pages_per_range = 64, autosummarize = on);

Interpretation: with no operator class named, PostgreSQL uses the default for the type, timestamptz_minmax_ops. A concurrent build takes longer and waits for running transactions, but it lets inserts continue.

The arithmetic is simple. A 100 GB table at the default 8 kB page size has 13,107,200 pages: 102,400 entries at 128, or 204,800 at 64. A smaller value gives a larger index but tighter summaries. Compare the pages your typical query needs with the range size: if an hour of data fills 300 pages, a 128-page range wastes little; if it fills 20, try 16 or 32. To estimate pages per hour, divide pg_relation_size('events') / current_setting('block_size')::int by the hours of data held.

PostgreSQL 17 added parallel BRIN builds. Workers are limited by max_parallel_maintenance_workers and need a share of maintenance_work_mem. With CONCURRENTLY, only the first table scan runs in parallel.

-- PostgreSQL 17 or later, in the session that builds the index
SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '1GB';

Interpretation: each parallel worker needs at least a 32 MB share of maintenance_work_mem, plus 32 MB for the leader, so a small setting quietly reduces the workers.

How do you read a BRIN plan in EXPLAIN ANALYZE?

BRIN is only used through bitmap scans, so the plan shows a Bitmap Index Scan under a Bitmap Heap Scan. In PostgreSQL 18, EXPLAIN ANALYZE includes buffer counts automatically, shows fractional row counts, and adds an Index Searches line, according to the PostgreSQL 18 release notes. On 14 to 17, add BUFFERS yourself.

Three lines matter. Heap Blocks: lossy=1536 is the number of table pages read from matching ranges; with BRIN, every heap block is lossy. Rows Removed by Index Recheck counts rows on those pages that failed the condition. The actual rows on the Bitmap Index Scan is not a row count at all: in the PostgreSQL 18 source, bringetbitmap() in brin.c returns the matching page count multiplied by 10. Here 1,536 pages is 12 ranges of 128.

Compare lossy= with the pages the result really needs. If an hour of data fills about 1,200 pages and the scan reads 1,536, the index is working. If it reads 40,000, the ranges overlap; see the failure modes below. For more on plan nodes, see our EXPLAIN ANALYZE guide.

BRIN or B-tree: what do you trade?

A B-tree stores one entry per row and can find one row, return rows in order, and enforce uniqueness. A BRIN stores one entry per range and can only rule ranges out, which buys a much smaller index and cheaper inserts.

Only B-tree produces sorted output and supports unique indexes. BRIN cannot do index-only scans. Since PostgreSQL 16, heap-only tuple (HOT) updates ignore summarizing indexes, and BRIN is the only summarizing index in core. On a table that updates an indexed timestamp such as updated_at, replacing that B-tree with BRIN on 16 or later lets more updates be HOT; compare n_tup_hot_upd with n_tup_upd in pg_stat_user_tables.

Which operator class: minmax, minmax-multi, or bloom?

PostgreSQL 14 added two BRIN operator classes, according to the PostgreSQL 14 release notes: minmax-multi, which records multiple minimum and maximum values per range, and bloom, which suits data that is not well localized in the heap. Both are opt-in; minmax stays the default.

Minmax-multi keeps tight intervals around clusters of values, so a few outliers no longer stretch a range across weeks. Bloom answers only equality, such as device_id = $1 on an unordered column, and its summaries are much larger than a minimum and maximum pair.

-- Outliers in an otherwise time-ordered column
CREATE INDEX CONCURRENTLY events_brin_mm
  ON events USING brin
  (created_at timestamptz_minmax_multi_ops (values_per_range = 16));

-- Equality lookups on a column with no physical order
CREATE INDEX CONCURRENTLY events_device_bloom
  ON events USING brin
  (device_id uuid_bloom_ops (false_positive_rate = 0.01));

Interpretation: build the alternative beside the existing index, compare lossy= and Rows Removed by Index Recheck for the same query, then drop the loser.

How do new rows get summarized?

The build summarizes every existing range. After that, inserts into a summarized range widen its summary straight away, but a new range at the end of the table has no summary until something creates one, and an unsummarized range is returned for every query.

Three things summarize new ranges: any VACUUM of the table (manual or autovacuum), the autosummarize storage parameter, and the maintenance functions. With autosummarize on, an insert into the first page of the next range queues a request for autovacuum to summarize the previous one. If that queue is full, the request is dropped and the server logs a line like this:

LOG:  request for BRIN range summarization for index "brin_wi_idx" page 128 was not recorded

Interpretation: this message is quoted from the documentation. The range stays unsummarized until the next vacuum or a manual call.

-- Summarize every unsummarized range; returns the number of new summaries
SELECT brin_summarize_new_values('events_brin');

-- Summarize only the range that contains heap block 1048576
SELECT brin_summarize_range('events_brin', 1048576);

-- Drop the summary for that range so it can be rebuilt tighter
SELECT brin_desummarize_range('events_brin', 1048576);
SELECT brin_summarize_range('events_brin', 1048576);

Interpretation: the index maintenance functions take the index, not the table. Only superusers and the index owner can run them, and not on a standby. Desummarizing and summarizing again is the way to tighten one range whose values changed. A VACUUM only summarizes ranges that have no summary, so it never narrows a wide one.

On an existing index, ALTER INDEX events_brin SET (autosummarize = on) works, but in the PostgreSQL 18 source it takes an ACCESS EXCLUSIVE lock, so set a short lock_timeout. To change pages_per_range, build a new index.

When does a BRIN index stop working?

BRIN fails quietly. The index stays valid and small, but summaries widen until most queries match most ranges, and lossy= climbs towards the table's page count. The usual causes:

• Out-of-order inserts. A backfill of last year's data lands at the end of the table, so the newest range's minimum drops to last year and every historical query reads it.
• Updates and deletes. A non-HOT update writes the new row version wherever there is free space. After a large DELETE and a vacuum, new rows refill old pages, and old summaries widen to include today's timestamps.
• Columns with no physical order. A random UUID or customer ID has a correlation near 0, so minmax ranges cover almost every value.
• Tiny queries. A one-row lookup still reads a whole range; on a hot path, a B-tree wins.

To repair drifted order, rewrite the table in column order. CLUSTER does that, but it must cluster on an existing index (BRIN indexes cannot be used for it), holds an ACCESS EXCLUSIVE lock that blocks reads and writes for the whole run, and is a one-time operation. pg_repack can do the same online with --order-by.

# Rewrite the table online in created_at order (pg_repack extension required)
pg_repack --dbname=app --table=public.events --order-by=created_at

Interpretation: pg_repack rebuilds the table and its indexes, so the new summaries start tight. Its documentation asks for free disk space about twice the size of the table and its indexes, and it holds an ACCESS EXCLUSIVE lock only briefly at the start and at the final swap. Our pg_repack guide covers the prerequisites. If backfills are routine, load them into their own partition instead.

How does BRIN work with partitioned tables?

Partition pruning removes whole partitions using their bounds; BRIN then skips ranges inside the partitions that remain. With daily partitions and hourly queries, pruning leaves one partition and BRIN narrows it to a few ranges. Partitioning also contains the failure modes: a backfill into last month's partition does not widen today's summaries.

CREATE INDEX on a partitioned table recurses to every partition, and future partitions get a matching index automatically. Concurrent builds are not supported on the partitioned parent, so the documented pattern is to build each partition's index concurrently and attach it:

-- 1. Create the parent index without recursing (it starts invalid)
CREATE INDEX events_brin ON ONLY events USING brin (created_at);

-- 2. Build each partition's index without blocking writes
CREATE INDEX CONCURRENTLY events_2026_11_02_brin
  ON events_2026_11_02 USING brin (created_at);

-- 3. Attach it; the parent becomes valid once every partition is attached
ALTER INDEX events_brin ATTACH PARTITION events_2026_11_02_brin;

Interpretation: repeat steps 2 and 3 for each partition. Partitions created later with CREATE TABLE ... PARTITION OF get the index automatically. The primary key stays a B-tree that must include the partition key. Our declarative partitioning and pg_partman guide covers partition creation and retention.

How do you evaluate and roll out a BRIN index?

Treat BRIN as a change to query plans, not only a disk saving: add it beside the B-tree, prove the plans, then drop the B-tree.

Frequently asked questions

Key takeaways

Review your PostgreSQL indexing with JusDB

JusDB's PostgreSQL performance tuning team can review your largest time-ordered tables, read their execution plans, and help you decide where BRIN, partitioning, or a B-tree fits, then plan a rollout that drops nothing before the plans are proven.

Bring the table definitions, sizes, top queries from pg_stat_statements, and your PostgreSQL version. Your team approves every production change.

Related reading

• PostgreSQL EXPLAIN ANALYZE: a complete guide to reading plans
• PostgreSQL declarative partitioning: range, indexes, and pg_partman automation
• pg_repack: online table and index compaction without exclusive locks
• PostgreSQL documentation: BRIN Indexes