Column Stores
Values stored by column, not row. Analytics, compression, and vectorized execution.
Level: intermediate
What is it?
A column-oriented database stores or processes values by column rather than row-oriented records. Analytical systems use columnar layout, compression, and vectorized execution to scan a few columns across many rows efficiently.
Why does it matter?
Analytical queries often aggregate a small subset of fields across millions or billions of rows. Columnar storage can reduce I/O and improve compression when the query does not need every field.
Mental model
A row store is efficient when an operation needs a complete record; a column store is efficient when it needs a few attributes from many records. Modern systems may use hybrid layouts or row groups rather than a pure choice.
How it works
- Columnar layout avoids reading unrelated columns for projection-heavy analytical queries.
- Similar values compress well, reducing I/O and storage when the data distribution is suitable.
- Vectorized execution processes batches of values to reduce per-row overhead and improve CPU utilization.
- Point updates and small transaction-oriented reads can be less natural in some columnar systems, depending on implementation.
- Partitioning, sorting, clustering, and statistics remain important for scan pruning and query planning.
Steps
- Identify the analytical query's selected columns and filter predicates.
- The engine prunes irrelevant partitions/row groups and reads only required column chunks.
- It decompresses and processes vectors of values for filters and aggregates.
- The query combines partial results and returns the required rows or summaries.
Real-world usage
- Business intelligence, telemetry analytics, data warehouses, and large aggregations.
- Scanning massive tables when most columns are irrelevant to the query.
Common mistakes
- Assuming all column stores are unsuitable for updates; modern systems vary widely.
- Choosing columnar storage solely because the table has many columns without studying the workload.
- Ignoring sort order and pruning when optimizing scans.
Interview questions
Why do column stores compress well?
Values from the same column often have similar types and distributions, making encoding and compression more efficient than mixed row payloads.
Why are column stores common in analytics?
Analytical queries often scan a subset of columns across many rows, so columnar layout reduces I/O and supports vectorized processing.