Storage Extras: richer indexes, warehouse schemas, column tricks

The corners of storage the main board skipped: a second kind of index, how a warehouse lays out its tables, and how a column store squeezes a sorted list to nothing. Read Storage Engines first; this picks up right where it left off.

Before you start. The last lesson found two ways a database keeps data findable without reading every row - a sorted tree of pages, or sorted files merged in the background - and both lean on the same trick: keep something sorted. This lesson is about what “sorted” buys you, and what it doesn’t.

The idea in 30 seconds

An index is a sorted list kept beside the real data; it only lets you jump to what it’s sorted by.
Glue two fields into a concatenated index and you can jump on the first field, or both together, but never the second alone.
Two numbers that matter at once, like a map location, need a real two-dimensional index - an R-tree, nesting boxes inside boxes instead of one long list.
A star-shaped warehouse table, one wide fact table with plain tables around it, is usually simpler to query than the same thing split into more sub-tables.
Sorting a column buys huge compression, but only the first sort key gets it, and a sorted column can’t be edited in place.
A materialized view is a question already answered and saved: fast to read, costs time to keep fresh, stale until the next refresh.

Read the story · Skip to the numbers