Firebase indexing on huge lists (100000+ items)

Question

I'm migrating my relational database to Firebase. In general, I have a planner for workers. They can add an item ('appointment') to their schedule. I've read the FireBase documentation, and found a section on indexing.

So I've created following structure (date = YYYYMMDD and time = HHMMSS):

{ 
     appointments : 
         'id1' : { 'date' : '20141207', 'time' : '170000', worker : 'worker1' },
         'id2' : { 'date' : '20141208', 'time' : '170000', worker : 'worker1' }
}

I've added an index for date, time and worker, to be able to query data like this (e.g. fetch all appointments for today):

curl -X GET 'https://myapp.firebaseio.com/appointments.json?orderBy="date"&equalsTo="20141207"'

This works as expected and does the job well. The problem is, the number of appointments can grow exponentially (about a year from now, there could be 100000+ appointments). Is it a good approach to use these indexes? Another option would be to store the date and time also separately, like this:

{
    '20141207' : 
        { '170000' : { 'id1' : true } },
    '20141208' : 
        { '170000' : { 'id2' : true } }
}

In order to ensure that appointments can be fetched per day very fast. Or is FireBase able to handle this just using indexes?

Out of those 100k items, how many will fall on a single day? Because (if your app displays a day at a time) that is the number of items you will have to download. — Frank van Puffelen, Dec 07 '14 at 21:47
I estimate about 100 a day. So that's ok. I am just wondering whether FireBase can handle filtering 100 items out of a lot (100000+) without a problem, if an index is defined. Or if it would be best to create an extra path (as shown below) to prune the tree a little. — Flock Dawson, Dec 08 '14 at 09:41
If Firebase currently can not handle that index lookup, I hope they'll fix it by the time you reach 100K. :-) But if you can structure your data differently (e.g. put all items of a day under the same node), that keeps things under your own control. — Frank van Puffelen, Dec 08 '14 at 12:37
why don't you use timestamp as priority? It would be perfect fit for queries by date range. — webduvet, Dec 09 '14 at 14:50

score 2 · Answer 1 · answered Dec 19 '14 at 19:38

The number of records in the path won't be an issue; Firebase is a scalable, real-time back end that handles hundreds of thousands of concurrent connections and millions of nodes. Querying should be fast. This is the point of an index and, like all things Firebase, must meet our standards of speed and excellence.

Be sure to read about '.indexOn' and to implement this in your security rules:

{
  "rules": {
     "appointments": {
        ".indexOn": ["date", "time", "worker"]
     }
  }
}

Also, your real limitation here will be the bandwidth of transferring data over the tubes, so be sure to limit your results in some manner and paginate:

curl -X GET 'https://myapp.firebaseio.com/appointments.json?orderBy="date"&equalsTo="20141207"&limitToFirst=100'

Firebase indexing on huge lists (100000+ items)

1 Answers1