待翻译：BRIN is 1/4570th the size of a B-tree, until 5% of rows are updated

AI 服务暂时不可用，以下为来源正文，待恢复后补全翻译。

All posts Oracle vs PostgresAug 27, 202611 min read BRIN is 1/4570th the size of a B-tree, until 5% of rows are updated I co-owned the Zonemaps module at Oracle, and BRIN is the same design in Postgres. It stops pruning long before pg_stats.correlation admits anything is wrong — at correlation 0.921, a 28x collapse. Measured on 10M rows, harness inline. VS Venkat Sakamuri DeepSQL R&D · Ex Oracle Query Engine Team · YC & CMU 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: BRINB-tree Index size48 kB214 MB Probe execution time21.2 ms— Heap pages touched1,536— 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: Statepg_stats correlationHeap pages (lossy)Rows removed by recheckExecution time Fresh1.0001,53611,78121.2 ms 1% rows updated0.9791,80632,24324.2 ms 5% rows updated0.92151,2683,827,572558.7 ms 20% rows updated0.78263,9234,216,606690.6 ms 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 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 = timestamptz ''2026-02-15 00:00:00+00'' AND created_at 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: 17.9 (this post)16.13 (re-run) Heap size at load976 MB976 MB B-tree size214 MB214 MB Fresh — heap pages1,5361,536 Fresh — rows removed by recheck11,78111,781 1% churn — heap pages1,8061,806 5% churn — heap pages51,26852,036 20% churn — heap pages63,923114,490 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.