Back to roadmap
Module 6 · Relational Data at ScaleDay 05225 min

Composite and Covering Indexes

Order matters; covering avoids the heap.

← PreviousNext →
Day 052

Composite and Covering Indexes

(user_id, created_at, i...
datastore
WHERE/ORDER
service
Rows
datastore
Signal path
Composite index as a single sorted structure
WHERE/ORDER
service
flow
(user_id, created_at, id)
datastore
(user_id, created_at, id)
datastore
flow
Rows
datastore
Memory hook

Composite and Covering Indexes: order matters

Mental model

shape data so reads and writes stay honest

Design lens

Covering increases write/storage cost.

Recall anchors
Leftmost prefixEquality before rangeINCLUDE / covering

Why it matters

Composite indexes serve queries that filter on a prefix of their column list. Covering indexes include extra non-key columns so the query can be answered without touching the table.

Deep dive

Leftmost prefix: index (a,b,c) supports queries on a, a+b, a+b+c — not on b alone.

Order columns by selectivity and equality vs range; equality cols first.

Covering indexes: pay storage for read-only speed.

Demo / scenario

Pagination by (user_id, created_at) with id tiebreaker.

  1. CREATE INDEX events_uid_ts_id ON events(user_id, created_at DESC, id).
  2. Query SELECT id, type WHERE user_id=$1 AND created_at < $2 ORDER BY created_at DESC.
  3. Index can be covering: INCLUDE (type).
  4. All reads from index leaves.

Tradeoffs

  • Covering increases write/storage cost.
  • Wrong column order misses index.
  • Test EXPLAIN before believing.

Diagram

(user_id, created_at, id)
WHERE/ORDER
Rows
Composite index as a single sorted structure.

Mind map

Check yourself

Loading quiz…

Sources & further reading

← PreviousNext →