Column Databases

Columns can be stored together

In a conventional row-oriented database, values from one row are convenient to read together. A Column-Oriented Database organizes storage so values from the same column are located together. This is especially useful for analytical queries that read millions of rows but use only a few fields.

row storage:    [id, time, country, amount] [id, time, country, amount]
column storage: [id, id, ...] [time, time, ...] [country, country, ...]

Analytics rarely needs the whole row

Imagine an event table with dozens of fields. A query such as “sum of sales by country for a month” may use only country, amount, and time. Column storage makes it possible to avoid reading the remaining fields for every record.

Values of the same type placed together also compress well, reducing the amount of data read from disk and across the network.

OLAP and OLTP have different priorities

Online transactional workloads usually perform many small operations on particular records. Analytical workloads, by contrast, often scan large ranges and aggregate huge datasets.

Column systems fit the second model well. That does not mean every column database should become the primary store for user transactions.

Data is often loaded in batches

For an analytical store, a natural flow is for events to originate in operational systems and later arrive in analytical storage for reports, aggregations, and research.

operational systems -> data pipeline -> column store -> analytics