Typorithm · medium

Indexing & Partitioning

indexing-partitioningsystem-designindexpartitioningdenormalize

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Indexing & Partitioning

Data Layer

Indexes (typically B-trees) speed reads at the cost of writes and storage; in a composite index the column order matters. Partitioning splits a table by range or hash, and denormalization trades extra write work for faster reads.

An index is a side structure — usually a B-tree — that lets the database find rows by a column without scanning the whole table. It makes matching reads dramatically faster, but every write must also update the index, and the index uses extra storage.

A composite index covers several columns, and the order of those columns matters: an index on (user_id, created_at) helps queries that filter on user_id first, but not ones that filter only on created_at. Partitioning splits one table into pieces by range (e.g. by date) or by hash of a column.

Denormalization deliberately duplicates data — for example storing a precomputed total — so reads avoid expensive joins or aggregations. The trade-off is consistent across all of these: you pay more on writes to make reads cheaper.

Indexes (B-trees) speed reads, cost writes and storage.

In a composite index, column order matters.

Denormalization trades write cost for read speed.

performance:
  index: b-tree-on-query-cols
  composite: multi-column-order
  partition: range-or-hash
  denormalize: read-speed
  tradeoff: write-cost

Read the implementation

Connect each part of the code to the concept before you type.

1. Index

An index, usually a B-tree, on the columns you query speeds up reads.

index: b-tree-on-query-cols

2. Composite index

In a composite index, the column order decides which queries it can serve.

composite: multi-column-order

3. Partitioning

Partitioning splits a table by range or by hash of a column.

partition: range-or-hash

4. Denormalization

Denormalization duplicates data to make reads faster.

denormalize: read-speed

5. The trade-off

The cost of indexes and denormalization is extra work on every write.

tradeoff: write-cost

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.