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.
On this page
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:
- Spread: split the data so several machines each take a share of the writes (sharding).
- Batch: turn many small writes into fewer large ones.
- Defer: accept the write into something fast and durable (a queue or log), and apply it to the database at a steady pace.
- 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
| Tool | Good at | Weak at | Use when |
|---|---|---|---|
| Sharding | Linear write scaling; bigger datasets | Cross-shard queries, resharding, hot keys | Sustained load beyond one primary |
| Batching | 10×+ on the same hardware | Adds delay; memory lost on crash | Many small writes to the same table |
| Queue in front | Absorbs bursts; decouples producers | Eventual consistency; duplicates | Bursty load; writes that can be a little late |
| Write-behind cache | Lowest write latency | Can lose recent writes | Counters, presence, "last seen" |
| LSM store | Fast sequential ingest | Slower reads, compaction cost | Append-heavy data: events, messages, metrics |
| Fewer indexes | Cheaper every write | Slower or impossible queries | Write-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:
| Cause | Example | Fix |
|---|---|---|
| Time-based key | Partition by date or hour | Add a high-cardinality part: (sensor_id, day) |
| Increasing key in a range-partitioned store | Auto-increment IDs in HBase or Bigtable all land in the last range | Hash or salt the key (prefix with hash(id) % 16) |
| One popular entity | A celebrity's like counter | Split it into N sub-keys and sum on read |
| Low-cardinality key | Partition by country or status | Choose 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
- Design Uber: drivers send location every few seconds; updates go to an in-memory geo index, sharded by region, not to the main database.
- Design WhatsApp: billions of messages a day in a store sharded by conversation.
- Design YouTube: view counts aggregated in batches rather than one update per view.
- Design a game leaderboard: score updates at high rates, batched into sorted sets.
- Design a web crawler and Design a notification system: queues absorbing large, bursty write volumes.
In the interview
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 byuser_idor 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
+Nonce. 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.
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.
- 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.
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?
Replication copies writes; it doesn't divide them. Replicas help reads only. To scale writes you shard, batch, defer or use a cheaper storage engine.
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?
All new events have today's date, so they all land on one shard: a hot partition. A compound key spreads them. More shards keyed by date still put every new write on one shard.
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?
Thousands of updates to one row serialise on its lock. Summing first turns 30,000 writes into one a second. Stricter transactions add cost, and a replica cannot accept writes.
4. Why does an LSM-tree store usually accept writes faster than a B-tree store on the same disks?
Appending and flushing sorted files sequentially avoids scattered page updates. The cost moves to reads and background compaction, which rewrites data (write amplification).
5. Kafka consumers write 80,000 records a second to the database, and producers send 95,000 a second for hours. What will happen?
A queue smooths bursts but cannot fix a sustained deficit: the backlog grows by 15,000 a second until retention deletes unread data. Kafka does not throttle producers to consumer speed.
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?
Write-behind acknowledges before the durable write, so a crash loses up to 30 seconds of confirmed orders. That is fine for view counts but not for purchases.
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?
100,000 × 200 B = 20 MB/s; × 86,400 s ≈ 1.7 TB a day; × 3 replicas ≈ 5 TB.
Score 60% or more and the lesson is marked complete.