32
$\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

Improve this question
$\endgroup$
3
  • $\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
    Commented May 6, 2018 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
    Commented May 7, 2018 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
    Commented May 7, 2018 at 12:37

4 Answers 4

Reset to default
15
$\begingroup$

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

Improve this answer
$\endgroup$
4
  • $\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
    Commented Sep 5, 2015 at 10:02
  • $\begingroup$ @AndyFlury Fixed. $\endgroup$
    – madilyn
    Commented Sep 5, 2015 at 20:54
  • $\begingroup$ Interesting! Looks like this is related to this video from ManAHL: youtube.com/watch?v=FVyIxdxsyok $\endgroup$
    – Andy Flury
    Commented Sep 6, 2015 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
    Commented Sep 15, 2015 at 12:41
5
$\begingroup$

(I work for Axibase)

Axibase Time Series Database is not open-source but it's free on single node.

Time precision is microseconds.

It supports OLCHV+VWAP aggregators in SQL and REST API with various functions for filtering by trading calendar, auction stages, indices etc.

SELECT datetime, symbol, close(), vwap()
  FROM atsd_trade
WHERE in_index('<index-name>')
  AND in_session(DAY, CLOSING)
  AND datetime BETWEEN '2021-01-01' AND '2021-01-15' EXCL
GROUP BY exchange, class, symbol, PERIOD(1 DAY)

(I work Axibase)

Improve this answer
$\endgroup$
2
  • 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
    Commented Sep 21, 2015 at 8:30
  • 1
    $\begingroup$ @AndyFlury Thanks for pointing it out. Disclosure added. $\endgroup$
    – Sergei Rodionov
    Commented Sep 21, 2015 at 8:36
1
$\begingroup$

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

Improve this answer
$\endgroup$
1
  • 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
    Commented Jan 29, 2017 at 12:12
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

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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