Storage engines and data models

01Data model versus storage engine

Choose the logical key independently of the storage engine. A message record can be (room_id, sequence, author_id, body, version). Fetching the latest fifty messages in one room favors an ordered key beginning with room and sequence. For example, a request updates message 42 in room R7 from “Train at five” to “Train at six” and deletes message 8, whose old value is “Hello.”

One machine can store this correctly. It becomes slow when the active data no longer fits memory or disk work exceeds capacity. Before adding shards, understand which physical work each logical write creates. A write-heavy service can saturate its storage while the incoming request count appears modest.

02B-trees and B+ trees: ordered page lookup

A B-tree keeps keys ordered in a branching tree of pages. A page is a block that the engine reads or writes as a unit. Internal pages guide a search toward a child; leaf pages contain index entries, with the exact record layout depending on the engine. A B+ tree keeps record-bearing entries in leaves and supports walking adjacent leaves for ranges.

Imagine the root’s separators for R7 are message 20 and message 50. Looking up 42 follows the middle child to a leaf containing 21, 35, 42 and 49. If that index entry points to a separately stored row, fetching the message body is additional work. A latest-fifty query can seek near the end of R7’s range and walk backward through ordered entries.

Changing 42 updates the relevant data and index structures rather than scanning every message. A full leaf may split, requiring parent changes. Cached upper pages reduce physical reads, but cache misses, page splits, transaction versions, and recovery logging still matter. “Logarithmic lookup” describes growth; it does not specify a fixed number of disk operations for every product.

03Write-ahead logging: recovery and acknowledgment

A write-ahead log, or WAL, makes the recovery records for a change durable before the corresponding changed data pages are written to durable storage. That is the write-ahead ordering rule: the log reaches durable storage first. After a crash, the engine can reconstruct committed state from durable records and its persisted files. The exact protocol varies; a log is a recovery mechanism, not automatically an application event stream.

Assume the database acknowledges an edit only after its required recovery and commit records are durable under the configured local storage policy. At time 0 it logs message 42 version 2. At time 1 it acknowledges the edit. If the process crashes before the ordinary data page is flushed, recovery can replay the relevant durable information. If the service instead acknowledges only an in-memory buffer, the same crash may lose the edit.

State which failures each storage stage can survive:

Synchronization asks the storage stack to persist the necessary bytes; it does not make one local device indestructible. Group commit lets several transactions share one synchronization operation. This can improve throughput, but a transaction may wait for the group before receiving its acknowledgment.

04LSM trees: memory tables and immutable sorted files

A log-structured merge tree, abbreviated LSM, accumulates updates in a memory table and writes sorted immutable files as buffers fill. The WAL protects updates that have not yet become durable table files under the chosen configuration. Immutable means a later edit is stored as another version rather than rewriting that old file in place.

A read of message 42 must select the newest visible version according to the engine’s ordering and snapshot rules. It cannot stop at v1 merely because F1 was convenient to open. A read of message 8 encounters a deletion marker, often called a tombstone, which suppresses its older value. Range reads merge ordered streams from relevant files. They are supported, but their cost depends on how many streams and obsolete versions must be considered.

05Compaction, tombstones, and amplification

Assume an illustrative workload ingests 100 MB/s and the measured total local write amplification, including the log in this measurement, is 8. The device must sustain about 800 MB/s of writes, before adding other workloads or safety margin. This is arithmetic from assumed inputs, not a hardware guarantee. Compaction also consumes read bandwidth and CPU. Deferring it forever makes later reads and space usage worse.

Two common compaction policies move that cost differently. Leveled compaction limits overlap within deeper levels, usually reducing read and space amplification but rewriting overlapping data. Tiered compaction accumulates several sorted runs before merging them, often reducing write amplification while increasing read sources and temporary space. These are tendencies, not universal benchmark results: key order, skew, overwrite rate and tuning matter.

06Storage-engine comparison and row versus column layouts

Two different physical choices are being compared. B-trees and LSM trees organize key lookup and update work. Row-oriented and column-oriented layouts determine whether fields of one record or values of one field are stored together. These choices can be combined; select them from whether the workload fetches individual messages, scans room ranges, or analyzes a few fields across many messages.

For an assumed room-history workload dominated by appends and bounded room-range reads, I would evaluate an LSM-backed ordered store. The key (room, sequence) makes the common range explicit. I would benchmark it against an indexed relational design before assuming its extra operational complexity is worthwhile. The choice depends on latency targets, transactional requirements, updates, retention and operating experience.

A key-value API does not remove the need to design keys. Hashing every entire message key across shards scatters a room’s range; partitioning by room preserves locality but creates a hot partition for a huge room. Time buckets or subpartitions can bound growth at the cost of merging reads. Physical engine selection does not solve those ownership decisions.

Row-oriented storage places a record’s fields together, useful when fetching a message. Column-oriented analytical storage groups values by column, useful when scanning a few fields across many records. A wide-column database’s data model is not synonymous with a columnar analytics layout. Ask which query the layout accelerates rather than matching names.

A practical baseline is PostgreSQL with an ordered B-tree index for transactional room history. Evaluate RocksDB when the application needs an embedded ordered key-value engine and can own the surrounding service protocol; RocksDB alone is not a replicated database service. Its write options distinguish asynchronous WAL writes from synchronized writes. If an acknowledged edit must survive machine restart, verify that WAL is enabled and the required synchronization policy is applied rather than assuming the default write call provides it.

07Storage failure, recovery, and benchmarking

After a crash, check that every acknowledged change covered by the durability policy survived, including the new value of message 42 and the deletion of message 8. A deleted message disappearing from ordinary reads does not prove its bytes vanished from snapshots, old files, replicas or backups; physical erasure follows a separate retention and cleanup policy.

In an interview I would say: “The key supports room-history reads. An LSM may suit frequent appends, but edits and deletion markers leave versions that reads and compaction must resolve. I will state which failures saved messages survive, budget the extra reads, writes and disk space, and test range reads while background maintenance runs.”

Recall the key ideas

Answer from memory before opening each card. Explain why the choice works and what it costs. Revisit missed cards tomorrow.