MODULE 2/LESSON 2
šŸ—„ļø Database Indexing & Caching

Composite & Covering Indexes

Designing composite and covering indexes that eliminate table lookups entirely

ā± 12 min
A well-designed covering index can reduce query execution from hundreds of milliseconds to under 1ms by making the database answer queries entirely from the index structure, never touching the actual table rows.

Key Concepts

Composite Index Design

Column order matters critically. Put equality columns first (WHERE col = X), then range/sort columns last (ORDER BY col). This matches the query pattern to the sorted index order.

Covering Index

When all SELECT, WHERE, and ORDER BY columns are inside the index, PostgreSQL never reads the actual table. This is the Index-Only Scan ā€” the fastest possible read path.

Index Only vs Heap Fetch

Without covering index: index lookup ā†’ table heap fetch (random I/O, ~10ms). With covering index: index lookup only (sequential I/O, ~0.1ms). 100x speed difference at scale.

The Left-Most Prefix Rule Explained

Left-Most Prefix Rule: Index (tenant_id, status, created_at) Col A: tenant_id Col B: status Col C: created_at WHERE tenant_id = 1 āœ… WHERE tenant_id = 1 AND status = 'NEW' āœ… WHERE tenant_id = 1 AND status = 'NEW' ORDER BY created_at āœ… Skipped / Ignored WHERE status = 'NEW' āŒ Full Table Scan WHERE tenant_id = 1 Skipped / Ignored AND created < NOW() āš ļø Uses A only, filters C

Understanding the Left-Most Prefix Rule

  • Index: (tenant_id, status, created_at) ā€” the index sorts by tenant_id first, then status within each tenant_id, then created_at within each status
  • āœ… Works: WHERE tenant_id = 1001 ā†’ uses index on first column
  • āœ… Works: WHERE tenant_id = 1001 AND status = 'ACTIVE' ā†’ uses index on first two columns
  • āœ… Works: WHERE tenant_id = 1001 AND status = 'ACTIVE' ORDER BY created_at DESC ā†’ uses all three columns
  • āŒ Fails: WHERE status = 'ACTIVE' ā†’ cannot start at middle of index ā€” full table scan
  • āŒ Fails: WHERE created_at > '2026-01-01' ā†’ cannot skip tenant_id ā€” full table scan

Index Scan vs Index-Only Scan

Normal Index Scan (Slower) SELECT id, status, total_amount FROM orders WHERE tenant_id = 1; Index: (tenant_id, status) Index Lookup Finds tenant_id, status Missing: total_amount Random I/O (Slow) Table Heap Fetch Goes to disk to fetch the missing total_amount Index-Only Scan (Blazing Fast) SELECT id, status, total_amount FROM orders WHERE tenant_id = 1; Index: (tenant_id, status) INCLUDE (total_amount) Index Lookup ONLY Finds tenant_id, status AND total_amount is attached! Return Data Instantly Table Heap (Unused)
šŸ’”
Senior Architect Insight: Use EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL after every schema change. Look for 'Index Only Scan' (best), 'Index Scan' (good), 'Bitmap Heap Scan' (acceptable for range queries), and 'Seq Scan' (fix this immediately on large tables).
PreviousB-Tree Index Internals
Next LessonPartial Indexes & Left-Prefix Rule