Storage: Physical Organization

Choose a layout

Data is about to be stored: row, columnar, PAX (columns within row groups) or key-value? Six questions, from the storage medium to the share of columns each query touches.

Choose a mutation strategy

Stored data changes: update in place, rewrite whole files (copy-on-write), or write changes separately and merge on read with delete vectors or equality deletes? Four questions, then the compaction interval and the metric that predicts failure.

Choose a clustering strategy

Rows can be physically ordered so queries skip most files: a single sort key, Z-order across a few dimensions, bucketing for joins, or no clustering at all? Two questions, then the checks that decide whether it pays its rent.

Measure your medium

Before choosing any block, page or file size: measure your storage's latency, bandwidth and achievable concurrency, then derive the minimum efficient request and the queue depth your reads need.

The one ratio: bytes touched versus bytes needed on your own disk

Random 200-byte reads from a 1 GB file, as plain and as 4 KiB aligned reads with the page cache bypassed; then 1,000 small range GETs against 10 large ones from an S3-compatible store.

Row versus column on the same table

10 million rows of 25 columns as CSV and as Parquet: file size, reading 2 columns, and reading one row by position.

Copy-on-write versus delete vector

5,000 row corrections on a table of 640,000-row files: copy-on-write rewrites against merge-on-read delta files, and scan time as deltas accumulate.

Clustering payoff: sorted versus random files

20 million rows as 20 Parquet files, random and sorted by (depot_id, scan_time): files a one-depot query must open, then after one unsorted append.

The composite-key rule (`C_a ≤ U`)

20 files sorted by (depot_id, scan_time) and by (user_id, scan_time): files a one-day query must open, alone and with a depot.

Z-order floor

A million 2-D points in 4,096 units: sorted on dimension 1, Z-ordered, and random; units a thin query on each dimension touches, and a 50/50 mix.

Queue depth and the 0.7% reader

Random 4 KB reads from a 2 GB file with 1 to 128 requests in flight, page cache bypassed: IOPS against queue depth; then mmap.

Interview questions

Short questions on how data is laid out at rest, such as row and column layouts, updates and deletes, clustering and access paths, each with an answer you could give aloud and a link to the full explanation.

Scenarios

Longer problems on physical data layout, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.