Columnar storage: store each column together, and let the scan skip everything else

What it is for. Analytical queries read few columns of many rows (e.g. avg(price) over millions of rows) and rarely write single rows. A row store reads whole rows to use one column, and maintains a per-row index the query never needs. A column store rearranges the bytes for the scan.

The waste it removes (row store, same query).

• Index dead weight: a b-tree helps only selective lookups. A full-scan aggregation has roughly 100% selectivity, so the index saves no row access and costs storage plus write maintenance.
• Row dead weight: a heap page packs all 20 columns of each row. A query using 1 column reads 19/20 useless bytes and pollutes the buffer pool.

Layout. Each column's values are stored contiguously in their own files, in the same row order across columns. Reading one column touches only that column's pages: a 20-column table with 1 column read costs roughly 1/20 of the I/O, before compression.

Addressing: subscript, not pointer. A row's position in each column IS its address: row N's price is the Nth value in the price column. No pointer column is stored.

• A pointer column would cost 8 bytes per row, turn sequential scans into pointer chasing (one cache miss per row, breaking vectorization), resist compression (addresses are high-entropy), and need rewriting on every merge. A stored sequential id column (0, 1, 2, ...) is pure redundancy: position already encodes it.
• Pointers are the right answer for point lookups (row stores keep TIDs in B-tree leaves). Subscript is the right answer for scans.

Skipping: coarse metadata at three levels, one idea at different granularity.

• Partition: coarsest, manual, one key (e.g. PARTITION BY month). Queries filtering on the partition key prune whole directories.
• Zone map: finer, automatic, all columns. Each block carries min/max per column; blocks whose range cannot match are skipped whole.
• Sparse primary index: finest. One mark per granule (e.g. every 8192 rows), not per row. Locate the granule, then scan sequentially.
• Partition pruning works only on the partition key; zone maps cover all columns and are the backstop. Over-fine partitioning backfires (partition explosion: many tiny files, merge and metadata overhead eat the pruning benefit), so partition on something low-cardinality like month, not user_id.

Writes: immutable parts plus background merge (the life of a part).

1. An INSERT lands in memory, sorted by the ORDER BY key.
2. Sorted data is written as a new immutable part (one file per column, plus min/max, marks, checksums). It is invisible until committed.
3. Commit is atomic: the part either exists whole or not at all. There is no half-written part.
4. A query snapshots the part set at start. Parts never change under a running query, so reads are consistent without version chains.
5. Background merge picks small parts in the same partition, merge-sorts them into a bigger part, and atomically swaps it in. Readers see the old set or the new part, never half-and-half.
6. UPDATE/DELETE is not in place: a mutation record is written and the affected parts are rewritten in the background. Heavy and asynchronous.
7. TTL expiry drops whole parts or partitions, nearly free.
8. Crash: uncommitted parts in temp directories are discarded; committed parts are intact. No log replay is needed.

Why no WAL and no MVCC. Both serve in-place writes: wal rescues a half-written row, mvcc lets readers see old versions while writers change rows (price: see isolation-levels). A column store's workload is bulk appends with rare changes, scanned far more than updated, so:

• WAL is pure overhead: write amplification times two, plus checkpoints and replay, while part-level atomic commit already gives crash safety. There is no half-written row for a log to rescue.
• MVCC is pure burden: version chains would make scans read dead versions and check visibility per row, the worst thing for a scan, while immutable parts plus query-time part snapshots already give read consistency. The conflict MVCC solves cannot occur, because nothing is modified in place.
• In one sentence: once you decide "never modify in place, only append new parts and merge in the background", the problems WAL and MVCC solve stop existing. What remains is part-level atomic commit plus merge scheduling.

The presumption underneath. This is a bet on the business, not a law of nature: analytical data, once landed, does not change. Logs, events, metrics, order history: an event that happened stays happened, and corrections arrive as newly appended records, not single-row rewrites. When the bet breaks (a workload that constantly updates single rows, like user profiles or order status), a column store is the wrong tool. That is why HTAP pairs a row store for transactions with a columnar replica for analytics. The same philosophy in row-store land is LSM: immutable files plus background compaction (next on this shelf).

Costs, stated honestly.

• Single-row point lookup and single-row update/delete are expensive (rewrite a whole part).
• No cross-part traditional transaction: an INSERT is atomic per part, not across parts. Duplicate writes are handled by insert-block deduplication.
• Tiny batches produce many small parts and merge can fall behind ("too many parts"). Column stores want batch writes, not row-by-row feeding.

Agent audit checklist: read this node, then check the system against every line, and report each item with evidence (a file, a config, or a query plan).

1. Workload: is the write pattern append-mostly and the read pattern scan-heavy with few columns per query? If single-row churn dominates, flag it: wrong tool, recommend a row store or an HTAP split.
2. Layout: is each column stored contiguously, in the same row order across columns? Quote the file layout.
3. Addressing: subscript (position is the address) or stored pointers? If pointers, price them: bytes per row, scan behavior, merge rewrite cost.
4. Skip metadata, all three levels: partition key and pruning behavior; zone maps (min/max per block, all columns?); sparse index granularity (rows per mark). Name one query each level prunes.
5. Part lifecycle: are parts immutable? Is commit atomic? Is merge background and non-blocking for readers? How are UPDATE/DELETE and TTL implemented?
6. WAL/MVCC: are they absent? Is the absence justified by immutability, or is there a real in-place write path that needs them?
7. Presumption: is the append-only assumption stated anywhere? How do late-arriving data, corrections, and deletions flow?
8. Costs: point-lookup cost, single-row mutation cost, small-batch behavior (part count, merge lag). Report numbers, not adjectives.