Skip to main content
PreparationZone Logo
PrepZone

Indexing Fundamentals

B-tree indexes, selective columns, and why VaultCommerce indexes email but not description.

Pass checkpoint below

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 description rarely 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.

Java
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 BY on indexed column.
  • Composite keys — (category_id, price_cents) supports filters on leftmost prefix.
  • Uniqueness — CREATE UNIQUE INDEX enforces constraints faster than table scans.

Selectivity: index the lookups you actually run

Selectivity is the fraction of rows a condition matches. VaultCommerce indexes:

Java
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.