TopolearnB-trees are the default index in almost every database; knowing their shape explains which queries they help.
A B-tree (in practice a B+tree) stores keys in sorted order in fixed-size pages. Internal pages hold separator keys and pointers to child pages; leaf pages hold the keys with pointers to rows and are linked to their neighbours. Because each page holds hundreds of keys, the tree is very shallow: three or four levels cover hundreds of millions of rows, so a lookup reads only a few pages, and the upper levels are almost always cached.
Because the keys are sorted, one B-tree supports many operations:
- equality:
WHERE id = 7 - ranges:
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' - prefix matches:
WHERE name LIKE 'Ana%'(with a suitable collation or operator class) - sorting:
ORDER BY created_at DESC LIMIT 20can read the index in order and stop early MIN/MAXof the indexed column
It does not help with LIKE '%ana%' (no fixed prefix) or with a function of the column (WHERE lower(email) = ...) unless you index that expression.
The tree stays balanced as rows are inserted by splitting full pages. Inserting increasing keys (identities, timestamps, UUIDv7) appends to the rightmost leaf, which is cache-friendly; random keys (UUIDv4) cause splits all over the tree and a larger working set.
Example
CREATE TABLE post (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author text NOT NULL, created_at timestamptz NOT NULL);
INSERT INTO post (author, created_at)
SELECT 'user' || (g % 500), now() - g * interval '1 minute'
FROM generate_series(1, 100000) g;
CREATE INDEX post_created_idx ON post (created_at);
ANALYZE post;
EXPLAIN SELECT * FROM post ORDER BY created_at DESC LIMIT 10;
-- Limit -> Index Scan Backward using post_created_idx on post
-- (no sort: rows come out of the index already ordered)
EXPLAIN SELECT * FROM post WHERE author LIKE '%er4%';
-- Seq Scan on post: no fixed prefix, the B-tree can't helpRemember
- B-trees are sorted, balanced and shallow: lookups read only a few pages
- They serve equality, ranges, prefix matches and ORDER BY ... LIMIT
- Sequential keys insert cheaply; random keys spread writes across the tree
Watch out for
- Expecting a B-tree to speed up LIKE '%text%' or a function applied to the column.