A leaderboard looks trivial until it has to be live, global, and correct under load: rank the world by score, push every change to every connected client, and never melt the database doing it. Here's a pragmatic architecture for exactly that, built on Postgres and Supabase Realtime.
The data model
Keep the source of truth boring. One append-only events table for every scored action, and a scores aggregate you actually rank on.
create table scores (
user_id uuid primary key references users(id),
total bigint not null default 0,
updated_at timestamptz not null default now()
);
create index scores_total_idx on scores (total desc, user_id);
The descending index on total is the whole game — it lets you read the top N without a sort, and lets window functions find a single user's neighborhood cheaply. Friends-only views are just the same query with a where user_id = any($friend_ids) filter; you do not need a separate table for it.
Computing rank without scanning everything
The naive count(*) where total > me is O(n) per lookup and gets quadratic when everyone refreshes at once. Use a window function and let the index do the work:
select user_id, total,
rank() over (order by total desc) as rank
from scores
order by total desc
limit 100;
For the top of the board, that's bounded by limit. The hard part is "where do I stand at rank 48,201?" Don't paginate to find them — compute a single rank with a count against the index, then fetch the window around it:
-- the user's own rank, one indexed count
select 1 + count(*) from scores where total > $my_total;
When reads outpace writes by orders of magnitude (the usual case for a global board), materialize it. A materialized view refreshed on a schedule, or a small leaderboard_top table you maintain in a trigger, turns every read into a primary-key lookup. Refresh concurrently so readers never block:
refresh materialized view concurrently leaderboard_top;
Reach for the maintained/materialized path once the full-table window function shows up in your slow-query log — not before. Premature caching just adds a staleness bug.
Pushing updates with Supabase Realtime
Two transport choices. postgres_changes taps the WAL and emits a message per affected row — accurate, but it fans out raw row events and gets chatty on a hot table. broadcast is a lightweight pub/sub channel you send to yourself, ideal for shipping one already-computed payload (the new top 100) instead of a flood of per-row diffs.
The pattern that scales: a trigger or edge function recomputes the affected slice, then broadcasts the result. Clients subscribe to one channel:
const channel = supabase
.channel('leaderboard:global')
.on('broadcast', { event: 'rank_update' }, ({ payload }) => {
applyLeaderboard(payload.top) // pre-sorted top N
})
.subscribe()
One channel, one small message per change, every client in sync. Reserve postgres_changes for views where clients genuinely need row-level granularity.
Optimistic UI
Don't wait for the round trip to show the user their own move. Bump their score locally the instant they act, then reconcile when the authoritative event lands:
function onScore(delta) {
setMyScore(s => s + delta) // optimistic
supabase.rpc('record_event', { delta }) // fire to server
}
// reconcile from the broadcast — server value wins
function applyLeaderboard(top) {
const me = top.find(r => r.user_id === myId)
if (me) setMyScore(me.total)
}
The local guess makes the app feel instant; the broadcast is the correction. If they ever disagree, the server wins — silently, because the numbers usually match.
Surviving many concurrent writes
A global board with thousands of active users will try to recompute on every single event. Don't. Debounce the recompute and coalesce bursts into one refresh:
let pending = false
function scheduleRefresh() {
if (pending) return
pending = true
setTimeout(async () => {
pending = false
await refreshAndBroadcast() // one recompute per window
}, 500)
}
Combine that with only recomputing affected ranks — a single new score only shifts the people it leapfrogged, so you rarely need to touch the whole table. Batch writes with insert ... on conflict do update, push the recompute to a debounced edge function, and broadcast at a human-perceptible cadence (a few times per second is plenty). Your database stays calm while the board still looks live.
Put together, that's a leaderboard that's instant for the actor, eventually-and-quickly consistent for everyone else, and cheap to run. We use exactly this stack on FartRank, a (yes, tongue-in-cheek) social fart-tracking app where the global ranking is the entire point — the silliness of the subject is a good stress test for the seriousness of the infrastructure.
Built by Furiosa Studio.
