Why use a column database for tick/bar data?

Question

I often hear that column-oriented databases are the best choice method for storing time series data in finance applications. Especially by people selling expensive column-oriented databases.

Yet, at first glance it seems a poor choice. You want to append new ticks, or new bars, at the end (and you need to do this a lot and quickly). That is a classic row operation: you append to one file. In a column DB you have to update three files for a tick (timestamp/price/trade size), or five to six for a bar (datestamp, open, high, low, close, volume). (I said 5-6, as for regularly spaced bar data I suppose datestamp could be implicit from row number.)

For reading I don't normally want to just grab one column; I want to grab the whole bar so I can draw a candlestick (for instance). OK, I may just want the close column, or just want the volume column (but I still need two reads to also get the datestamps in a column-oriented DB, don't I?).

But what seems even more important is that when I want to read historical data I generally want to grab a sub-period, and that will be stored contiguously in the row-oriented DB.

Q1: Is there any good reason to go with column-oriented over row-oriented if all you store is trade ticks?

Q2: Is there any good reason to go with column-oriented over row-oriented if all you store is OHLCV bars?

Q3: If you think no for Q1 and Q2 what kind of columns do you need to have for column-oriented DBs to be the clearly superior choice?

UPDATE

Thanks to Chris Aycock for links to similar questions. Some of the reasoning why column-oriented DBs are better is still not making sense to me, but from the first part of https://quant.stackexchange.com/a/949/1587 I think people may be using row-oriented DBs differently. So, for the purposes of this question, please assume I have only one symbol per database table (as opposed to one huge table with a 'symbol' column). So, following the example in the above answer, the raw on-disk storage looks like:

09:30:01 | 164.05; 09:30:02 | 164.02; ...

Answer 1

For Q1 and Q2 I would suggest you should not use a columnar database. The reasons are as follows:

1. A typical write-access for your data-type would need to update several symbols with both timestamp and price together in different tables. Due to the high cardinality of your data (low no. of duplicates), columnar compression techniques would not be able to provide the promised speed benefits.
2. Consider whether you would need joins on these large tables when reading them later, because columnar databases don't perform well on joins.
3. For a time-series database with one symbol per table, I would recommend using a traditional RDBMS whose layout and queries have been fine-tuned considering the machine available and the data size/growth estimates. A properly partitioned conventional modern RDBMS would work well. Indexes may (or may not!) speed up read times, but will definitely slow down writes.

Answer to Q3: Columnar databases are good for low cardinality data, for example status flags - Y/N, male/female, address fields such as state/country, etc. with mostly repeated values throughout the column. A simplistic understanding would be that they break down a table by its fields and record its unique values into a dictionary, the column is then stored as an array of indexes to the dictionary, this allows for high compression and higher speeds as the amount of data retrieved/manipulated is reduced. Actual implementations use many other optimizations such as cache-aware sorting, etc. But the overhead makes writes much slower than modern conventional RDBMS. Columnar databases are specialized software and show great performance only for specific cases, whereas modern RDBMS can be customized and fine-tuned to many different use cases and provide much better help and support to accomplish this.

I've had very good results with fast writes using Oracle and PostgresQL; and materialized views for fast reads/reporting/analytics. For high performance applications, I've benefited greatly by advice from seasoned, experienced DBAs; I would strongly recommend investing in them instead of buying a shiny new columnar database which a consultant recommended.

Answer 2

Like everything, which solution is most suitable completely depends on your specific case. But first I think you confuse couple concepts here. One thing is how fast a DB can retrieve data/read. Another is storing raw data. And an entirely different issue is analytics, queries. Columnar databases shine at reading and writing raw time series based data. Col DBs are not good at performing analytics. Keep in mind that even KDB itself does not shine at aggregating data, KDB itself is just a smart file system with index structures. Its the built-in query language that adds a lot of firepower in terms of query capabilities. Please keep this in mind.

1) Yes, think about how you generally read data. Think about Key/Value, which is essentially what columnar databases are all about (Edit: There is a very close connection, they are not identical). You want to retrieve a specific point in time or a time frame and its associated values. Columnar dbs are very fast at handling such requests. Once such data is in memory it can be operated upon much faster. 2) Same here: Essentially you want to read bars in the same way than raw ticks or any other time series for that matter. You want to acquire bars from Monday 9am to Tuesday 2pm. Whats the difference here? You store each value in its own column. 3) You mean if I answered "yes" to Q1 and/or Q2? Columns are symbol or symbol + open or whatever you chose. Keys are date/time/ticks...

Remember what I said first: Your use case is all that matters. If you constantly need to get prices/bars/... of many different symbols at a specific time point then a row-based database cannot be beat (well given you setup the schema in an intelligent way within an RDBMS). But if you pull out data over time of a single metric (or 4 metrics such as o/h/l/c of bars) then a columnar database is way faster than RDBMS. Why? Because I/O is the most expensive operation and having to only read the columns, needed, is way faster than having to read whole rows. Keep in mind your assertion that each column is stored in a different file is incorrect.

I would read the very same Wiki article you linked to because it answers most of your own question. Also, look at some open source structured, non-SQL, columnar databases to get started on the concepts.

But if you ask me to summarize my points in one sentence then here goes: Columnar databases are optimized for read-operations of time series like data, while row-based databases are more optimized for write operations.

Edit:

For clarification purposes, what I meant with "Think about Key/Value, which is essentially what columnar databases are all about" is the following:

I used the term "key-value" because its essentially the simplest No-SQL data storage approach. The point being is that one cannot run queries on values, cannot aggregate values or search by values such as one could in a purely RDBMS through schemata and indexes. This I think (and I am not alone here) is what sets RDBMS apart from "No-SQL" solutions. My point was that once this concept is understood that No-SQL databases are generally schema-less, lack tables (generally not always), and that, and here is the key similarity between key value and columnar dbs, queries are limited to just by keys, so that the DB knows exactly what node a query can run on. Please note that I am making the comparison looking at things from above 30,000 feet, not a detailed key-value store vs. columnar DB comparison. I just believe that once one understands the concept of key-value and the way key-values are queried then I find it much easier to understand columnar database concepts, EVEN THOUGH on the surface columnar databases look very similar to RDBMS which could not be any further from the truth.