Counting at scale

The problem: 8.6 billion clicks a day

At 100,000 redirects a second, Snip records about 8.6 billion clicks a day. Owners want, per link: total clicks, clicks per minute for a chart, unique visitors, top countries and referrers, and a live counter during launches. Counting sounds trivial. At this rate it's one of the hardest write workloads Snip has.

Why the obvious way fails

UPDATE links SET clicks = clicks + 1 WHERE code = $1 per click means 100,000 writes a second to the primary, and a viral link's row takes thousands of updates a second, each waiting for the previous one's row lock (02.06 Async workers and queues). Every fix below reduces either how many writes reach storage or how many go to the same place.

The ladder of fixes

1. Batch. Clicks go to a log; consumers add up a few thousand at a time and write one update per code (02.06 Async workers and queues). Thousands of writes become one.
2. Pre-aggregate at the edge. Each app server keeps a small map of code → clicks for the last second and sends one event with counts, so a viral link becomes one event per server per second instead of 50,000 (07.04 Partitions, consumer groups and ordering).
3. Split the hot counter. If one key's batched updates still collide, keep N rows and sum on read (05.02 Hot partitions and celebrity keys).
4. Count in memory, persist periodically. A Redis INCRBY per batch is fast enough for a live counter. Copying it to Postgres every few seconds is write-behind (06.02 Cache-aside, write-through, write-behind): fine for a display, risky as the only record.
5. Store time buckets, not one total. Write (code, minute, clicks) rows rather than updating one ever-growing total. Inserts spread out, charts come for free, and the total is a sum (11.02 Time-window aggregation).

Snip combines them: per-second pre-aggregation in app servers, a clicks topic partitioned by code, consumers that write per-minute buckets with their offsets in the same transaction (07.03 Delivery guarantees and idempotent consumers), and Redis for the live number.

Decide what must be exact

Exactness costs. Decide per count:

Exact counts get the durable path (log, idempotent consumer, buckets in the database). Approximate counts can use memory and sketches, small structures that answer within a known error using a tiny fraction of the space.

Four kinds of count

Almost every analytics question is one of four, and each has its own tool:

• How many? A sum: buckets and batching, this lesson.
• How many different? Distinct counts, like unique visitors: HyperLogLog (11.03 HyperLogLog).
• Which are the most common? Heavy hitters, like top links: count-min sketch and top-k (11.04 Count-min sketch and top-k).
• How is it spread? Distributions, like latency percentiles: histograms and sketches (11.05 Percentiles and t-digest).

All four are usually computed over time windows (11.02 Time-window aggregation), and all are derived from raw events that are kept somewhere cheaper (11.06 Raw events vs derived data).

Recap

Interview questions