Record scan speed, column orientated databases and SSDs 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: 


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)?
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?


Thanks
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 &amp;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.