Click counting off the hot path

The easy way to count clicks is to run UPDATE links SET clicks = clicks + 1 on every redirect. That turns your busiest table into a write hot spot, and it ties redirect availability to analytics. The fix is to emit a small event per click and count those events somewhere else.

The big idea

Think of a toll booth that waves cars through and drops a tally slip into a box. Someone else counts the slips later, so the booth never waits on the counting.

The picture breaks in one place. Slips can be lost or counted twice. For dashboards that is usually fine. For billing, it is not.

Walking through it

The redirect service writes one small event per click, then returns the 302 without waiting for the analytics store.

Each event carries the code, a timestamp, a hashed IP, user agent, referrer and country. Workers group events into minute buckets and add to the totals. Here is one minute for one code:

Events received, 10:01:00 to 10:01:59:
  x7Kp2Qa  10:01:04   +1
  x7Kp2Qa  10:01:31   +1
  x7Kp2Qa  10:01:58   +1

Bucket upsert:
  (x7Kp2Qa, 10:01)  clicks = clicks + 3

The worker adds the count it saw rather than writing a total. If several workers each count part of the same minute, their partial counts still add up to the right total.

Duplicates and failures

Most queues deliver each message at least once, so a click can arrive twice. For dashboards, a few duplicates rarely matter. If they do, give each click a click_id and ignore repeats, or deduplicate on (code, coarse timestamp, IP hash) within a window.

If the queue is down, drop the event, count the drop with a metric, and return the redirect anyway. A lost click is better than a failed link.

Common mistakes

• Updating a counter in the redirect path. It couples redirect speed to your write capacity on one row.
• Waiting for the queue before responding. Send the redirect first, or accept a fire-and-forget write that fails open.
• Reading raw events for every dashboard chart. Charts should read the minute and hour buckets.
• Treating click counts as exact billing data. The design gives you good analytics, not a ledger.

Check yourself

1. If the analytics store is down, what does a user see in this design?

Answer: Nothing. Events queue up, or are dropped with a metric if the queue is down, and the 302 still goes out.

2. Why do workers add deltas instead of writing totals?

Answer: Several workers can each see part of the same minute, and adding their counts keeps the total right.

Key takeaways

• Never update a counter on the links row in the redirect path.
• Emit a small event per click, then count in batches per time bucket.
• Fail open: analytics problems must never break a redirect.
• Duplicate events are possible, so accept them or deduplicate with an ID.

Further learning

For the queue itself, read message queues. To finish the case study, return to the URL shortener.