A counter looks trivial. Then a viral post hits, 100k increments per second land on one row, and the database falls over. This post is the design at every scale.

Phase 1: Postgres single-row

UPDATE posts SET view_count = view_count + 1 WHERE id = $1;

Works up to ~10k updates/sec on the same row. Beyond that: row-level lock contention kills throughput.

For 99% of products: this is enough.

Phase 2: Redis single-key

await redis.incr(f"views:post:{post_id}")

Atomic. Fast. ~100k ops/sec on one Redis instance.

Persist to Postgres periodically:

async def flush_views():
    keys = await redis.keys("views:post:*")
    for k in keys:
        post_id = int(k.split(":")[-1])
        delta = await redis.getdel(k)        # atomic get + delete
        if delta:
            await db.execute(
                "UPDATE posts SET view_count = view_count + $1 WHERE id = $2",
                int(delta), post_id,
            )

Run every minute. Redis takes the load; Postgres has the durable counter.

Phase 3: Sharded counter

For very hot keys (>100k/sec):

SHARDS = 8

async def increment(post_id: int):
    shard = random.randrange(SHARDS)
    await redis.incr(f"views:post:{post_id}:s{shard}")

async def read(post_id: int) -> int:
    pipeline = redis.pipeline()
    for s in range(SHARDS):
        pipeline.get(f"views:post:{post_id}:s{s}")
    values = await pipeline.execute()
    return sum(int(v or 0) for v in values)

8 keys per logical counter. Random shard per increment → 8× write throughput. Read sums all 8.

Pair with caching strategies so reads cache for ~5 seconds.

Phase 4: Async aggregation

For massive scale (>1M ops/sec):

Increment events → Kafka → consumers
                            ↓
                       Per-window aggregate
                            ↓
                        Postgres / ClickHouse

Don’t increment in-line. Emit an event. A consumer aggregates per minute / hour. The view count is “eventually consistent” — fine for view counts.

For Kafka patterns .

Reading

  • Real-time-ish: read from Redis (sharded sum).
  • Cached: 5s TTL on the sum.
  • Long-tail: read from Postgres or ClickHouse periodically aggregated.

Most “live counter” UIs tolerate 5–30s delay; cache aggressively.

Idempotency

Counters incremented from clients can double-count if requests retry:

# Naive: same view counted twice
await redis.incr(f"views:post:{post_id}")

# Better: dedupe by event id
key = f"viewed:{user_id}:{post_id}:{day}"
if await redis.set(key, "1", nx=True, ex=86400):
    await redis.incr(f"views:post:{post_id}")

Dedup window of “user viewed post X today” prevents farmers from inflating counters. See Idempotency .

Decay / windowed counters

For “trending” counts: weight recent more than old.

trend_score(post) = views_last_hour + 0.5*views_last_day + 0.1*views_last_week

Compute via async windows. ClickHouse handles this elegantly.

Common mistakes

1. Single-row Postgres at hot-key scale

Lock contention. Fall over. Move to Redis.

2. Reading raw counter on every page load

100k req/sec hitting Redis × 8 shard reads = 800k ops/sec. Cache the sum.

3. Synchronous flush to Postgres

Every increment writes through. Postgres is the bottleneck again. Async batch.

4. No idempotency

Bots farm; counters inflate. Dedup with sensible windows.

5. No max bound

Buggy client increments 1000× per page. Counter explodes. Rate-limit increments per (user, key, time).

What I’d ship today

  • Up to 10k req/sec on a single counter: Postgres direct.
  • 10k–100k: Redis with batched flush.
  • 100k–1M: Redis sharded counters.
  • >1M: Kafka + windowed aggregation.

Match the architecture to the actual load.

Read this next

If you want a Python sharded-counter library + flush worker, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .