Understanding Columnar Databases
TL;DR
Columnar databases store data by columns instead of rows, enabling massive compression and fast analytical queries. Columnar storage groups similar values together, allowing 10-100x better compression. Column pruning reads only needed columns, dramatically reducing I/O for analytical workloads.
What is a Columnar Database?
Traditional row-based databases store data like this:
Row 1: [timestamp, user_id, event_type, value]
Row 2: [timestamp, user_id, event_type, value]
Row 3: [timestamp, user_id, event_type, value]Columnar databases store data like this:
Column timestamp: [ts1, ts2, ts3, ...]
Column user_id: [id1, id2, id3, ...]
Column event_type: [type1, type2, type3, ...]
Column value: [val1, val2, val3, ...]Why Columnar Storage Matters
Columnar storage delivers three key benefits:
- Better compression: Similar values grouped together compress 10-100x better than row-based storage
- Column pruning: Only read the columns you need, not entire rows
- Analytical optimization: Optimized for aggregations and scans across many rows
Loading diagram...
Columnar databases excel at analytical workloads by reading only needed columns, enabling better compression, and optimizing for aggregation patterns.
Storage and Compression
Each column is stored separately with compressed data blocks, marks files for skipping blocks, and metadata. Sorted data compresses dramatically better, 50-100x compression ratios for sequential patterns.
Tip
Sorting keys aren't just for query performance: they dramatically improve compression. Always sort by columns that have sequential or repeating patterns.
Key Takeaways
- Columnar storage groups similar values - Storing data by columns instead of rows groups similar values together, enabling 10-100x better compression than row-based storage. This fundamental difference is why columnar databases excel at analytics.
- Column pruning improves performance - Columnar databases only read the columns needed for queries, not entire rows. This dramatically reduces I/O and enables fast analytical queries on billions of rows.
- Sorted data compresses better - Sorting keys do not just improve query performance. They also dramatically improve compression ratios (50-100x) by creating sequential patterns that compress much more effectively than random data.