What Is Columnar Database?

What is a columnar database?

In a row-oriented database, each row's values are stored together — ideal for fetching or updating one record at a time, the shape of transactional work. In a columnar database, the values of each column are stored together. A query that aggregates two columns out of forty reads roughly 5% of the data instead of all of it. This is the core trade-off behind the OLTP vs OLAP divide: row storage favors point lookups and single-row writes, columnar storage favors scans and aggregation.

The design was popularized by research systems such as MonetDB and C-Store, then commercialized in analytical engines and warehouses — ClickHouse, DuckDB, Amazon Redshift, Google BigQuery, and Snowflake are all columnar at their core. The idea also lives in open file formats: Apache Parquet and ORC are columnar layouts used across data lake tooling.

Columnar systems come in three broad flavors: pure columnar engines built for analytics, columnar file formats consumed by many engines, and hybrid transactional/analytical (HTAP) systems that pair a row-oriented store for writes and point lookups with a columnar representation for scans.

How a columnar database works

Data is organized into column segments (chunks of one column), each carrying lightweight metadata such as min/max values. That metadata enables segment pruning: the engine skips segments whose range cannot match the filter. Within a segment, encodings like dictionary compression, run-length encoding, and delta encoding shrink the data dramatically, and the engine often operates directly on compressed, vectorized batches of values rather than one row at a time.

-- transactions: 40 columns, billions of rows
SELECT merchant_category, SUM(amount)
FROM transactions
GROUP BY merchant_category;

A row store must read every row in full to answer this. A columnar engine reads exactly two columns — merchant_category and amount — decompresses them in batches, and aggregates with CPU-cache-friendly loops.

The cost shows up on writes. Updating one row in place would mean touching many separate column segments, so columnar systems instead buffer incoming writes in a small delta store (often row-oriented), then merge them into immutable columnar segments in the background. Deletes are typically recorded as tombstones and compacted later.

Why columnar databases matter in real-time systems

Automated decision systems often need analytical-shaped reads — a sum of recent spending, a count of events in the last minute, a distribution over a cohort — but they need them over data that is seconds old, not hours old. Columnar storage makes such scans fast; the open question in any real-time architecture is how quickly new events become visible to those scans.

That freshness depends on the ingestion path. Systems that load in large batches introduce minutes of delay between an event occurring and being queryable. Systems that ingest continuously through a delta store narrow the gap to seconds or less, at the cost of merge overhead — one reason analytical queries can slow down as write volume and concurrency climb. Where decisions must combine per-record lookups with fresh aggregates, hybrid row-columnar designs exist precisely to serve both access patterns against the same data.

The honest summary: columnar layout is a scan optimization, not a freshness guarantee. When evaluating one for real-time work, the questions to ask are ingestion-to-visibility latency, behavior under concurrent reads and writes, and how updates and deletes are handled.

FAQ