At Oracle I co-owned the Zonemaps module in the query engine and contributed to its core. A zonemap is a small, unglamorous structure: for a contiguous range of blocks, store the min and max of a column. When a predicate arrives, compare it against each zone's range and skip the blocks that cannot match. No tree, no per-row entries, no maintenance proportional to row count. For large tables the game is won by not reading blocks, and a zonemap is close to the cheapest way to not read a block.

Postgres has the same idea in BRIN: page ranges, min/max summaries per range, a tiny index. The design is not a lesser copy — in one important way it is cheaper than what Oracle built. But it depends on a property Postgres never promises, and this post measures what happens when that property erodes.

Everything below was produced by a script you can run yourself. There is no repository to clone — the harness is reproduced in full at the end of this post. Conditions: PostgreSQL 17.9 on x86_64, shared_buffers=256MB, work_mem=32MB, fsync=off, autovacuum=off, single container, no other load. 10,000,000 rows spanning 90 days, inserted in timestamp order — a 976 MB heap. BRIN on created_at with pages_per_range=128. The probe is a one-day range: 111,112 rows out of ten million.

The fresh case: BRIN is remarkable

On the freshly loaded, physically ordered heap:

48 kilobytes. The equivalent B-tree is 224,641,024 bytes — 4,570 times larger. That ratio is the whole argument for BRIN, and it is not marketing: an index that fits in L2 cache costs almost nothing to keep, almost nothing to write to, and almost nothing to read.

Churn, measured

Then I updated a growing fraction of rows — status = status || '*', an update to an unindexed column, the most benign kind — and ran VACUUM (ANALYZE) before each measurement. Cumulative churn, same probe every time:

The interesting row is the third one. Between 1% and 5% churn the index goes from touching 1,806 pages to touching 51,268 — a 28-fold increase in I/O for an identical query returning identical rows — and execution time goes up 23x. The heap grew by only 3%.

Here is the real plan at 5% churn, from that session:

 Aggregate  (cost=134586.09..134586.10 rows=1 width=40) (actual time=691.472..691.474 rows=1 loops=1)
   Buffers: shared hit=75 read=51201 written=30905
   ->  Bitmap Heap Scan on events  (cost=49.89..134075.84 rows=102049 width=6) (actual time=1.701..680.083 rows=111112 loops=1)
         Recheck Cond: ((created_at >= '2026-02-15 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-02-16 00:00:00+00'::timestamp with time zone))
         Rows Removed by Index Recheck: 3827572
         Heap Blocks: lossy=51268
         Buffers: shared hit=75 read=51201 written=30905
         ->  Bitmap Index Scan on events_brin  (cost=0.00..24.38 rows=117557 width=0) (actual time=1.198..1.199 rows=512680 loops=1)
               Index Cond: ((created_at >= '2026-02-15 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-02-16 00:00:00+00'::timestamp with time zone))
 Planning Time: 0.201 ms
 Execution Time: 691.503 ms

3.8 million rows fetched and thrown away to return 111,112. That is the lossy recheck doing all the work the index was supposed to avoid.

Why 5% churn is a cliff and not a slope

Because a page range is summarized by its extremes. An UPDATE in Postgres writes a new tuple version, and when the old page has no room the new version lands wherever there is free space — typically at the end of the heap, and later in the recycled holes VACUUM left behind. Each relocated row carries its original created_at into whatever page range it lands in, and that range's summary widens to cover it.

One row from January landing in a range full of March rows makes the entire 128-page range match every January predicate forever. It does not take many misplaced rows to widen most ranges to span most of the time domain, and once a range's summary spans your predicate, the whole range is read. The transition is sharp because ranges are wide: it is not "5% of rows moved so 5% more pages are read", it is "5% of rows moved and they are spread across most of the ranges."

This is also the point where I would stop trusting pg_stats.correlation as the health signal for a BRIN index. At 5% churn the correlation is still 0.921 — a number most people would read as "fine, well correlated" — while pruning has already collapsed by a factor of 28. Correlation measures the global rank ordering of the column against physical position. BRIN cares about per-range extremes, and a small number of outliers barely moves the former while destroying the latter. If you monitor one number, monitor the lossy block count of the plan, not the correlation.

The fix, and what it costs

Physical order can be restored. CLUSTER events USING events_btree on the 20%-churned table, followed by ANALYZE:

Full recovery. But CLUSTER takes an ACCESS EXCLUSIVE lock for the duration of a full table rewrite, which on a real table means pg_repack and a temporary doubling of storage plus the WAL to match. You are buying back pruning with a maintenance window, on a schedule set by your write rate — which is a real answer, just not a free one.

Which design is better, honestly

Where Postgres wins: BRIN is 48 kB against a B-tree's 214 MB, and it needs no dictionary, no refresh job, and no separate metadata to keep consistent. Oracle's zonemaps are a materialized object with a refresh model you have to reason about — REFRESH ON COMMIT, staleness states, interactions with direct-path loads. BRIN has none of that machinery, and for append-mostly tables it delivers most of the same benefit for a rounding error of storage.

Where Oracle wins: staleness is explicit. A zonemap knows it is stale and the optimizer knows it too. A BRIN index that has silently stopped pruning looks exactly like one that is working — same size, same catalog entry, same plan shape. The failure is invisible in every place you would look except the plan's block counts. And Oracle's model was built alongside a storage engine where physical order is a thing you can maintain, rather than something entropy takes back after the first UPDATE.

Where it does not matter: if your table is genuinely append-only — event logs, metrics, audit trails, anything where rows are never updated after insert — none of the above applies. Correlation stays at 1.0, the index stays at 48 kB, and BRIN is the best deal in the Postgres index catalog. On my 10M-row fresh load it read 1,536 pages instead of the 124,000-page sequential scan alternative, for an index you could email to someone.

The failure mode is specific and worth naming precisely: BRIN degrades on tables that are range-queried and also updated in place. Not "large tables". Not "tables with low correlation". That specific combination — and it degrades faster than any single statistic in pg_stats will tell you.

Rerun it

There is no repository to clone. The whole harness is below: it builds its own table, runs its own churn, and prints every number in this post. About three minutes and 2 GB of disk.

Run it against a throwaway cluster, not a database you care about:

initdb -D /tmp/pgdata -U pg
pg_ctl -D /tmp/pgdata -l /tmp/pg.log -o "-p 55432 -k /tmp -c fsync=off \
  -c shared_buffers=256MB -c work_mem=32MB -c max_wal_size=4GB -c autovacuum=off" start
psql -h /tmp -p 55432 -U pg -d postgres -f brin-churn.sql

\set ON_ERROR_STOP on

DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS results;

CREATE TABLE events (
  id         bigint,
  created_at timestamptz   not null,
  status     text          not null,
  amount     numeric(10,2) not null,
  note       text
);

-- 10M rows over 90 days, inserted in timestamp order: the append-only shape
-- BRIN is documented to be good at.
INSERT INTO events
SELECT g,
       timestamptz '2026-01-01 00:00:00+00' + (g * interval '0.7776 seconds'),
       (ARRAY['new','paid','shipped','refunded'])[1 + (g % 4)],
       (g % 10000)::numeric / 100,
       repeat('x', 40)
FROM generate_series(1, 10000000) g;

VACUUM ANALYZE events;

CREATE TABLE results (step text, metric text, value text);
CREATE OR REPLACE FUNCTION m(_step text, _metric text, _value text) RETURNS void
LANGUAGE sql AS $fn$ INSERT INTO results VALUES (_step,_metric,_value) $fn$;

-- One day out of ninety: 111,112 rows of 10,000,000.
CREATE OR REPLACE FUNCTION probe(_step text) RETURNS void
LANGUAGE plpgsql AS $fn$
DECLARE j json; plan json;
BEGIN
  FOR i IN 1..2 LOOP   -- warm, then measure
    EXECUTE 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
             SELECT count(*), sum(amount) FROM events
             WHERE created_at >= timestamptz ''2026-02-15 00:00:00+00''
               AND created_at <  timestamptz ''2026-02-16 00:00:00+00''' INTO j;
  END LOOP;
  plan := j->0->'Plan'->'Plans'->0;
  PERFORM m(_step,'exec_ms',        j->0->>'Execution Time');
  PERFORM m(_step,'lossy_blocks',   plan->>'Lossy Heap Blocks');
  PERFORM m(_step,'exact_blocks',   coalesce(plan->>'Exact Heap Blocks','0'));
  PERFORM m(_step,'recheck_removed',plan->>'Rows Removed by Index Recheck');
END $fn$;

CREATE OR REPLACE FUNCTION snap(_step text) RETURNS void
LANGUAGE plpgsql AS $fn$
BEGIN
  PERFORM m(_step,'table_bytes', pg_relation_size('events')::text);
  PERFORM m(_step,'brin_bytes',  pg_relation_size('events_brin')::text);
  PERFORM m(_step,'correlation',
    (SELECT correlation::text FROM pg_stats WHERE tablename='events' AND attname='created_at'));
  SET enable_seqscan = off;   -- we are measuring the index, not the planner's choice
  PERFORM probe(_step);
  RESET enable_seqscan;
END $fn$;

-- fresh load
CREATE INDEX events_brin ON events USING brin (created_at) WITH (pages_per_range = 128);
ANALYZE events;
SELECT snap('fresh');

CREATE INDEX events_btree ON events USING btree (created_at);
SELECT m('fresh','btree_bytes', pg_relation_size('events_btree')::text);
DROP INDEX events_btree;

-- cumulative churn: 1%, then 5%, then 20% of rows updated
UPDATE events SET status = status || '*' WHERE id % 100 < 1  AND status NOT LIKE '%*';
VACUUM (ANALYZE) events;  SELECT snap('churn_1');

UPDATE events SET status = status || '*' WHERE id % 100 < 5  AND status NOT LIKE '%*';
VACUUM (ANALYZE) events;  SELECT snap('churn_5');

UPDATE events SET status = status || '*' WHERE id % 100 < 20 AND status NOT LIKE '%*';
VACUUM (ANALYZE) events;  SELECT snap('churn_20');

-- repack and re-measure
CREATE INDEX events_btree ON events USING btree (created_at);
ANALYZE events;
SELECT m('churn_20','btree_bytes', pg_relation_size('events_btree')::text);
CLUSTER events USING events_btree;
ANALYZE events;
DROP INDEX events_btree;
SELECT snap('after_repack');

SELECT step, metric, value FROM results ORDER BY
  array_position(ARRAY['fresh','churn_1','churn_5','churn_20','after_repack'], step), metric;

What reproduces, and what does not

Re-running the same script on PostgreSQL 16.13 instead of 17.9, on different hardware, separates the properties of BRIN from the properties of my machine:

The load is deterministic, so everything up to the first UPDATE matches exactly — same page counts, same recheck rows, to the digit. The 5% figure lands within 1.5%.

The 20% figure does not reproduce. It nearly doubled, and that is worth stating plainly rather than leaving for someone else to find. Where an updated row's new version lands depends on free-space-map state and on when vacuum last ran, and both differ across versions and across runs. So: the cliff between 1% and 5% churn is a property of BRIN and it is robust. The exact depth of the hole past the cliff is a property of the run, and it is not.

If yours produces a different crossover point, that is a more interesting result than mine, and I would like to see it.