Benchmarking Databases for Tick Storage and Bar Aggregation
Summary
The document compares database options for storing large financial tick datasets and aggregating them into open, high, low, and close bars. It reports an initial test in which InfluxDB was slower than MySQL for one-minute bar creation on a dataset of around 10 million ticks. A later test found OpenTSDB imported that quantity of prices in about a minute and aggregated one-minute bars in about five seconds. In a subsequent update, the authors report improved InfluxDB write performance and say they chose it for integration into their product.
These results are dated, tied to the authors’ specific tests, and do not provide hardware, schema, query details, or a controlled comparison across all listed products. The answers suggest additional database options, including column-oriented stores and Cassandra; some responses disclose commercial affiliations or edition limits. The document is useful as a historical example of workload-specific benchmarking, not as current database guidance.
Key ideas
- Tick storage and OHLC bar aggregation place different demands on a database.
- The authors report that early InfluxDB bar aggregation was slower than their MySQL test.
- A later OpenTSDB test reported fast import and one-minute bar aggregation for the tested dataset.
- A subsequent InfluxDB version showed improved write performance in the authors’ experience.
- The reported comparisons are specific to their setup and do not establish current or general performance rankings.
Tags
Full text
# Performance of Open Source Time Series Database for Financial Market Data
# Performance of Open Source Time Series Database for Financial Market Data
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):
- InfluxDB
- OpenTSDB
- KairosDB
- MonnetDB
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
## Answer by madilyn (score 15)
https://quant.stackexchange.com/a/20636
You could try Arctic. Other open source column-oriented databases that you may not have considered include LucidDB and C-Store.
## Answer by Sergei Rodionov (score 5)
https://quant.stackexchange.com/a/20806
(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)
## Answer by AlexZeDim (score 1)
https://quant.stackexchange.com/a/32125
Take a look at Cassandra. Free and Open Source DB, noSQL. It almost perfectly fits your case.
## Answer by Gilles (score 1)
https://quant.stackexchange.com/a/33905
** 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 GillesShown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.