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.
Clustered vs non-clustered layout
Postgres clusters tables by primary key on insert order initially, but the heap is not strictly clustered like SQL Server. The primary key index is the main access path; secondary indexes store (index_key, ctid) pointers to heap rows.
Updating an indexed column moves the heap row and every index entry pointing to it — another reason VaultCommerce avoids indexing frequently mutated low-value columns.
Composite and covering indexes
CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC)
WHERE status IN ('PENDING', 'PAID');
Column order matters: (status, created_at) serves WHERE status = 'PAID' and WHERE status = 'PAID' AND created_at > ... but not WHERE created_at > ... alone.
Partial indexes (WHERE status IN (...)) shrink index size when queries always filter on that subset.
Index trade-offs
| Aspect | Benefit | Cost |
|---|---|---|
| Read speed | Index scan vs seq scan | Extra disk space |
| Sort avoidance | Index provides order | INSERT/UPDATE maintain index |
| Uniqueness | Enforced at write time | Failed inserts on duplicate |
Read speed
BenefitIndex scan vs seq scanCostExtra disk spaceSort avoidance
BenefitIndex provides orderCostINSERT/UPDATE maintain indexUniqueness
BenefitEnforced at write timeCostFailed inserts on duplicate
VaultCommerce's products table carries five indexes — each justified by a measured slow query, not speculative "index everything" policies.
When not to index
Skip indexing when
- Column has very low cardinality (
is_activeboolean on a mostly-active table) unless combined in a composite index. - Table is tiny — sequential scan is faster than index lookup for hundreds of rows.
- Write rate dominates and the column is rarely queried.
-- Remove unused index after verifying pg_stat_user_indexes
DROP INDEX CONCURRENTLY idx_products_legacy_flag;
CONCURRENTLY avoids blocking writes during drop on large tables.
Quick recall
Everything you need if you only revisit this box.
- B-tree indexes accelerate equality and range lookups on selective columns.
- Index foreign keys and columns in frequent WHERE/JOIN clauses — email, SKU, customer_id.
- Composite index column order matters; partial indexes reduce size for filtered queries.
- Every index slows INSERT/UPDATE on that table — index only measured bottlenecks.
- Postgres secondary indexes point to heap rows; primary key is the dominant access path.
Test yourself
Answer these before moving on — recall is what makes it stick.