Interview questions

It is how many bytes the layout and the medium force you to read, divided by the bytes the query actually needs: its read amplification. Reading one 200-byte row from a 4 KiB page is 20×. On object storage the smallest sensible read is 1–10 MB, because every request has a fixed latency of 20–100 ms and a per-request price, so the same row costs 10,000–100,000×.

Read moreThe one ratio

Because the natural size of a storage unit is the fixed cost of one access divided by the cost of each extra byte. On object storage the fixed cost per request is thousands of times larger than on a local disk, so the unit grows by about the same factor: big enough that the fixed cost is amortized.

Read moreThe one ratioPAX (hybrid) layout

Read amplification (bytes read per byte needed), write amplification (bytes written per byte changed) and space amplification (bytes stored per byte of live data). They are one phenomenon counted three ways: the unit you write, the unit you read and the unit you update are different sizes.

Read moreThe shape of the layer

No. It is an empirical conjecture with no proof, and better compression can improve reads and memory together. The safe statement is narrower: one physical layout makes one family of queries fast, and keeping more copies in other layouts converts read cost into write and storage cost.

Read moreThe RUM conjecture, and how far to trust it

"OLTP means rows" is a correlation, not the reason. What decides is how many of the columns each query touches, per row it touches, and how big updates are relative to a row. Queries over a few columns of many rows favour columnar; lookups of whole entities favour rows.

Read moreRow-oriented layoutFour numbers per layout

Neighbouring values in a column come from one distribution, such as one type and often few distinct values, so type-aware encodings work: dictionary, run-length, delta and bit-packing. Neighbouring bytes in a row belong to unrelated fields and have little in common.

Read moreEntropy and compression

Encoding fuses many values into one block: dictionary codes, run lengths and bit-packed widths all depend on the neighbours. Changing one value means decoding and re-encoding the whole block, so compression makes the block, not the value, the smallest thing you can update.

Read moreThe encoding block becomes the unit of update

PAX splits rows into groups and stores each group column by column. It was invented in 2001 to stop wasting CPU cache lines, then reused at the scale of disk blocks and of whole objects (Parquet row groups, ORC stripes) as the fixed cost of an access moved up the hierarchy.

Read morePAX (hybrid) layoutOne mechanism, three reinventions

No, despite the name "wide-column". A partition is stored row by row, as a sorted map of cells. They are key-value stores with sorted composite keys, so reasoning about their scans with columnar intuition gives wrong answers.

Read moreWide-column layout

The value is opaque to the engine, so it cannot encode, skip or index anything inside it. All the layout freedom sits in the key. What the engine cannot see it cannot optimize, and that work reappears in the application.

Read moreKey-value layout

In a heap, a row's address is stable, so secondary indexes store that address and reach the row in one hop. In a clustered table, rows are physically ordered by primary key and move when pages split, so secondary indexes store the key instead and need a second lookup. You cannot have both a stable physical identity and a useful physical order.

Read moreHeap versus clustered layout

Copy-on-write rewrites the whole unit to change any part of it. Its cost per change is the unit size U divided by the change size d: 128,000,000 / 200 = 640,000. Every 200-byte correction rewrites 128 MB.

Read moreCopy-on-writeThe price U/d

Writes are cheap, proportional to the size of the change, but every read must merge the deltas accumulated since the last compaction. It turns a known, fixed write cost into a read cost that keeps growing until compaction runs. The break-even depends on how often the data is read between compactions.

Read moreMerge-on-read

A tombstone, the marker that a key was deleted, must be kept until every older copy of that key is gone, and a scan over many tombstones reads a lot to return nothing. Their cost scales with data you no longer have.

Read moreTombstones

A positional delete records "file F, row 1,042": cheap to apply, but the writer must read first to find the position, and it breaks if rows move. An equality delete records "delete where id = 7": cheap to write, expensive to apply because every read must check it, and it survives the data being reorganized.

Read morePositional versus equality deletes

Old row versions cannot be removed while any active reader might still see them, so reclamation can only advance as far as the oldest open transaction. One transaction left open for hours pins every version written since. Monitor the age of the oldest reader.

Read moreThe oldest reader pins everything

Engines skip a file only if its [min, max] range for the filtered column excludes the value. Without clustering, every file's range covers nearly all customers, so no file can be ruled out. Clustering can gain at most about min(G, 1 / s): capped by the number of rows in a unit (G) and by the fraction of rows the query selects (s).

Read moreClustering payoff

A columnar layout's gain is guaranteed and computable: reading k of n columns saves about n / k, times compression. Clustering is a bet on which filters will be used: a query that filters on a different column gains exactly nothing, and you still pay to keep the data sorted.

Read moreFewer units, not less waste per unit

The leading column has more distinct values than there are files, so each file holds a slice of users across all of time, and every file's time range is wide. A second sort column helps only if the first has fewer distinct values than there are units: C_a ≤ U.

Read moreThe composite-key rule

Writes use up sortedness. The late-arriving data landed in new files spanning all key values, so the files' ranges now overlap and none can be skipped. Only reclustering restores it, which is why clustering is a recurring rent rather than a one-off price.

Read moreClustering is rent, not a price

When queries filter on several columns about equally, and without it they would scan everything. Z-order does not create skipping; it shares a fixed skip budget across d dimensions, about U^(1/d) for each, where U is the number of units. It is insurance for mixed workloads, not an optimization for one query.

Read moreWhen should you use Z-order?The skip-budget rule

A query on a single dimension touches at least U^(1 − 1/d) units. With a million units and four dimensions, that is about 31,600 units, 3% of the table, on every such query, forever.

Read moreZ-order and space-filling curves

Hive partitioning puts the value in the directory path, date=2026-10-21: a name, which is exact and free to check, but rigid and only workable for low-cardinality columns. Micro-partitions keep statistics for every column of every small file: a description, which is approximate and works for any column, at the cost of metadata.

Read moreMicro-partitioningNaming versus describing

Bucketing hashes rows into a fixed number of files by a key, so a join on that key needs no shuffle. You pay at write time, forever. It is rigid, since the bucket count is baked into the data, and it puts all rows of a hot key in one bucket that cannot be split.

Read moreBucketingWhy bucketing is brittle

Two needs conflict. Amortizing the cost of a request wants about 40 MB per read; skipping precisely for a selective query wants units of about 2 MB. No single size satisfies both, so formats nest: file, row group and page in Parquet; file, stripe and row index in ORC.

Read moreSegment, row group and stripe organizationThe answer is a hierarchy

Metadata used for skipping must be wrong in one direction only. It may wrongly say "maybe present", which wastes a read, but must never wrongly say "absent", which would give a wrong result. A Bloom filter errs only towards "maybe present".

Read moreSkipping metadata is one-sided

Min/max statistics are sensitive to outliers. One sentinel value such as 9999-12-31 stretches the unit's date range to cover everything, so every date filter must read it.

Read moreThree properties of zone maps

Its latency times its bandwidth: the request size at which half the time goes on latency and half on transfer. About 4 KB for DRAM, 560 KB for NVMe, 2 MB for a hard disk and 4 MB for an object store. To spend only 10% of the time on latency, requests must be about ten times larger, around 40 MB for an object store.

Read moreThe three numbers

Objects are immutable and each request costs about 50 ms, so units must be large. Large units make copy-on-write cost U / d ≈ 100,000–1,000,000 per small change, which forces delete vectors. Immutability forces a layer of manifests pointing at files, and manifests plus delete vectors force compaction. The lakehouse follows from the API.

Read moreBlock, file and object storageOne forced construction

A single compare-and-swap on one pointer to the table's current metadata, held in a catalog such as DynamoDB, a metastore or a database. The object store itself cannot update several objects atomically.

Read moreOne forced construction