Paging & Ranking With Large Offsets: MongoDB vs Redis vs PostgreSQL

Paging & Ranking With Large Offsets: MongoDB vs Redis vs PostgreSQL.

Paging

pg.exec("select score, u from scores where lid = $1 order by score limit 20 offset $2", [lids.sample, i * 10000]).to_a

Testing the impact of large offsets is really simple. Assume we have some dummy data that looks like:

db.scores.find();
{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 1, name: 'user_1'}
{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 2, name: 'user_2'}
{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 3, name: 'user_3'}
{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 4, name: 'user_4'}

MongoDB: 0.6 seconds, 17 seconds and 173 seconds.

Redis: 0.028, 0.025 and 0.028 seconds.

PostgreSQL :  1, 122 and 650 seconds.

mongo small   0.6
mongo medium  17
mongo large   173

redis small   0.028
redis medium  0.025
redis large   0.028

pg small      1
pg medium     122
pg large      650

Ranking

select count(*) from scores where lid = $1 and score > $2

  //mongo
  db.scores.find({lid: lid, score: {$gt: score}}).count()
mongo top rank   1.155847
mongo average    22.291007

redis top rank   0.169442
redis average    0.162205

pg top rank      0.714144
pg average       21.771570

Redis is doing 100x the amount of work in less than 1/100th the amount of time.

Memory

Redis and MongoDB take up roughly the same amount of memory (~900MB). Furthermore, the MongoDB version could be optimized by shrinking the field names and partitioning each leader board into its own collection (doing these two changes brings the total storage+index space to 675MB).

 

 

 

 

 

 

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s