Scaling Writes

Sharding, batching, queues as buffers, and write-optimized storage.

Writes are harder to scale than reads: you can't cache them away, and every write must land durably somewhere. When write volume outgrows a single database, these are the standard techniques.

1. Choose a write-friendly store

Before anything else, make sure the database fits the workload.

B-tree databases (Postgres, MySQL) update pages in place; they handle a lot, but random writes and many indexes add up.
LSM-tree databases (Cassandra, ScyllaDB, RocksDB-based stores) turn writes into sequential appends and handle far higher write volumes.
Time-series databases are specialized for append-only metrics.

Also: drop unneeded indexes — every index multiplies the cost of a write.

2. Partition (shard) the writes

Spread writes across many machines by a partition key (see Sharding). The key must distribute writes evenly — a time-based key sends every new write to the same partition.

3. Buffer with a queue

Put a durable queue (Kafka, SQS) between producers and the database:

Client -> [API] -> {Kafka} -> [Writer Workers] -> (Database)

Absorbs bursts — the API accepts writes at peak rate while workers write at a steady rate.
Isolates the database from traffic spikes.
Trade-off: writes become asynchronous. Make sure the product tolerates a short delay before data is visible, and that consumers are idempotent.

4. Batch

Writing 1,000 rows in one batch is far cheaper than 1,000 single-row writes. Batch in the client, in the writer workers, or with periodic flushes.

5. Pre-aggregate

Often you don't need every raw event in the hot database:

Counters — instead of inserting a row per "like", increment an in-memory or Redis counter and flush periodically.
Stream aggregation — a stream processor (Flink) turns 100k click events/second into per-minute aggregates; store raw events cheaply in object storage for audit and reprocessing.

6. Handle hot keys

A single viral post's like counter can receive more writes than one node can handle.

Split the key: write to likes:post:9:{0..15} at random and sum on read.
Buffer and coalesce: accumulate increments in memory for a second and write one update.

[!INTERVIEW] Connect it to the requirements "Clicks arrive at 100k/second at peak, which a single Postgres primary can't absorb. I'll write click events to Kafka partitioned by ad ID, have Flink aggregate per-minute counts, and write those aggregates to the database — about 1,000 writes per second. Raw clicks go to S3 for reconciliation."