I can already say this though.
Aggregations
There are limits in place due to the distributed/scalable nature. Since Fauna is an OLTP database and not an OLAP, these kinds of aggregations are (currently) not its strength. You can write it, but you’ll bump into pagination or computation limits probably when your data size grows. More about how to do that can be found in related posts such as:
- SQL group by, having counterpart in FaunaDB - #2 by databrecht
-
Would you use Fauna for analytics? - #2 by Jay-Fauna
Or how you could bump into the computation limit and the workaround: - Can't count collections with more than 600 000 documents - #2 by databrecht
In your case, for your aggregation you would probably (I didn’t think this through so it might not be the best way) start with all hero ids. Map over that and then per hero get all heroes that were picked by using an index on is_pick and hero_id as terms and count that. That way you will get all counts within a page and could sort externally… or could opt to continue paginating if the result of 100 000 max per page is not enough (you have more players) and then sort afterwards.
Alternative approach 1 - precalculate
Instead of aggregating on the fly, you could store aggregations. For example you could:
- Opt to store a document per hero and increase the count each time a hero is picked. This is a tradeoff since if many users would write to that document at the same time, that’s a bad idea. There are techniques to reduce the amount of writes to the same document, e.g you could have 10 documents and randomly pick one.
- Opt to store a count of picks in the document you provided above, which if I’m correct that this is a player, means you have a picked hero count per player. You could also store that in a separate document though that links back to player and hero. This is an intermediate solution, writes are separated over multiple documents (to which you write anyway) yet you have to count less compared to counting over all picks_bans in te array.
Alternative approach 2 - stream to external system
Stream data into an engine that is good in aggregations (RockSet / Clickhouse), you could use temporality to get changesets and stream it into such a system.