Each record in traditional row-oriented database takes up space on a single or more adjacent disk blocks.On the other hand, each column is kept in its own contiguous block or blocks in column-oriented databases.
Look at these sections of the hard drive of a row-oriented database
512,Seabiscuit,Book,10.95,201712241200,goodreads.com
513,Bowler,Apparel,59.95,201712241200,google.com
514.Cuphead,Game,20.00,201712241201,gamerassaultweekly.com
In column-based the block in the hard disk will look as following for the dame data:
512,513,514
Seabiscuit,Bowler,Cuphead
Book,Apparel,Game 10.95,59.95,20.00 201712241200,201712241200,201712241201 goodreads.com,google.com,gamerassaultweekly.com
Column-oriented database Use cases
OLAP applications, when subset of the data is in the interest, to dive decisions.
As an example, a store may analyze data to estimate the impact of different advertising channels on sales and decide which referral sources are most effective.
To answer these questions, we don’t need to know the values of individual rows, just what’s in particular columns across all rows are what matter.
To give an further illustration let’s take another example, if your company has tons of rows and you need to retrieve simply customers and how much they spent, using a column database will save you time and effort because you won’t have to sift through all of the extraneous information (name, website, address, etc.) in each row.
Benefits and Drawbacks of Column-oriented database
By retrieving data from only the columns that are relevant to a given query, columnar storage eliminates the need to process data that is irrelevant to that query.
On the other hand, columnar database does not lend itself to OLTP because it requires constantly inserting rows of data. If a column database is used in this case, each insertion of a row will be in multiple blocks, making it inefficient in this case, by the same token deleting a row in a columnar database is expensive, a row-orianted database will be a natural fit for this use cases.