4.11 Decision cheat sheet

LSM or B-tree? Write-heavy, high ingest, sequential-write-friendly, want cheap snapshots and lower write amplification → LSM. Read-heavy, latency-predictability matters, lots of range scans, mature transactional needs → B-tree. Test with your workload — and run the test long enough for compaction to reach steady state.

Do I add this index? Only if a real query pattern needs it. Every index costs disk and slows every write. Prefer covering indexes when a hot query can be answered from the index alone; accept the extra space knowingly.

Row or column store? Point lookups and updates of individual records → row. Aggregates over many rows touching few columns → column. If a query would touch >5–10% of rows, columnar wins; if it touches a handful of rows by key, row wins.

How do I pick the sort/order key for a columnar table? Put the most commonly filtered range predicate first (usually time). Second key = the next most common grouping dimension. Remember: compression benefit is concentrated in the first key, and this choice is effectively irreversible without a full rewrite.

Concatenated index or multidimensional? Concatenated works when queries always constrain a prefix of the columns. If you need two independent ranges simultaneously (lat and lon, date and temperature), you need a real multidimensional index (R-tree/BKD/Z-order).

Full-text or vector search? Exact terms, names, codes, filters, and explainability → inverted index. Meaning-level matching, paraphrase, cross-lingual → vectors. In practice, hybrid (BM25 + vector, fused) beats either alone for most product search.

In-memory database? When the dataset fits in RAM and you need either the lowest possible latency or data structures that are awkward on disk (Redis's sorted sets, priority queues). Remember the speedup comes from avoiding disk-encoding overhead, not from avoiding disk reads.

---