What Is Columnar Storage? Column vs Row Databases Explained

Key takeaways

• Columnar storage groups values by column. Row storage groups values by record.
• A query on 3 of 50 columns reads about 6% of the bytes, plus compression.
• Columnar engines win at OLAP (scans, aggregates, filters). Row engines win at OLTP (single-row reads and writes).
• Apache Parquet is the standard columnar file format. A columnar database (DuckDB, ClickHouse, Snowflake) has its own internal layout, indexes, and sometimes updates.
• Do not use a columnar store as your app database. Point lookups, frequent single-row updates, and SELECT * on tiny tables are the wrong shape.
• Cassandra, HBase, Bigtable, and ScyllaDB are wide-column stores — row-oriented under the hood, built for high-volume writes and key-based reads — and are not columnar OLAP databases.

What is columnar storage?

Columnar storage is how the bytes sit on disk. All customer_id values are packed together, then all region values, then all amount values, then all ts values. A row store packs customer_id, amount, ts for row 1, then the same three fields for row 2.

Columnar vs row storage: what's the difference?

Row storage keeps one record's fields together on disk; columnar storage keeps one field's values together — the difference decides whether a scan reads the whole row or just the columns a query touches.

The gap is measurable. On ClickBench (99.9M rows, 43 queries, c6a.4xlarge, stock configs), PostgreSQL stores the dataset in 106 GB with a median query of 4.3 minutes, MySQL in 94 GB at 7.3 minutes, and DuckDB in 20.5 GB at 0.348 seconds — roughly 5× smaller and three orders of magnitude faster on the same machine.

A sales table with 40 columns and a query that averages two of them is a textbook query: the row store eads 40 fields per row, while the column store reads only two.

Why are columnar databases faster?

Columnar databases are faster on analytics because they read fewer bytes, skip unread blocks, decompress less, operate on batches, and assemble rows late.

Five mechanisms, roughly in execution order:

1. Column pruning. A query touching 3 of 50 columns reads roughly 6% of the bytes. SELECT avg(trip_distance) FROM trips never opens the other 19 columns.
2. Data skipping. Unread blocks never get decompressed. Zone maps (min/max), Bloom filters (equality on high-cardinality columns), and a sparse primary index (range on the sort key) do the pruning. In Parquet, those min/max zone maps live in the file footer written at write time. DuckDB's own storage builds the same min/max stats automatically per row group. WHERE trip_distance > 10 skips every block whose max is 8. On our unsorted taxi file no row group qualifies — long trips appear throughout, so nothing is skipped.
3. Compression. Same-type values encode well (dictionary, RLE, delta). On the taxi file, dictionary encoding plus Zstd turned 1.09 GB of CSV into 164 MB of Parquet (6.8×).
4. Vectorized execution. The engine processes batches of 1,024–4,096 values with SIMD instead of one row at a time. The remaining bytes process roughly 10× faster per cycle.
5. Late materialisation. The engine defers stitching columns back into rows until after filters run, so it does not assemble unread fields for rows the WHERE clause is about to throw away.

Reading 3 of 50 columns saves roughly 94% of I/O, the remaining bytes process roughly 10× faster per cycle, and skipping indexes prune roughly 90% of surviving blocks — each mechanism multiplies the others.

On our CSV-vs-Parquet benchmark (DuckDB v1.5.5, Apple M3 Max, OS-cached, median of 3, 11,198,026 NYC taxi rows): avg(trip_distance) was 0.008 s on Parquet-Zstd vs 0.490 s on CSV (~60×). The all-columns top-5 query was still 22×. count(*) was ~160× because Parquet answers it from row-group metadata. Treat 160× as a format trick, 22–60× as the working range.

DuckDB can spill sorts and joins to disk and analyze 100 GB on a laptop with 16 GB of RAM. Columns make the working set smaller.

How does columnar compression work?

Encodings run before a general compressor (Snappy, Zstd, Gzip). The engine picks per column.

Parquet's spec covers dictionary, RLE, and delta encoding; FOR and FSST are DuckDB's internal storage encodings, not part of the Parquet format itself.

Sort or cluster on a common filter column (usually a date) so RLE and zone maps fire. Unsorted high-cardinality strings compress the least. On the taxi data, dictionary encoding plus Zstd turned 1.09 GB of CSV into 164 MB of Parquet (6.8×); Snappy landed at 5.1×.

What are the key columnar file formats?

Apache Parquet is the interchange format: row groups, column chunks, pages, a footer of min/max stats. Spark, DuckDB, pandas, and the cloud warehouses all read it. Files are immutable; an update means rewriting the file.

Parquet is how columnar data rests on disk; Arrow is how it moves and computes in memory; engines read Parquet from object storage and decode into Arrow buffers. ORC is the Hadoop-era sibling of Parquet. DuckDB's internal table format is also columnar, but mutable: built for INSERT / UPDATE / DELETE with ACID inside one file, not for shipping datasets between tools.

Iceberg, Delta Lake, Hudi, and DuckLake are table formats on top of Parquet. They are not a third storage layout.

When should you use a columnar database?

Reach for a columnar database when the workload scans wide tables narrowly, favors reads over single-row writes, cares about storage cost, and benefits from running SQL directly on files.

• The workload is analytical: SUM, AVG, GROUP BY, filters over many rows.
• Tables are wide and queries are narrow.
• Reads matter more than single-row writes.
• Storage cost matters. Columnar files are usually several times smaller than CSV.
• You want SQL on files. DuckDB queries Parquet in place; no load step.

The engines split by architecture, and the architecture decides which limit you hit first. Deep evaluation lives on /learn/best-columnar-databases-2026/.

Logistics company Trunkrs moved operational reporting off Redshift onto MotherDuck for snappier drill-downs in daily meetings.

Implementation that actually matters: sort on ingest by the filter you always use, load in batches of thousands of rows, size Parquet row groups for your writer — Spark and Trino default to 128 MB row groups; DuckDB defaults to 122,880 rows per row group (~2 MB on taxi-shaped data with Zstd) — set ROW_GROUP_SIZE if you need larger, expect vectorized batches of 1,024–4,096 values, partition large facts by date, and never SELECT * unless you need every column. Columnar starts to pay from roughly ten million rows up — our taxi benchmark ran at 11.2 million and measured 22-60x.

Columnar storage is the ideal substrate for agent and LLM analytics: agents need sub-second structured context, and embeddings sit in the same files as nested Parquet lists or in Lance, where FP16/FP8 instead of FP32 cuts 50–75% of the bytes.

When should you not use a columnar database?

Skip a columnar database for OLTP-style single-row writes, whole-row lookups, tiny tables, trickle ingest, and workloads that run into a specific engine's own limits.

• OLTP. Frequent single-row inserts, updates, deletes. Updating one logical row means touching many column segments. Use Postgres.
• Write amplification. Columnar systems write large immutable blocks. A one-row update often rewrites a block.
• SELECT * of whole rows, especially one row by primary key. Reconstructing a row from N columns is the slow path.
• Tiny tables. A few thousand rows plus columnar metadata can lose to SQLite.
• Trickle ingest. One event at a time fragments the layout and wrecks compression. Buffer, then flush a batch.
• Named engine limits. SQLite's single-writer lock model makes even light concurrent writes a bottleneck. Snowflake bills a 60-second minimum on every warehouse resume. MotherDuck scales one node per query and hits a memory ceiling where MPP clusters keep going.

A columnar warehouse does not replace the application database. Keep both.

How do you query columnar data in DuckDB?

Column pruning is just naming the columns you need:

Copy code
SELECT avg(trip_distance)
FROM 'trips.parquet'
WHERE passenger_count = 1;

DuckDB reads trip_distance and passenger_count. The other 18 columns are skipped entirely.

Write a columnar file from anything DuckDB can see:

Copy code
COPY (
  SELECT * FROM 'trips.csv'
) TO 'trips.parquet' (
  FORMAT PARQUET,
  COMPRESSION ZSTD
);

An all-columns query on the same Parquet file is the fair lower bound (22× vs CSV on the taxi data), not the headline case. Name your columns.

Copy code
EXPLAIN ANALYZE
SELECT avg(trip_distance)
FROM 'trips.parquet'
WHERE passenger_count = 1;

SELECT
  path_in_schema,
  row_group_id,
  stats_min_value,
  stats_max_value,
  row_group_num_rows
FROM parquet_metadata('trips.parquet')
WHERE path_in_schema IN ('trip_distance', 'passenger_count')
ORDER BY row_group_id, path_in_schema;

Is SQL the standard for columnar databases?

Yes. DuckDB, ClickHouse, Snowflake, BigQuery, and Redshift all speak SQL: SELECT, JOIN, GROUP BY, window functions. Dialects differ (ClickHouse is case-sensitive and picky about joins), but the interface is SQL.