The key difference between column and row-oriented databases is the way values are stored. Column-oriented databases store all the values for a given column in the same place, while row-oriented databases store all the values in a row (no matter the number of columns) together.
This means that if one needs to query a lot of rows but only a few columns, using a database that keeps columns stored together can provide a huge performance advantage, as data can be retrieved based only on the columns that need to be projected. If we were to use a row-oriented database, since each row is stored together we would always have to go over the entire row even when not required.
Let’s consider the following example:
In a row-oriented database, data is stored row by row, i.e. the data is represented in the disk by a sequential list of rows, like the following example:
This is usually how traditional databases like Postgres and MySQL work and it works quite well for OLTP (Online Transactional Processing) applications, as they can provide good performance for writes - since writing new data is just a matter of appending a new row after the last one.
When it comes to reads, it depends on whether we need to read the entire row or just a few columns - i.e. when dealing with complex operations usually the entire row has to be kept in memory even though only part of its columns are needed.
Column-oriented databases, on the other hand, were created to support fast reads (and ad-hoc queries) even if that requires compromising writes as we will see. This is why they are usually used by OLAP (Online Analytical Processing) applications. In a column-oriented database, the values for a given column are grouped, like the following example:
This means that adding a new row forces the database to add a new value after all the values for each of the columns the table has - this can be simplified by having each column stored in a different file/disk.
When it comes to reads, this database is more appropriate for fast reads as it’s a lot easier to go over the values for a specific column without having to go through all the other columns.