I'm learning about column orientated databases. I have just watched a YouTube video on column orientated databases by Arnaldo from MIT, who suggests an interesting motivation for column orientated databases. About half way through he talks about how long it takes to do a table scan for a query, and discusses the actual physical disk head reading from a spinning magnetic disk. He says that even though we may only want to query, say, 3 columns from a table with 100 columns, the disk head still needs to physically pass over the entire records in a row-orientated database, hence one reason why a column orientated storage system may be faster in some scenarios for querying.

I have a couple of questions about this:

  1. How true is this, given that data in records may not actually be physically next to each other on disk (because of B+Tree index structures)? Is this an oversimplification, or is it true in some situations and not in others (e.g. in tables with no indexes)?

  2. How does this relate to SSDs - as there is no spinning disk and head scanning over it, does the same principle hold true when data is stored in an SSD?



Don't know enough about SSDs to comment, but I have studied columnar stores and how traditional disk technology affect/are affected by this model.

It's perfectly true that records in "OldSQL" (Google Michael Stonebraker and OldSQL) are not adjacent - this is entirely by design - the RDBMS hands placement of files over to the OS - hence fragmentation. (I'm guessing here, but I don't see why SSDs wouldn't suffer (maybe to a lesser degree) from the same effects as traditional HDDs).

What with DELETEs and subsequent INSERTs, your data for a given table could be scattered all over a disk. Columnar stores are meant to mitigate this problem - BUT by imposing restrictions on updating &c. There are complex ways in which they can retain bits of space for reorganisations, but it gets tricky fairly quickly.

The argument is that if you are summing over, say, sales, it would easier to trip along the disk and pick up the million (or whatever) sales in one pass rather than going to each record and picking out the sale value and adding. (Again, I can't see why a straight scan wouldn't be more efficient on SSDs - no idea of benchmarks in this area).

This also makes compression easier, since if datatypes are the same, more efficient algorithms can be used as opposed to compressing records with varying datatypes - which in turn makes the data smaller and quicker to read.

Take a look at Infinidb (now defunct) and Infobright (Community Edition). Vertica is a columnar store - kicked off by Stonebraker and sold subsequently to HP.

It's an interesting concept for the OLAP world and one which IMHO has validity in that space. I'm interested for the performance of statistical analysis which often requires an OLAP approach.

| improve this answer | |
  • Thanks some good info here, that confirms some of my thoughts about columnar stores. It would be interesting to see how SSDs change things (if at all) in terms of defragmentation - for example, I can see how a spinning disk might struggle to collect / sum millions of sale values scattered all over a disk, as the disk head has to move between them all. I wonder if an SSD can just go straight to where it needs so the 'disk head movement' overhead disappears? I also don't know much about SSDs, I think I might have to research this! – James Allen Aug 28 '15 at 8:10
  • As I said I'm not sure about how SSDs work - I have a "feeling" that (like HDDs), it would be easier for them to be told "go to point x and slurp up 1,000,000 (or whatever number) of integers" - just like it would be easier for a HDD. Just to be very clear, what I am unsure about is the exact level of performance improvement that one could expect from SSDs if data placement could be properly arranged. One I also have to research :-) – Vérace Aug 28 '15 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.