Hot Rows and High-Traffic Counters

Scenario

Example app: a social app stores posts.like_count. A celebrity post goes viral: 2,000 likes per second for twenty minutes. Each like runs UPDATE posts SET like_count = like_count + 1 WHERE id = $1.

What you see in production

Like requests slow from 5 ms to several seconds; the connection pool saturates and unrelated endpoints start failing. Database CPU is moderate, but lock-wait time on one row dominates.

The naive code

UPDATE posts SET like_count = like_count + 1 WHERE id = $1;   -- every like serializes on one row lock

Post.objects.filter(pk=post_id).update(like_count=F("like_count") + 1)
Like.objects.create(post_id=post_id, user=request.user)

await prisma.$transaction([
  prisma.post.update({ where: { id }, data: { likeCount: { increment: 1 } } }),
  prisma.like.create({ data: { postId: id, userId } }),
]);

Reproduce it

Each update holds the row lock for ~2 ms (including commit). One row can only absorb about 500 updates per second; spreading writes over N counter slots multiplies that:

def simulate(rows, rps=2000, seconds=1, hold_ms=2.0):
    free_at = [0.0] * rows
    worst_wait = 0.0
    for i in range(rps * seconds):
        arrive = i * 1000 / rps
        slot = i % rows                          # random/round-robin slot choice
        start = max(arrive, free_at[slot])
        worst_wait = max(worst_wait, start - arrive)
        free_at[slot] = start + hold_ms
    return worst_wait

for rows in (1, 4, 16):
    print(f"{rows:2} counter row(s): capacity ~{rows * 500:5} updates/s, "
          f"worst wait after 1 s = {simulate(rows):7.1f} ms")

 1 counter row(s): capacity ~  500 updates/s, worst wait after 1 s =  2998.5 ms
 4 counter row(s): capacity ~ 2000 updates/s, worst wait after 1 s =     0.0 ms
16 counter row(s): capacity ~ 8000 updates/s, worst wait after 1 s =     0.0 ms

Root cause

Row-level locking means updates to the same row are processed one at a time, each holding the lock until commit. The row's maximum throughput is roughly 1 / (lock hold time). When arrivals exceed it, a queue builds without bound — and the queued requests hold pooled connections, spreading the damage.

The fix

Pick by how fresh the number must be:

1. Sharded counters — N rows per post, increment a random slot, sum on read.
2. Append-only + aggregate — insert a likes row (no hot row), and update the counter asynchronously in batches.
3. In-memory counter — Redis INCR, flushed to the database periodically.

CREATE TABLE post_like_counts (post_id bigint, slot smallint, n bigint NOT NULL DEFAULT 0,
                               PRIMARY KEY (post_id, slot));
-- write: pick a random slot 0..15
INSERT INTO post_like_counts (post_id, slot, n) VALUES ($1, floor(random() * 16), 1)
ON CONFLICT (post_id, slot) DO UPDATE SET n = post_like_counts.n + 1;
-- read
SELECT coalesce(sum(n), 0) FROM post_like_counts WHERE post_id = $1;

# every 10 s: move new likes into the cached counter in one statement per post
session.execute(text('''
    UPDATE posts p SET like_count = p.like_count + d.cnt
    FROM (SELECT post_id, count(*) AS cnt FROM likes
          WHERE id > :last_id AND id <= :upto GROUP BY post_id) d
    WHERE p.id = d.post_id'''), {"last_id": last_id, "upto": upto})

redis.opsForValue().increment("likes:" + postId);          // ~100k+ ops/s on one key
// scheduled every few seconds: GETSET-style drain and add to the database row
String drained = redis.opsForValue().getAndSet("likes:" + postId, "0");
long delta = drained == null ? 0 : Long.parseLong(drained);
jdbc.update("UPDATE posts SET like_count = like_count + ? WHERE id = ?", delta, postId);

$slot = random_int(0, 15);
DB::statement('INSERT INTO post_like_counts (post_id, slot, n) VALUES (?, ?, 1)
               ON CONFLICT (post_id, slot) DO UPDATE SET n = post_like_counts.n + 1', [$postId, $slot]);

Trade-offs

• Sharded counters: exact and transactional, but reads sum N rows and you must choose N per hotness (or adapt it).
• Async aggregation: no write contention at all; the visible count lags by seconds — acceptable for likes, not for bank balances.
• Redis counters: fastest, but you must handle flush failures and Redis restarts (persistence, or recompute from the likes table).
• For money, don't shard the balance — model it as an append-only ledger and derive balances, or partition the account (sub-accounts).

Detect it in production

• Postgres: many sessions with wait_event_type = 'Lock' and wait_event = 'transactionid' on the same relation; pg_stat_statements shows a tiny UPDATE with huge mean time.
• MySQL: performance_schema.data_lock_waits concentrated on one record.
• Per-key metrics (top updated ids) catch hot keys before they melt the pool.

Key points

• One row absorbs only ~1 / lock-hold-time updates per second; beyond that, queues grow.
• Spread writes (sharded counters), defer them (append + aggregate), or move them (Redis) depending on freshness needs.
• Watch lock waits per row and protect the pool from one hot key.