Why this matters
- Missing indexes turn millisecond lookups into full-table scans that stall checkout during peak traffic.
- Over-indexing slows writes because every INSERT updates every index on the table.
- Choosing indexed columns requires understanding selectivity — indexing
descriptionrarely helps. - Execution plans and EXPLAIN output reference indexes; this article is prerequisite for query optimization.
How B-tree indexes work
Postgres default CREATE INDEX builds a B-tree — a balanced tree of pages sorted by key. Finding sku = 'VLT-9001' traverses log(n) pages instead of reading the entire heap.
CREATE INDEX idx_products_sku ON products(sku);
EXPLAIN SELECT * FROM products WHERE sku = 'VLT-9001';
-- Index Scan using idx_products_sku on products
Range queries benefit too: WHERE created_at >= '2026-09-01' uses a B-tree on created_at to seek the start point and scan forward.
B-tree strengths
- Equality and range —
=,<,>,BETWEEN,ORDER BYon indexed column. - Composite keys —
(category_id, price_cents)supports filters on leftmost prefix. - Uniqueness —
CREATE UNIQUE INDEXenforces constraints faster than table scans.
Selectivity: index the lookups you actually run
Selectivity is the fraction of rows a condition matches. VaultCommerce indexes:
CREATE UNIQUE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);
email is highly selective — one row per value. description is low selective — full-text search belongs in Elasticsearch, not a B-tree on a TEXT column.
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 4 sections
- Clustered vs non-clustered layout
- Composite and covering indexes
- Index trade-offs
- When not to index
Test yourself
Answer these before moving on — recall is what makes it stick.