The Zonemap-Shaped Hole in Postgres

TL;DR

• Oracle Zonemaps let you skip massive chunks of data on table scans by storing min/max values per storage region. Postgres has no direct equivalent.
• The popular replacement, BRIN indexes, are often useless. They only work if your data is physically sorted on disk, which Postgres does not guarantee after the first INSERT.
• The real toolkit is partitioning (a coarse-grained zonemap), covering indexes, and for serious analytics, columnar extensions. Each is a heavy commitment with irreversible consequences if you get it wrong.

---

At Oracle, I owned the Zonemaps module for the query engine. A Zonemap is a beautiful, simple thing. For a given range of database blocks, it stores the minimum and maximum value for a column. When a query comes in with a WHERE clause, like WHERE creation_date > '2024-05-01', the engine first checks the zonemap. If the MAX(creation_date) for a 1MB region of blocks is '2024-04-30', the engine knows it can skip reading all 128 of those 8KB blocks. No I/O, no buffer cache churn. It's an incredibly efficient I/O elimination device that lives outside the complexity of a B-tree index.

It was my job to manage the trade-offs: the storage overhead, the cost of keeping them fresh, how they interacted with direct path loads. And it taught me one thing: for large tables, the game is won or lost by avoiding I/O.

When I moved deep into Postgres, the first thing I looked for was the zonemap. It doesn't exist. Not in the same way. What you have instead is a collection of tools that approximate the same goal, each with its own sharp edges.

The Seductive Lie of BRIN Indexes

Postgres users seeking to speed up scans on large tables are pointed to BRIN (Block Range Index). The documentation sounds perfect: BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table. A BRIN index divides the table's pages into ranges and stores a small summary for each range—the min/max values, just like a zonemap.

The key phrase is natural correlation with their physical location. What does that mean?

It means that if you SELECT * FROM events ORDER BY event_time, the rows should stream off the disk in roughly that order. If row 1 has event_time 'Monday' and row 1,000,000 has event_time 'Friday', the rows in between should have times between Monday and Friday.

This is true exactly once: when you first bulk-load the data into an empty table. After that, chaos. An INSERT goes into the first page with free space. An UPDATE that bloats a row might move it to a completely different page. DELETEs and VACUUM leave holes that get filled by unrelated data. Your table's physical layout, once pristine, quickly decays into a scattered mess.

You can see this for yourself. pg_stats has a correlation column that measures this physical-to-logical ordering. A value of 1.0 or -1.0 is perfect correlation. 0.0 is pure chaos. A BRIN index on a column with correlation near zero is worse than useless—it consumes storage, costs CPU and WAL overhead on INSERT/UPDATE, and gives the query planner nothing.

Let's say you have a 2TB events table with a BRIN index on created_at. The data has been churned for a year. The min(created_at) and max(created_at) for almost every single block range in the BRIN summary will likely be 2022-01-01 and 2024-01-01. When you query for last week's data, the BRIN index tells the planner, "Well, the data could be in any of these 100,000 block ranges." The planner then correctly decides a full sequential scan is faster.

How Pruning Fails: Lossy Scans and Bad Plans

Even when BRIN works on well-correlated data, it's a "lossy" index. A B-tree gives the planner an exact tuple ID (TID): (page, item). Go to this page, get this specific row. A BRIN index gives the planner a bitmap of pages that might contain matching rows. The database still has to visit every page in that bitmap, load every single row from those pages into memory, and re-check the WHERE clause condition.

Here’s a query plan for a 500M row table where BRIN on created_at (with high correlation) is helping, but still has to do recheck work:

EXPLAIN ANALYZE SELECT count(*) FROM events WHERE created_at > '2024-06-01';

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19996.14..19996.15 rows=1 width=8) (actual time=281.481..281.482 rows=1 loops=1)
   ->  Bitmap Heap Scan on events  (cost=498.05..19972.18 rows=9585 width=0) (actual time=97.973..279.141 rows=100512 loops=1)
         Recheck Cond: (created_at > '2024-06-01'::timestamp)
         Rows Removed by Index Recheck: 1240
         Heap Blocks: exact=1024 lossy=3072
         ->  Bitmap Index Scan on events_created_at_brin_idx  (cost=0.00..495.65 rows=9585 width=0) (actual time=96.195..96.195 rows=4096 loops=1)
               Index Cond: (created_at > '2024-06-01'::timestamp)
 Planning Time: 0.158 ms
 Execution Time: 281.562 ms

See those Heap Blocks: exact=1024 lossy=3072? The index identified 1024 pages that definitely match and 3072 pages that might match. The engine had to scan all 4096 of those 8KB blocks (32MB of I/O) and re-check the filter. It's better than a full table scan that might read 100GB, but it's not the surgical precision of a B-tree or the clean block skipping of a true zonemap.

The Real I/O Avoidance Toolkit

So if BRIN is a gamble, what actually works?

1. Partitioning: This is the real answer for I/O avoidance on large, growing tables. A table partitioned by RANGE (created_at) is, effectively, a coarse-grained, manually managed zonemap. Each partition is a separate sub-table, and when you filter by created_at, the planner uses "partition pruning" to ignore all partitions whose boundaries don't overlap with your WHERE clause. This is a perfect, non-lossy block skipping mechanism. The planner doesn't even consider scanning those partitions. But this is a monumental architectural decision. Choosing the wrong partition key, or choosing a UUID as a primary key that makes partitioning impossible, are classic examples of creating irreversible database liability. You cannot easily ALTER a 5TB table to change its partition key without days or weeks of downtime and careful data migration.

2. CLUSTER and pg_repack: To make BRIN viable, you can force physical correlation by rewriting the whole table. CLUSTER does this, but it takes an exclusive lock, meaning total downtime. pg_repack can do it online, but it's a heavy operation that temporarily doubles your storage use and generates significant WAL. This isn't a strategy; it's a periodic, high-effort cleanup task. You are fighting entropy, and you will eventually lose.

3. Covering Indexes (Index-Only Scans): If all the columns in your SELECT, WHERE, and JOIN clauses are present in a single B-tree index, Postgres can perform an Index-Only Scan. It never needs to touch the table heap at all, completely avoiding the main table's I/O. This is incredibly powerful but comes at the cost of B-tree write amplification (every INSERT/UPDATE now pays the price) and index bloat. This is a scalpel, not a chainsaw.

4. Columnar Storage: Projects like Citus columnar storage tables, Hydra, or pg_mooncake bring true zonemaps to Postgres for analytical workloads. They store data by column, not by row, and for each chunk of column data, they store min/max statistics. This is exactly how Oracle Exadata's Storage Indexes work, and it's devastatingly effective for data warehousing. But it means moving your data into a different storage format, which is another significant architectural leap.

Choosing between these isn't an academic exercise. Picking a B-tree when a BRIN would do adds write latency to every transaction. Adding a BRIN index that does nothing is just dead weight. Committing to a partitioning scheme you can't change is a multi-year mistake. This is where prevention is the only cure.

What DeepSQL does about this

Instead of guessing, you should measure. DeepSQL automatically cross-references query patterns from pg_stat_statements with physical table statistics from pg_stats. It identifies your most heavily filtered columns and then checks their physical data correlation. This allows it to make precise, data-driven recommendations: if a column is frequently filtered and has a correlation > 0.8, we recommend a BRIN index. If it has low correlation but is a time-series key, we model the impact of partitioning. It also flags existing BRIN indexes on columns with near-zero correlation as dead weight that should be dropped, reducing maintenance overhead and freeing the planner to make better choices.