Columnar databases don’t store data like your typical row-store databases, which is why they excel at analytical queries.
Let’s see one in action. Imagine we have a sales table with millions of rows and columns like product_id, sale_date, quantity, and revenue.
-- Traditional Row-Store (simplified representation)
-- Row 1: [product_id: 101, sale_date: '2023-01-15', quantity: 2, revenue: 50.00]
-- Row 2: [product_id: 102, sale_date: '2023-01-15', quantity: 1, revenue: 30.00]
-- Row 3: [product_id: 101, sale_date: '2023-01-16', quantity: 3, revenue: 75.00]
In a row-store, all data for a single sale (row) is physically stored together. When you run an analytical query like SELECT SUM(revenue) FROM sales WHERE product_id = 101;, the database has to read all the data for every row, even the sale_date and quantity columns, just to get to the revenue value. This is inefficient for analytical workloads that often only touch a few columns across many rows.
-- Columnar Store (simplified representation)
-- Column 'product_id': [101, 102, 101, ...]
-- Column 'sale_date': ['2023-01-15', '2023-01-15', '2023-01-16', ...]
-- Column 'quantity': [2, 1, 3, ...]
-- Column 'revenue': [50.00, 30.00, 75.00, ...]
In a columnar database, data is stored by column. So, all product_id values are stored together, all sale_date values together, and so on. When you run SELECT SUM(revenue) FROM sales WHERE product_id = 101;, the database can directly access only the revenue column and the product_id column. It skips reading the sale_date and quantity entirely for this query. This dramatically reduces I/O.
This columnar storage also enables powerful compression techniques. Since all values within a column are of the same data type and often have similar patterns (e.g., many dates are in the same year, many product IDs repeat), compression algorithms like run-length encoding (RLE) or dictionary encoding can achieve very high compression ratios. For instance, a column of product_id values might be stored as (101, 500), (102, 250), (103, 750) if product ID 101 appears 500 times consecutively, 102 appears 250 times, etc. This further shrinks the data footprint and reduces I/O.
The system is built for aggregations and scans. Queries that involve SUM(), AVG(), COUNT(), GROUP BY, and WHERE clauses on specific columns are blazingly fast. The query optimizer in a columnar database is specifically tuned to exploit the columnar layout. It knows which columns are needed and can prune entire data blocks or files that don’t contain relevant data for the query.
Consider a query like SELECT AVG(quantity) FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';. A columnar engine will only read the quantity column and the sale_date column. It will then filter based on sale_date and compute the average of the relevant quantity values. The physical storage order (by column) and compression make this incredibly efficient compared to a row-store that would have to traverse every row, extract sale_date and quantity, and then perform the filter and aggregation.
The true magic happens with vectorization. Modern columnar databases process data in batches, or vectors, rather than row by row. When an operation like SUM() is performed, it operates on a whole vector of revenue values simultaneously. This allows the database to leverage CPU cache efficiencies and SIMD (Single Instruction, Multiple Data) instructions, processing many data points with a single instruction. This is a massive performance boost for analytical operations.
Most people understand that columnar databases store data by column for scan performance. What they often miss is how deeply this affects everything else, including query planning, compression strategies, and even the hardware utilization through vectorization. For example, a query that seems to require a JOIN might be optimized by the columnar engine to perform a "hash join" or "merge join" directly on the compressed column data, avoiding costly decompression or de-blocking steps that a row-store would incur. The engine can often perform joins more efficiently by reading only the necessary columns from each table and performing the join directly on those columns, potentially even in a vectorized manner.
The next step in understanding columnar databases is exploring their specific indexing strategies, which differ significantly from traditional B-tree indexes and are optimized for column-level operations.