Analytics on the hot path

The owner wants a click count, so the redirect adds one to a column. Why does a viral link make every redirect slow?

Before this: The stale redirect·Next: The vanity alias race

In 30 seconds

The problem: After the redirect starts counting clicks in Postgres, it gets slower with every click on a popular link, and can take unrelated links down with it.
Why it happens: Every click runs an UPDATE on the same row, and Postgres lets one transaction change a row at a time. Click number 50 waits for the 49 ahead of it.
The fix: Count in Redis with INCR on each click, and let a background worker add the total to Postgres every few seconds.
In an interview: The hot path does only what the user is waiting for. Count in memory, flush to the database in batches, and say the trade-off out loud: counts lag by a few seconds.

Why it matters

We cached the destination so a redirect never touches the database. The click counter puts a database write back on every single click, on the one row everyone shares.

In our run, 50 simultaneous clicks took about 68 ms at the median and 79 ms for the slowest, against a 50 ms budget for a redirect. That is 50 clicks on a laptop with an idle database. A real viral link is thousands a second on a database that is busy with other work, and each waiting click also holds a connection from the pool. When the pool runs dry, every other link waits too.

Words you need

Hot path: The code that runs on every request and that the user is waiting on. Here, the redirect itself. Anything extra you put there is paid on every click.
Row lock: While one transaction updates a row, the database makes every other update to that row wait its turn.
Bottleneck: The one part that everything else has to wait for.
Batching: Collecting many small changes and writing them once. 10,000 clicks become one UPDATE.
Atomic: Done as one step that nothing can interrupt. Another request sees it either not started or fully finished.

Picture it

Three people click the same link in the same instant.

Counting inside the redirect

Click 1 locks the row
Click 2 waits
Click 3 waits
Click 1 commits
Click 2 runs
Click 3 runs

The count is right, but every redirect now waits for its turn on one row. The more clicks, the longer the queue.

Counting off the hot path

Click 1 INCR in Redis
Click 2 INCR in Redis
Click 3 INCR in Redis
Worker adds 3 to Postgres

Each redirect finishes in under a millisecond. Postgres gets one write for the whole batch.

The fixes people try

UPDATE in the redirect
Fails
UPDATE links SET clicks = clicks + 1
Correct count, but every click queues on one row and holds a connection while it waits.
Fire and forget the UPDATE
Half a fix
db.query(…) // no await
The redirect stops waiting, but the writes still pile up on the same row and still use the pool. You moved the queue, not removed it.
Insert a row per click
Half a fix
INSERT INTO clicks (code, at) …
Inserts do not fight over one row, so it scales much further. It is still a database write on every click.
Spread over N counter rows
Works
clicks_shard = random(0, 9)
Spreads the lock across several rows and sums them on read. Good when the count has to stay in Postgres.
Redis INCR and a periodic flush
Works
INCR clicks:<code>; flush every ~5s
No row lock, no disk, no queue on the redirect. Ten thousand clicks become one write.
Click events to a queue
Works
queue.send({ code, at, country })
The right answer when you need per-click data (country, referrer), durability, or replays.

The fix and its cost

Make the redirect do only the redirect. On each click, run INCR on a Redis key: it is atomic, in memory, and takes no row lock. A background worker reads and clears the key every few seconds with GETDEL and adds the total to Postgres in one UPDATE.

This is the same idea as the cache we already have: keep the slow, shared thing off the path the user is waiting on.

What it costs

Counts lag by the flush interval, so analytics can be a few seconds late. If the worker crashes between GETDEL and the UPDATE, that batch is lost. Fine for click counts, not for money.

Interviewers also listen for: durability and 301

If losing a batch is not acceptable, push click events to a durable queue (Kafka, SQS) and aggregate from there. And counting only works with 302 redirects: a browser caches a 301, so repeat clicks never reach you.

Check yourself

1The count is correct. Why is this still a bug?Show answerHide
The cost is the problem, not the number. The read path now writes, and every click waits on one row lock and holds a connection while it does.
2Why can one hot link slow down other links?Show answerHide
Waiting clicks each hold a database connection. When the pool runs out, requests for every other link wait for a free one.
3What do you give up with the Redis counter?Show answerHide
Freshness: counts lag by the flush interval, and a crash between reading and flushing loses that batch. For click counts that is acceptable, and you say so before the interviewer asks.

The code

GitHub

redirect.bug.ts

import type { Pool } from "pg";
import type Redis from "ioredis";

// Redirect, and count the click right here.
export async function redirect(db: Pool, redis: Redis, code: string) {
  // Every click writes to the same row, one transaction at a time.
  await db.query(
    "UPDATE links SET clicks = clicks + 1 WHERE code = $1",
    [code],
  );
  const dest = await redis.get(`dest:${code}`);
  return `302 → ${dest}`;
}

Example code. The tested version, with a proof test against real Postgres, lands in the repo.

Star the repo for the runnable version

Each trap in this world ships with the bug, the fix and a proof test that fires concurrent requests at a real database.

Star the repo See the folder

Watch the 60-second version

Open on YouTube

Remember

Whatever you add to the redirect is paid on every click. Count off the path, flush in batches, and say that counts lag.

NextThe vanity alias raceTwo users claim /launch at once, and both are told it is theirs.