Choosing Storage Layouts for Intraday Market Data

# Efficiently storing real-time intraday data in an application agnostic way


# Efficiently storing real-time intraday data in an application agnostic way












What would be the best approach to handle real-time intraday data storage?

For personal research I've always imported from flat files only into memory (historical EOD), so I don't have much experience with this. I'm currently working on a side project, which would require daily stock quotes updated every minute from an external feed. For the time being, I suppose any popular database solution should handle it without sweating too much in this scenario. But I would like the adopted solution to scale easily when real-time ticks become necessary.

A similar problem has been mentioned by Marko, though it was mostly specific to R. I'm looking for a universal data storage accessible both for lightweight web front-ends (PHP/Ruby/Flex) and analytical back-end (C++, R or Python, don't know yet).

From what chrisaycock mentioned column oriented databases should be the most viable solution. And it seems to be the case.

But I'm not sure I understand all the intricacies of column oriented storage in some exemplary usage scenarios:

- Fetching all or subset of price data for a specific ticker for front-end charting Compared to row based solutions fetching price data should be faster because it's a sequential read. But how does storing multiple tickers in one place influence this? For example a statement like "select all timestamps and price data where ticker is equal to something". Don't I have to compare the ticker on every row I fetch? And in the situation where I have to provide complete data for some front-end application, wouldn't serving a raw flat file for the instrument requested be more efficient?

- Analytics performed in the back-end Things like computing single values for a stock (e.g. variance, return for last x days) and dependent time-series (daily returns, technical indicators etc.). Fetching input data for computations should be more efficient as in the preceding case, but what about writing? The gain I see is bulk writing the final result (like value of computed indicator for every timestamp), but still I don't know how the database handles my mashup of different tickers in one table. Does horizontal partitioning/sharding handle it for me automatically or am I better splitting manually into table per instrument structure (which seems unnecessary cumbersome)?

- Updating the database with new incoming ticks Using row based orientation would be more efficient here, wouldn't it? And the same goes about updating aggregated data (for example daily OHLC tables). Won't it be a possible bottleneck?

All this is in the context of available open source solutions. I thought initially about InfiniDB or HBase, but I've seen MonetDB and InfoBright being mentioned around here too. I don't really need "production quality" (at least not yet) as mentioned by chrisaycock in the referenced question, so would any of this be a better choice than the others?

And the last issue - from approximately which load point are specialized time-series databases necessary? Unfortunately, things like kdb+ or FAME are out of scope in this case, so I'm contemplating how much can be done on commodity hardware with standard relational databases (MySQL/PostgreSQL) or key-value stores (like Tokyo/Kyoto Cabinet's B+ tree) - is it a dead end really? Should I just stick with some of the aforementioned column oriented solutions owing to the fact that my application is not mission critical or is even that an unnecessary precaution?

Thanks in advance for your input on this. If some part is too convoluted, let me know in a comment. I will try to amend accordingly.

EDIT:

It seems that strictly speaking HBase is not a column oriented store but rather a sparse, distributed, persistent multidimensional sorted map, so I've crossed it out from the original question.

After some research I'm mostly inclined towards InfiniDB. It has all the features I need, supports SQL (standard MySQL connectors/wrappers can be used for access) and full DML subset. The only thing missing in the open source edition is on the fly compression and scaling out to clusters. But I guess it's still a good bang for the buck, considering it's free.

## Answer by chrisaycock (score 52, accepted)

https://quant.stackexchange.com/a/949

Column-oriented storage is faster for reading because of the cache efficiency. Looking at your sample query:

```
select price, time from data where symbol = `AAPL
```

Here I'm concerned with three columns: `price`, `time`, and `symbol`. If all ticks were stored by row, the database would have to read through all rows just to search for the symbols. It would look like this on disk:

```
IBM | 09:30:01 | 164.05; IBM | 09:30:02 | 164.02; AAPL | 09:30:02 | 336.85
```

So the software must skip over the price and time entries just to read the symbols. That would cause a cache miss for every tick!

Now let's look at the column-oriented storage:

```
IBM | IBM | AAPL; 09:30:01 | 09:30:02 | 09:30:02; 164.05 | 164.02 | 336.85
```

Here the database can sequentially scan the symbol list. This is cache efficient. Once the software has the array indices that represent the symbol locations of interest, the database can jump to the specific time and price entries via random access. (You may notice that the columns are actually associative arrays; the first element in each column refers to the first row in aggregate, so jumping to the N th row means simply accessing the N th element in each array.)

As you can imagine, column-oriented storage really shines during analytics. To compute the moving average of the prices per symbol, the database will index-sort the symbol column to determine the proper ordering of the price entries, and then begin the calculation with the prices in contiguous (sequential) layout. Again, cache efficient.

Beyond the column-oriented layout, many of these really new databases also store everything in memory when performing calculations. That is, if the data set is small enough, the software will read the entire tick history into memory, which will eliminate page faults when running queries. Thus, it will never access the disk!

A second optimization that kdb+ does is that it will automatically enumerate text. (This feature is inspired by Lisp symbols). So searching for a particular stock does not involve typical string searching; it's simply an integer search after the initial enumeration look-up.

With the sequential storage, in-memory allocation, and the automatic text enumeration, searching for a symbol is really just scanning for an integer in an array. That's why a database like kdb+ is a few orders of magnitude faster than common relational databases for reading and analytics.

As you've pointed-out in your question, writing is a weakness of column-oriented storage. Because each column is an array (in-memory) or file (on-disk), changing a single row means updating each array or file individually as opposed to simply streaming the entire row at once. Furthermore, appending data in-memory or on-disk is pretty straightforward, as is updating/inserting data in-memory, but updating/inserting data on-disk is practically impossible. That is, the user can't change historical data without some massive hack.

For this reason, historical data (stored on-disk) is often considered append-only. In practice, column-oriented databases require the user to adopt a bitemporal or point-in-time schema. (I advise this schema for financial applications anyway for both better time-series analysis and proper compliance reporting.)

I don't know enough about your application to determine performance or production-level requirements. I just hope the above guide will help you make an informed decision with regard to why column-oriented storage is often your best bet for analytics.

## Answer by Rich C (score 30)

https://quant.stackexchange.com/a/950

I have long hungered for the ultimate, super-fast, super-scaleable data storage solution. I have used relational databases, kdb, flatfiles, and binary files. In the end, I used binary files in my research language of choice. My advice is to KISS. The choice of storage is actually not that critical (unless maybe you're working with options tick data). What is critical is how you decide to splay the data.

If you look at kdb, it can actually be quite slow if you don't splay (segment) the data for your particular need. It just gives you a fast management layer, but it is up to you to design the data storage on disk for your need. What you are trying to do is store the data in such a way so that you group together the data that you need and minimize the amount of extra data that has to be read off disk.

For me, I found storing data in binary format in the language that I do research in is the least amount of overhead. Managing a simple splay is easy. One key is don't be afraid to store multiple copies of your data for different research tasks, so long as the creation of the copies is driven off of one golden source. So for example, if you very often need all ticks for one stock for the past 5 years, then I would splay by stock. But if you also need all stocks for a given day, then I would store another dataset that splays by day. Process and store the data in a way that will be most useful to you.

If you are a big institution, then by all means spend the big $ to get kdb and hire a hotshot q programmer (b/c you are probably not going to figure it out on your own very easily). It is quite nice. But, if you are an individual, do the simple thing and move on to more interesting work.

## Answer by wburzyns (score 16)

https://quant.stackexchange.com/a/951

Personally I make a distinction between two conflicting goals: (1) storing data incoming in real-time for immediate processing and (2) storing the gathered data for "offline" purposes. Such approach makes things a lot easier if we're talking about a home-grown solution.

(1) must be as fast as possible but not necessarily scalable beyond a few dozen millions of ticks (remember we're still talking about a home-grow solution and not about a full-blown system for an investment bank). (2) mustn't be slow and must scale well to hold billions of ticks.

The simplest and fastest solution for (1) are arrays kept in memory. At the end of trading day you'll just put them all into (2). They have one drawback: in case of crash your data is lost. If that worries you then you have to replace them (or back them) with a DB. Any non-toy DB will easily accommodate a few millions of records.

(2) is more demanding - you can't just put billions of ticks into a DB straight away. And even if you do then retrieval performance will be abysmal. You need to split your data in a way that serves best for your purposes - there is no universal silver bullet here and what works for others may not work so well for you.

Personally I store data gathered from my real-time feed in RAM. Actually I store them only for drawing purposes. For real deal I put them in my CEP subsystem. After the session is over I download all the ticks via my brokerage account and put them in (2).

In my solution historical tick data is stored in a database. I use it solely as a storage engine as I hit a filesystem's (NTFS in this case) limits after saving approx. 20 millions of files. My DB has one big table that holds all the data as BLOBs. Each BLOB corresponds to quotations of a single security for a given day. Of course this approach has its drawback but there are two important advantages: it was easy to develop and it's able to accommodate huge amounts of data without any performance drop.

## Answer by tobias (score 11)

https://quant.stackexchange.com/a/1419

I have been using FastBit for a while now and find it to be quite performant. It's very non-intrusive to your existing binary storage format provided your data is stored in a columnar manner.

I have briefly tested Tokyo/KyotoCabinet and didnt find it suitable for my (persistent storage) requirements.

## Answer by Paschover (score 5)

https://quant.stackexchange.com/a/1397

I have become a fan of SQLite. It's a very lightweight SQL database, which you can use as an intermediate solution. I agree with Rich C that the best thing to do is probably come up with a custom solution that is optimal for your needs. Using SQLite as persistent storage, and loading the data in memory when you want to do intensive computations on it seems like a best of both worlds approach.

## Answer by Laurent (score 5)

https://quant.stackexchange.com/a/9511

Have you considered the HDF5 data model?

Edit for Louis : Why using HDF5 ?

As stated in the HFDF short description page :

> HDF5 is a unique technology suite that makes possible the management of extremely large and complex data collections.

HDF5 is a suitable solution when dealing with very large datasets and you need performance.

Again, as stated in the provided link, you will find why using HDF5

If you are also using python pandas library (wich is specialy designed to work with time series), Pandas provides HDF5 support for I/O

## Answer by relativeview (score 1)

https://quant.stackexchange.com/a/27782

Apache Cassandra would be a good fit for storing real-time intraday data. It's a partitioned row store, where rows are organized into table using a partition key.

It you use a schema where you store data for one ticker per row with partitioning by day or month (it has a limit of 2B records in a row), the operations in your questions would be very performant.

For example, fetching all or subset of price data for specific ticket would only need sequential scan. And the query would look similar to one in accepted answer.

```
select price, time from data where symbol = "AAPL" and month = "2012-04" and
time > "2012-04-10T16:02" and time < "2012-04-10T17:00"
```

Cassandra is optimized for writes, so it's unlikely that you will have any problems with write performance.

Additionally, with the right schema, both read and write performance scales linearly with the number of servers.

## Answer by omencat (score 1)

https://quant.stackexchange.com/a/29765

Regarding storage, I stream real-time updates for exchange listed contracts (outright + exchange listed calendar spreads) to InfluxDB. Its a time-series database, mostly geared towards IT Ops for storing log data, but it works fine with homogeneous finance data.

For options strips, due to the sheer amount of data generated per day, I use TeaFiles. Pros for using teafiles are that the file sizes are small, and load times are fast. For each .tea file I assign the following strategy: `[ContractSymbol]_[Strike]_[CallOrPut]_[Date].tea`. Files are then stored in Azure BLOB storage. Read benchmarks are good; ~25ms to read a file with 300k entries from a C# loader.

## Answer by Pavel Fedortsov (score 0)

https://quant.stackexchange.com/a/17798

Try use WiredTiger (http://www.wiredtiger.com/)

WT is embedded no-sql solution and can be confugured as column-oriented or row-orinted on table level.

Time series data may implemented by using time-sequenced primary key.

MongoDB and Amazon using this database for their solutions. Also some of HFT trading systems using this database.

Michael Cahill and Keith Bostic lead developers of this solution, they have more than 20 years experience of DBMS development in "Sleepycat Software" then Oracle