# Low Level Design: Column Store Database

Source: https://www.techinterview.org/post/3233471157/lld-column-store/
Updated: 2026-06-30 · techinterview.org

## Column Storage Basics

In a column store, each column is stored separately on disk. This enables:

- Reading only the columns needed for a query (column pruning)

- High compression ratios — values in a column share the same type and often similar values

## Row Store vs Column Store

- **Row store**: entire rows stored together — fast for point lookups and OLTP (read/write individual records)

- **Column store**: entire columns stored together — fast for OLAP aggregations (sum, avg, count across millions of rows)

## Encoding Types

### Run-Length Encoding (RLE)

Compress repeated values: `[A, A, A, B, B]` becomes `[(A, 3), (B, 2)]`. Excellent for sorted or low-cardinality columns.

### Dictionary Encoding

Map string values to integers, store the integer array. Dictionary stored separately. Reduces storage and enables integer-based comparisons.

### Bit-Packing

Store small integers using only the bits required. If max value is 15, use 4 bits per value instead of 64.

### Delta Encoding

Store differences between consecutive values instead of absolute values. Effective for timestamps or monotonically increasing sequences.

## Column File Format


```
Header: schema, encoding type, row count, min/max (zone map)
Body:   encoded column data (compressed)
```


## Vectorized Execution

Process data in batches of **1024 rows** using SIMD CPU instructions:

- Apply filter predicates to entire batches at once

- Compute aggregations over batches

- Dramatically faster than row-by-row interpretation

## Query Execution Pipeline

- Parse SQL into AST

- Build logical plan (relational algebra)

- Apply optimizations: column pruning, predicate pushdown

- Generate physical plan with vectorized operators

- Execute: Scan → Filter → Aggregate → Output

## Late Materialization

Filter and aggregate using column IDs (integers) as long as possible. Reconstruct full rows only at the final output stage. Avoids reading unnecessary columns during intermediate processing.

## Partition Pruning with Zone Maps

Each column chunk stores a **zone map**: min and max value for the chunk. At query time, skip chunks where the predicate cannot match. Example: `WHERE age > 50` skips chunks where `max(age) <= 50`.

## Key Metrics

- **Compression ratio**: 10x vs row store for typical analytics workloads

- **Query speedup**: 100x for aggregations on large tables due to column pruning + vectorization

- **Write amplification**: higher than row store — column store is optimized for batch loads, not real-time inserts

See also: [Databricks Interview Guide 2026: Spark Internals, Delta Lake, and Lakehouse Architecture](https://www.techinterview.org/post/3233460280/databricks-interview-guide-2026-spark-internals-delta-lake-and-lakehouse-architecture/)

See also: [Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering](https://www.techinterview.org/post/3233460275/scale-ai-interview-guide-2026-data-infrastructure-rlhf-pipelines-and-ml-engineering/)

See also: [Netflix Interview Guide 2026: Streaming Architecture, Recommendation Systems, and Engineering Excellence](https://www.techinterview.org/post/3233460274/netflix-interview-guide-2026-streaming-architecture-recommendation-systems-and-engineering-excellence/)