B-tree: The Default Workhorse

~12 min · indexes, btree

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The default for a reason

When you write CREATE INDEX without specifying a type, PostgreSQL uses a B-tree. B-trees are sorted, balanced, and support equality (=), range (<, >, BETWEEN), prefix matching (LIKE 'foo%'), and ORDER BY. They are the right answer for the vast majority of indexed columns.

What B-trees do well

Equality: WHERE email = 'x'
Range: WHERE created_at BETWEEN '2026-01-01' AND '2026-04-30'
Prefix LIKE: WHERE name LIKE 'A%' (NOT '%A')
Sorting: ORDER BY created_at DESC can use a B-tree directly

What B-trees can't help

Suffix or substring LIKE ('%foo', '%foo%') — needs pg_trgm/GIN.
JSONB containment, full-text search — needs GIN.
Array operators, geospatial — needs GIN/GiST.

Code

B-tree variants·sql

-- Plain B-tree (the default)
CREATE INDEX orders_placed_at_idx ON orders (placed_at);

-- Descending order baked in (matches ORDER BY ... DESC queries)
CREATE INDEX orders_placed_at_desc_idx ON orders (placed_at DESC);

-- Multi-column (composite) — see next lesson
CREATE INDEX orders_customer_placed_idx ON orders (customer_id, placed_at DESC);

-- Specify B-tree explicitly (rarely needed)
CREATE INDEX orders_total_idx ON orders USING btree (total);

Range query that uses the index·sql

EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE placed_at BETWEEN '2026-04-01' AND '2026-04-30'
ORDER BY placed_at DESC;
-- Expect: Index Scan or Index Only Scan on orders_placed_at_desc_idx.

External links

PostgreSQL B-tree Indexes

Exercise

Add a B-tree index on a column you query by range. Run a range query before and after with EXPLAIN ANALYZE. Repeat with the column added in DESC order; compare plans for ORDER BY DESC queries.