The structure of my data is the following:
date: <timestamp> filter_a: <integer> -> range [0, 1000] filter_b: <integer> -> range [0, 1000] filter_c: <integer> -> range [0, 86400] filter_d: <integer> -> range [0, 6] group: <string> second_group: <integer> variable_a: <float> variable_b: <float> variable_c: <float> a couple more no very important
I need to perform the following queries:
- Filter data by
Second, with the filtered data:
- count all records
- get average of
- get standard deviation of
- get quartiles of
- group data by
second_groupand aggregate(Count, Avg, Std, ..)
The number of the system's users is about 10 or 15, but the number of items is huge, right now it is 70M but it will be 500M in a couple of weeks and it will be 1000M in about a year.
The number of queries is small, no more than 10 users concurrently, my problem is how to handle those queries with this huge amount of data.
What have I tried so far?
I started with
mongodb, at the beginning it was fast but it became slow when calculating quartiles with 10M+. It improved when I added indexes but it didn't help very much when I had to query all data. I started using mongodb because data was very dynamic but luckily the data format "isn't going to change anymore".
filter_bcould be seen like nodes, I tried
neo4j. I liked it neo4j very much but my graph had A LOT of edges so that queries wasn't very fast.
Finally, since data format isn't going to change and it is only one collection/table so needs no joins in SQL, I checked postgresql. My tests has been faster with postgresql, but I'm scared it could not scale properly in the future.
What do I need?
- Is postgresql a good choice for this case?
- Is there another kind of database I could use? which one is the best for this case?
- What else could I do to improve it?
- About 1M of elements are inserted every day and "should not change" along the time.
- Write speed is not important
- The hard requirement is to read/aggregate fast