Scenarios

2 × 10⁹ rows × 200 B = 400 GB. The aggregate needs 2 of 25 columns, so a columnar layout reads about 8% of the bytes before compression, and less after.

Parquet (a PAX layout) in object storage with 128 MB row groups: about 640,000 rows each, about 3,125 files. Object storage's smallest efficient read is above 1 MB, which these units clear.

It needs the whole entity. In pure columnar form that is 25 separate reads, each paying latency, so keep a row-store or key-value copy keyed by parcel.

Double storage, more write amplification at ingestion, and a freshness gap between the two copies.

Its cost per change is the unit size divided by the change size, U / d ≈ 640,000×. The corrections touch about 2,500 distinct files, so roughly 320 GB would be rewritten for 1 MB of change.

Merge-on-read for the corrections, and positional delete vectors for the deletions: files are immutable and the writer can look up positions. A raw bitmap is about 80 KB per file, smaller with Roaring compression.

Derive the compaction interval from measured costs (the square-root rule), and rewrite delete vectors in the same transaction as compaction. If the table is reclustered often, equality deletes become the better fit, since positions keep changing.

Deltas accumulating without bound, and positions broken by rows that moved.

• Deltas accumulating with no compaction schedule: read cost grows with their number.
• Tombstones building up.
• A few hot files with huge numbers of deltas, hidden by a normal-looking average.

Deltas per file, as a distribution rather than a mean.

Compute the right compaction interval, and compact the hot files first.

Compaction costs write bandwidth.

The engine shreds JSON into hidden typed columns based on the schema it infers. That works only while the schema holds: renamed keys create new sparse columns, and skipping stops working, silently.

Shred the stable keys into explicit typed columns, validate schemas at ingestion, alert on the rate of new keys, and keep the raw JSON as a fallback. When flattening nested data, keep path information (repetition and definition levels).

L = log₁₀(1,000) = 3 levels. Leveled: write amplification about T·L = 30, reads touching about 3 runs, space about 1.1×. Tiered: writes about 3, reads touching about 30 runs, space up to 10×.

Heavy writes and mostly recent reads point to tiered or time-windowed compaction, with Bloom filters to cut the read cost.

These formulas are asymptotic models; measure on your workload.

4 TB in 128 MB files is about 31,000 files. One day out of two years selects 1/730 of the data, so a date-sorted table opens about 31,000 ÷ 730 ≈ 43 files instead of 31,000. A customer lookup selects about 10⁻⁸ of the rows: one file if sorted by customer, all of them if not.

Two dimensions with different shapes. Do not Z-order if one of them carries 80% of the scanned bytes. Sort by event_date (730 distinct values, fewer than the number of files), and serve customer lookups with a Bloom filter (about 10 bits per row; on 10¹⁰ rows, check its size) or with a separate key-value copy, since a lakehouse query has a planning floor of about 200 ms and cannot serve fast point lookups.

A second copy (storage, write amplification, a freshness gap) and the ongoing rent of reclustering.

Late-arriving data breaks the date order, so monitor clustering depth.

The data's sortedness decayed: a backfill, a new source writing in a different order, a compaction that dropped the order, or a skewed hot key.

For the filtered column: the overlap of [min, max] ranges across files, the clustering depth, and the ratio of files read to files with matching rows. If every file's range overlaps, clustering is the cause. Also look for one file with a sentinel outlier.

Recluster only the affected files (each costs a full rewrite of its unit; budget it as rent), write backfills sorted, and put clustering depth on the dashboard.

Averages look fine until the overlap crosses a threshold, and then every query falls off the cliff at once.

With d = 4 dimensions, any single-column query reads at least U^(3/4) ≈ 31,600 files, 3% of the table.

If one column carries 80% or more of the scanned bytes, sort by that column alone: Z-order would give up a factor of U on the dimension that matters to buy a factor of about 32 (U^(1/4)) on each of the others. If the four columns are used about equally, Z-order wins by turning a full scan into a 3% scan for every query.

Correlation between the columns (correlated columns waste bits but lower the floor), value ranges (bucket by quantiles first), and the write rate (reclustering rent). With five or more dimensions, consider secondary structures instead.

One long task suggests key skew. Bucketing concentrates skew: every row of a hot key lands in one bucket that cannot be split, so on its own it would not help. Check the key histogram first.

If skew is mild, bucketing both tables on the join key, with the same count and hash, removes a shuffle of about 4 TB. But it fixes both layouts permanently, and every writer must follow it. If a few keys are hot, handle them separately (salt them, or broadcast the small side), then consider bucketing for the rest.

The cost moves to write time, where it is paid on every write.

One request in flight at 80 µs gives about 50 MB/s, which matches what you see, so a faster drive will not help. Filling the drive needs 7 GB/s × 80 µs ÷ 4 KB ≈ 140 requests in flight (Little's Law).

Whether the reader is synchronous or uses mmap (page faults are one request at a time); then move to asynchronous I/O (io_uring) or enough threads. Also check alignment, since misaligned 4 KB reads can cost up to 4×, and whether kernel copies and system calls, about 1 µs per I/O, have become the fixed cost.

With explicit I/O you own caching and eviction.

The target is 1.2 GB/s. One connection streams about 80 MB/s, and an 8 MB request takes about 50 ms of latency + 100 ms of transfer = 150 ms, so one connection delivers about 53 MB/s. That needs about 23 requests in flight (1,200 ÷ 53); with margin, 32–64.

Requests of 8 MB or more (for latency to be only 10% of each request, about 36 MB). Use column pruning and row-group skipping to read fewer bytes in the first place.

Request fees (12 GB ÷ 8 MB = 1,500 requests, small) and buffer memory (64 × 8 MB = 512 MB).

Per-prefix rate limits (503 errors) and stragglers: hedge the slowest requests.