Database Engines and Write Amplification

Database Engines and Write Amplification

Database performance follows physical access paths and storage-engine behavior, not the database label. A B-tree maintains ordered pages for equality and range access. An LSM-style engine buffers writes in memory and flushes sorted runs, then compacts them. WAL supports recovery before data pages are flushed. MVCC keeps row versions so readers can observe a consistent snapshot.

Mechanism and worked example

For a tenant inbox ordered newest first, an index on (tenant_id, created_at DESC, id DESC) supports tenant equality and cursor range. A created_at-only index may scan other tenants. A write-heavy event store may favor LSM throughput, while read-heavy range queries may favor B-tree locality.

Failure modes and operations

Page splits, dead tuples, vacuum lag, compaction debt, tombstones, WAL growth, replica lag, lock contention and connection exhaustion can dominate. An index can be unused when predicates do not match leading columns. A sequential scan may be optimal for a query returning much of the table.

Trade-offs and design boundary

B-trees offer ordered range access with page maintenance. LSM designs trade write amplification and read amplification differently and need compaction capacity. Covering indexes reduce some reads but increase storage and write cost.

Interview transfer exercise

Choose an engine for a high-write time-series workload. Explain retention, late writes, query windows, compaction, partitioning, recovery and the expected hot-key distribution.

Read the diagram

Commit is recorded in WAL → B-tree updates ordered pages → LSM flushes memtable into sorted runs → Compaction merges runs and obsolete versions

View full sizeOpen original

Database Engines and Write Amplification

Reset viewClose

Mechanism and decision flow for Database Engines and Write Amplification.