Interview patterns · Design 2

Scaling writes

How to accept more writes than one database can take: sharding, batching, queues, write-behind and LSM stores, with worked numbers, hot partitions and write amplification.

IntermediateInterview priority: High30 min
On this page
  1. The problem: a hundred thousand writes a second, one primary
  2. Signals that you are facing it
  3. The idea: spread, batch, defer
  4. The toolbox
  5. Worked example: the smart-meter ingest
  6. Hot partitions: the key that defeats sharding
  7. When not to use these tools
  8. Which case studies use it
  9. In the interview
  10. Quiz

The problem: a hundred thousand writes a second, one primary

A smart-meter company has 1 million sensors, and each one sends a reading every 10 seconds. That is 100,000 writes a second, all day, with bursts when a region reconnects after an outage. Nobody reads most of the readings until a monthly report.

The first version inserts every reading into one Postgres primary. A well-tuned primary handles thousands to low tens of thousands of simple writes a second, so it falls over at a fraction of the load. Each write is not one disk operation either: the row goes into the table, into every index on it, and into the write-ahead log (WAL, the append-only journal the database flushes to disk before it confirms a commit).

The tricks from scaling reads don't help. A cache can't absorb a write, because the data has to be stored somewhere durable. Read replicas make it worse: every replica must replay every write. Writes have to be spread out, made cheaper, or deferred.

Signals that you are facing it

  • A high write rate in the estimates: more than ~5,000 writes a second sustained, or a dataset growing by terabytes a day.
  • Append-heavy data: events, logs, metrics, GPS pings, chat messages, clicks, likes.
  • Bursty traffic: a flash sale, a live event, devices reconnecting together.
  • Counters on popular things: views, likes, votes, stock levels.
  • A write-to-read ratio near or above 1:1: data is written once and read rarely, or in bulk.

Say it early: "This is write-heavy, so a single primary won't do. I'll shard by a key that spreads writes evenly, batch them, and use a store built for appends."

The idea: spread, batch, defer

There are only a few levers, and strong designs combine them:

  1. Spread: split the data so several machines each take a share of the writes (sharding).
  2. Batch: turn many small writes into fewer large ones.
  3. Defer: accept the write into something fast and durable (a queue or log), and apply it to the database at a steady pace.
  4. Cheapen: use a storage engine that turns random writes into sequential ones, and drop indexes the write path doesn't need.

The toolbox

Sharding

Sharding (also called partitioning) splits one dataset across several independent databases, each owning a slice of the keys. A router (in the app, a proxy, or the database itself) uses the shard key to decide where each row lives. Four shards means each primary takes about a quarter of the writes. See sharding for hashing and resharding.

Sharding spreads writes, unless the key concentrates them

The shard key decides everything. A good key has many distinct values, evenly used, and matches how you read: sensor_id spreads writes and keeps one sensor's history together. A bad key concentrates traffic. Sharding by date sends every new reading to today's shard; sharding by country puts most rows on a few shards.

Catch: queries that span shards become scatter-gather (ask every shard, merge the answers); transactions across shards need extra machinery; adding shards later means moving data.

Batching

A single-row insert pays for a network round trip, a transaction, and a WAL flush to disk. One insert of 500 rows pays for those once. Batching commonly gives 10× or more throughput on the same hardware. In Postgres, multi-row INSERT or COPY does it; Kafka producers batch by default (linger.ms, batch.size).

Counters batch especially well: instead of 500 separate like_count = like_count + 1 updates on one row, a worker sums them in memory for a second and writes +500 once.

Catch: a batch adds delay (you wait to fill it), and a crash loses whatever was in memory and not yet written, unless the batch was built from a durable source such as a queue.

Queues and write-behind

Put a durable log, such as Kafka, in front of the database. Producers append to it, which is cheap and sequential; consumers read at the rate the database can sustain, in batches. A burst becomes a backlog (a queue that drains a little later) instead of an outage. See message queues.

A queue absorbs bursts; workers batch what they write

Write-behind (or write-back) is the cache-based version: the app writes to Redis, acknowledges the user, and a background job flushes changes to the database later. It is fast, but data that was only in Redis memory is lost if the node dies before the flush. Use it for data you can afford to lose a few seconds of (view counts, "last seen" times), not for orders or payments.

Catch: the write is accepted before it is stored in the final database, so reads may not see it yet (eventual consistency), and consumers must handle duplicates (see idempotency).

Write-optimised storage: LSM trees

Postgres and MySQL store tables in B-trees, sorted structures that are updated in place: inserting a row can touch several pages scattered across the disk. Stores such as Cassandra, ScyllaDB, HBase and RocksDB use LSM trees instead.

An LSM tree turns random writes into sequential ones

Reads are the price. A key may live in the memtable and in several SSTables, so a read checks them newest first. Bloom filters (small in-memory structures that answer "definitely not in this file" or "maybe") let it skip most files, and compaction keeps the count down.

Write amplification is the ratio of bytes written to disk to bytes the application wrote. Every engine has some: a B-tree writes the WAL and then whole pages; an LSM tree writes the log, the flush, and each compaction. Replication multiplies it again (×3 with three replicas), and every secondary index adds a write. Counting it matters when you size disks and pick SSDs that wear out with writes.

Comparing the tools

ToolGood atWeak atUse when
ShardingLinear write scaling; bigger datasetsCross-shard queries, resharding, hot keysSustained load beyond one primary
Batching10×+ on the same hardwareAdds delay; memory lost on crashMany small writes to the same table
Queue in frontAbsorbs bursts; decouples producersEventual consistency; duplicatesBursty load; writes that can be a little late
Write-behind cacheLowest write latencyCan lose recent writesCounters, presence, "last seen"
LSM storeFast sequential ingestSlower reads, compaction costAppend-heavy data: events, messages, metrics
Fewer indexesCheaper every writeSlower or impossible queriesWrite-mostly tables read in bulk

Worked example: the smart-meter ingest

Load: 1 M sensors ÷ 10 s = 100,000 writes a second. At 200 bytes per reading that is 20 MB/s, about 1.7 TB a day before replication and compression.

Front door: producers append readings to Kafka, keyed by sensor_id so each sensor's readings stay in order. One Kafka partition handles tens of MB/s, so throughput needs only a few, but consumer parallelism wants more: say 32 partitions, one consumer each.

Storage: an LSM store such as Cassandra, with partition key (sensor_id, day) and rows sorted by timestamp. The day part stops a partition growing forever; sensor_id spreads writes evenly. With replication factor 3, the cluster absorbs 300,000 replica writes a second. If a node sustains roughly 20,000–30,000 such writes a second on this hardware (measure it; it varies a lot), that is 10–15 nodes, plus headroom for compaction and a failed node: about 18 nodes.

Disk: 1.7 TB × 3 replicas ≈ 5 TB a day; with 4× compression, about 1.3 TB a day. Keeping 90 days of raw readings needs roughly 120 TB, so older data is downsampled (hourly averages) or moved to cheap object storage.

The burst: when 200,000 sensors in a region reconnect and upload an hour of buffered readings, the queue grows; consumers drain it over minutes. Monitor consumer lag (how far consumers are behind the newest message) and alert if it keeps growing.

Hot partitions: the key that defeats sharding

A hot partition is a shard that receives far more traffic than its neighbours. It caps the whole system: ten shards with one hot one behave like one shard.

Common causes and fixes:

CauseExampleFix
Time-based keyPartition by date or hourAdd a high-cardinality part: (sensor_id, day)
Increasing key in a range-partitioned storeAuto-increment IDs in HBase or Bigtable all land in the last rangeHash or salt the key (prefix with hash(id) % 16)
One popular entityA celebrity's like counterSplit it into N sub-keys and sum on read
Low-cardinality keyPartition by country or statusChoose a different key, or a compound one

The hot keys lesson covers detection and the celebrity case in depth.

When not to use these tools

  • You're not at the limit. A single Postgres primary with batching and sensible indexes takes thousands of writes a second. Sharding brings permanent complexity; buy a bigger machine first.
  • You need multi-row transactions across the data. Sharding breaks easy cross-row atomicity; if most writes touch several entities that can't share a shard key, rethink the model before sharding.
  • The write must be visible immediately. Queues and write-behind make writes eventually visible; a bank transfer or a seat booking should commit synchronously.
  • Reads dominate. If the store is read 100× more than written, an LSM store's read cost hurts more than its write speed helps.

Which case studies use it

In the interview

Your turn: likes on a viral post

A social app records likes. Normally 2,000 likes a second spread over millions of posts, but a viral post gets 50,000 likes a second for an hour. Each like must be stored (who liked what) and the post shows a like count. Design the write path.

Compare with our answer
  • Store each like as a row keyed by (post_id, user_id): inserts spread well if the likes table is sharded by user_id or by (post_id, user_id) hash, and the key makes repeat likes idempotent.
  • The counter is the hot spot: 50,000 updates a second on one row is far beyond what a single row can take with row locks.
  • Fix: aggregate before writing. Likes go to Kafka; consumers sum per post every second and apply +N once. Or split the counter into 32 sub-counters (post:42:c0..c31), increment a random one, and sum on read (cache the sum for a second).
  • The displayed count is a little stale (seconds), which is fine for likes.
  • Consumers must be idempotent (dedupe on (post_id, user_id)) so a retry does not double-count.

A strong answer mentions: separate the like record from the count · 50 K/s on one row is the bottleneck · batching or sharded counters · queue in front · seconds of staleness is acceptable · idempotency on retries.

Your turn: the requirement changes

The smart-meter company now wants a live dashboard: for each sensor, the latest reading within 2 seconds of it being sent, and an alert if any reading crosses a threshold. The current design writes to Cassandra through Kafka with consumer lag of up to a few minutes during bursts. What changes?

Compare with our answer
  • The bulk path can stay as it is: minutes of lag is fine for history.
  • Add a second, fast consumer group on the same Kafka topic that only keeps the latest value per sensor, in memory or in Redis (a hash keyed by sensor_id): one small overwrite per reading, 100 K/s fits a few Redis shards.
  • Alerts run in that consumer (a stream processor), checking each reading as it arrives.
  • During bursts, the fast consumer must not fall behind: give it its own consumers and skip stale buffered readings older than the latest stored value.
  • The dashboard reads Redis, not Cassandra.

A strong answer mentions: separate hot path from bulk path · a second consumer group reading the same log · latest-value store overwritten in place · alerting in the stream · skipping stale readings during a backlog.

Scaling writes on one page
  • Trigger: > ~5 K writes/s sustained, TB/day growth, append-heavy data, bursts, hot counters.
  • Caches and replicas don't scale writes; replicas replay every write.
  • Levers: spread (shard), batch, defer (queue, write-behind), cheapen (LSM, fewer indexes).
  • Shard key: high cardinality, even use, matches reads. Never time alone. Salt increasing keys.
  • Batching: one 500-row insert costs about the same as a few single-row ones.
  • Queue: consumers must beat producers on average; alert on consumer lag.
  • Write-behind loses unflushed data: fine for counts, never for money.
  • LSM: append + memtable + SSTables + compaction; fast writes, slower reads, write amplification.
  • Numbers: 100 K writes/s × 200 B = 20 MB/s ≈ 1.7 TB/day, ×3 replicas.
Check what stuck

1. A team adds three read replicas to a Postgres primary that is at 95% CPU from inserts. What happens to the primary's write capacity?

2. An events table is sharded by event date across 30 shards. Writes are slow even though 29 shards are nearly idle. What is the best fix?

3. A view counter is updated 30,000 times a second for one popular video and the row has become a bottleneck. Which change helps most?

4. Why does an LSM-tree store usually accept writes faster than a B-tree store on the same disks?

5. Kafka consumers write 80,000 records a second to the database, and producers send 95,000 a second for hours. What will happen?

6. A team plans to keep shopping-cart checkouts in Redis and flush them to the database every 30 seconds to cut write load. What is the main risk?

7. A store has 100,000 writes a second at 200 bytes each, replication factor 3. Roughly how much raw data lands on disks per day before compression?

Score 60% or more and the lesson is marked complete.

Passing the quiz also marks it complete. Progress is saved in this browser only.