Counting at scale
How to count likes, views and unique visitors at huge rates: sharded counters, batching, windowed stream aggregation, HyperLogLog, count-min sketch and top-k.
On this page
The problem: one number, fifty thousand writers
A post goes viral and collects 50,000 likes a second. The first version stores the count in the post's row and runs UPDATE posts SET like_count = like_count + 1 WHERE id = 42 for each like.
Every one of those updates needs the row lock (the database's guard that lets only one transaction change a row at a time) and holds it until it commits. At roughly a millisecond per commit, one row tops out somewhere around a thousand updates a second, however large the machine. The other 49,000 wait, time out and retry, while connections pile up and slow down every other query on the database.
Views are worse: a video platform logs hundreds of thousands of view events a second. And product asks for more than totals: unique viewers, views in the last hour, and the top 10 trending videos right now.
Signals that you are facing it
- Counters on popular items: likes, views, followers, votes, stock reservations, rate limits.
- Bursty, skewed traffic: one item gets a large share of all increments.
- "Unique" or "distinct": unique visitors, distinct searches, daily active users.
- Time windows: "in the last 5 minutes", "per hour", "trending today".
- Ranking: top-k, leaderboards, heavy hitters (the few items with most of the traffic).
- A tolerance for approximation: nobody can tell 1,203,554 views from 1,203,561.
The idea: don't make every event touch the same place
All the techniques below do one of three things: spread the writes over many places (sharded counters), combine many events into one write (batching and windowed aggregation), or summarise the data in a small, approximate structure (HyperLogLog, count-min sketch). Ask early: must this count be exact, and must it be current? A bank balance must be both. A view count needs neither.
The toolbox
Sharded counters
Split one counter into N sub-counters (shards). Each increment picks one at random; a read sums them.
One hot row, then the same count split into shards
-- write: pick shard = random(0..63) in the app
INSERT INTO like_counts (post_id, shard, n) VALUES (42, 17, 1)
ON CONFLICT (post_id, shard) DO UPDATE SET n = like_counts.n + 1;
-- read: sum the shards (cache this result for a few seconds)
SELECT SUM(n) FROM like_counts WHERE post_id = 42;
64 shards turn 50,000 increments a second into about 800 per row. Catch: reads cost N rows, so cache the sum; and you only need many shards for the few hot items, so some systems start with one shard and add more when an item gets hot.
In Redis the same idea is INCR on keys likes:42:0 to likes:42:63. A single Redis key can take tens of thousands of increments a second, but all of them land on one node, which becomes a hot key.
Batching: count in memory, write the total
Each app server keeps a local map of post_id → pending increments and flushes it every second or two as one UPDATE ... SET like_count = like_count + 312. With 200 app servers flushing every second, the hot row sees at most 200 updates a second, whatever the like rate.
Catch: a server crash loses its unflushed increments. That is fine for views, not for anything billed. The durable version writes each event to a log first (below).
Windowed stream aggregation
Send every event to a durable log such as Kafka, then let a stream processor (Flink, Kafka Streams, Spark Structured Streaming) count them. It groups events into windows: time buckets over which a count is computed.
| Window | Shape | Example |
|---|---|---|
| Tumbling | Fixed, back-to-back, no overlap | Views per video per minute |
| Hopping / sliding | Fixed length, overlapping (5 min, advancing every 1 min) | "Views in the last 5 minutes", updated each minute |
| Session | Ends after a gap of inactivity | Actions per user visit |
Aggregating view events in one-minute windows before writing
Two details separate a working pipeline from a buggy one:
- Event time versus processing time. A phone that was offline sends its views 10 minutes late. Counting by event time (when the view happened) puts them in the right window; the processor uses a watermark (its estimate of "all events up to time T have now arrived") to decide when a window can be closed, and sends late events to a correction path.
- Duplicates. Logs deliver at least once, so a restarted processor can count some events twice. Flink's checkpoints with transactional Kafka output give exactly-once results inside the pipeline; at the sink, write each window's result idempotently (
SET count = 4120forvideo 7, 12:01) rather than adding to it.
HyperLogLog: counting distinct things
Counting unique viewers exactly means remembering every viewer ID. For a video with 50 million unique viewers that is 50 M × 8 bytes = 400 MB for one number. HyperLogLog (HLL) estimates a distinct count from a fixed, tiny summary: it hashes each item and tracks, across many buckets, the longest run of leading zero bits seen, which grows predictably with the number of distinct items.
Redis implements it as PFADD and PFCOUNT: at most 12 KB per counter with a standard error of 0.81%, whether it has seen 1,000 or a billion distinct items. HLLs also merge (PFMERGE): the union of 30 daily HLLs gives monthly uniques without double-counting people who came on several days. You cannot remove an item, and you cannot ask whether a specific user is in it.
Count-min sketch: frequencies in fixed memory
A count-min sketch answers "roughly how many times has this item appeared?" It is a small grid of counters, d rows by w columns, with one hash function per row. Adding an item increments one counter in each row; querying takes the minimum of the item's d counters. Collisions can only add, so it never undercounts, only overcounts.
With w = 2,719 and d = 7 (about 76 KB of 4-byte counters), estimates are within 0.1% of the total event count with probability 99.9%. It is the usual building block for heavy hitters: keep a sketch of all items plus a small heap of the current top k candidates.
Top-k
For trending lists, count per item in windows (above), then keep the k largest. If events are partitioned by item, each partition holds complete counts for its items, so merging each partition's local top k gives the exact global top k.
Merging per-partition top-k lists
Comparing the tools
| Tool | Exact? | Memory | Good at | Weak at |
|---|---|---|---|---|
| Single row / key | Yes | Tiny | Low write rates | Hot items: lock or single-node limit |
| Sharded counter | Yes | N rows | Hot exact counters | Reads sum N values |
| In-memory batching | Loses unflushed data on crash | Small | Huge rates, cheap | Crash loss; seconds of delay |
| Stream aggregation | Yes, with care | State per key per window | Windows, trends, durability | Operating a stream processor; late data |
| HyperLogLog | ±0.81% | 12 KB per counter | Distinct counts, mergeable | No deletes, no membership test |
| Count-min sketch | Overcounts only | Fixed (KB) | Frequencies, heavy hitters | Rare items look bigger than they are |
Worked example: view counts for a video platform
200,000 view events a second at peak. In any one minute, about 1 million distinct videos receive at least one view.
- Per-event writes: 200,000 database increments a second, concentrated on the most popular videos. Not viable.
- One-minute tumbling windows: one upsert per video per minute = 1 M per minute ≈ 17,000 writes a second, spread over many rows. Hot videos cost one write a minute, not thousands a second.
- Kafka sizing: 200,000 events × 100 bytes = 20 MB/s, a handful of partitions by throughput; use more (say 64) so the aggregators can scale out, partitioned by video ID so each video's count lives in one aggregator.
- Uniques: one HLL per video per day. If 5 M videos are viewed daily, that is up to 5 M × 12 KB = 60 GB in the worst case; Redis's sparse encoding stores HLLs with few distinct items in a few hundred bytes, so the real figure is much smaller.
- Display: the page shows the stored count plus nothing more recent; "1.2M views" hides the minute of delay.
When not to use it
- The rate is modest. A few hundred increments a second on a well-indexed row are fine. Don't build a stream pipeline for a blog.
- The number is money. Balances and inventory need exact, transactional updates, ideally as a ledger of entries summed on demand, not an approximate counter.
- You need to know who. HLL and sketches answer "how many", never "which ones". "Has this user already liked this?" needs the actual likes table (with a unique constraint on user and post), and the count is derived from it.
Which case studies use it
- Design YouTube: view counts through a log and windowed aggregation; uniques with HLL.
- Design Twitter / X timeline and Design Instagram: like and follower counts on celebrity accounts as sharded or batched counters.
- Design a game leaderboard: exact scores in a sorted set, sharded when one board gets too big.
- Design search autocomplete: query frequencies and top-k per prefix, from windowed aggregation.
In the interview
A social network gets 100,000 posts a second, each with 0–5 hashtags. Show the top 20 hashtags over the last hour, refreshed every minute, in every country. Design the counting pipeline.
Compare with our answer
- Extract hashtags into events
(hashtag, country, event_time): up to ~300,000 a second. Publish to Kafka partitioned by (country, hashtag). - A stream job counts in hopping windows: one hour long, advancing every minute (or keep 60 one-minute tumbling counts per key and sum the last 60).
- Each partition keeps a local top 20 per country; a merge step combines them. Partitioning by hashtag makes the merge exact.
- For the long tail, a count-min sketch plus heap bounds memory; tiny hashtags never need exact counts.
- Use event time with a watermark so delayed posts land in the right minute. Write results to Redis per country; reads are a single key.
- Filter spam and bots before counting, and maybe normalise (case, accents).
A strong answer mentions: an event rate estimate · partitioning by hashtag so merges are exact · hopping windows or summed minute buckets · sketch + heap for bounded memory · event time and watermarks · results precomputed per country.
A news site tracks daily unique visitors per article with one Redis HyperLogLog per article per day. To show monthly uniques, the dashboard adds the 30 daily PFCOUNT results together. Readers who visit daily see numbers far higher than the site's analytics vendor reports. What's wrong?
Compare with our answer
Adding daily unique counts counts a reader who came on 20 days 20 times. Monthly uniques are the size of the union of the daily sets, not the sum of their sizes.
A strong answer mentions:
- Sum of distinct counts ≠ distinct count of the union.
- HLLs are mergeable:
PFMERGEthe 30 daily HLLs (or callPFCOUNTwith all 30 keys) for monthly uniques. - The result keeps the same ~0.81% standard error.
- Optionally maintain a monthly HLL directly by
PFADDing into both daily and monthly keys. - Check the definition of "unique" (cookie, logged-in user, device) matches the vendor's before comparing.
- One hot row: every increment waits for the row lock, roughly a thousand per second at best.
- Ask first: exact or approximate? Instant or seconds late?
- Sharded counter: N sub-counters, write one at random, read = cached sum.
- Batching: count in memory, flush every second; loses unflushed counts on crash.
- Stream aggregation: Kafka keyed by item → windows (tumbling, hopping, session) → one upsert per key per window. Event time + watermarks; idempotent sink writes.
- HyperLogLog: distinct counts, 12 KB, 0.81% error, mergeable (PFMERGE), no deletes.
- Count-min sketch: frequencies in fixed memory, overcounts only; with a heap for heavy hitters.
- Top-k: partition by item, merge local top-k exactly; partitioned otherwise, it's approximate.
- Counts that gate actions (seats, limits) need one atomic decision point or fixed allocations.
1. A post receives 30,000 likes a second and the database shows long waits on the posts table. The like count must be exact. What fits best?
The bottleneck is writes queuing for one row lock. Sharding the counter spreads them across rows. Caches and replicas only help reads; HLL counts distinct items approximately.
2. A dashboard needs the number of distinct users who searched each day, for 10,000 search terms. Exact sets would use about 2 TB. What fits best?
HyperLogLog estimates distinct counts in fixed small memory. A counter counts searches, not distinct users; a count-min sketch gives frequencies; a Bloom filter answers membership, not cardinality.
3. Trending is computed by partitioning view events by user ID, taking each partition's top 10 videos, and merging those lists. What is the flaw?
Each partition sees only part of each video's views, so local top lists don't contain the global winners. Partition by video so each count is complete, or accept an approximate answer.
4. A stream job counts views per minute by processing time. After a mobile network outage, 10 minutes of buffered views arrive at once. What happens?
Processing-time windows assign events to when they are processed. Event-time windows with a watermark (and late-event handling) put them in the minute the view happened.
5. A count-min sketch reports that a rare product was viewed 1,450 times today, while the exact logs say 30. Why?
Cells are shared, so each of the product's cells also holds counts of colliding items; the minimum reduces but cannot remove that. Sketches only ever overcount, and errors are large relative to rare items.
6. A video site has 120,000 view events a second. About 600,000 distinct videos get views in a typical minute. With one-minute tumbling windows, roughly how many writes a second reach the counts database?
One upsert per video per window: 600,000 per minute ÷ 60 ≈ 10,000 writes a second, instead of 120,000.
Score 60% or more and the lesson is marked complete.