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?
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; ...