git clone https://github.com/andrey123h/Leaderboard-System
PORT=3000
NODE_ENV=production
DB_HOST=postgres
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=123456
DB_NAME=leaderboard_db
REDIS_URL=redis://redis:6379
docker-compose up --build
users Holds user information.
| Column | Type |
|---|---|
id |
BIGSERIAL |
name |
VARCHAR(100) |
image_url |
TEXT |
created_at |
TIMESTAMP |
scores Holds user score data used for leaderboard rankings.
| Column | Type |
|---|---|
user_id |
BIGINT |
score |
BIGINT |
updated_at |
TIMESTAMP |
The schema is designed to handle high read and write throughput - separating user and scores data into different tables, where scores are updated frequently but user information are not.
Reads are faster in this two-table approach because theres less to read. PostgreSQL reads far fewer pages and moves far less data per query then single table (users + scores). In this design the scores table has tiny rows, this makes leaderboard queries significantly faster.
Writes are faster in this approach also, each update writes far fewer bytes compared to updating a wide single table - In PostgreSQL, updates create new row versions (MVCC), so smaller rows mean fewer bytes written.
An index on score lets PostgreSQL quickly retrieve the top N users without scanning the entire table, providing efficient leaderboard operation. Since the scores table is small - index maintenance is lightweight.
Each of the two tables has a clear responsibility, this separation improves modularity, allowing each table to evolve independently - making the system easier to maintain, scale, and adapt to new features.
The caching layer is designed to improve performance for the leaderboard query retrieving the top users.
This simple cache implementation stores the top 100 users in memory.
Reads are faster in this approach because the system can return the leaderboard instantly without querying PostgreSQL. The database is only accessed when a request asks for more than 100 users.
Writes (adding or updating scores) trigger both a database update and a full cache refresh.
Add or Update Score
- Update the PostgreSQL database.
- Query the latest top 100 users ordered by score.
- Replace the cache with the new result.
Retrieve Top N Users
- If N ≤ 100, read from cache
- If N > 100, query the DB
We can optimize even further by implementing an eviction policy and by caching partial ranking windows (e.g., rank ranges) to speed up the 'Retrieve a user’s position along with 5 users above and below' operation.
