DeepSQL's benchmark finds BRIN's 5% update cliff in Postgres

Why it matters

Teams using BRIN on range-queried, frequently updated tables need plan-level monitoring because index size and global correlation can conceal a sharp loss of pruning.

DeepSQL's Venkat Sakamuri has published a benchmark showing how quickly a compact PostgreSQL BRIN index can lose its block-skipping advantage when updates disturb the physical order of a table. In an article dated August 27th, Sakamuri found that cumulative updates to 5% of rows drove a 28-fold jump in lossy heap pages, even as PostgreSQL reported a correlation of 0.921.

Sakamuri came to the test with unusual founder-market fit for a fight over database page ranges. He spent seven years in Oracle R&D and says he co-owned the database maker's Zonemaps module, which uses minimum and maximum values to determine which blocks a query can skip. He is also co-founder and CEO of Y Combinator-backed Stayflexi, a hotel software vendor, and a computer science graduate of Carnegie Mellon University.

That storage-engine background is central to the argument Sakamuri is making for DeepSQL. DeepSQL is an open-source, self-hosted database agent for PostgreSQL and MySQL that analyzes slow queries, execution plans and schemas, then recommends indexes and drafts SQL. The August 27th post doubles as a demonstration of the judgment DeepSQL is trying to encode: an index can look healthy in the catalog while performing far more work than its operator realizes.

A 48 kB index meets an untidy heap

Sakamuri loaded 10 million rows covering 90 days into PostgreSQL 17.9, inserting them in timestamp order. The resulting heap occupied 976 MB. He created a BRIN index on created_at with pages_per_range=128, then used a one-day query that returned 111,112 rows.

On the fresh table, the BRIN index occupied 48 kB. An equivalent B-tree occupied 214 MB, or 4,570 times as much space. The BRIN query ran in 21.2 milliseconds and touched 1,536 heap pages.

That result illustrates the appeal described in PostgreSQL's BRIN documentation. BRIN stores summaries for consecutive physical block ranges rather than one index entry for each row. If the values in a range cannot satisfy a query, PostgreSQL skips the range. The trade-off is lossiness: PostgreSQL must fetch and recheck every tuple in any range whose summary could match.

Sakamuri then updated an unindexed status column across increasing portions of the table and ran VACUUM (ANALYZE) before each measurement. At 1% cumulative churn, the query touched 1,806 lossy heap pages and ran in 24.2 milliseconds. At 5%, it touched 51,268 pages and discarded 3,827,572 rows during recheck to return the same 111,112 matching rows.

The post's summary table reports 558.7 milliseconds for the 5% run. A captured execution plan from that session reports 691.5 milliseconds. The measurements differ, but both show the same operational failure: most of the query's work had shifted from skipping irrelevant storage to fetching and rejecting rows.

At 20% cumulative churn, the summary result reached 63,923 lossy pages and 690.6 milliseconds. The index itself had not become large. Its summaries had become too broad to eliminate much of the heap.

The global statistic misses local disorder

BRIN's weakness in this test comes from how PostgreSQL updates rows. An update creates a new tuple version. When the original page lacks room, PostgreSQL can place that version elsewhere in the heap. A January timestamp moved into a page range dominated by March data forces that range's minimum and maximum to stretch across both months.

A relatively small number of relocated rows can contaminate many ranges. Once a range spans the query's date boundary, PostgreSQL reads the whole range and filters the false positives afterward.

The usual correlation statistic did not expose the damage. At 5% churn, pg_stats.correlation still stood at 0.921. Correlation measures broad ordering between a column and physical position. BRIN pruning depends on the extremes inside each individual page range, where a few outliers can do disproportionate harm.

"If you monitor one number, monitor the lossy block count of the plan, not the correlation," Sakamuri wrote.

That recommendation is the most useful part of the test. A BRIN index can retain the same size, catalog entry and plan shape while the amount of heap work underneath it rises sharply. Operators watching index size or global correlation alone can miss the deterioration.

A benchmark with deliberately favorable conditions

Sakamuri's result establishes a failure mode, rather than a universal rule that every BRIN index breaks at exactly 5% churn. The benchmark used one ordered dataset, one date-range predicate and 128 pages per summarized range. It ran in a single container with no competing load, fsync disabled and autovacuum turned off. The harness also disabled sequential scans while taking measurements, forcing PostgreSQL to use the index so the test could isolate its behavior.

Those choices make the experiment reproducible and easier to interpret. They also mean the latency figures should not be treated as production forecasts. Workload shape, tuple width, free-space-map state, update distribution and the chosen pages_per_range can move the crossover point.

The experiment shows that modest, distributed row movement can ruin BRIN's pruning well before a high global correlation figure looks alarming. It does not establish 5% as a database-wide constant.

Restoring order has a bill attached

Sakamuri restored physical ordering with CLUSTER after the 20% churn test. Correlation returned to 1.000, lossy pages fell from 63,923 to 1,536 and execution time dropped from 690.6 milliseconds to 23 milliseconds. The heap also contracted from 1,132 MB to 987 MB.

CLUSTER rewrites the table and takes an ACCESS EXCLUSIVE lock. Production teams may instead use an online repacking tool, which requires temporary storage and generates additional write-ahead log traffic. Either route turns BRIN's tiny storage footprint into a maintenance decision governed by update volume.

Append-only event logs, metrics and audit tables remain the clean case for BRIN. Their physical ordering can persist because old rows rarely move. Tables that combine range queries with in-place updates carry the risk Sakamuri measured.

For DeepSQL, the post is a technically grounded way to introduce the problem its product wants to automate. The open-source repository packages a database agent, CLI and MCP server that can run inside a customer's VPC and connect to Claude, Codex or Cursor. DeepSQL says the agent analyzes workload history and execution plans before recommending indexes.

The benchmark gives that pitch a concrete target. Choosing a tiny index is easy. Detecting when its assumptions have expired is the harder operational job.