System Design: Storage Engines

To avoid checking every file on a read, LSM engines attach a Bloom filter to each SSTable: a compact structure that answers "this key is definitely not here" or "it might be here". A negative answer skips the file at almost no cost; false positives merely cost an unnecessary read.

Compaction strategies

Compaction is where LSM engines spend their tuning effort.

Compaction competes with foreground traffic for disk bandwidth. If writes arrive faster than compaction can keep up, files pile up, reads slow, and the engine may deliberately stall writes to recover. Watching compaction backlog is part of operating an LSM database.

B-tree versus LSM-tree

Neither is "faster" in general: a B-tree database with a well-sized cache handles mixed workloads very well, and a write-heavy LSM store can ingest at rates that would stress a B-tree.

Row stores versus column stores

So far each record has been kept together: all the fields of a row sit next to each other. That is a row store, ideal when you usually want whole rows: "load order 9031 with all its fields".

Analytics asks different questions: "what was the average temperature per site last week?" That touches two columns out of perhaps fifty, across millions of rows, and a row store must read every column of every row to answer it. A column store keeps each column's values together on disk, so the same query reads only the two needed columns.

How to read it: the row layout keeps each record together; the column layout keeps each field together, so an average over temp touches one list.

Values in one column are similar, so they compress very well (dictionary and run-length encoding), and engines process them in batches with vectorised execution. The downside: reconstructing or updating a single whole row touches many column files, so column stores usually load data in large batches and handle single-row updates poorly.

OLTP versus OLAP

This split gives two broad workload families:

Do not run heavy analytics on your primary OLTP database, because long scans compete with user-facing queries for cache and I/O. Copy data to a separate analytical system with change data capture or batch export. Some databases offer both layouts in one product; even then, check how the workloads are isolated.

Time-series data

Metrics, sensor readings and logs have a distinctive shape: writes are almost all appends of the newest timestamps, data is rarely updated, queries ask for a time range and aggregates, and old data loses value.

Time-series engines exploit this:

• Partition by time, so recent data is hot and old partitions are dropped whole, far cheaper than deleting row by row.
• Compress aggressively. Timestamps are regular (store deltas) and neighbouring values are similar, so specialised encodings help a lot.
• Downsample. Keep fine-grained data briefly and coarser rollups for years, retaining trends at lower cost.
• Control cardinality. Each unique combination of labels creates a separate series, so unbounded labels such as user ids can explode series counts and memory. It is one of the most common operational problems with metrics systems.

Dedicated time-series databases exist, and general-purpose databases with time partitioning or column layouts can serve smaller needs.

Denormalisation and schema evolution

Storage layout interacts with data modelling. Relational design normalises data to avoid duplication, then uses joins. At scale, joins across large tables or shards get expensive, so designers denormalise: store data in the shape the query needs, such as a device's site name inside each reading row, accepting duplicated values. The cost is that a rename must update every copy or tolerate staleness, so denormalise deliberately and keep one clear source of truth.

Schema evolution is the other long-term problem. Safe habits:

• Add, do not rewrite. A nullable column or optional field is cheap and backwards compatible; renames and type changes need multi-step migrations (expand, backfill, switch reads, contract).
• Know your online-change behaviour. Some changes lock or rewrite whole tables, which is dangerous on big, busy ones.
• Schemaless is not free. Readers must handle every historical shape, so version records and tolerate unknown fields. Event formats need the same compatibility rules.

Choosing an engine

Work from the workload, not the brand name.

Practical advice: start with a mature general-purpose relational database, measure, and move specific workloads to specialised engines when you can name the bottleneck. Running several engines is normal in large systems, but each one adds operational burden and a data-synchronisation problem.

The same advice as a flowchart.

How to read it: start from the dominant access pattern. Real systems often answer "yes" to more than one question, which is the signal to split the workloads across engines.

In practice: an IoT telemetry platform choosing storage for high-write sensor data

The situation

A building-monitoring platform collects temperature, humidity and energy readings from 200,000 devices, each reporting every 10 seconds, so about 20,000 writes per second on average (assumed). Seven engineers run it on Kubernetes: devices publish to an MQTT broker, Kafka buffers the stream, and ingest consumers write into a single PostgreSQL primary. Grafana dashboards read from the same database.

At roughly 100 bytes per reading (assumed), the stream is 1.728 billion rows and about 173 GB of raw data per day, or roughly 63 TB per year. The single readings table has two B-tree indexes, and a nightly DELETE removes data older than 90 days. Pain: insert latency rises through the day, vacuum and WAL volume are high, deletes leave bloat, and fleet-wide dashboard queries take minutes and slow the device-registry queries that share the primary.

Starting architecture

Options considered

The decision

The latest reading per device is a point lookup, which a columnar store does poorly, so a small Redis hash (or a compacted Kafka topic feeding a table) serves "current value" queries.

Rollout and verification

Kafka is the log, so you can feed both stores from independent consumer groups without application dual writes. Backfill the last 30 days from PostgreSQL, then compare row counts per device and hour between stores daily. Move Grafana panels one at a time, comparing results, and stop PostgreSQL telemetry writes only after two weeks of agreement.

Prove it with: ingest lag per consumer group, rows per second, insert batch size, the number of unmerged data parts and merge backlog, disk usage and compression ratio, dashboard query p95, and the daily count mismatch between stores. Load test at about 5 times average ingest (assumed), since reconnect storms arrive in bursts.

The 2 a.m. failure

The page: "Ingest consumer lag above 10 minutes". A regional network outage ended and about 60,000 devices flushed buffered readings at once. The ingest tier autoscaled to many pods, each inserting very small batches. Every insert creates a new data part, background merges fell behind, and the store began throttling and then rejecting inserts because of too many unmerged parts.

First three checks:

1. kafka-consumer-groups.sh --bootstrap-server <broker> --describe --group telemetry-ingest to measure lag per partition.
2. In the store, count active parts per table (in ClickHouse-style systems, a query on system.parts) to confirm the merge backlog.
3. kubectl get hpa -n ingest to see how far the autoscaler scaled the consumers.

Mitigation: cap the replica count (consumers beyond the partition count add nothing anyway), raise the minimum batch (for example flush by row count or every second, whichever comes first), and let merges drain; lag then falls on its own. Follow-ups: enforce batching in the ingest library, bound autoscaling, alert on part count as a leading indicator, and add a rehearsal of a reconnect storm to the load test. This is the LSM compaction trade-off earlier in this lesson, seen from the operator's chair.

Architect's notes

• Measure the query mix before picking an engine. Count point lookups, range scans and fleet aggregates per day. A dashboard that runs once an hour does not justify the same machine as 20,000 inserts per second.
• Ask what the retention and the audit rules are. Retention decides cost more than the engine does; legal hold on raw data changes the archive design.
• The cost trap is the second copy. Two stores, Kafka retention, archives and backups can exceed the original database bill; model total bytes stored, not one table.
• Own the batching contract. One team must own how writers batch, because bad batching can take down a healthy store.
• Test restore, not backup. A columnar store restore is a different procedure from PostgreSQL; rehearse it, including the archive replay path.
• Defer sharding the new store. One well-sized node group may carry this load for a long time; shard when measured ingest or storage demands it.

Common mistakes

• Choosing a store by popularity. The engine's layout must match your access pattern. Define reads, writes and queries first.
• Running analytics on the OLTP primary. Long scans evict cache and slow user requests. Replicate or export to an analytical system.
• Over-indexing a B-tree table. Every index must be updated on each write, increasing write cost and storage. Index for queries you actually run.
• Ignoring compaction in LSM systems. Backlogs cause read slowdowns and write stalls. Monitor pending compaction and disk headroom, including temporary space during merges.
• Heavy deletes with tombstones. Mass deletions can slow range reads. Prefer time-partitioned data that can be dropped whole, or schedule compaction.
• Unbounded metric labels. Using user ids as labels explodes cardinality. Keep labels to bounded sets.
• Disabling durability for speed without understanding it. Relaxing log flushing can lose acknowledged writes on a crash. Make the choice consciously per workload.

Key takeaways

• Engine choice is a trade-off among three amplifications. Write, read and space costs move against each other.
• B-trees update pages in place. They give steady point reads and range scans, with write amplification from page rewrites.
• The WAL makes writes durable. Append and flush first, apply later, replay after a crash; it also powers replication and change capture.
• LSM-trees turn random writes into sequential ones. Memtable, SSTables, Bloom filters and compaction trade write cost for fast ingest.
• Compaction is the operational heart of LSM engines. Strategy choice shifts cost between reads, writes and space.
• Row stores serve whole-row transactions; column stores serve scans. Compression and skipping unused columns make analytics fast.
• Keep OLTP and OLAP separate. Copy data to a system built for analysis instead of straining the primary.
• Plan for denormalisation and schema change. Both are long-term costs you pay for read speed and flexibility.

Practice exercises

Exercise 1 — Starter (30 min): A table has 50 columns and 100 million rows (assumed). A report needs the average of one numeric column grouped by one categorical column. Explain in steps how many columns a row store and a column store each must read, and estimate the ratio of data read, assuming equal-width columns.

Exercise 2 — Intermediate (2 hours): Trace by hand an LSM engine handling these operations with a memtable that flushes every 3 keys: put a=1, b=2, c=3, put a=4, delete b, put d=5, put e=6. Draw the memtable and SSTables after each flush, show where a tombstone appears, what a read of a and of b checks, and what one compaction of all SSTables produces.

Exercise 3 — Advanced (half day): Design storage for an IoT platform ingesting readings from 200,000 devices every 10 seconds (assumed). Choose the engine type and justify it, propose the time partitioning and retention, a downsampling scheme with dates for each tier, an approach to label cardinality, how dashboards query recent versus historical data, and how you would back up and restore. Estimate daily row count and raw storage using stated assumptions about bytes per reading.

Related reading

• Databases basics — indexes, transactions and the relational model this lesson builds on.
• Replication — how the write-ahead log becomes the replication stream.
• Sharding — splitting data across several engines once one node is not enough.
• Search and indexing — segments and merging, an LSM-like idea applied to text.
• Observability and SLOs — time-series data in practice, including cardinality limits.