Redis Sorted-Set Leaderboards

ELI5

Leaderboards live in Redis, not Postgres, because Redis sorted sets give O(log N) inserts and O(1) rank reads. Each XP award fires ZINCRBY leaderboard:global and ZINCRBY leaderboard:weekly:2026-W18. The weekly key carries an 8-day TTL so old weeks evaporate without housekeeping.

Technical Deep Dive

Key Layout

Postgres also holds leaderboard_global and leaderboard_weekly materialised views as a backup / cold-start source.

Update Path

flowchart LR
    XP[xp_transactions INSERT] --> H[Gamification handler]
    H --> Z1["ZINCRBY leaderboard:global +Δ user_id"]
    H --> Z2["ZINCRBY leaderboard:weekly:{week} +Δ user_id"]
    H --> E[EXPIRE weekly key 8d]

Read Path

sequenceDiagram
    autonumber
    participant API as core-api
    participant G as gamification-service
    participant RD as Redis
    participant PG as Postgres

    API->>G: GetLeaderboard(GLOBAL, limit=100, offset=0)
    G->>RD: ZREVRANGE leaderboard:global 0 99 WITHSCORES
    RD-->>G: [(user_id, score), ...]
    G->>PG: SELECT handle, avatar_url FROM users WHERE id IN (...)
    G-->>API: LeaderboardEntry[]

    API->>G: GetUserStats(user_id)
    G->>RD: ZREVRANK leaderboard:global user_id
    RD-->>G: rank

ZREVRANK is documented as O(log(N)) — close enough to constant for the operative range. ZREVRANGE over the top 100 is also bounded.

Weekly Reset

A cron job at Monday 00:00 UTC archives the previous week’s top-1000 to the leaderboards Postgres table, then lets the 8-day TTL expire the Redis key naturally.

INSERT INTO leaderboards (period, rank, user_id, xp)
VALUES ($1, $2, $3, $4);

Memory Footprint (rough)

A Redis ZSET entry is ~64 bytes. 1M users on leaderboard:global ≈ 64 MB. Weekly keys hold only users active that week — typically < 10% of global.

Key Terms

• ZADD / ZINCRBY → atomic add or increment; primary leaderboard mutators
• ZREVRANGE → reverse-ordered slice (top N first)
• ZREVRANK → user’s 0-indexed position in reverse order
• ISO week format 2006-W02 → Go time-format reference token; produces 2026-W18-style keys
• Cold-start backup → if Redis is wiped, leaderboard_global materialised view rebuilds the global ZSET via ZADD from Postgres

Q&A

Q: Why Redis sorted sets instead of ORDER BY total_xp DESC in Postgres? A: Sorted-set inserts and rank lookups are sub-millisecond at million-user scale; Postgres ORDER BY over a growing ledger requires either an index on a denormalised total or a materialised view refresh, both of which lag.

Q: How does the weekly leaderboard avoid unbounded growth? A: The TTL of 8 days on leaderboard:weekly:{week}. The “+1 day” buffer accommodates the Monday-archive cron job before the key disappears.

Q: What if Redis crashes? A: Persistence is on (--appendonly yes in docker-compose) so most state survives. Catastrophic loss is recoverable from xp_transactions: replay sums into Redis. The leaderboard_global materialised view is a faster snapshot source.

Q: How does the “tier leaderboard” envisioned in LeaderboardType.LEADERBOARD_TYPE_TIER get implemented? A: It is declared in the proto but no key layout is documented in nats/topology.md or the Gamification spec; it is reserved for future work. Don’t assume Redis keys exist for it yet.

Examples

Imagine the bowling alley scoreboard. Every time anyone scores, the score is dropped into the right slot of an always-sorted shelf — finding “rank #4” is just looking at slot 4. The weekly board has a sign that says “expires Sunday midnight” and the janitor doesn’t even need to reset it.