83
$\begingroup$

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.

Improve this question
$\endgroup$
1

9 Answers 9

Reset to default
52
$\begingroup$

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.

Improve this answer
$\endgroup$
5
  • $\begingroup$ Thank you for a thorough answer. That's exactly what I'm trying to do - learn more so that I know what are the pros and cons of available options. I'm not sure I understood the penultimate section though. You mention that on-disk update operations are practically impossible. You mean from a performance standpoint not implementation constraints, right? Something like worst case being roughly O(n)? And about bitemporal - if there are no compliance requirements, isn't it an overkill artificially blowing up the amount of data if for instance all I need is close price adjustment for stock splits? $\endgroup$
    – Karol J. Piczak
    Commented Apr 8, 2011 at 19:02
  • $\begingroup$ @Karol On-disk updates are impossible from an implementation standpoint. Columns are stored as files (each column is its own file of sequential data, so it can be read into memory as an array). So any (non-append) update requires rewriting the entire file; this is the exact process your text editor does every time you change some code. That means you'll need to reset any reading process that may have memory-mapped the column prior to update to ensure consistency (as per CAP Theorem). Kdb+ doesn't support reseting readers natively; a competing product might, but it's still a nasty process. $\endgroup$
    – chrisaycock
    Commented Apr 8, 2011 at 19:51
  • 2
    $\begingroup$ As for bitemporal, feel free to rewrite your entire OHLC table if you want, though really you should only need to append the last day's worth of market data anyway since raw prices don't change. If your concern is adjusted prices (ie, corporate actions), I'm of the belief that you should only store raw data and then pre-compute the adjusted on-the-fly. To do this, simply store splits, dividends, and name changes in a separate table, then provide a user-level function to materialize the adjusted data. $\endgroup$
    – chrisaycock
    Commented Apr 8, 2011 at 19:56
  • 1
    $\begingroup$ I see, indeed I found later yesterday that with Kdb+ it was an implementation decision (append only) to maintain performance. Infobright also has limitations on DML operations in its free version. But I don't like it. It's like taking away a knife from your kitchen so that you accidentally don't get hurt. InfiniDB approach seems more relaxed here. Full DML is supported. And it's left to the user to know the impact. Sure, inserting something at the front of a column is essentially equal to rewriting it from scratch. But randomly updating non-sorted values shouldn't be a problem. $\endgroup$
    – Karol J. Piczak
    Commented Apr 9, 2011 at 12:25
  • $\begingroup$ what is the right data type to store OHLC volume values and indicator values (RSI moving average, stochastics, bollinger etc etc) $\endgroup$
    – PirateApp
    Commented Apr 19, 2018 at 16:09
30
$\begingroup$

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.

Improve this answer
$\endgroup$
4
  • 1
    $\begingroup$ Do you mean "partition"? In kdb-speak, "splay" is just to split the columns into separate files. Partitioning is when those columns are further divided by a key, like date or sym. $\endgroup$
    – chrisaycock
    Commented Apr 8, 2011 at 21:08
  • 2
    $\begingroup$ Yes, you are right. I am being loose with my terminology. I think the word splay is cool. $\endgroup$
    – Rich C
    Commented Apr 9, 2011 at 2:19
  • 3
    $\begingroup$ "One key is don't be afraid to store multiple copies of your data for different research tasks" - thanks, that's a valid point you bring up. Though I think I can feel a bit uneasy about this at first, it can be a really helpful and straightforward solution in some situations. One problem I see is keeping those derived datasets synchronized with recent updates so that they all present the same state. Nevertheless, my concerns are theoretical, I would have to verify it in some practical context. $\endgroup$
    – Karol J. Piczak
    Commented Apr 9, 2011 at 12:41
  • $\begingroup$ And commenting on the last point. No, I'm not an institution. Just individual curiosity, I would call it. It's always starting small and thinking big, despite the fact that premature optimization is the root of all evil. :-) $\endgroup$
    – Karol J. Piczak
    Commented Apr 9, 2011 at 12:44
16
$\begingroup$

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.

Improve this answer
$\endgroup$
3
  • 2
    $\begingroup$ You can have a "spool" process that subscribes to your ticker plant and whose sole purpose is to log ticks every few seconds to non-volatile storage. That will take care of (2) without worrying about crashes or massive writes. As for (1), I assume the in-memory process is also a subscriber to your ticker plant, possibly via your CEP. Your (1) and (2) processes should both be subscribers, with the first for real-time analytics and the second a simple spooling set-up. $\endgroup$
    – chrisaycock
    Commented Apr 9, 2011 at 0:24
  • 1
    $\begingroup$ @chrisaycock "You can have a "spool" process that subscribes to your ticker plant and whose sole purpose is to log ticks every few seconds to non-volatile storage. That will take care of (2)" Note that my solution for (2) isn't designed "to log ticks every few seconds": its architecture (series as BLOBs) renders doing that ineffective, esp. as the day advances - its not possible to append data to BLOBs without rewriting them. That's why I need (1) as a separate subsystem. $\endgroup$
    – wburzyns
    Commented Apr 9, 2011 at 8:34
  • $\begingroup$ Ah, gotcha. I hadn't realized the constraints of the BLOB. $\endgroup$
    – chrisaycock
    Commented Apr 9, 2011 at 16:09
11
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • 3
    $\begingroup$ For the uninitiated, FastBit is a column-oriented data store. $\endgroup$
    – chrisaycock
    Commented Jul 8, 2011 at 1:52
  • $\begingroup$ Out of curiosity, what type of queries are you performing on your data store using FastBit. Just temporal? Do you also query based on price, etc? $\endgroup$
    – Louis Marascio
    Commented Sep 9, 2011 at 3:04
5
$\begingroup$

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.

Improve this answer
$\endgroup$
1
  • $\begingroup$ multiple readers and one writer at a time, definitely works if you got a dedicated process to write while millions of people want to read $\endgroup$
    – PirateApp
    Commented Jun 27, 2018 at 9:31
5
$\begingroup$

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

Improve this answer
$\endgroup$
3
  • $\begingroup$ Perhaps you could expand on WHY he would want to consider an HDF5 based setup. Asking another question in an answer isn't adding any value. $\endgroup$
    – Louis Marascio
    Commented Nov 20, 2013 at 19:07
  • $\begingroup$ Rather than simply provide a link, you could explain why HDF5 is efficient. How is the data stored? What searching capabilities does it provide? Etc. $\endgroup$
    – chrisaycock
    Commented Nov 20, 2013 at 20:00
  • $\begingroup$ hdf5 is row oriented or column oriented ? is it specifically built for time series? have you seen timescale db? $\endgroup$
    – PirateApp
    Commented Apr 13, 2018 at 13:53
1
$\begingroup$

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.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Not sure if Cassandra is a good fit for that. In any case ttps://www.scylladb.com/ is faster and you don't need a whole server farm to make good use of it. $\endgroup$
    – Michael
    Commented Nov 26, 2018 at 18:06
1
$\begingroup$

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.

Improve this answer
$\endgroup$
0
$\begingroup$

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

Improve this answer
$\endgroup$
3
  • 1
    $\begingroup$ wiredtiger.com seems not to be a sustainable solution since they have merged with mongoDB $\endgroup$
    – lehalle
    Commented May 14, 2015 at 20:05
  • $\begingroup$ No, now WT is part of MongoDB company but this is standalone backend. I am using this library separately from MongoDB to store wide set of information for my trading app. $\endgroup$
    – Pavel Fedortsov
    Commented May 14, 2015 at 20:14
  • $\begingroup$ Source code and API documentation are available on Developer Site $\endgroup$
    – Pavel Fedortsov
    Commented May 14, 2015 at 20:21

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.