How to build a real-time leaderboard that handles millions of entries with O(log N) updates—using Redis Sorted Set instead of SQL ORDER BY.

📊 The Problem: Why SQL Struggles with Real-time Rankings

The High-Frequency Update Challenge

In competitive systems—games, trading platforms, live streaming—scores change constantly. Thousands of updates per second, and users expect rankings to reflect changes instantly.

SQL’s Limitations

Read-time Sorting

Traditional SQL approach:

1
2
3
4

SELECT user_id, score 
FROM leaderboard 
ORDER BY score DESC 
LIMIT 10;

Problems:

• Without index: O(N log N) sort on every query
• With B-tree index: Reads are fast (O(K) for LIMIT K), but…

Write Pressure on Index

The real bottleneck is writes:

• Every score update triggers B-tree rebalancing
• High-frequency writes cause lock contention
• Index fragmentation degrades performance over time
• MVCC overhead in concurrent update scenarios

1
2
3
4

Write rate: 10,000/sec
→ 10,000 index rebalances/sec
→ Disk I/O bottleneck
→ Read latency spikes to 100ms+

Maintaining global sorted order under high write rates leads to disproportionate index maintenance cost:

• ✅ Fast reads (show top 100) — achievable with proper indexing
• ✅ Fast writes (update scores constantly) — achievable in isolation
• ❌ Both simultaneously at high frequency — B-tree rebalancing + lock contention becomes the bottleneck

⚠️ This doesn’t mean SQL “can’t” do leaderboards. Advanced patterns (materialized views, CQRS, async aggregation) can work. But for the naive single-table model, Redis offers a simpler path.

🔧 The Solution: Redis Sorted Set (ZSet)

Data Structure Internals

ZSet is implemented as skip list + hash table:

• Skip list — Maintains sorted order with O(log N) insert/update/delete
• Hash table — Provides O(1) member lookup

1
2
3
4
5
6
7
8

Skip List Structure:

Level 3:  1 ─────────────────────────────────▶ 9
Level 2:  1 ────────▶ 4 ─────────────────────▶ 9
Level 1:  1 ───▶ 3 ─▶ 4 ───▶ 6 ───▶ 7 ───────▶ 9
Level 0:  1 ─▶ 2 ─▶ 3 ─▶ 4 ─▶ 5 ─▶ 6 ─▶ 7 ─▶ 8 ─▶ 9

Insert/Search: O(log N) by skipping levels

Insert-time Sorting

Unlike SQL’s read-time sorting, ZSet sorts on write:

Both offer similar asymptotic complexity. The difference is contention model: SQL has lock/latch contention under concurrent writes; Redis serializes operations in a single-threaded event loop, shifting the constraint to CPU throughput.

The key advantage: Redis avoids intra-data-structure lock contention by serializing all operations.

Data Model

1
2
3

Key:     leaderboard:season_2024
Score:   142.57 (float, used for sorting)
Member:  player_123 (unique identifier)

💻 Implementation

Basic Operations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

import Redis from 'ioredis';
const redis = new Redis();

const LEADERBOARD_KEY = 'leaderboard:season_2024';

// Update score (insert or overwrite)
await redis.zadd(LEADERBOARD_KEY, score, playerId);

// Get top 10 (descending order)
const top10 = await redis.zrevrange(LEADERBOARD_KEY, 0, 9, 'WITHSCORES');
// Returns: ['player_1', '250.5', 'player_2', '180.3', ...]

// Get specific user's rank (0-indexed)
const rank = await redis.zrevrank(LEADERBOARD_KEY, playerId);
// Returns: 42 (meaning 43rd place)

// Get user's score
const score = await redis.zscore(LEADERBOARD_KEY, playerId);

Data Enrichment Pattern

ZSet only stores IDs and scores. For full data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

async function getLeaderboardWithDetails(limit: number) {
  // Step 1: Get IDs and scores from ZSet
  const raw = await redis.zrevrange(LEADERBOARD_KEY, 0, limit - 1, 'WITHSCORES');
  
  // Step 2: Parse into structured data
  const entries = [];
  for (let i = 0; i < raw.length; i += 2) {
    entries.push({ playerId: raw[i], score: parseFloat(raw[i + 1]) });
  }
  
  // Step 3: Batch fetch details from cache/DB
  const details = await getPlayerDetailsFromCache(entries.map(e => e.playerId));
  
  // Step 4: Merge
  return entries.map((entry, i) => ({
    rank: i + 1,
    ...entry,
    ...details[i], // name, avatar, etc.
  }));
}

When scores are equal, ZSet sorts by member name (lexicographically). For custom tie-breaking:

1
2
3

// Option 1: Encode timestamp into score (simple but has precision limits)
const compositeScore = score + (1 - timestamp / 1e13);
await redis.zadd(LEADERBOARD_KEY, compositeScore, playerId);

⚠️ Production warning: Float encoding has precision drift risks for large scores or long-running leaderboards. A more robust approach is using structured member keys (playerId|timestamp) or a secondary index.

⚠️ Production Caveats

Memory Constraints

• ZSet stores all members + scores in RAM
• 1 million entries ≈ 50-100 MB (depending on member string length)
• Monitor memory usage; implement TTL or periodic cleanup for old seasons

Cold Data Strategy

Don’t keep historical leaderboards in Redis forever:

1
2
3
4
5
6

// Archive old season to database
async function archiveSeason(seasonKey: string) {
  const all = await redis.zrevrange(seasonKey, 0, -1, 'WITHSCORES');
  await db.leaderboardArchive.bulkInsert(parseEntries(all));
  await redis.del(seasonKey);
}

Cluster Mode Considerations

In Redis Cluster, related keys should use hash tags to ensure they’re on the same shard:

1
2
3
4
5
6
7

// Good: Same hash slot
const key1 = '{leaderboard}:season_1';
const key2 = '{leaderboard}:season_2';

// Bad: May be on different shards
const key1 = 'leaderboard:season_1';
const key2 = 'leaderboard:season_2';

Score Precision

Redis scores are IEEE 754 double-precision floats:

• Safe integer range: ±2^53
• For monetary values, consider storing as integers (cents, not dollars)

📝 Summary

• SQL struggles with high-frequency write + read scenarios due to index maintenance overhead
• ZSet = skip list + hash — O(log N) insert, O(log N) rank lookup, no lock contention
• Insert-time sorting — Reads are essentially free; sorting happens on write
• Trade-off — Memory-bound; requires data enrichment for full details

For leaderboards with millions of entries and thousands of updates per second, Redis ZSet delivers millisecond-level response times. The win isn’t Big-O—it’s the contention model.

References

• Skip List Data Structure
• Series Index: Redis Beyond Caching