How Games Rank Millions of Players in Real Time

Why this matters: SQL ORDER BY Doesn’t Scale to Millions of Players

Your mobile game has 4 million active players. Every match updates a player’s score. Players check the leaderboard constantly — their rank, the top 100, their friends’ positions.

The naive approach is a SQL table with a score column and SELECT * FROM players ORDER BY score DESC LIMIT 100. At 4 million rows, that query takes 800ms even with an index. Now add 10,000 score updates per second from concurrent matches, and you’re running 10,000 UPDATE statements per second while simultaneously running thousands of ORDER BY queries. The database locks up. Leaderboard queries time out at 5 seconds. Players see stale rankings or loading spinners.

The core issue is that SQL databases aren’t optimized for “insert/update a value AND maintain a sorted rank in real time.” An ORDER BY query re-sorts or scans an index on every read. An UPDATE to a score doesn’t automatically recalculate the player’s rank — that requires re-scanning the index. At scale, these two operations fight for the same index locks.

Redis Sorted Sets (ZSETs) solve this with a data structure purpose-built for ranked data. A ZSET maintains elements in score-sorted order using a skip list internally. ZADD inserts or updates a member with a score in O(log N). ZREVRANK returns a member’s rank from the top in O(log N). ZREVRANGE retrieves the top N members in O(log N + M) where M is the count. At 4 million members, every operation completes in microseconds — not milliseconds, not seconds.

My rule of thumb Leaderboards are read-heavy until they are not. The write path, archive policy, and consistency model matter as much as the top-100 query.

The mental model: Skip Lists, Atomic Updates, and O(log N) Everything

A Redis Sorted Set stores (member, score) pairs in a structure that maintains automatic score-based ordering. Under the hood, Redis uses a skip list — a probabilistic data structure that provides O(log N) search, insert, and delete, similar to a balanced binary tree but simpler to implement.

The key operations that the interactive visualization walks through step by step:

ZADD inserts a member with a score. If the member already exists, it updates the score and repositions the element in the sorted order. This is atomic — no read-modify-write race condition.

ZADD leaderboard 1500 "player:alice"
ZADD leaderboard 2300 "player:bob"
ZADD leaderboard 1800 "player:carol"
# Sorted internally: bob(2300), carol(1800), alice(1500)

ZINCRBY atomically increments a member’s score. This is the workhorse for leaderboards — after every match, you increment the winner’s score. Redis handles the re-sorting internally.

ZINCRBY leaderboard 150 "player:alice"
# alice's score: 1500 + 150 = 1650
# New order: bob(2300), carol(1800), alice(1650)

ZREVRANK returns a member’s 0-indexed rank from the top (highest score = rank 0). At 4 million members, this takes ~20 microseconds.

ZREVRANK leaderboard "player:carol"
# Returns: 1 (carol is ranked #2, 0-indexed as 1)

ZREVRANGE retrieves a range of members sorted by score descending, with optional WITHSCORES. This is how you build the “Top 100” view.

ZREVRANGE leaderboard 0 9 WITHSCORES
# Returns top 10 players with their scores

ZRANGEBYSCORE finds all members within a score bracket — useful for matchmaking tiers or division-based leaderboards.

ZRANGEBYSCORE leaderboard 1000 2000 WITHSCORES
# Returns all players with scores between 1000 and 2000

In the visualization, you can step through 8 stages that build a leaderboard from scratch. Watch players slide to new positions as scores update, and see the Redis commands execute with animated results showing the internal re-ordering.

Decisions that matter

Parameter	Range	Low End Effect	High End Effect
ZSET Size (members)	1K – 100M	Small leaderboard. Fits easily in memory. All operations sub-microsecond.	100M members consume ~10-15GB of Redis memory. Operations still O(log N) but memory becomes the constraint.
Update Frequency	1/s – 100K/s	Lazy updates. Rankings are nearly real-time with minimal Redis load.	100K ZINCRBY/s. Redis single-threaded command processing becomes the bottleneck. Need pipelining or sharding.
maxmemory	256MB – 64GB	Small Redis instance. ZSET size capped early. Risk of OOM and eviction.	Large instance. Room for massive leaderboards but expensive infrastructure.
maxmemory-policy	noeviction, allkeys-lru, volatile-lru	noeviction: Redis rejects writes when full. Leaderboard updates fail. Safe — no data loss.	allkeys-lru: Redis evicts least-recently-used keys. Other cached data may be evicted to make room for the leaderboard.
Persistence (RDB/AOF)	Off – AOF always	No persistence. Fastest. But leaderboard lost on Redis restart. Full rebuild from database required.	AOF with fsync=always. Every write persisted to disk. ~10x slower writes but no data loss on crash.

Where it breaks: Redis Memory Exhaustion from Large ZSETs

The failure mode: Your game grows to 50 million registered players. The main leaderboard ZSET contains all 50M members, consuming ~8GB of Redis memory. You also maintain seasonal leaderboards (winter, spring, summer, fall), weekly leaderboards, and per-region leaderboards.

Total ZSET memory usage hits 45GB. Your Redis instance has maxmemory=48GB. A new seasonal leaderboard is created, pushing memory usage past the limit. With maxmemory-policy=noeviction, Redis starts rejecting all write commands. Leaderboard updates fail. Player scores from completed matches are lost.

What you will notice:

• Application logs show OOM command not allowed when used memory > 'maxmemory' from Redis
• redis-cli INFO memory shows used_memory approaching or exceeding maxmemory
• redis-cli ZCARD leaderboard:global returns 50,000,000+ members
• redis-cli MEMORY USAGE leaderboard:global shows the single key consuming 8GB+
• Score update API endpoints return 500 errors while read endpoints still work (cached responses)

What I would do: Shard leaderboards by region and tier. Instead of one global ZSET with 50M members, create leaderboard:na, leaderboard:eu, leaderboard:asia — each with ~15M members.

For tiered games, create leaderboard:gold, leaderboard:platinum, etc. Aggregate the top 1000 from each shard into a leaderboard:global:top1000 that’s much smaller. Remove inactive players (no score change in 90 days) from active leaderboards and archive them to PostgreSQL.

Hint Run redis-cli MEMORY USAGE leaderboard:global SAMPLES 0 regularly to track your ZSET’s memory footprint. Plan for 30% headroom above your peak expected size.

# Sharded leaderboard updates
import redis

r = redis.Redis(host="localhost", port=6379)

def update_score(player_id: str, region: str, tier: str, score_delta: int):
    pipe = r.pipeline()
    
    # Update regional leaderboard
    pipe.zincrby(f"leaderboard:{region}", score_delta, player_id)
    
    # Update tier leaderboard
    pipe.zincrby(f"leaderboard:{tier}", score_delta, player_id)
    
    # Update weekly leaderboard (with TTL)
    weekly_key = f"leaderboard:weekly:{get_week_number()}"
    pipe.zincrby(weekly_key, score_delta, player_id)
    pipe.expire(weekly_key, 604800)  # 7 days TTL
    
    pipe.execute()

def get_global_top_100():
    """Merge top players from all regional shards"""
    all_top = []
    for region in ["na", "eu", "asia", "latam"]:
        top = r.zrevrange(f"leaderboard:{region}", 0, 99, withscores=True)
        all_top.extend(top)
    
    # Sort merged results and take top 100
    all_top.sort(key=lambda x: x[1], reverse=True)
    return all_top[:100]

Where it breaks: Single-Key Write Contention on Hot Leaderboards

The failure mode: Your battle royale game ends 200 matches per minute, each with 100 players. Every match completion triggers 100 ZINCRBY commands to the same leaderboard key. That’s 20,000 writes per minute — 333 per second — to a single Redis key.

Redis is single-threaded: all 333 commands are serialized. Each ZINCRBY on a 10M-member ZSET takes ~25 microseconds. Total serialized processing time: 333 × 25μs = 8.3ms per second. Sounds fine — but add score reads (ZREVRANK, ZREVRANGE), and the total command rate hits 5,000/second on one key. Redis command queue depth grows. redis-cli --latency shows p99 jumping from 0.2ms to 15ms. Downstream API latency follows.

What you will notice:

• redis-cli --latency-history shows latency increasing over time
• redis-cli INFO commandstats shows cmdstat_zincrby:calls=350,usec=8750,usec_per_call=25.00 — normal per-call but high volume
• redis-cli SLOWLOG GET 10 starts showing ZREVRANGE commands taking >10ms
• Application metrics show redis_command_duration_p99 climbing from 1ms to 15ms
• The Redis instance CPU (single core) is at 40%+ utilization

What I would do: Pipeline batch updates instead of sending individual commands. Buffer score updates in the application for 100-500ms, then send them all in a single Redis pipeline. This reduces the number of round trips and allows Redis to process commands in a burst.

For extreme scale, shard the leaderboard across multiple Redis instances using consistent hashing on the player ID.

Warning Redis is single-threaded. Even though individual ZINCRBY commands are fast (~25μs), thousands of commands per second to a single key will saturate the command queue and spike p99 latency for all Redis operations.

# Batch updates with pipelining
from collections import defaultdict
import threading
import time

class LeaderboardBatcher:
    def __init__(self, redis_client, flush_interval=0.5):
        self.redis = redis_client
        self.buffer = defaultdict(int)  # player_id -> score_delta
        self.lock = threading.Lock()
        self.flush_interval = flush_interval
        self._start_flusher()
    
    def increment(self, player_id: str, delta: int):
        with self.lock:
            self.buffer[player_id] += delta
    
    def _flush(self):
        with self.lock:
            if not self.buffer:
                return
            updates = dict(self.buffer)
            self.buffer.clear()
        
        pipe = self.redis.pipeline()
        for player_id, delta in updates.items():
            pipe.zincrby("leaderboard:global", delta, player_id)
        pipe.execute()  # One round trip for all updates
    
    def _start_flusher(self):
        def run():
            while True:
                time.sleep(self.flush_interval)
                self._flush()
        threading.Thread(target=run, daemon=True).start()

Where it breaks: Stale Rankings from Eventual Consistency

The failure mode: To reduce Redis load, your team introduces a caching layer. The API caches ZREVRANGE leaderboard 0 99 results in a local in-memory cache with a 30-second TTL.

When a player checks their rank, they see where they were 30 seconds ago. After a big win that should push them from rank 150 to rank 12, they still see rank 150 for up to 30 seconds. They close the app thinking it’s broken. Worse, during a timed tournament with a leaderboard-based reward cutoff, players near the boundary can’t tell if they’ve qualified until the cache refreshes.

What you will notice:

• Player support tickets report “my score updated but my rank didn’t change”
• A/B comparison of cached vs. uncached rank queries shows discrepancies for recently active players
• The caching layer’s hit rate is 98%+ — reads are fast but stale
• During tournament endings, the leaderboard shows a “frozen” snapshot while scores are still updating
• redis-cli ZREVRANK leaderboard "player:xyz" returns the correct rank, but the API returns a stale value

What I would do: Use a tiered consistency approach. For the “Top 100” view (high read volume, low staleness sensitivity), cache with a 5-10 second TTL. For a player’s own rank (low read volume per user, high staleness sensitivity), query Redis directly — ZREVRANK takes 20μs, so there’s no need to cache it.

For tournament cutoff scenarios, bypass the cache entirely during the last 5 minutes of the event.

Hint A player’s own rank should always be fetched directly from Redis via ZREVRANK — it takes ~20μs even at 10M members. Only cache aggregate views like the “Top 100” list.

# Tiered consistency leaderboard API
import time

class LeaderboardService:
    def __init__(self, redis_client, cache):
        self.redis = redis_client
        self.cache = cache
    
    def get_top_100(self) -> list:
        """Cached — acceptable staleness for browsing"""
        cached = self.cache.get("top_100")
        if cached:
            return cached
        
        result = self.redis.zrevrange("leaderboard", 0, 99, withscores=True)
        self.cache.set("top_100", result, ttl=10)  # 10s TTL
        return result
    
    def get_my_rank(self, player_id: str) -> int:
        """Direct Redis query — player's own rank must be fresh"""
        rank = self.redis.zrevrank("leaderboard", player_id)
        return rank + 1 if rank is not None else None  # 1-indexed
    
    def get_my_score(self, player_id: str) -> float:
        """Direct Redis query — player's own score must be fresh"""
        return self.redis.zscore("leaderboard", player_id)
    
    def get_players_near_me(self, player_id: str, count: int = 5) -> list:
        """Direct query — contextual ranking around the player"""
        rank = self.redis.zrevrank("leaderboard", player_id)
        if rank is None:
            return []
        start = max(0, rank - count)
        end = rank + count
        return self.redis.zrevrange("leaderboard", start, end, withscores=True)

The tradeoff

Approach	Pros	Cons
Single Redis ZSET	Simple. All operations in one place. Atomic updates. Consistent rankings.	Memory-bound. Single-key write contention at scale. Single point of failure.
Sharded ZSETs (by region/tier)	Horizontal scaling. Each shard fits in smaller Redis instances. Reduced contention.	Cross-shard ranking requires merging. Global rank computation is approximate or expensive.
Pipelined batch updates	Reduces Redis round trips by 10-50x. Higher throughput.	Adds latency (batch window) to score updates. More complex application code.
Individual ZINCRBY per event	Immediate ranking updates. Simple code.	High command rate. Redis single-thread saturation at 10K+ updates/second per key.
Cached leaderboard reads	Massive read throughput. Low Redis load for popular queries (top 100).	Stale data. Players see old rankings for up to TTL duration. Tournament edge cases.
Direct Redis reads	Always-fresh rankings. Consistent with latest scores.	Every read hits Redis. At 100K readers checking rank simultaneously, Redis becomes a bottleneck.
Periodic snapshots to PostgreSQL	Durable backup. Enables historical analytics and time-series ranking.	Snapshot lag. Recovery from PostgreSQL is slow (minutes to rebuild 10M-member ZSET from SQL).

A practical checklist

Step 1: Measure your ZSET memory footprint. Run redis-cli MEMORY USAGE leaderboard:global SAMPLES 0 to get the exact memory consumption. A ZSET with 10M members and 20-character member names uses approximately 1.5-2GB. Plan Redis maxmemory accordingly with 30% headroom.

Step 2: Monitor Redis command latency. Use redis-cli --latency-history -i 5 to sample latency every 5 seconds. If p99 exceeds 5ms, investigate SLOWLOG GET 10 for expensive commands. ZREVRANGE on large ranges (top 10,000) can take 5-10ms.

Step 3: Implement leaderboard archival. Players who haven’t updated their score in 90+ days should be moved to a separate leaderboard:inactive or persisted to PostgreSQL. Use ZSCAN to iterate and ZREM to remove stale members. Run this during off-peak hours.

# Redis memory diagnostics
redis-cli DBSIZE                                    # Total keys
redis-cli ZCARD leaderboard:global                  # Members in ZSET
redis-cli MEMORY USAGE leaderboard:global SAMPLES 0 # Bytes used
redis-cli INFO memory                               # Overall memory stats
redis-cli OBJECT ENCODING leaderboard:global        # Internal encoding (skiplist vs ziplist)

Step 4: Set up persistence for recovery. Configure RDB snapshots every 5 minutes and AOF with appendfsync everysec. This gives you a recovery point within 1-5 minutes if Redis crashes. Test your recovery procedure: redis-cli BGSAVE, stop Redis, restart, and verify the leaderboard is intact.

Step 5: Load test before launch. Use redis-benchmark to simulate your expected write and read patterns. redis-benchmark -t zadd -n 1000000 -q tests ZADD throughput. Compare with your expected peak load and add 50% margin.

Try it yourself

The visualization builds a leaderboard from scratch in 8 animated stages. Watch ZADD insert players with scores, see ZINCRBY atomically update scores and re-sort the leaderboard in real time, and observe ZREVRANK and ZREVRANGE return rankings instantly.

Players visually slide to new positions as their scores change — making the O(log N) re-ordering tangible and intuitive.

Try the interactive Leaderboard with Sorted Sets visualization → Step through Redis ZADD, ZINCRBY, ZREVRANK, and ZREVRANGE operations to understand how sorted sets maintain real-time rankings at the scale of millions of players.