Engineering Atlas › System Design Interviews

Handling write-heavy workloads

13 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Find the ceiling first

Before choosing a technique, divide. If a ride-hailing app ingests 1.2 million GPS pings per second and one Postgres primary on good NVMe sustains perhaps 20,000 to 50,000 small durable inserts per second, no tuning closes a 25x gap. If a review site takes 300 writes per second, one primary is fine and everything below is overkill. Say the arithmetic out loud.

Partition by a key that matches the write

Sharding splits the key space so each node owns a slice of the writes. The partition key needs high cardinality, a roughly uniform write rate per value, and should co-locate data that one transaction updates together. For GPS pings, driver_id works. For chat, conversation_id keeps a conversation's messages ordered on one shard. A bare timestamp is the classic mistake: every current write lands in the newest range, on one shard. Put a high-cardinality field such as sensor_id before the time, as Bigtable's schema guide advises (Google Cloud docs), or a bucket such as hash(sensor_id) % 64 when the store has no natural high-cardinality prefix. Managed stores publish the per-partition ceiling you are designing against: a DynamoDB partition takes at most 1,000 write units per second, one unit per 1 KB item written, however large the table (AWS docs).

Buffer through a durable log

Put a partitioned log such as Kafka (see the Kafka lesson) between the API and the database. The API acknowledges once the event is replicated in the log, a few milliseconds, and consumers write to the database at the rate it can sustain. A 5-minute spike at 3x normal becomes a backlog that drains over half an hour instead of an outage (Azure queue-based load leveling). The price: the write is durable but not yet queryable, so read-your-own-writes needs care.

Batch and coalesce

Most write cost is per operation, not per byte: a round trip, a commit, an fsync. Grouping 500 rows into one multi-row INSERT or COPY cuts that overhead by orders of magnitude. Kafka producers batch by default: since Kafka 4.0, linger.ms defaults to 5 ms instead of 0, with batch.size at 16 KB, acks=all and idempotence on (Kafka docs). Coalescing goes further for counters and last-value state: 400 like increments for one post in a second become one UPDATE ... SET likes = likes + 400. The hot-keys lesson covers splitting a hot counter; coalescing is the cheaper first step.

Write-behind

A write-behind cache accepts writes in memory and flushes them asynchronously. It is coalescing with a cache attached, acceptable only where losing the last few seconds on a crash is tolerable (view counts, presence, game positions) or the cache itself is replicated and persisted. Databases offer a milder version of the same trade: Postgres with synchronous_commit = off acknowledges before the WAL flush, so a crash can lose up to three times wal_writer_delay (about 600 ms at the 200 ms default) of acknowledged commits, but never corrupts data (Postgres docs).

Write-optimised storage

B-tree engines update pages in place, so random inserts touch scattered pages. LSM-tree engines (RocksDB, Cassandra, ScyllaDB, HBase) append to a write-ahead log, insert into an in-memory sorted table, and later flush whole sorted files, so writes are sequential and batched by construction. Event data often goes further: append-only files in object storage, partitioned by hour, never updated.

Backpressure

Every buffer is finite. When consumers stay slower than producers, something must give. In order of preference: slow the producer (bounded queues, HTTP 429 with Retry-After), coalesce harder, shed low-value writes (debug telemetry, sampled analytics), and only last let the queue grow. A backlog that grows past what consumers can clear becomes its own outage, because the newest work waits behind hours of stale work (AWS Builders' Library).

How it shows up in interviews

Triggers: metrics or logging pipelines, ad click aggregation, location tracking, like counters, chat message stores, IoT ingestion. A strong answer sizes the write rate, justifies the partition key, names the durability point and storage engine, batches, and handles the hot partition. Probes: a 10x spike, one key at 30% of traffic, reading your own write, a consumer crashing mid-batch.

Down the stack

Layer 1The contract

The caller needs to know what "200 OK" means: replicated to min.insync.replicas log brokers (durable, visible within seconds), committed in the database (durable and visible, bounded by database throughput), or accepted by an in-memory buffer (fast, lossy on crash). Buffered pipelines apply writes at least once, so writes must be idempotent via an event ID or an upsert keyed by (entity, sequence). Ordering holds only per partition key.

Layer 2The mechanism

In RocksDB, a Put appends to the WAL, then inserts into the memtable, a skiplist sized by write_buffer_size (64 MB by default, per options.h). A full memtable becomes immutable and is flushed to level 0 as an SSTable: sorted blocks of about 4 KB, an index block and a Bloom filter. Compaction merges files into lower levels, each roughly 10x larger. When level 0 holds too many files (level0_slowdown_writes_trigger, 20 by default) RocksDB delays writes, and at level0_stop_writes_trigger (36) it stops them; the same happens when too many memtables wait to flush or pending compaction bytes pass a soft or hard limit, and a stall in one column family stalls the whole database (RocksDB wiki). That is backpressure inside the engine. Cassandra has the same shape with a commit log and per-table memtables. Kafka producers use linger.ms and batch.size to trade milliseconds for larger compressed batches.

Layer 3The machine

A durable write costs at least one fsync. On a consumer NVMe drive without power-loss protection that is roughly hundreds of microseconds to a few milliseconds; enterprise drives with capacitor-backed caches take tens of microseconds. One sync per row caps a log at a few thousand rows per second, so Postgres, InnoDB and LSM WALs use group commit: many commits share one flush. Flash prefers sequential writes: SSDs program 4 to 16 KB pages but erase much larger blocks, so small random overwrites cause internal garbage collection stalls, while whole SSTables do not. LSM trees pay their own amplification: with leveled compaction a byte may be rewritten tens of times (the RocksDB tuning guide works one 500 GB example out to about 33), so compaction throughput is usually the long-run write ceiling (RocksDB tuning guide).

Why it's built this way

Writes have one owner per key, so throughput scales only by splitting keys across owners. Durable storage charges per sync and per seek, so writes are batched behind one sync and laid out sequentially. Sequential layouts leave stale versions behind, so they need compaction, and compaction competes for the same disk, so engines throttle writers when it falls behind. Producers and consumers run at different speeds, so a durable buffer decouples them, and any buffer is finite, so backpressure must be explicit.

Diagram

flowchart LR
    C["Clients"] --> API["Ingest API"]
    API -->|"key = driver_id"| K["Partitioned log (64 partitions)"]
    API -.->|"429 when lag too high"| C
    K --> W["Consumers: coalesce per key, batch 500"]
    W --> DB["LSM store (sharded by driver_id)"]
    DB --> WAL["WAL append + memtable"]
    WAL --> SST["Flush to SSTable, compact"]

Worked example

A fleet-tracking product has 3 million active vehicles, each sending a 200-byte position every 2 seconds: 1.5 million writes per second, about 300 MB/s of payload. Dashboards need the latest position per vehicle; analytics needs history.

Ingest: a log with 128 partitions keyed by vehicle_id gives about 12,000 events per second per partition. Producers use linger.ms=5 and compression, so each request carries a few hundred events.

Latest position: a 16-node LSM-backed cluster takes about 94,000 upserts per second per node, in batches of 500, so under 200 requests per second per node. History: a second consumer group writes compressed Parquet files to object storage once a minute per partition, keeping append-only data out of the database.

Spike: a firmware bug makes 100,000 vehicles send 20 pings per second, adding 2 million events per second. The log absorbs them and lag climbs, but consumers coalesce each vehicle to one upsert per second, so database load barely moves. If lag passes 60 seconds, the API returns 429 to devices above their per-device rate.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Single primary, bigger boxSimple, transactionalHard ceiling, one failure domainUnder tens of thousands of writes/s
Hash sharding by entity keyEven spread, linear scalingCross-shard transactions and queries hurtHigh-cardinality keys, per-entity access
Durable log in frontAbsorbs bursts, replayableRead-your-writes lag, more moving partsSpiky ingest, many downstream consumers
Coalescing and write-behindHuge reduction for countersLossy on crash unless the buffer is durableCounters, last-value state
LSM storageSequential writes, high ingestCompaction cost, slower point readsWrite-heavy key-value or time series

Pitfalls in production

Leadership lens

Check yourself

  1. Why does adding read replicas not help a write-bound database?

    Show answer

    Every replica must apply every write, so replicas multiply write work rather than divide it. Only splitting ownership across shards divides the writes.

  2. Why is a timestamp a poor partition key for an event stream?

    Show answer

    All current writes share nearby timestamps, so they land on the one partition owning the newest range while the rest idle.

  3. What does group commit buy, and what does it cost?

    Show answer

    Many transactions share one fsync, raising throughput by the batch size; each transaction waits a little longer for the shared flush.

  4. Why do LSM engines stall writes on purpose?

    Show answer

    If flushes and compaction fall behind, read amplification and space grow without bound, so the engine slows writers to let background work catch up.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →