In the competitive world of fitness apps, real-time feedback is king. A live leaderboard showing workout rankings can dramatically increase user engagement, motivating users to push their limits. However, building a leaderboard that is both real-time and scalable presents a significant technical challenge. Naively querying your main database for rankings every few seconds can lead to performance bottlenecks and a sluggish user experience, especially as your user base grows.
This tutorial will guide you through building a performance-focused, real-time workout leaderboard using a powerful combination of technologies. We'll store our core data in PostgreSQL, a robust and reliable relational database. For lightning-fast ranking and score updates, we'll use Redis as a caching layer. Finally, we'll use Node.js and Socket.IO to push live updates to the client, ensuring the leaderboard is always current.
By the end of this guide, you will have a solid understanding of how to architect a real-time system that is efficient, scalable, and ready for production.
Prerequisites:
- Basic understanding of Node.js and Express.
- Familiarity with JavaScript (ES6+).
- Docker installed on your machine (for easily running PostgreSQL and Redis).
- Node.js and npm (or yarn) installed.
Understanding the Problem
A real-time leaderboard has to do two things exceptionally well: ingest a high volume of score updates and serve rankings to many concurrent users instantly.
The Challenge:
- Database Load: Constantly re-calculating ranks with SQL queries that involve ordering large datasets (
ORDER BY score DESC) is computationally expensive and puts a heavy load on your primary database. - Latency: Users expect immediate updates. The time it takes to write to the database, re-calculate the leaderboard, and have the client re-fetch the data can result in noticeable lag.
Our Approach: The "Cache-Aside" Pattern
To solve this, we will use the "cache-aside" pattern. The flow will be:
- Write-through: New workout scores are written to our primary database (PostgreSQL) for durability.
- Cache Update: Simultaneously, we update the user's score in a Redis Sorted Set. Redis is an in-memory data store, making operations like sorting and ranking incredibly fast.
- Real-time Push: After the update, our Node.js server will emit a "leaderboard updated" event via WebSockets (Socket.IO) to all connected clients.
- Client Update: The clients receive this event and update the leaderboard display in real-time, without needing to send another HTTP request.
This architecture ensures that our PostgreSQL database isn't hammered with read requests for the leaderboard, and users get a seamless, real-time experience.
Prerequisites
Before we start coding, let's get our environment set up. We'll use Docker to quickly spin up PostgreSQL and Redis instances.
Create a docker-compose.yml file in your project root:
# docker-compose.yml
version: '3.8'
services:
postgres:
image: postgres:14-alpine
restart: always
environment:
- POSTGRES_USER=admin
- POSTGRES_PASSWORD=password
- POSTGRES_DB=workout_leaderboard
ports:
- '5432:5432'
volumes:
- db_data:/var/lib/postgresql/data
redis:
image: redis:7-alpine
restart: always
ports:
- '6379:6379'
volumes:
- redis_data:/data
volumes:
db_data:
redis_data:
Run the following command to start the services:
docker-compose up -d
You should see both postgres and redis containers running.
Now, let's initialize our Node.js project:
mkdir workout-leaderboard-api
cd workout-leaderboard-api
npm init -y
npm install express pg redis socket.io cors dotenv
Step 1: Setting Up the Node.js Server and Database Connection
First, let's create a basic Express server and establish connections to PostgreSQL and Redis.
What we're doing
We'll create an Express server, connect to our PostgreSQL database to persist user and workout data, and connect to Redis for leaderboard caching.
Implementation
Create an index.js file:
// index.js
require('dotenv').config();
const express = require('express');
const http = require('http');
const { Server } = require("socket.io");
const cors = require('cors');
const { Pool } = require('pg');
const { createClient } = require('redis');
const app = express();
app.use(cors());
app.use(express.json());
const server = http.createServer(app);
const io = new Server(server, {
cors: {
origin: "*", // Allow all origins for simplicity
}
});
// PostgreSQL Connection
const pgClient = new Pool({
user: 'admin',
host: 'localhost',
database: 'workout_leaderboard',
password: 'password',
port: 5432,
});
pgClient.on('connect', () => console.log('Connected to PostgreSQL'));
// Redis Connection
const redisClient = createClient({ url: 'redis://localhost:6379' });
redisClient.on('connect', () => console.log('Connected to Redis'));
redisClient.on('error', err => console.log('Redis Client Error', err));
// Database schema initialization
const initializeDatabase = async () => {
await pgClient.query(`
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS workouts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
score INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
`);
console.log("Database tables are ready.");
};
const PORT = process.env.PORT || 3001;
server.listen(PORT, async () => {
console.log(`Server running on port ${PORT}`);
await pgClient.connect();
await redisClient.connect();
await initializeDatabase();
});
io.on('connection', (socket) => {
console.log('a user connected:', socket.id);
socket.on('disconnect', () => {
console.log('user disconnected:', socket.id);
});
});
How it works
This code sets up an Express server and integrates Socket.IO. It also creates connection clients for both PostgreSQL and Redis. When the server starts, it connects to both data stores and runs a function to ensure the necessary tables (users, workouts) exist in our PostgreSQL database.
Step 2: Submitting Scores and Updating the Leaderboard
Now let's create an endpoint to submit a new workout score. This is where our cache-aside logic will be implemented.
What we're doing
We'll create a POST /score endpoint that:
- Validates the input.
- Inserts the new workout score into the PostgreSQL
workoutstable. - Updates the user's total score in the Redis leaderboard.
- Broadcasts the updated leaderboard to all clients.
Implementation
Add the following to your index.js:
// Add this before server.listen()
const LEADERBOARD_KEY = 'workout_leaderboard';
// Function to get the leaderboard from Redis
const getLeaderboard = async () => {
const rawLeaderboard = await redisClient.zRangeWithScores(LEADERBOARD_KEY, 0, 9, { REV: true });
// ZRANGE with REV returns highest scores first.
return rawLeaderboard.map(entry => ({
username: entry.value,
score: entry.score,
}));
};
app.post('/score', async (req, res) => {
const { userId, score } = req.body;
if (!userId || score == null) {
return res.status(400).json({ error: 'User ID and score are required.' });
}
try {
// 1. Write to PostgreSQL (Primary DB)
await pgClient.query('INSERT INTO workouts(user_id, score) VALUES($1, $2)', [userId, score]);
// 2. Update score in Redis Sorted Set
// ZINCRBY increments the score of a member in a sorted set
const newTotalScore = await redisClient.zIncrBy(LEADERBOARD_KEY, score, userId.toString());
console.log(`User ${userId} new total score: ${newTotalScore}`);
// 3. Get updated leaderboard and broadcast
const updatedLeaderboard = await getLeaderboard();
io.emit('leaderboard_update', updatedLeaderboard); // Emit to all clients
res.status(201).json({ success: true, newTotalScore });
} catch (error) {
console.error('Error submitting score:', error);
res.status(500).json({ error: 'Internal server error' });
}
});
app.get('/leaderboard', async (req, res) => {
try {
const leaderboard = await getLeaderboard();
res.json(leaderboard);
} catch (error) {
console.error('Error fetching leaderboard:', error);
res.status(500).json({ error: 'Internal server error' });
}
});
How it works
- PostgreSQL Write: We first write the individual workout score to PostgreSQL. This is our system of record.
- Redis
ZINCRBY: We use theZINCRBYcommand, which is atomic and incredibly efficient. It increments the score for a given member (userId) in the sorted set. If the member doesn't exist, it's added with the score. Redis automatically re-orders the set. - Broadcast: We then fetch the top 10 from Redis using
ZRANGEand emit aleaderboard_updateevent. All connected clients will receive this new top 10 list instantly.
Step 3: Building the Real-time Client
Let's create a simple HTML page with JavaScript to display the leaderboard and listen for real-time updates.
What we're doing
We will create a basic frontend that fetches the initial leaderboard and then uses the Socket.IO client to listen for the leaderboard_update event to refresh the display.
Implementation
Create a public folder in your project root, and inside it, an index.html file.
<!-- public/index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Real-time Workout Leaderboard</title>
<style>
/* Add some basic styling */
body { font-family: sans-serif; padding: 20px; }
h1 { text-align: center; }
#leaderboard { list-style: none; padding: 0; max-width: 500px; margin: auto; }
#leaderboard li { display: flex; justify-content: space-between; padding: 10px; border-bottom: 1px solid #ddd; }
#leaderboard li:nth-child(1) { font-weight: bold; background-color: #ffd700; }
</style>
</head>
<body>
<h1>Live Workout Leaderboard</h1>
<ol id="leaderboard"></ol>
<script src="https://cdn.socket.io/4.7.5/socket.io.min.js"></script>
<script>
const leaderboardList = document.getElementById('leaderboard');
const API_URL = 'http://localhost:3001';
// Function to render the leaderboard
function renderLeaderboard(leaderboard) {
leaderboardList.innerHTML = '';
leaderboard.forEach((entry, index) => {
const li = document.createElement('li');
li.innerHTML = `<span>${index + 1}. ${entry.username}</span> <span>${entry.score}</span>`;
leaderboardList.appendChild(li);
});
}
// Fetch initial leaderboard data
async function fetchInitialLeaderboard() {
try {
const response = await fetch(`${API_URL}/leaderboard`);
const data = await response.json();
renderLeaderboard(data);
} catch (error) {
console.error('Failed to fetch leaderboard:', error);
}
}
// Connect to the WebSocket server
const socket = io(API_URL);
socket.on('connect', () => {
console.log('Connected to server with ID:', socket.id);
});
// Listen for real-time updates
socket.on('leaderboard_update', (leaderboard) => {
console.log('Received leaderboard update:', leaderboard);
renderLeaderboard(leaderboard);
});
socket.on('disconnect', () => {
console.log('Disconnected from server');
});
// Initial load
fetchInitialLeaderboard();
</script>
</body>
</html>
```To serve this file, add this to `index.js`:
```javascript
// index.js
app.use(express.static('public'));
Performance Considerations
- PostgreSQL Indexing: For a production system with many users, you should add an index on
workouts(user_id)to speed up queries that might aggregate a user's total score. However, in our design, this is less critical since the hot path (reading the leaderboard) is served by Redis. - Redis Memory: Since Redis is an in-memory store, ensure you have enough RAM to hold your entire leaderboard. For millions of users, this can be significant. Redis does offer persistence options if needed.
- Data Consistency: There's a small chance of inconsistency if the Redis update succeeds but the PostgreSQL write fails. For a leaderboard, this is often an acceptable trade-off for performance. For mission-critical data, you would need a more complex two-phase commit or transactional outbox pattern.
Security Best Practices
- Input Validation: Always validate and sanitize user input on the server-side before processing it.
- Authentication: The
/scoreendpoint should be protected. Only authenticated users should be able to post scores for themselves. - WebSocket Security: Implement authentication for your WebSocket connections to prevent unauthorized clients from connecting and listening to events.
Conclusion
We have successfully built a high-performance, real-time leaderboard. By strategically using PostgreSQL for data persistence and Redis for its speed in handling sorted data, we have created an architecture that is both robust and scalable. The addition of WebSockets provides a seamless, engaging real-time experience for the user.
This project demonstrates the power of choosing the right tool for the job. Instead of forcing a single database to handle all tasks, we've composed a solution that leverages the strengths of multiple technologies to create a superior product.
Next steps for readers:
- Add user creation and authentication.
- Implement pagination or infinite scrolling for the leaderboard.
- Create a front-end interface for submitting scores.