Everyone thinks a leaderboard is a SELECT ... ORDER BY score DESC LIMIT 100. “Store the scores, sort them, return the top 100, done.” Then the interviewer adds the real constraints: it is not just the top 100, a player who ranks 4,192,304th wants to see their own rank and the ten people around them; scores update constantly as millions of people play, and a single kill or a single point can move someone up thousands of positions; the board must feel live, so a score bump should show up in a second, not on the next nightly batch; and there is not one board, there is a global all-time board, plus a daily board, a weekly board, and one per region, all at once.

A leaderboard is deceptive because the naive ORDER BY demos fine on a laptop with ten thousand rows and dies the moment you have fifty million players and a firehose of score updates. The whole design comes down to four decisions: how you answer “what is this player’s rank” without scanning the table (sorted sets), how you absorb millions of writes without every one of them re-sorting the world (in-memory ranked structures plus write batching), how you split a board too big for one machine (sharding a fundamentally ordered structure, which is the hard one), and how you keep a daily/weekly board without recomputing it from scratch (time-windowed keys). All of it while a rank query stays under a few milliseconds.

Let me do it properly.

Functional Requirements (FR)

In scope:

  • Submit a score. A player finishes a match / earns points; the system records their new score. Depending on the game this is either “set to the new absolute score” or “increment by a delta.” Both matter, we handle both.
  • Top-N query. Return the top N players (name, score, rank) for a board. N is small and fixed, typically 10, 50, or 100.
  • Get a player’s rank. Given a player ID, return their current rank and score on a board - even if that rank is four million. This is the query the naive design cannot do cheaply, and it is the whole point.
  • Rank neighborhood. Return the K players immediately above and below a given player (“you are #4,192,304, here are the 5 above and 5 below you”). This is what actually renders on a player’s screen.
  • Multiple boards / time windows. A global all-time board, plus rolling daily and weekly boards, plus per-region or per-game-mode boards. Same machinery, different keys.

Explicitly out of scope (say it so you own the scope):

  • Anti-cheat / score validation. Deciding whether a submitted score is legitimate is a large separate system (server-authoritative game logic, anomaly detection). We assume scores arriving at the leaderboard are already validated, and note where the validation gate sits.
  • Full match history / replays. We store the current score and rank, not the audit trail of every match. That is a separate event store.
  • Rewards / payouts. Turning “top 10 this week” into prizes is downstream business logic reading our board, not part of the leaderboard itself.
  • Social graph (friends-only boards). A “leaderboard among your friends” is a fan-out/intersection problem on top of ours; we build the global and windowed boards and note where friend boards graft on.
  • Exact real-time consistency across regions. A globally strongly-consistent single ranking across continents is not worth the latency. We serve per-region fast and reconcile a global view asynchronously.

The one decision that drives everything: a leaderboard is a write-heavy, ordered, top-and-rank query problem, and the killer operation is “rank of an arbitrary element,” which a normal index does not give you cheaply. So the design is dominated by picking a data structure that keeps elements ordered by score and can answer “how many elements are above this one” in O(log n) - which is exactly a skip-list-backed sorted set - and then figuring out how to feed it millions of writes and split it when it no longer fits.

Non-Functional Requirements (NFR)

  • Scale: ~50M registered players, ~5M daily active. Peak score updates on the order of hundreds of thousands per second during big events (everyone plays after a content drop). Read traffic - people checking their rank and the top-N - is a few times higher than writes, call it low millions of QPS at peak, heavily cacheable for the top-N.
  • Latency: rank query and top-N p99 under ~10ms server-side; score-update ack p99 under ~20ms. A leaderboard that lags feels broken - you scored, you want to see yourself move.
  • Freshness: near-real-time, on the order of 1-2 seconds from score submission to the board reflecting it. Not strictly synchronous (we can batch writes), but not a nightly batch either.
  • Availability: 99.9%+. The leaderboard is engagement-critical and user-facing. It must survive node failures without losing the ranking. Reads should degrade gracefully (serve slightly stale) rather than error.
  • Consistency: eventual and monotonic-ish is fine. It is acceptable for two players to briefly see themselves off by a rank or two, or for a score to take a second to propagate. It is not acceptable to lose a score permanently or to show a player below someone they clearly beat for long. So: durable writes, fast-converging reads.
  • Durability: high for the source-of-truth score (persisted so a Redis node loss cannot erase someone’s progress). The in-memory ranked structure is a derived, rebuildable index - we can reconstruct it from the durable store, and we design for exactly that.

Back-of-the-Envelope Estimation (BoE)

Let me ground the design in numbers.

Traffic:

Registered players:            50,000,000  (50M)
Daily active players:          5,000,000   (5M)
Matches per active player/day: ~10
Score updates/day = 5M * 10  = 50,000,000  (50M writes/day)

Average write QPS = 50M / 86,400 sec ≈ 580 QPS   (misleadingly low)
Events are spiky - a content drop concentrates play:
Peak write QPS (say 200x avg during an event) ≈ 100,000 - 300,000 QPS

The daily average of ~580 QPS is a trap. Leaderboards live and die by their peaks: after a new season launches, a huge fraction of the DAU plays in the same hour, so we size for ~200K-300K score updates/sec, not 580.

Read vs write:

Reads:
  - Top-N board views:   very frequent, but IDENTICAL for everyone ->
                         cache the top-100 hard. Effectively a few
                         cache reads/sec after caching, regardless of demand.
  - "My rank" queries:   per-player, unique -> cannot share a cache entry.
                         Say each active player checks rank ~5x/day:
                         5M * 5 = 25M/day -> avg ~290 QPS, peak ~50K-100K QPS.
Writes:
  - Score updates:       ~200K-300K QPS peak (the hard number above).

Two very different read patterns: the top-N is trivially cacheable (one answer for the whole planet, refresh every second), while “my rank” is per-player and uncacheable, so it has to hit the ranked structure directly and must be O(log n). Writes are the firehose. This asymmetry - cacheable top-N, uncacheable per-player rank, huge write peak - shapes the whole design.

Storage:

Per player, per board, the ranked structure stores:
  member (player_id ~8-16 bytes) + score (8 bytes) + skip-list overhead
  ≈ ~80-100 bytes/player in a Redis sorted set (with pointers/levels).

Global all-time board, 50M players:
  50M * ~100 bytes ≈ 5 GB in RAM for one board.

Durable source of truth (player_id -> score) in a datastore:
  50M rows * ~50 bytes ≈ 2.5 GB, trivial on disk, plus indexes.

Time-windowed boards (daily/weekly) only hold ACTIVE players in the window:
  daily ≈ 5M active * 100 bytes ≈ 500 MB per day-key
  weekly ≈ up to ~15M distinct * 100 bytes ≈ 1.5 GB per week-key

So a single global board is ~5 GB - fits in one beefy Redis node’s RAM today, but that is the number that grows with players and the reason we plan sharding before we are forced into it. The windowed boards are smaller because they only ever contain players active in that window.

Bandwidth:

Top-N response: 100 entries * ~40 bytes ≈ 4 KB, served from cache.
Rank/neighborhood response: ~11 entries * 40 bytes ≈ ~500 bytes.
Even at 100K rank QPS: 100K * 500 B ≈ 50 MB/sec egress. Modest.

Bandwidth is a non-issue. The scarce resources are write throughput (absorbing 200K+ updates/sec into an ordered structure) and rank-query latency (O(log n), not O(n)). Size for those.

High-Level Design (HLD)

The system splits into a write path that validates and durably records a score then updates the in-memory ranked index, and a read path that answers top-N from cache and per-player rank/neighborhood directly from the ranked index. The heart of it is the Ranking Store: Redis sorted sets, one per board, giving O(log n) inserts and, critically, O(log n) rank lookups.

                 WRITE PATH (score updates, ~200K-300K QPS peak)
   ┌──────────────────────────────────────────────────────────────────┐
   │  Game Server  --submit score-->  API Gateway / LB                  │
   └───────────────────────────────────┬───────────────────────────────┘
                                        │  POST /scores  (already validated)
                              ┌─────────▼──────────┐
                              │  Score Ingest Svc  │  (stateless)
                              │  - write to WAL    │
                              │  - enqueue update  │
                              └─────┬─────────┬────┘
                    durable write   │         │  async, batched
                          ┌─────────▼──┐   ┌──▼───────────────┐
                          │ Score Store │   │  Kafka (per-board│
                          │ (Postgres/  │   │  partitioned)    │
                          │  Cassandra) │   └──┬───────────────┘
                          │ SOURCE OF   │      │  consumers apply
                          │ TRUTH       │      │  ZADD / ZINCRBY
                          └─────────────┘      │
                                               ▼
   ┌─────────────────────────────  RANKING STORE  ─────────────────────┐
   │   Redis sorted sets, sharded by score-range, replicated           │
   │  ┌──────────────┐  ┌──────────────┐        ┌──────────────┐        │
   │  │ Shard 0      │  │ Shard 1      │  ...   │ Shard S      │        │
   │  │ scores 0-1k  │  │ scores 1k-5k │        │ scores 90k+  │        │
   │  │ ZSET global  │  │ ZSET global  │        │ ZSET global  │        │
   │  │ ZSET day:... │  │ ZSET day:... │        │ ZSET day:... │        │
   │  │ ZSET week:...│  │ ZSET week:...│        │ ZSET week:...│        │
   │  └──────────────┘  └──────────────┘        └──────────────┘        │
   │        ▲ each shard also keeps a live COUNT for cross-shard rank   │
   └────────┼──────────────────────────────────────────────────────────┘
            │
    ┌───────┴─────────┐   READ PATH
    │  Leaderboard    │   - GET /top      -> Top-N cache (refresh ~1s)
    │  Read Service   │   - GET /rank     -> sum shard counts above player
    │  (stateless)    │   - GET /around   -> ZRANGE around player's rank
    └───────┬─────────┘
     ┌──────▼───────┐
     │  Top-N Cache │  (Redis/CDN, one entry per board, ~1s TTL)
     └──────────────┘

Request flow - a score update (the write hot path):

  1. The game server (server-authoritative, so the score is already validated) sends POST /scores with player_id, board, and either an absolute score or a delta.
  2. The Score Ingest Service durably records the score first - a write-ahead to the Score Store (source of truth). This is what guarantees we never lose a score even if a Redis node dies. It also stamps an event time used for windowing.
  3. The service enqueues the update onto Kafka, partitioned by board (and by score shard, see the deep dive), and acks the client. The ack does not wait for the ranked index to update - the durable write already happened, so the ack is safe.
  4. Consumers read the partitioned stream and apply the update to the Ranking Store: ZADD board player new_score (absolute) or ZINCRBY board delta player (increment). Batching many updates per pipeline call is what makes 200K+/sec survivable (deep dive).
  5. Within ~1-2 seconds the sorted set reflects the new score, so the next rank query sees it.

Request flow - the read paths:

  1. Top-N: the Read Service returns the cached top-N for the board. A background refresher runs ZREVRANGE board 0 N-1 WITHSCORES every ~1 second and updates the cache. So the millions of identical top-N views collapse into one query per second per board. Sub-millisecond from cache.
  2. My rank: ZREVRANK board player on a single unsharded board is O(log n) and returns the rank directly. With score-range sharding, rank = (players above me in my own shard) + (total players in all higher-scoring shards), which each shard tracks as a live count (deep dive). Still a handful of O(log n) / O(1) ops.
  3. Neighborhood: once we know the player’s rank R, ZREVRANGE board R-5 R+5 WITHSCORES returns the window around them.

The key architectural insight: the durable write and the ranked-index update are decoupled. The client is acked as soon as the score is durably logged; the ordered index is updated asynchronously and in batches. That decoupling is what lets us absorb a 200K/sec write spike (the queue smooths it) while keeping the ack fast and never losing a score. The sorted set is a derived index we can rebuild from the durable store - never the source of truth.

Component Deep Dive

Three hard parts: (1) the rank query itself - why a database ORDER BY cannot answer “what is my rank” and what does, (2) absorbing millions of score updates without melting the ranked structure, and (3) sharding an inherently ordered structure across machines, plus building time-windowed boards. I will walk each from naive to scalable.

1. The Rank Query: SQL ORDER BY -> Sorted Set (skip list)

Approach A: A table and ORDER BY (the naive instinct)

Store scores in a table, query the top-N and rank with SQL:

-- top-N: fine, with an index on score this is a cheap index scan
SELECT player_id, score FROM scores ORDER BY score DESC LIMIT 100;

-- "my rank": count how many players score higher, add one
SELECT COUNT(*) + 1 AS rank FROM scores WHERE score > :my_score;

The top-N is genuinely fine - a B-tree index on score makes ORDER BY score DESC LIMIT 100 an index scan of 100 rows. Where it breaks is the rank query. COUNT(*) WHERE score > my_score has to count every row above you. For a player near the bottom of a 50M-row board that is counting tens of millions of rows, per query. Even with an index on score, the database still has to traverse or aggregate that whole range - there is no B-tree operation that returns “the ordinal position of this key” in log time out of the box. At 50K-100K rank QPS, each scanning millions of rows, the database is done. And that is the most common read: everybody wants to know their own rank, and most players are not in the top 100.

You can cache the top-N (it is shared), but you cannot cache “my rank” because every player’s is different. So the uncacheable, most-frequent query is exactly the one ORDER BY is worst at.

Approach B: An in-memory sorted set (skip list + hash)

The operation we actually need is “given an element, return its ordinal position in a sorted order,” in O(log n), plus O(log n) inserts as scores change. That is precisely a sorted set, which Redis implements as a skip list (ordered by score, giving ordered range and rank queries) alongside a hash map (member -> score, giving O(1) score lookup and O(log n) updates).

Skip list, ordered by score (higher = better rank):

 L3:  head ------------------------------> 9800 -----------> NULL
 L2:  head ---------> 4200 -------------> 9800 -----------> NULL
 L1:  head -> 1500 -> 4200 -> 6100 -----> 9800 -> 12100 --> NULL
 L0:  head -> 1500 -> 4200 -> 6100 -> 8000 -> 9800 -> 12100 -> NULL
             (player) (player) ...

Each skip-list node also stores a SPAN (how many L0 nodes each
forward pointer skips). Summing spans along the search path gives
the element's rank WITHOUT walking every node -> O(log n) rank.

The crucial detail is the span stored on each forward pointer: the number of bottom-level elements that pointer jumps over. To get an element’s rank, you walk the search path from the top and sum the spans you traverse - that yields the exact ordinal position in O(log n), never touching the millions of elements below. This is the single operation SQL could not do cheaply, and it is what a sorted set is built for.

The relevant operations, all O(log n) or better:

ZADD    board score member      # insert/update a score            O(log n)
ZINCRBY board delta member      # increment a score                O(log n)
ZREVRANK board member           # MY RANK (0-based, high->low)     O(log n)
ZSCORE  board member            # my score                         O(1)
ZREVRANGE board 0 99 WITHSCORES # top-100                          O(log n + 100)
ZREVRANGE board R-5 R+5 WSCORES # neighborhood around rank R       O(log n + 11)
ZCARD   board                   # total players on the board       O(1)

ZREVRANK is the whole ballgame: it answers “my rank” in O(log n) instead of the database’s O(n) count. Where a single sorted set breaks: memory and write throughput on one node. A 50M-member global board is ~5 GB and every update is a write to one machine - and at 200K+ writes/sec plus 100K rank reads/sec, one Redis instance becomes the bottleneck. That pushes us to batching (part 2) and sharding (part 3).

Approach C: Sorted set as the index, database as the truth

Redis sorted sets in RAM are fast but not the durable source of truth. So we run both: the durable Score Store (Postgres/Cassandra) is authoritative and survives anything; the sorted set is a derived ranking index rebuilt from it on cold start or node loss. Reads hit the sorted set; the durable store is only read to rebuild. This is the standard “system of record + fast derived index” split, and it is what lets us treat any single Redis node as disposable.

2. Absorbing Millions of Score Updates

The write peak is ~200K-300K updates/sec. Each update is an O(log n) skip-list operation, which is cheap individually, but the coordination around it - network round trips, durability, contention on a hot board - is what actually breaks first.

Approach A: Synchronous write straight to Redis per event (naive)

For each score, the game server calls ZADD (and a durable write) synchronously and waits.

Where it breaks: three places at once. (a) One network round trip per update to Redis at 200K/sec is 200K RTTs/sec against a single logical board - the per-command overhead (not the O(log n) itself) dominates and saturates the connection/CPU. (b) If we also write durably to the database synchronously on the request path, the ack now waits on a disk write, blowing the 20ms budget under load. (c) A “hot” board (the global one everyone is updating) is a single write-contended key. This does not survive an event spike.

Approach B: Durable-log-then-async-apply, with pipelined batching

Decouple the ack from the index update. On a score submission:

  1. Write durably first, cheaply. Append the score event to a write-ahead log / the Score Store (an append is fast and is what makes the score un-loseable). Ack the client here. The ack is honest because the score is now durable - even if every Redis node died, we could rebuild the ranking.
  2. Enqueue the update to Kafka, partitioned by board. Consumers apply updates to Redis in batches using pipelining: instead of 200K individual ZADD round trips, a consumer collects, say, 500 updates and sends them in one pipelined batch, amortizing the network/round-trip cost by ~500x. The O(log n) work still happens per element, but the per-command overhead - the thing that was actually killing us - collapses.
consumer loop (per board partition):
  batch = drain up to 500 updates OR wait up to 10ms, whichever first
  pipeline:
     for (player, score) in batch:
        ZADD board score player          # or ZINCRBY for deltas
  flush pipeline (one network round trip for the whole batch)

Coalescing within a batch: if the same player submits three scores inside one 10ms window, we only need to apply the last absolute score (or the summed delta) once. Collapsing duplicate members in a batch further cuts the work on hot players. This bounds the actual ZADD rate to well below the raw event rate during a burst.

Absolute score vs delta - the correctness fork. ZADD sets an absolute value; ZINCRBY adds a delta. They behave very differently under retries and reordering:

Update typeOpIdempotent on retry?Safe if reordered?
Absolute “new total score”ZADDYes - re-applying the same value is a no-opNeeds last-writer-wins by event time
Delta “+50 points”ZINCRBYNo - a retried delta double-countsNo - must apply exactly once, in order

For deltas, at-least-once delivery (which queues give you) is dangerous: a redelivered “+50” double-counts. Fixes: make deltas idempotent by carrying a unique event_id and deduping (a short-lived set of applied event IDs), or prefer absolute scores computed by the authoritative game server so the leaderboard write is a naturally idempotent ZADD with last-writer-wins by timestamp. When possible, prefer absolute scores for exactly this reason - it turns the ranking update into an idempotent set operation and removes a whole class of double-count bugs.

Ordering: partition Kafka by player_id (within a board) so all of one player’s updates land on one partition and are applied in order - a later score never gets overwritten by an earlier one arriving late. Between different players, order does not matter.

Approach C: Shed and smooth the spikes

Even batched, a true spike can outrun a shard. Two more levers: (a) the queue itself is the shock absorber - a 300K/sec burst drains at whatever rate consumers sustain, trading a little extra freshness lag (a few seconds) for never dropping a write; (b) for the very hottest board, run more consumer partitions and let the score-range sharding (next) spread the ZADDs across multiple Redis nodes so no single node eats the whole write rate.

3. Sharding an Ordered Structure + Time Windows

A single sorted set is one Redis node’s RAM and one node’s write throughput. Once the global board outgrows that, we shard. Sharding an ordered structure is genuinely harder than sharding a hash table, because rank is a global property - your rank depends on everyone, so you cannot just hash players to nodes and forget it.

Approach A: Shard by hash of player_id (the naive, and wrong, split)

Hash players across N Redis nodes, each holding a sorted set of its players.

Where it breaks: rank is now unanswerable cheaply. To compute a player’s global rank you must ask every shard “how many of your players score above X” and sum - a fan-out to all N shards on every rank query. Worse, the top-N global requires pulling each shard’s local top-N and merging. It spreads writes and memory evenly (good) but destroys the one query we care about most. Hash sharding is right for key-value, wrong for ranking.

Approach B: Shard by score range (the right split)

Partition by score range, not by player. Shard 0 holds scores 0-999, shard 1 holds 1,000-4,999, and so on, with the highest scores in the last shard. Now the structure stays globally ordered across shards: everyone in shard 2 outranks everyone in shard 1.

Shard 0: scores    0 - 1,000     (the big low-score bucket, most players)
Shard 1: scores 1,001 - 5,000
Shard 2: scores 5,001 - 20,000
Shard 3: scores 20,001+          (the elite tail, few players)

Global rank of a player in shard 2:
   rank = (players in shard 3, the higher shard)          # a COUNT
        + (players above me within shard 2)               # ZREVRANK local
  • Top-N global: read only from the highest shard(s). The top-100 lives entirely in the top score range, so ZREVRANGE on the top shard (spilling into the next only if the top shard has fewer than N) answers it - no all-shard fan-out.
  • My rank: local ZREVRANK within my shard, plus the sum of member counts of all higher-scoring shards. Each shard exposes an O(1) ZCARD, and there are only a handful of shards, so this is a few O(1) reads plus one O(log n) - not a 50M-row scan and not an N-way fan-out over players.
  • Writes land on the shard owning the new score, and a score crossing a boundary (a player leveling from 990 to 1,050) moves from shard 0 to shard 1 - a delete-then-insert across two nodes, handled by the consumer.

The catch is skew: score distributions are lopsided - the vast majority of players sit in the low buckets, a tiny elite in the high ones. Equal score-width ranges create a giant hot shard 0 and near-empty top shards. Fix by sizing ranges by population, not by score width (narrow ranges where players are dense, wide ranges up in the sparse tail), and rebalancing boundaries as the distribution drifts. This mirrors the range-vs-hash trade in any ordered partitioning: you accept manual/adaptive balancing in exchange for keeping the order that makes rank queries cheap.

Shard keyTop-N costRank costWrite spreadVerdict
Hash of player_idfan-out all shards + mergefan-out all shards + sumevenRejected - kills rank
Score rangeread top shard onlylocal rank + sum higher-shard countsuneven (skew)Right - order preserved
Score range + population-sized boundariessamesamebalancedBest in practice

For many real systems a single board fits in one node’s RAM (~5 GB for 50M players) and you shard reads via replicas rather than splitting the set at all - only split by score range when one node truly cannot hold or write it. State that trade explicitly in the interview: do not shard until you must, because score-range sharding adds real balancing complexity.

Time-windowed boards: daily and weekly without recomputation

A “today” board and a “this week” board must not be computed by filtering the global board (you would have to scan and re-rank on every read). Instead, maintain separate sorted sets per window, keyed by the window:

lb:global                 # all-time
lb:day:2026-07-08         # today  (only players active today)
lb:week:2026-W28          # this ISO week
lb:region:eu:day:2026-07-08

On a score event at time T, the consumer updates ALL relevant keys:
   ZADD lb:global            score player       # or ZINCRBY
   ZADD lb:day:2026-07-08    daily_score player
   ZADD lb:week:2026-W28     weekly_score player
  • Window semantics: the daily board’s score is usually the player’s best or total within that day, computed by the authoritative server and written as an absolute ZADD per window (so windows can have different score semantics than the all-time board).
  • Expiry, not deletion jobs: set a TTL on each window key (a day key expires ~48h after the day ends, a week key ~2 weeks after). Redis reclaims the memory automatically - no nightly “delete old board” batch, no scan. This is why time-windowed keys are cheap: the window rolls over simply because you start writing to a new key, and the old one evaporates on its TTL.
  • Rollover is free: at midnight the consumer just starts writing lb:day:2026-07-09. There is no cutover job; the key name is the window. Reads for “today” always target the current day key.
  • Cost control: window keys only contain players active in that window (500 MB/day, not 5 GB), because a player who did not play today never gets written to today’s key.

API Design & Data Schema

Serving / read API (the data plane)

GET /v1/boards/{board}/top?limit=100
  board  string   e.g. "global", "day:2026-07-08", "week:2026-W28", "region:eu"
  limit  int      1..100 (capped)
  200 OK  (served from the Top-N cache, ~1s fresh)
  {
    "board": "day:2026-07-08",
    "top": [
      { "rank": 1, "player_id": "p_9f2", "name": "Ada",  "score": 98200 },
      { "rank": 2, "player_id": "p_1c7", "name": "Grace","score": 96110 }
    ]
  }

GET /v1/boards/{board}/rank?player_id=p_4a1
  200 OK
  { "board": "global", "player_id": "p_4a1", "rank": 4192304, "score": 1530 }

GET /v1/boards/{board}/around?player_id=p_4a1&window=5
  200 OK   (the 5 above and 5 below, centered on the player)
  {
    "board": "global", "center_rank": 4192304,
    "entries": [
      { "rank": 4192299, "player_id": "p_77a", "score": 1535 },
      { "rank": 4192304, "player_id": "p_4a1", "score": 1530, "me": true },
      { "rank": 4192309, "player_id": "p_0b2", "score": 1527 }
    ]
  }

Ingestion API (the write plane)

POST /v1/scores
  {
    "player_id": "p_4a1",
    "boards": ["global", "day:2026-07-08", "week:2026-W28"],
    "mode": "absolute",          # "absolute" (ZADD) or "delta" (ZINCRBY)
    "score": 1530,               # absolute value, or the delta if mode=delta
    "event_id": "evt_8812f",     # for idempotent dedup of deltas / retries
    "ts": 1751932800             # authoritative event time (for windowing)
  }
  202 Accepted   -> score durably logged; index update is async (~1-2s)

Notes:
  - Ack is 202 after the DURABLE write, not after the ZADD.
  - `event_id` makes retried deltas idempotent (dedup applied-set).
  - Scores are assumed already validated by the server-authoritative
    game logic; this endpoint is internal, behind the game backend.

Data model: durable store of record + derived Redis index

Two stores for two jobs - a durable system of record (survives everything, source of truth) and the in-memory sorted sets (derived, fast, rebuildable).

Score Store - source of truth (Postgres for <= a few tens of millions of rows, or Cassandra if you need it wider):

player_score
  player_id     TEXT/UUID    PK       # one row per player per board
  board         TEXT         PK       # ("global","day:2026-07-08",...)
  score         BIGINT                # current authoritative score
  updated_at    TIMESTAMP
  PRIMARY KEY (player_id, board)
  -- point lookups and durable rebuilds; NOT used for rank queries.

score_event   (append-only, optional audit / rebuild log)
  event_id      TEXT  PK              # idempotency key
  player_id     TEXT
  board         TEXT
  delta_or_abs  BIGINT
  mode          TEXT                  # absolute | delta
  ts            TIMESTAMP
  -- partitioned by day; the WAL the ranking index can be rebuilt from.

Why SQL/Cassandra here and not for ranking: this store does point lookups and durable appends, not “rank of an arbitrary element.” A relational/wide-column store is perfect for WHERE player_id = ? and for being the thing you never lose. It is deliberately not asked to answer rank - that is the sorted set’s job.

Ranking index - Redis sorted sets (derived, in RAM):

Key pattern:   lb:{board}           e.g. lb:global, lb:day:2026-07-08
Type:          ZSET  (skip list + hash), member=player_id, score=score
Sharding:      by SCORE RANGE across N Redis nodes (population-balanced)
Replication:   each shard has >=1 replica (async) for read scaling + HA
Persistence:   AOF/RDB on for warm restart; truth is still the Score Store
TTL:           set on window keys (day ~48h post-window, week ~2w post-window)

Supporting keys:
  lb:{board}:shardcounts   # per-shard ZCARD, refreshed, for cross-shard rank
  applied:{event_id}       # short-TTL dedup marker for idempotent deltas

Why a Redis sorted set and not a database index: the access pattern is “insert/update a score and ask an element’s ordinal rank among tens of millions, at hundreds of thousands of ops/sec, in a few milliseconds.” The skip-list-with-spans answers rank in O(log n); a B-tree COUNT(*) WHERE score > x is O(n). The set is in RAM, sharded by score range, and replicated - and it is disposable, because the Score Store can rebuild it.

Top-N cache:

key:   topn:{board}                 # e.g. topn:day:2026-07-08
value: serialized top-100 list
TTL:   ~1s (a background refresher runs ZREVRANGE and overwrites it)

Bottlenecks & Scaling

Where it breaks first, in order, and the fix for each:

1. Rank query cost (breaks first, conceptually). “My rank” is the most common, least cacheable read, and a database COUNT(*) WHERE score > mine scans millions of rows. Fix: a Redis sorted set (skip list with spans) answers ZREVRANK in O(log n). This is the single move that makes the system possible - it removes the O(n) scan from the hottest read.

2. Write throughput at event spikes (~200K-300K/sec). Synchronous per-event ZADD plus synchronous durable writes blow the ack budget and saturate one node. Fix: durable-log-then-async-apply - ack after a cheap durable append, then apply to Redis via pipelined, coalesced batches off a per-board Kafka partition. The queue absorbs the spike; batching amortizes per-command overhead ~500x.

3. Hot board / hot key. The global board is one write-contended key everyone updates, and the top-N is one read-contended key everyone views. Fix: writes spread across score-range shards so no single node eats the whole write rate; the top-N is served from a ~1s cache so millions of identical views collapse into one ZREVRANGE/sec. Replicas serve rank/neighborhood reads.

4. One node cannot hold or write the whole board. 50M+ players and growing exceed one node’s RAM and write ceiling. Fix: shard by score range (population-balanced boundaries) so the board stays globally ordered, top-N reads hit only the top shard, and rank is local-rank + sum-of-higher-shard-counts. Do this only when a single node truly cannot cope - replicas first, split later.

5. Skewed shards. Most players cluster in low scores; equal score-width ranges create a giant hot low shard. Fix: size ranges by population, not score width (narrow low, wide high), and rebalance boundaries as the distribution drifts. Give the hot low range more shards.

6. Delta double-counting under retries/reordering. At-least-once queues redeliver, so a ZINCRBY +50 can apply twice. Fix: prefer absolute scores (idempotent ZADD, last-writer-wins by ts); when deltas are unavoidable, carry an event_id and dedup with a short-TTL applied-set. Partition by player_id so a player’s updates apply in order.

7. Redis node loss. The ranking index is in RAM; a node dying could vanish a shard. Fix: the Score Store is the source of truth - rebuild any lost sorted set by replaying scores. Meanwhile run replicas (async replication) per shard for instant failover, and AOF/RDB for warm restart. Losing a Redis node loses no scores, only forces a rebuild/failover.

8. Time-window memory growth and rollover. Daily/weekly boards would pile up and need cleanup. Fix: window is the key (lb:day:YYYY-MM-DD) with a TTL - old windows expire automatically, rollover is just writing to a new key, and each window holds only players active in it. No delete jobs, no re-ranking.

9. Global multi-region view. A single strongly-consistent worldwide ranking is too slow across continents. Fix: keep per-region boards authoritative and fast; compute the global board asynchronously by merging regional score streams into a global sorted set with a small extra freshness lag. Players see their regional rank instantly and the global rank a beat later.

Wrap-Up

The trade-offs that define this design:

  • Sorted set over ORDER BY. We use a skip-list-backed sorted set specifically because it answers “rank of an arbitrary element” in O(log n) via span sums, the one query a B-tree index does with an O(n) count. That single structural choice is why “my rank” - the most common, least cacheable read - is cheap.
  • Durable log first, index async. We ack the score after a cheap durable write and update the ordered index asynchronously in batches, trading ~1-2s of freshness for the ability to absorb a 200K+/sec write spike without dropping a score or blowing the ack budget. The queue is the shock absorber.
  • Derived index over source of truth. The Redis sorted set is a disposable, rebuildable ranking index; the durable Score Store is the thing we never lose. Any Redis node is replaceable, which is what makes replication and warm restarts simple.
  • Score-range shard over hash shard. We deliberately partition by score, not player, so the board stays globally ordered and rank stays a local op plus a few counts - paying for it with manual/adaptive boundary balancing against score skew. And we do not shard at all until one node genuinely cannot cope.
  • Window is the key. Daily and weekly boards are separate TTL’d keys, not filtered views of the global board, so rollover is free, cleanup is automatic, and each window only holds its active players.

One-line summary: a durable Score Store fronting sharded, replicated Redis sorted sets fed by a batched async write pipeline - answering top-N from a one-second cache, “my rank” via O(log n) ZREVRANK plus cross-shard counts, and daily/weekly boards via TTL’d window keys - trading a second of freshness for rank queries in single-digit milliseconds under a 200K-updates-per-second firehose.