Is Firebase efficient in 'Group By' operations?

Question

I am trying to adapt my normalized datamodel, to a Firebase-friendly datamodel. My application primarily runs 'summary queries' on the normalised tables e.g. things analogous to:

'SELECT ctryid, avg(age) FROM users GROUP BY ctryid'

Having an integer ctryid speeds things up tremendously, and in my mind it's because he has to compare an integer ctryid, instead of strings (USA, FRA, ITA, ...). Now I learned that Firebase generates keys like 'Xj34Fhe2sP0'. Would that indeed imply less efficiency as compared to my SQL queries?

What would such a query look like in Firebase? I do not wish to denormalize any calculated results.

Edit: Denormalizing for avoiding costly joins, would imply also including ctryname in the users object right?

Thanks a lot.

Answer 1

Firebase doesn't support group-by clauses in its queries, nor any other aggregation operations. I don't think the keys that it generates are very important though.

---

What I often recommend is that you model your database to reflect what you show in the screens of your app. So your SQL queries seems to delivery a list of country IDs with the average age of the users in each country ID.

If that's what you want to show, consider storing exactly that data in Firebase:

averageAgeByCountryId: {
 "NL": 43.3,
 "US": 38.1
}

---

Not to write this data you'll need to update the existing average each time you write a new user to a country. To allow that you'll probably want to instead store the total number of users in each country, and their total age:

averageAgeByCountryId: {
 "NL": { userCount: 5, sumOfAge: 217 },
 "US": { userCount: 10, sumOfAge: 381 }
}

Now you can still easily calculate the average age, but in this format it is also easier to update the average ages as you add users.