Every product eventually asks for a leaderboard: top 10 sellers, most-liked posts, highest-scoring players. The naive query — SELECT * FROM items ORDER BY score DESC LIMIT 10 — works fine until it doesn't. At scale, this pattern becomes one of the most common causes of database meltdowns.

Why it breaks: Even with an index on score, that query is read-heavy. Every write updates the score, invalidating any cache. Every read scans the top of the index. When millions of users refresh the leaderboard simultaneously, you're doing a sorted scan per request against a hot index page that's constantly being modified.

Real-world example: A gaming company launched a battle royale mode with a global leaderboard refreshing every 30 seconds on 500K concurrent clients. Their Postgres query — indexed and tuned — took 8ms in isolation. Under load, lock contention on the top index pages pushed p99 to 4 seconds. Solution: they moved leaderboards to Redis ZSET (sorted set). ZADD on score change is O(log N), ZREVRANGE 0 9 for the top 10 is O(log N + 10). Query time dropped to 200μs.

Patterns for scale, in order of cost:

• Redis sorted sets — the default answer for real-time leaderboards under 10M entries. Skip lists give you O(log N) rank queries and updates.
• Approximate leaderboards — for "top influencers" style queries, use Count-Min Sketch or a probabilistic top-K structure. Accept 5% error, gain 100x throughput.
• Bucketed leaderboards — bucket users by score range (e.g., 0-100, 101-500), rank within buckets. Trades global precision for locality.
• Materialized snapshots — if freshness tolerance is minutes, precompute the top 100 every N seconds. One read, cached everywhere.
• Percentile rank instead of absolute rank — "you're in the top 3%" is often more meaningful than "you're #14,832" and can be computed from a histogram, not a full sort.

Rule of thumb: If your leaderboard has more than 10K entries and refreshes more than 1 request/second per user, do NOT use SQL ORDER BY ... LIMIT. The read amplification from index contention will find you. Redis ZSETs handle 100K ops/sec on a single node for ranked queries — that's your first stop.

The subtler mistake: computing user's own rank with COUNT(*) WHERE score > X. That's O(N) per lookup. Use ZREVRANK for O(log N), or serve an approximate percentile from a t-digest.