Archetype 13 — Ranking / Leaderboard #

What this archetype is #

Submissions or events are aggregated into ranked outputs such as top-k, best score, or trending lists.

Examples: game leaderboard, top products, trending hashtags.

We will use global game leaderboard as the running example.

Layer 1: Entities and Postgres table design #

SubmissionState
BestState
AggregateState
LeaderboardView

Optional mechanic-dependent states:

WindowState
WatermarkState
ApproximationState

create table score_submissions (
  submission_id bigserial primary key,
  user_id bigint not null,
  score bigint not null,
  created_at timestamptz not null default now()
);

create table user_best_scores (
  user_id bigint primary key,
  best_score bigint not null,
  updated_at timestamptz not null default now()
);

create index user_best_scores_rank_idx
on user_best_scores (best_score desc, user_id asc);

Layer 2: Write path mechanics #

Record score #

insert into score_submissions (user_id, score)
values ($1, $2);

Update best score #

insert into user_best_scores (user_id, best_score)
values ($1, $2)
on conflict (user_id) do update
set best_score = greatest(user_best_scores.best_score, excluded.best_score),
    updated_at = now();

Read leaderboard #

select user_id, best_score
from user_best_scores
order by best_score desc, user_id asc
limit 100;

For windowed trending, use separate aggregate tables keyed by (window_start, key).

Layer 3: Fault tolerance #

• duplicate submission
• stale rank
• incorrect top-k
• ranking lag after score update

Layer 4: Scale #

Default hotspots:

• global aggregation
• hot score updates
• hot window state
• ranking store saturation

Common mitigations:

• materialized aggregate tables
• approximate heavy-hitter/sketch pipeline for trending
• periodic recompute for large windows