Zonemaps vs. B-Trees vs. Partitioning: A Costly Guessing Game

TL;DR

• B-trees are not free. You pay for every B-tree index with write amplification, WAL overhead, and cache pressure. Adding one to speed up a single query can slow down your entire write path.
• Partitioning is a one-way door. Partition pruning is the fastest way to cull data, but choosing the wrong partition key on a large table is an irreversible, multi-terabyte mistake that you can't git revert.
• BRIN indexes (Postgres's Zonemaps) are specialists. They offer near-zero write overhead and a tiny footprint, but only work on physically correlated data (like append-only created_at columns). Using them on UUIDs is pointless.

---

If you ask a junior engineer to speed up a query, they'll add a B-tree index. If you ask a mid-level engineer, they might suggest a composite B-tree index. This isn't a knock on them; it's what every textbook teaches. It's also how you end up with a 5TB table that has ten indexes, an INSERT latency of 800ms, and an autovacuum that can never keep up.

At the database layer, you don't get to undo architectural mistakes easily. A bad code deploy gets rolled back. A bad schema choice is a legacy you carry for years. Choosing the right data access structure—a B-tree, a partition key, or a BRIN index—is one of those foundational decisions. Guessing is not a strategy.

Let's cut through the theory and look at the real costs using a common scenario: a 2TB events table with queries that filter on a created_at range.

The Default Play: B-Tree Everything

The knee-jerk reaction is to add CREATE INDEX ON events (created_at). A query like SELECT * FROM events WHERE created_at BETWEEN '2023-10-01' AND '2023-10-02' gets a fast plan.

-- Cost: Low execution time, for this one query.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00';

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=47517.37..47517.38 rows=1 width=8) (actual time=234.120..234.121 rows=1 loops=1)
   Buffers: shared hit=42106
   ->  Index Only Scan using events_created_at_idx on events  (cost=0.56..47397.35 rows=48007 width=0) (actual time=0.046..220.581 rows=51840 loops=1)
         Index Cond: (created_at >= '2023-10-01 00:00:00'::timestamp AND created_at < '2023-10-02 00:00:00'::timestamp)
         Heap Fetches: 0
         Buffers: shared hit=42106
 Planning Time: 0.158 ms
 Execution Time: 234.176 ms

Looks great. Planning time is sub-millisecond, execution is 234ms. We ship it. Two quarters later, the alarms are firing. What happened?

1. Write Amplification: Every single INSERT into events now performs at least two writes: one to the table heap and one to the events_created_at_idx B-tree. Have five indexes? That's six writes. This isn't just disk I/O; it's a massive increase in WAL generation, which impacts replication and recovery time.
2. Cache Inefficiency: That multi-gigabyte index is now fighting for space in your shared_buffers with the table's actual data. If the index is used infrequently, you're paying a constant cache penalty for a rare benefit.
3. Bloat & Maintenance: If you UPDATE a row and change an indexed column, Postgres can't perform a Heap-Only Tuple (HOT) update. It has to create a new row version and update every single index. This is a primary driver of table and index bloat, which puts even more pressure on autovacuum.

Your index advisor won't tell you this. It sees a slow query and recommends an index. It doesn't model the global cost to the system—the increased write latency, the WAL traffic, the bloat.

The Big Hammer: Partitioning

For time-series data, partitioning is king. CREATE TABLE events PARTITION OF events_parent FOR VALUES FROM (...) TO (...). The planner doesn't need to consult an index to know that data from 2022 isn't in the 2023 partition. It just ignores it entirely. This is called partition pruning, and it's absurdly efficient.

The plan for our range query now shows the planner zapping entire partitions before execution even begins. The number of rows it has to consider drops by an order of magnitude. Dropping old data is an O(1) metadata operation (DROP TABLE old_partition), not a DELETE statement that scans billions of rows.

But here lies the trap: the partition key is forever.

If you partition events by created_at but then your product team ships a feature that requires querying by tenant_id, you're cooked. The planner can't prune partitions, so it has to scan every single one. You've traded one performance problem for another, but this new one is structural.

Re-partitioning a 2TB table is not a casual task. It's a months-long migration project requiring shadow tables, dual writes, and a terrifying cutover window. Get the partition key wrong, and you've just created years of technical debt.

The Specialist's Tool: BRIN (Zonemaps)

This takes me back to my time on the Oracle query engine team. We owned Zonemaps, which were created precisely to solve the B-tree write amplification problem on large, ordered fact tables. A BRIN index in Postgres is the direct equivalent.

Unlike a B-tree, which maps a value to a specific row location, a BRIN index is metadata. It stores the minimum and maximum value for a large range of physical table blocks (by default, 128 blocks in Postgres). A BRIN on created_at for our events table would store entries like (Block Range 0-127: min='2023-01-01', max='2023-01-03').

When you query a range, the planner scans the tiny BRIN index, determines which block ranges could contain your data, and then only scans those heap blocks. The key benefits are:

• Tiny Footprint: A BRIN index can be hundreds of times smaller than a B-tree on the same data.
• Near-Zero Write Cost: An INSERT doesn't touch the BRIN. The index is only updated when enough new blocks have been populated, or lazily via VACUUM.

But it's a specialist. If your data isn't physically correlated, it's useless. A BRIN on a user_id column where inserts are happening for all users simultaneously would have a min and max so wide for every block range that the planner would get no benefit. It would be forced to scan the whole table.

Combining list partitioning on tenant_id with a BRIN index on created_at is a potent combination for SaaS-style multi-tenant tables. The partitioning isolates tenant data, while the BRIN efficiently handles time-range queries within that data, all without the write penalty of a B-tree.

The Real Liability

You cannot just try out a partitioning scheme. By the time you realize it's wrong, your table is terabytes in size and serves production traffic. You can't just drop a dozen B-tree indexes that have bloated your tables without first understanding the blast radius for every query that might touch them. This isn't about one query. It's about the total cost of ownership of your data model—reads, writes, storage, and maintenance.

Making these decisions based on a single EXPLAIN plan is malpractice. It's optimizing for a tree and setting the forest on fire.

What DeepSQL does about this

DeepSQL observes the holistic workload against your database by analyzing sources like pg_stat_statements and the system catalog. It doesn't just look for slow queries; it models the trade-off of every potential optimization across a workload's entire read/write/update mix. It simulates the second-order effects: how adding a B-tree impacts INSERT latency and WAL volume, or how a BRIN index would behave given the physical clustering of your data. The platform will recommend dropping three indexes and adding one partition key not because it makes one query faster, but because it lowers the total system I/O and latency by a calculated amount.