Real-time Leaderboards

Why sorting a million rows in SQL every time someone scores a point will melt your database.

The idea

In a multiplayer game with 1 million active players, scores update constantly. If you store scores in a SQL database, finding the "Top 10 Players" requires running ORDER BY score DESC LIMIT 10. This is incredibly slow to run 1,000 times a second. Even worse is finding a specific player's rank (e.g., "You are rank #42,105"). SQL has to count all 42,104 people ahead of you. To build a real-time leaderboard, you must use a data structure that is always sorted in memory: a Redis Sorted Set.

Step 1: The SQL way. We need the Top 3 out of 1 million players. SQL scans the index and sorts.

How it works (Redis ZSET)

A Redis Sorted Set (ZSET) stores data in RAM using a specialized data structure called a Skip List. When Alice scores 50 points, Redis doesn't just save the number; it physically moves Alice's record in memory to ensure the entire list remains perfectly sorted at all times. Therefore, asking for the "Top 10" takes O(log N) time: nearly instantaneous, regardless of whether you have 1 thousand or 10 million players.

// 1. Updating a score
// Adds 50 points to "alice" in the "global_leaderboard" set.
// Redis automatically shifts her position in the sorted list!
await redis.zincrby("global_leaderboard", 50, "alice");

// 2. Fetching the Top 3 Players
// ZREVRANGE returns the highest scores (Reverse Range)
const topPlayers = await redis.zrevrange("global_leaderboard", 0, 2, "WITHSCORES");
// Returns: ["alice", "150", "bob", "120", "charlie", "90"]

// 3. Getting a specific player's Rank
// ZREVRANK instantly tells you how many people are ahead of Alice
const rank = await redis.zrevrank("global_leaderboard", "alice"); // returns 0 (1st place)

Cost

Redis stores this entirely in RAM. 1 million players in a ZSET takes roughly 100MB of RAM. This is very cheap for one global leaderboard. However, if you want to create a personalized leaderboard for every single user (e.g. "Rank among your friends"), you cannot create 1 million ZSETs without blowing through your RAM budget. You must calculate friend leaderboards on-the-fly using standard SQL queries.

Watch out for