The Challenge of Counting at Scale
Counting seems simple: UPDATE table SET count = count + 1 WHERE id = 123. However, in a distributed system with millions of events per second, this synchronous, locked approach creates severe bottlenecks. Database row locks lead to contention, timeouts, and system failure.
Batching and Asynchronous Processing
To scale counting, we must decouple the event generation from the database update. Instead of updating the database immediately, events (like clicks, views, or impressions) are pushed to a high-throughput message queue like Apache Kafka.
Consumer services read from this queue, buffer the events in memory for a short window (e.g., 5 seconds), aggregate them (e.g., "Video A got 500 views"), and then execute a single batch update to the database. This reduces database write load by orders of magnitude.
Idempotency and Exactly-Once Processing
Distributed queues commonly provide at-least-once delivery, so a worker can crash after incrementing the aggregate but before committing its input position. A retry then increments twice. Make the aggregate update and a durable event/partition receipt atomic, or publish immutable per-partition deltas and replace a partition's prior contribution during reconciliation. A separate cache check followed by an increment has its own crash race unless both actions share one atomic boundary.
Concrete tradeoff: sharding one viral post's counter across 64 keys reduces write contention, but reads must sum 64 values and repairs must know which shards are complete. Keep the accepted events or durable deltas long enough to rebuild a disputed total.
Worked example: a viral post
A post receives 50,000 likes per second at peak. A single relational row under heavy contention might sustain on the order of a few thousand updates per second, far short of what is needed.
| Design | DB writes per second | Read cost | Freshness |
|---|---|---|---|
| Per-event UPDATE on one row | 50,000, fails | 1 row | Immediate |
| Sharded counter, 64 keys | About 780 per key | Sum 64 keys, or cache the sum | Immediate |
| Kafka plus 1 s in-memory aggregation, 32 partitions | 32, one per partition per second | 1 row | About 1 to 2 s behind |
| Redis INCR in front, periodic flush to DB | Redis handles about 50,000 easily; DB sees one write per flush | 1 key | Immediate in Redis; DB lags |
Making the batch idempotent
The consumer for partition 7 aggregates events up to offset 1,204,500 into "post 42: +48,210". It then runs one transaction that both adds 48,210 to the count and records "partition 7 processed through offset 1,204,500". If it crashes before the commit, nothing was applied and the batch is replayed. If it crashes after, the stored offset tells the restarted consumer to skip those events. Kafka transactions or a Flink sink with two-phase commit achieve the same effect.
Other options
- CRDT counters: a G-counter keeps one slot per replica and merges by taking the per-slot maximum; a PN-counter pairs two G-counters for decrements. Used for multi-region active-active counting, for example Redis Enterprise active-active and Riak.
- Approximate display: most products show "1.2M likes"; exact counts matter only for billing or payouts, which can come from a slower batch job.
Failure mode to name in interviews: hot partitions. If all events for one viral key hash to one Kafka partition, that consumer becomes the bottleneck; salt the key with a small random suffix and merge the salted partial counts downstream.