8

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:

First:

  • Filter data by date, filter_a, filter_b, filter_c and others

Second, with the filtered data:

  • count all records
  • get average of variable_a, variable_b and variable_c
  • get standard deviation of variable_a, variable_b and variable_c
  • get quartiles of variable_a, variable_b and variable_c
  • group data by group or second_group and 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".

  • As filter_a and filter_b could 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?

Edit

  • 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

Thanks!

  • 1
    How about indexed views in SQL Server/metastasized views in Oracle? Those are a running aggregate of the base table so as the base table get's modified the index is also modified on the fly. Then you can always query aggregates which are already calculated for you. – Ali Razeghi Aug 24 '16 at 1:25
  • @AliRazeghi indexed views is good idea. Anyway first I want to choose the best database/design before optimize queries itself – Andres Aug 24 '16 at 2:08
  • 1
    For optimizing purely in Postgres, I want to say that BRIN indexes could help here, but I haven't done anything aside from read about them. postgresql.org/docs/9.5/static/brin-intro.html – Erik Darling Aug 24 '16 at 3:58
  • 1
    Personally I inherited a multi-billion row reporting DB on a OLTP server without a lot of amount of memory. Luckily the most queried portions of it were a rolling 'last 3 weeks' but table scans were not unheard of. Honestly by using very good compression, partitioning, partition elimination, partitioning scheme, SAN cache optimizations, and removing unused indexes we got very good performance on MS SQL 2008 Ent. 1 billion wont be too hard for PGSQL. How wide is each row or approx how much space do you think each row will take, and how many indexes will there be per table or input process? – Ali Razeghi Aug 29 '16 at 16:56
  • 2
    @Andres well that depends on what db engine it's in and what the max size of each row is so we can calculate. For example PostgreSQL has varchar and just char, char is easy to calculate, varchar we'd have to guess the average length. If we could know what field types it is (unless it's Mongo or something that stores it in a document with it's own format), approx how many characters we expect in each, and # of indexes with the columns. 8GB RAM sounds like it would be too low to efficiently pull it out of memory though esp if that RAM is shared with other tables and resources on the server. – Ali Razeghi Aug 29 '16 at 17:32
5

Instead of leaning on a relational database to perform these statistical calculations on time-series data, I'd suggest that you move this math and post-processing work outside of the database into a client application.

Using a scripting language like Python or Ruby, you can incremental solve the problem by querying for "chunks" of data over a fixed-width period of time, compute an intermediate statistical summary, and then combine the results across multiple chunks, as you loop over the whole history. Some statistical measures are hard to combine across chunks, but something like Avg() only needs sum() and count() per chunk, O(1) vs. O(chunksize), so chunk-merging may scale well.

  • I tried something like that using python/pandas. the calculus was faster(a couple of seconds) but retrieve all data was slow. Maybe a better chunksize could helps. +1 – Andres Aug 24 '16 at 12:15
1

Since your data doesn't change, and it's only appended, I would store the data wherever you like; Amazon S3 for example, but any fast-reading database will be ok. No indexes. The database/FS you choose should have the option to read the data in buckets: you could have, for example, one file per day with your 1M records.

Then I would use Spark to do the filtering/analysis. It's cluster based, you can scale it to your needs.

  • I agree, I already have my dataset separated per day. I also was thinking about HDFS and HBase – Andres Aug 29 '16 at 17:09
0

Response depend from way you going to use data after this. If for processing better use Cassandra, if for analysis better use Hive.

  • I understood hive couldn't be the best choice for real time. Am I wrong? – Andres Aug 24 '16 at 18:36
  • 1
    Yes, HBase is for real time read/write. But Cassandra can do same too. But i think HBase is better. – Artemy Prototyping Aug 25 '16 at 8:16
0

This kind of situation is ideal for data warehousing, using the techniques perfected by Ralph Kimball and co., on platforms like SQL Server (the one I'm most familiar with). They were designed specifically with this type of scenario in mind: huge amounts of records of data that is relatively static, for which you need to calculate aggregates of this kind. No relational technique will be a match for properly implemented data warehousing in applications of this kind, although some will certainly be better than others if your organization simply can't afford the licenses for the software packages (like SQL Server Analysis Services) that implement them. There is also a learning curve to implementing languages like MDX that are tailor-made for this kind of data access. If data warehousing is a viable option for your organization though, then don't waste time looking for a relational solution; this is not a relational database problem. I can post some basic references to Kimball etc. and links to SSAS and MDX (sorry I can't help with Oracle and others competitors I'm not familiar with) documentation if need be. I hope that helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.