Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating.

The database I'm using is postgres. That's not really something I can or want to change (unless there's very good reason for it).

version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.4 on x86_64-apple-darwin13.1.0, compiled by Apple LLVM version 5.1 (clang-503.0.38) (based on LLVM 3.4svn), 64-bit

My goal is to allow for up to 10 000 000 rows. I'm not sure if this is realistic.

In my initial attempt, the table is on the simplest of forms (note keys on alias and score).

Column  |          Type          |                        Modifiers
---------+------------------------+---------------------------------------------------------
id       | integer                | not null default nextval('highscores_id_seq'::regclass)
traceid  | integer                |
alias    | character varying(256) |
score    | integer                |
rows     | integer                |
level    | integer                |
duration | integer                |
Indexes:
    "highscores_pkey" PRIMARY KEY, btree (id)
    "highscores_alias_idx" btree (alias)
    "highscores_score_idx" btree (score)

Most interesting columns for my problem are alias and score (I included them all on the off chance that they have some impact I'm unaware of).
Queries

Specifically I need to perform two queries. One which selects highscores, sorted by score with OFFSET and LIMIT (at most 1000 rows per query). I've written this:

SELECT *, RANK() over(ORDER BY score DESC) AS rank FROM highscores OFFSET 0 LIMIT 100

This works well for the topmost results (which are by far most common) and decreases in performance the larger I make OFFSET. Not ideal, but acceptable (I'm guessing caches are at play).

The second query is more complicated; Given an alias (e.g. a name of a user), it should give the h

Assuming that you are prepared to compromise on the absolute correctness of the answer in order to obtain practical performance, you could do the following:

• Create an index on score (assuming descending, but either way is workable)

• Create a maintenance job to rebuild this index periodically (how often depends on your willingness to have your rank results be out of date)

I take it that you are OK with these assumptions, since you have noted an index on score in your question.

Then your user's global ranking for their scores would be something like this:

select 
  S.*
, count (R.id from highscores R
         where R.score > S.score) as rank
from highscores S
where S.alias = 'somealias'

Using correlated subqueries is not a recipe for blinding speed, but the advantage of this is that you don't have to rank everything, you just have to count entries in an index relative to a user's particular scores. Of course, only your query optimizer will know for sure.

StackExchange Database Administrators Q#89554, answer score: 2