Leaderboards and Fragment Caching

When I worked as a server engineer for a game company, I spent some significant time dealing with leaderboards (a.k.a. high score lists).  And lo and behold, when I started consulting and was asked to build a fantasy stock/options/futures trading site, there I was coding leaderboards again.

With a small number of people, of course, it's not a big deal to generate leaderboards on the fly.  But if your site is lucky enough to become successful, you don't want your database queries to bring your site to its knees.  Things also get more complicated if you want to implement "around me" leaderboards which show the current user's position with a few people above and below.

Leaderboard Tables

The first step is to create separate tables specifically for the leaderboards, rather than expecting to use other tables (e.g. user, team, or network tables).  Periodically, do a query from the source table sorting by score.  After clearing out the leaderboard table, the results can be inserted with the primary key being position in the table.  This makes it easy to look up a particular user to find their position and then do an "around me" query.

Doing a delete all from the table followed by an insert (all within a transaction) will work up to a certain point.  But when you're pushing hundreds of thousands of users that will likely be a performance issue.  An alternate approach is to create a new temporary table, do the inserts, then—in a new transaction—delete the old table and rename the temporary table to replace it.

Fragment Caching

I wanted to share one other trick I was able to use in my latest project.  I wanted to cache the HTML fragments showing the leaderboards, but also wanted the current user to see themselves highlighted in the table (meaning the view would look different to different users).  That would seem to eliminate the possibility of global caching.

But the only difference across users is a CSS styling, and one that could be controlled in a dynamic way.  So what I did was assign a CSS class to each row named to the user id.  For example:

<tr class="user_2635">...

Then, in the dynamic portion of the generated page I added a style definition targeting the current user:

tr.user_2635 { background-color: #f4f1ac; }

Then the same HTML table can be cached and shared across all users.

---