Choosing the right database for stock price history

Question

The model is [(stock_id, period, ts), open, high, low, close, last, volume]

We write new prices for all stocks (120,000) each minute and delete old once when they go out of retention time. It doesn't matter if retention cleanup will happen automatically or we'll do daily cleanup process. Periods are 1 minute, 10 minutes, 1 day, 7 days and about 1,000 to 10,000 last points data retention for each period. Currently there are about 200,000,000 rows (40GB) in postgres table and the performance is sometimes bad, especially if autovacuum is triggered. When we query we usually pull the whole period to show chart, but sometimes we access specific dates.

We try to understand if there are some optimizations that can be done in postgresql itself or maybe some other database will work better. NoSQL databases that I consider for testing are MongoDB and Aerospike with the following document model. The document key would be stock_id in this case.

{
 1111: {
   "symbol": "aapl",
   "hist_1m": {
      12334: {
        "last": 123.1,
        "close": 123.2,
        ...
      }, ...
   },
   "hist_10m": {...},
   "hist_1d": {...},
   "hist_7d": {...},
 },
2222: {...} 

But I'm really not sure about performance of such model where each sub-hash will be 1000 to 10,000 hashes or maybe even more in the future. In aerospike there's per-map max size of write-block-size (1mb default), in Mongo the limit per document is 16mb but it doesn't explain the performance. How fast or efficient are individual additions to large collections in MongoDB or Aerospike? Are they happen in-place or require loading whole collection in memory and rewriting it back to disk, like it would be with postgresql jsonb column? In postgres we just do thousands of inserts and it's very fast. The performance issue happens because of nature of infinite insert/delete - gaps in the tablespace and autovacuum. Also very it takes quite a long time to do global backfills.

I even thought about timeseries DBs like Prometheus or InfluxDB but really don't think they're designed for realtime high-load querying.

Please suggest in which database/model you think is ideal for this purpose. I searched for existing question with the same requirement (as I think thousands of systems store similar historical data in some way).

Thanks.

Answer 1

The worst work you can give a database is to delete data. This is because the database will try to maintain copies of the original data for mvcc.

This gets a lot better when you can just drop partitions. Here is where Postgres with the timescaleDB extension comes to rescue. Drop the partition[s] that are out of the retention window.

When timescaleDB is implemented to support the cleaning, it should not be a problem running Postgres. It also gets rid of most of the vacuum problems since they are caused by the delete operations.

Answer 2

The industry leader for stock price tick data is kdb+, owned by First Derivatives and originally built by Arthur Whitney under kx systems.

Big banks and Thompson Reuters all use kdb+ as a "tickerplant" database for doing large-scale analytics on time series data. kdb also now supports a powerful Python interface so that most engineers don't need to know the esoteric vector processing languages that underlies kdb+, called q and k.

A decade ago, big banks would have to pay independent consultants big hourly rates to fine tune these "tickerplant" systems. Nowadays, all these independent consultants work for First Derivative and First Derivative sells the tickerplant as a product rather than charging hourly to build one. If you don't want to purchase their tickerplant product, there is an open source alternative on GitHub, but it's not as good. But it will still be world's better than implementing a competing system in some "big data" framework.

You can also purchase an FPGA-accelerated tickerplant through Exegy, as well as their historical-data-as-a-service API. But it will likely blow post any budget you have.

Note, tickerplant normally refers to low latency, real-time TAQ data feeds. But generally the same timeseries storage is used in both, the differences are in how the TAQ data is loaded and stored.

One problem some "big data" frameworks run into is they do not natively support wavg (weighted average) operators, or the ability to define custom Aggregates over ranges in such a way that pipelines the computation and reduces extra range scans.

Additionally, it's not simply time-series data that makes kdb a differentiator. kdb supports a unique window operator called the "window join" or wj for short. Not even Python pandas has this, and it's a killer feature for doing lead-lag analysis on time series data, which is literally what most mark-to-market analysis on historical stock prices tries to do. Window join is a combination of a range join and aggregation operation, as illustrated here:

Other frameworks have begun adding support for "asof join", but the main reason why kdb+'s asof join is so fast is the basic mechanism for how it represents data is just a columnar vector and it loads the data via mmap. Typically, there is only one user running queries against the kdb+ database at a time, although systems scale horizontally through what is called a "Gateway pattern". Additionally, if you do pursue looking into kdb+, you will discover they have "solved" a host of other problems other frameworks have not even really "gotten to" yet or simply unaware of. For example, winnowing is the problem of reducing a data set down to the most salient features. So, for example, when rendering a historical stock price for a ticker, what data points do you choose? There is a whitepaper on how to solve just that problem, and so forth.

If you do decide against kdb+ for storing such data, then I suggest you pick a language/database system that supports, at minimum, as-of join operators. You can read a broad comparison of different approaches put together by Bryan W. Lewis of DuckDB; Bryan has also written a nice post on Range Joins in DuckDB as further food for thought on database system-programming language solution space problems you may run into performance-wise. In other words, DuckDb has decent performance...until you talk to it over R. Similarly, you could be brave and roll your own analysis framework. If you wanted to do that, I'd probably recommend Julia due to its general purpose join framework called FlexiJoin. Very few languages have an approach like it, and if you're deciding on something like this, top-notch performance is probably a secondary rather than primary concern (but still important).

Background: I worked for a top 10 broker agency on kdb+ and have written a few q scripts.