2

We recently made a shift from relational (MySQL) to NoSQL (couchbase). Basically its a back-end for social mobile game. We were facing a lot of problems scaling our backend to handle increasing number of users. When using MySQL loading a user took a lot of time as there were a lot of joins between multiple tables. We saw a huge improvement after moving to couchbase specially when loading data as most of it is kept in a single document.

On the downside, couchbase also seems to have a lot of limitations as far as querying is concerned. Couchbase alternative to SQL query is views. While we managed to handle most of our queries using map-reduce, we are really having a hard time figuring out how to handle time based queries. e.g. we need to filter users based on timestamp attribute. We only need a user in view if time is less than current time:

if(user.time < new Date().getTime() / 1000)

What happens is that once a user's time is set to some future time, it gets exempted from this view which is the desired behavior but it never gets added back to view unless we update it - a document only gets re-indexed in view when its updated.

Our solution right now is to load first x user documents and then check time in our application. Sorting is done on user.time attribute so we get those users who's time is less than or near to current time. But I am not sure if this is actually going to work in live environment. Ideally we would like to avoid these type of checks at application level.

Also there are times e.g. match making when we need to check multiple time based attributes. Our current strategy doesn't work in such cases and we frequently get documents from view which do not pass these checks when done in application. I would really appreciate if someone who has already tackled similar problems could share their experiences. Thanks in advance.

Update:

We tried using range queries which works for only one key. Like I said in most cases we have multiple time based keys meaning multiple ranges which does not work.

umair
  • 957
  • 1
  • 9
  • 21

1 Answers1

6

If you use Date().getTime() inside a view function, you'll always get the time when that view was indexed, just as you said "it never gets added back to view unless we update it".

There are two ways:

  1. Bad way (don't do this in production). Query views with stale=false param. That will cause view to update before it will return results. But view indexing is slow process, especially if you have > 1 milllion records.

  2. Good way. Use range requests. You just need to emit your date in map function as a key or a part of complex key and use that range request. You can see one example here or here (also if you want to use DateTime in couchbase this example will be more usefull). Or just look to my example below:

I.e. you will have docs like:

doc = {
 "id"=1,
 "type"="doctype",
 "timestamp"=123456, //document update or creation time
 "data"="lalala"
}

For those docs map function will look like:

map = function(){
 if (doc.type === "doctype"){
  emit(doc.timestamp,null);
 }
}

And now to get recently "updated" docs you need to query this view with params:

startKey="dateTimeNowFromApp" 
endKey="{}"
descending=true

Note that startKey and endKey are swapped, because I used descending order. Here is also a link to documnetation about key types that couchbase supports.

Also I've found a link to a question that can also help.

Community
  • 1
  • 1
m03geek
  • 2,508
  • 1
  • 21
  • 41
  • Thanks for the detailed answer. We tried using stale=false but it only re-indexes updated documents as it needs to ensure you get the latest state of view. It does not re-index all documents meaning we will still need to somehow update documents. – umair Sep 17 '13 at 05:26
  • We tried using range queries which works for only one key. Like I said in most cases we have multiple time based keys meaning multiple ranges which does not work. – umair Sep 17 '13 at 05:28
  • 1
    If you mean multiple time ranges like: 1..250, 600..900, 2222..3333, you need to perform one view query per each range. And if you need filter by multiple fields with `startkey=[1, 50] endkey=[10,99]` (or if you want sort by one field and order by another) you can't do that with one view, because couchbase view has only one index. If you get last situation you'll need to perform part of calculations on app-side. Or anther way is to change your data model to avoid this. – m03geek Sep 17 '13 at 05:44
  • I meant filter by multiple fields. We already have these checks in application but it would be nice if couchbase allowed these type of queries. Maybe I need to change my data models to handle this at database level. Thanks for your help :) – umair Sep 17 '13 at 06:32
  • You can found some explanation how and why couchbase has only one index here: http://www.couchbase.com/forums/thread/couchbase-map-function-ignores-long-values-startkey-and-endkey (see post by FilipeManana). Maybe in future multiple indexes will be also implemented in couchbase. – m03geek Sep 17 '13 at 07:21