3 ms·

I'm currently using a columnar SQL database with a denormalised schema of about 1000 columns that contains several billion rows. 99% of our queries are SUM over

by vosper 13y ago

I'm currently using a columnar SQL database with a denormalised schema of about 1000 columns that contains several billion rows. 99% of our queries are SUM over some of these columns, with a few simple filters: WHERE order_id IN [...] AND date > xyz It performs really well (sub-second queries over millions of rows) but it's licensed by data volume and (more importantly) it's a single-server solution - at some point we're going to need a distributed database. Is RethinkDB suitable for analytics workloads like this?

coffeemug 13y ago
For your workload a columnar store will always outperform a row-based store (like RethinkDB). Rethink does work for analytics, but it can never compete with columnar stores (like Vertica, etc.) on the workloads those systems are good at.
- vosper 13y ago
  That makes sense - thanks for info.
- coolsunglasses 13y ago
  Your honesty is much appreciated by the rest of us. Don't ever lose that please.
JulianMorrison 13y ago
You might want to look at that "precog" thing that was mentioned here on HN yesterday. It's for analytics on columnar data.
- JulianMorrison 13y ago
  http://precog.com/ http://precog.com/
homerowilson 13y ago
Maybe try http://SciDB.org http://SciDB.org. It's a free GPL array database for analytics
- vosper 13y ago
  Thanks for the suggestion - I hadn't thought to look at solutions from the scientific-computing realm. Have you used SciDB yourself?
gadamc 13y ago
If your queries are largely always the exact same and you need to update your sums as new data is added, you could look at the incremental MapReduce of CouchDB/Cloudant. A single CouchDB might hold your data, but Cloudant would scale it out for you. For example, in my experience I have 10 million documents, each with about 100 key-value pairs, where each value is a number. I have a MapReduce function that calculated the statistics of those values. With Cloudant, those statistics are done in the Reduce step and are done incrementally, so I always have the latest information. Additionally, I can select date ranges for the statistics and its already pre-calculated for me. Its very fast response. For example https://edelweiss.cloudant.com/automat/_design/cryo_2/_view/getData2?group_level=1&startkey=[%22T_Bolo%22,1376265137]&endkey=[%22T_Bolo%22,1378943538] https://edelweiss.cloudant.com/automat/_design/cryo_2/_view/... These are the stats for a particular measured value between two dates. Change the dates and you'll see that the return is pretty quick. Change startkey to "T_Bolo", 1378940038 and you'll get the statistics of that value for the last ~hour. You get sum, but also average and standard deviation. full disclosure: I work at Cloudant. cheers, Adam
tonywe64 13y ago
You should try Vertica, they have 3 nodes community edition.

by vosper 13y ago

coffeemug 13y ago

For your workload a columnar store will always outperform a row-based store (like RethinkDB). Rethink does work for analytics, but it can never compete with columnar stores (like Vertica, etc.) on the workloads those systems are good at.

vosper 13y ago
That makes sense - thanks for info.
coolsunglasses 13y ago
Your honesty is much appreciated by the rest of us. Don't ever lose that please.

JulianMorrison 13y ago

You might want to look at that "precog" thing that was mentioned here on HN yesterday. It's for analytics on columnar data.

JulianMorrison 13y ago
http://precog.com/ http://precog.com/

homerowilson 13y ago

Maybe try http://SciDB.org http://SciDB.org. It's a free GPL array database for analytics

vosper 13y ago
Thanks for the suggestion - I hadn't thought to look at solutions from the scientific-computing realm. Have you used SciDB yourself?

gadamc 13y ago

If your queries are largely always the exact same and you need to update your sums as new data is added, you could look at the incremental MapReduce of CouchDB/Cloudant. A single CouchDB might hold your data, but Cloudant would scale it out for you. For example, in my experience I have 10 million documents, each with about 100 key-value pairs, where each value is a number. I have a MapReduce function that calculated the statistics of those values. With Cloudant, those statistics are done in the Reduce step and are done incrementally, so I always have the latest information. Additionally, I can select date ranges for the statistics and its already pre-calculated for me. Its very fast response. For example https://edelweiss.cloudant.com/automat/_design/cryo_2/_view/getData2?group_level=1&startkey=[%22T_Bolo%22,1376265137]&endkey=[%22T_Bolo%22,1378943538] https://edelweiss.cloudant.com/automat/_design/cryo_2/_view/... These are the stats for a particular measured value between two dates. Change the dates and you'll see that the return is pretty quick. Change startkey to "T_Bolo", 1378940038 and you'll get the statistics of that value for the last ~hour. You get sum, but also average and standard deviation. full disclosure: I work at Cloudant. cheers, Adam

tonywe64 13y ago

You should try Vertica, they have 3 nodes community edition.