22
$\begingroup$

We would like to store financial tick data in a database (potentially billions of rows) and then create aggregated (open-high-low-close) bar data from it (e.g. 1min or 5min bars).

It was mentioned to us that a NoSQL or time series database might be a good choice for this. Can anybody give any advice on which open source product might fit this requirement best.

Note: query performance is very important for us.

In our research we came across the following products (maybe there are more):

We did run a test with InfluxDB with around 10 million ticks. Unfortunately the creation of 1min bars was 3-5 slower than with a relation database (i.e. MySQL).

We are aware that KDB now offers a free 32-bit version, but unfortunately 32-bit will not be enough for our use case.

Any advice is appreciated.

EDIT (Sept 2015): We also did a test with OpenTSDB which seems to be quite fast. The import of 10 mio. prices took about one minute and the aggregation into 1 Min Bars took about 5 seconds.

EDIT (Jan 2017): More than one year after the initial test we gave InfluxDB another try and it turns out that they have made huge progress in the meantime. Write performance is now up to 2 mio. data points per second (with version 1.2)! We have now decided to integrate InfluxDB into our own product AlgoTrader

$\endgroup$
  • $\begingroup$ InfluxDB doesn't provides out-of-the-box Technical Analysis functions so how do you perform indicators calculation on your timeseries data ? thanks $\endgroup$ – Florent May 6 '18 at 6:46
  • $\begingroup$ That is correct, you have to feed/stream data into your own system and do the analysis there. The is how we do it on our side $\endgroup$ – Andy Flury May 7 '18 at 11:29
  • $\begingroup$ I asked the question to their community and apparently Kapacitor is a data processing engine that gives the possibility to calculate custom indicators in the database. I have no idea if it is faster than doing it in a Node JS server served by a Mongodb for example - which is my plan. At least it's going to be much more easy. $\endgroup$ – Florent May 7 '18 at 12:37
13
$\begingroup$

You could try Arctic. Other open source column-oriented databases that you may not have considered include LucidDB and C-Store.

$\endgroup$
  • $\begingroup$ Thanks! Could you add a link to Arctic? The link above actually points back to this question. I'm aware of LucidDB and C-Store, but it looks like both are dead. May main question was which of the mentioned databases would actually produce the best performance. $\endgroup$ – Andy Flury Sep 5 '15 at 10:02
  • $\begingroup$ @AndyFlury Fixed. $\endgroup$ – madilyn Sep 5 '15 at 20:54
  • $\begingroup$ Interesting! Looks like this is related to this video from ManAHL: youtube.com/watch?v=FVyIxdxsyok $\endgroup$ – Andy Flury Sep 6 '15 at 13:23
  • 1
    $\begingroup$ Yes, Arctic is AHL's tick and TimeSeries data store. We've presented a few times on its implementation: github.com/manahl/arctic/wiki Performance (from Python) is good - millions of rows/s. Happy to answer any questions! $\endgroup$ – James Blackburn Sep 15 '15 at 12:41
4
$\begingroup$

Disclosure: I work for the company developing ATSD.

Axibase Time Series Database is not open-source but its community edition is free.

Time precision is milliseconds. Value is float, double or long.

EDIT 1: February 2016. ATSD JDBC Type 4 driver released under Apache 2 license to simplify data access for Java applications.

EDIT 2: March 2016. Decimal precision was introduced to preserve incoming data without precision loss.

It supports OLCH period aggregators (first, min, last, max) as well as min_value_time and max_value_time aggregators:

min_value_time  Time when the minimum value (min) occurred during the period.
max_value_time  Time when the maximum value (max) occurred during the period.

Assuming 24 hours of ticks at 10 millisecond frequency for a total of 24*3600000/10=8.64M ticks. It takes 70 seconds to load these samples into ATSD and between 14 and 22 seconds to calculate and curl-download 1-minute, 5-minute and 1-hour OLHC bars for the day on the fly.

I'd be interested to know does this compare with your current setup.

$\endgroup$
  • 2
    $\begingroup$ Thanks Sergei, interesting product! You are the owner of Axibase right? You should always disclose this information as they are very picky about advertising products in response to questions here on stackexchange. Looks like you are using similar technologies to OpenTSDB which produces similar performance figures too (see my comment on the main question). $\endgroup$ – Andy Flury Sep 21 '15 at 8:30
  • 1
    $\begingroup$ @AndyFlury Thanks for pointing it out. Disclosure added. $\endgroup$ – Sergei Rodionov Sep 21 '15 at 8:36
1
$\begingroup$

** disclosure: I work for quasardb **

Hi - you may want to run the community edition of quasardb. If your dataset if small enough (32 GB storage) - this may very well work !

https://download.quasardb.net/quasardb/nightly/server/ (get 2.1.0 that comes with native timeseries support)

It is coming with a Python/EXCEL API .. R to follow.

The community edition is 100% features complete. Just the back end storage capacity that is limited.

Cheers Gilles

$\endgroup$
0
$\begingroup$

Take a look at Cassandra. Free and Open Source DB, noSQL. It almost perfectly fits your case.

$\endgroup$
  • 1
    $\begingroup$ Thanks @AlexZeDim. Cassandra is a good NoSQL database. However it is not specifically built for time series data, so you would have to add a lot of that functionality on top yourself. As far as I know OpenTSDB can be used on top of Cassandra $\endgroup$ – Andy Flury Jan 29 '17 at 12:12

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.