Database Indexes Visualized: B-Trees, Pages & Why Queries Get Faster

An index is not magic attached to a column. It is another stored data structure that the database maintains so a query can find a small set of relevant pages instead of inspecting every candidate row.

The visualizer uses a small B+Tree-style teaching model: separator keys live at the root, sorted entries live in linked leaves, and an entry leads to table data. PostgreSQL's actual B-tree implementation includes page headers, high keys, sibling links, concurrency machinery, visibility rules, and many optimizations that this model does not reproduce byte-for-byte.

Start with database pages

Storage is transferred and cached in pages or blocks, not fetched as one magical row at a time. A table occupying many pages may require many page reads during a sequential scan. If pages are already in memory, that avoids physical storage I/O, but scanning and testing rows still costs CPU and memory bandwidth.

For a small table or a query returning most rows, a sequential scan can be excellent: page access is predictable and setup overhead is low. “Uses an index” is not a synonym for “fast.” The planner estimates which path is cheaper for this query and current statistics.

What an index stores

A B-tree index keeps keys ordered and associates them with pointers that ultimately identify matching table rows. Internal pages contain separator keys and child pointers. Leaf pages contain ordered index entries. Because a database page can hold many compact keys and pointers, one node can have a large branching factor.

That wide fan-out is the reason database B-trees are not merely binary search trees. A balanced binary tree branches twice per node and follows many pointers. A page-oriented B-tree branches many ways per page read, keeping its height small even for large datasets.

The asymptotic lookup is commonly described as logarithmic, but the base matters operationally. A shallow tree may need only a few page visits. Caches can make upper levels especially cheap because frequently accessed root and internal pages remain hot.

Looking up an email

Without a useful index, this predicate may inspect rows across many table pages:

SELECT *
FROM users
WHERE email = '[email protected]';

With an index on email, the conceptual path is:

root page
  -> internal page chosen by separator keys
    -> leaf entry for [email protected]
      -> matching table row/page

An index-only scan may avoid a table lookup when the index contains every value the query needs and the database can establish visibility from its metadata. That is an optimization, not the default mental model to assume for every lookup.

Insertion and page splitting

To insert a key, the database descends to the correct leaf and places the key in sorted order. If the page lacks room, it splits: entries are divided between pages and a parent separator is updated or added. A full parent may split too, potentially creating a new root.

Splits preserve balance and lookup behavior, but they create write work. Inserts and updates can touch the table plus every affected index, generate write-ahead log records, dirty cache pages, and increase storage. Random insertion patterns may split pages more often than append-friendly patterns.

This is why “index every column” is a poor rule. Each additional index accelerates some reads while taxing writes, maintenance, backups, memory, and storage.

Range queries and linked leaves

Ordered leaves make ranges natural. The tree finds the first qualifying key, then the scan walks neighboring leaf entries until the upper bound is passed:

SELECT id, created_at
FROM orders
WHERE created_at >= $1
  AND created_at < $2
ORDER BY created_at;

That is different from doing a separate root-to-leaf lookup for every row. The initial search narrows the starting point; leaf order supports the scan.

Composite indexes and the leftmost prefix

A composite index orders tuples, not each column independently:

CREATE INDEX orders_account_created_idx
ON orders (account_id, created_at);

Entries group first by account_id, then by created_at within each account. It naturally supports “orders for this account in this time range.” A filter only on created_at cannot generally jump to one contiguous region because every account has its own time range.

Column order should reflect real predicates, ordering, uniqueness, and selectivity—not a memorized rule that the most selective column always goes first. Workload shape decides.

Selectivity and when indexes do not help

Selectivity describes how narrowly a predicate filters. A unique email is highly selective. A boolean such as is_active may match most of a table. Following an index to fetch a large fraction of scattered rows can cost more than scanning the table once.

Indexes may be ignored or provide little benefit when:

• the table is small;
• the predicate returns a large fraction of rows;
• the indexed expression does not match the query expression or type;
• a composite index begins with keys the query cannot constrain effectively;
• statistics are stale or the planner's estimate differs from reality;
• a leading-wildcard pattern cannot use the chosen operator/index strategy;
• write cost outweighs the reads the index improves.

Reading EXPLAIN without treating it as a scoreboard

PostgreSQL EXPLAIN shows the plan selected from estimated costs and row counts. EXPLAIN ANALYZE executes the query and reports observed timing and rows, so use it carefully on writes and production workloads.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = '[email protected]';

Look for whether row estimates are plausible, how many loops ran, which filter removed rows, and whether buffers were hit or read. A single duration from a warm development database is not a universal benchmark. Query latency also includes locks, network transfer, serialization, connection acquisition, and application work.

PostgreSQL and Supabase

Supabase exposes PostgreSQL, so the same index design and planner reasoning applies. Row Level Security policies can add predicates to queries; foreign keys do not automatically imply every supporting access pattern is indexed; and a dashboard suggesting an index cannot know every write/read tradeoff in your product.

Measure representative queries, use production-like data volume, inspect plans, and remove redundant indexes deliberately. The request path around those queries is explored in What Happens When Your App Makes an HTTP Request?. For the complexity vocabulary behind tree lookup, revisit Big O Notation and Trees and Binary Search Trees.

Key takeaways

• Databases optimize around pages, caching, and minimizing expensive access—not abstract comparisons alone.
• B-trees stay shallow through high branching factor and balanced page splits.
• Ordered leaves support both point lookup and efficient bounded ranges.
• Every index consumes storage and makes relevant writes more expensive.
• Composite key order and selectivity must match actual query shapes.
• EXPLAIN is evidence about a plan, not a command to force index usage.