Database Indexes Visualized: B-Trees, Covering Indexes, and Planner Decisions
An index is a shortcut to sorted access, and the planner decides when the shortcut pays off. We build a B-tree index from leaf pages up, then walk the exact decisions the planner makes: when an index speeds up a lookup, when it is cheaper to scan the whole table, and when an index actually reads more pages than a scan would. We cover hash indexes, covering indexes, composite key order, and the classic mistakes — indexing low-cardinality columns, or expecting an index to fix a query shaped to avoid it. Every claim is backed by a look at actual page reads from a real engine.
Topics covered:
- B-tree structure and the leaf chain
- Index scan vs. seq scan decisions
- Covering and composite indexes
- When indexes hurt: write amplification and bloat
Related articles
Why Databases Use B-Trees (and Not Hash Tables)
Indexes are shaped by the physics of disk and memory. What a B-tree is, why hash indexes can't replace it, and what the shape of the tree says about your database.
B-Tree Indexing: Why It Wins
B-tree vs hash indexes, covering indexes, index-only scans, and why balanced trees dominate database storage engines.
The Query Planner Cost Model
How PostgreSQL's optimizer picks execution plans, why EXPLAIN ANALYZE lies, and what cost parameters actually control.
More in Databases
B-Trees: The Shape of Databases
Why every major database is a tree shaped like a disk page — and how to read your index's health from its shape.
WatchPostgres Internals Tour: Processes, Buffer Pool, WAL, and MVCC
A guided tour of PostgreSQL internals — process model, buffer manager, WAL, and MVCC — the mechanisms that make Postgres behave the way it does.
DetailsSQL Joins and Execution Plans: Nested Loop, Hash, and Merge
How the database executes joins — nested loop, hash join, and merge join — and how to read execution plans to see which strategy your query gets.
DetailsConnection Pools, Database-Side: What a Connection Really Costs
What actually happens to your database when connections pile up — the connection lifecycle, pool sizing math, and why max_connections is not a tuning knob.
DetailsStorage Engines: LSM-Trees vs. B-Trees
LSM-trees vs. B-trees — how each storage engine writes, compacts, and reads, and what that means for write and read amplification in your workload.
DetailsSharding Strategies: Partition Keys, Distribution, and Rebalancing
How sharding actually works — partition keys, data placement, cross-shard queries, and the operational reality of splitting one database into many.
DetailsReplication Explained: WAL Shipping, Lag, and Failover
How database replication actually works — the transaction log, the lag, and the failure modes of synchronous and asynchronous replication in production.
DetailsTransactions and Isolation Levels: ACID, MVCC, and Anomalies
What transactions actually guarantee — ACID mechanics, MVCC, and the real behavior behind each isolation level, demonstrated with concrete anomalies.
DetailsQuery Optimizer Internals: From Parse Tree to Execution Plan
What happens inside a query optimizer — parse, rewrite, join ordering, cost models, and how the planner decides the plan your query gets.
DetailsDepth, delivered weekly
One technical dispatch a week — articles and episode notes before they go public.
One technical dispatch per week. No noise.