Column-oriented Storage

Column-oriented storage is a clever way to organize data that can make analytics and reporting much faster.

In traditional row-oriented storage, all information about a single employee (name, department, salary, hire date) is stored together. This makes sense when you need complete employee profiles, like when HR needs to view all details about one person.

Column-oriented storage flips this approach. Instead of keeping all information about one employee together, it stores all the data from a single column together. For example, all salaries would be stored in one file, all names in another, and all departments in a third file.

This approach really shines when you're doing HR analytics. Want to find the average salary across departments? With column storage, all salary data is already grouped together, making calculations much faster.

Here are a few advantages of using Column-oriented storage:

1. Less data needs to be read: If you only need salary data for analysis, you only load the salary file, ignoring names, departments, and other information.

2. Better compression: Data in a single column often has similar values or patterns, making it easier to compress. For example, a "department" column might only contain a few dozen unique values that can be compressed efficiently.

There are some trade-offs though. When new employee data comes in, multiple files need to be updated for each column which makes the writes slower. To solve this problem, we can use LSM trees where data is first stored in memory using a special tree structure. Once enough data accumulates, it's written to disk in bulk.

Column storage works best when you're analyzing specific fields across many employees, but it's slower when you need to reconstruct complete employee profiles. Also, all columns must maintain the same sort order to keep data consistent.

Here's a comparison of Row vs. Column Oriented Storage: