Columnar Databases Are Incredibly Fast. Here's Why.

Columnar Databases vs. Postgres: Performance, Trade-offs, and Use Cases

This video explores why columnar databases can outperform Postgres by up to 40x for analytical queries. Using a 100-million-row dataset, the creator benchmarks Postgres against two columnar systems—ClickHouse and DuckDB—and explains the storage mechanics behind the results. Both columnar engines use Postgres-like SQL syntax, making them easy to adopt.

Why Columnar Databases Win ⚡

• Group-by aggregation: Postgres takes 9.7 seconds; ClickHouse 0.28s; DuckDB 0.24s.
• Filtered time-range count: Postgres 5.9s; ClickHouse 0.06s; DuckDB 0.03s.
• Distinct user count: Postgres 38.4s; ClickHouse 0.78s; DuckDB under 1s.

The key is that columnar stores read only the columns needed and skip large data ranges. ClickHouse sorts data by a sort key, splits it into ~8,192-row granules, and keeps only the first timestamp of each granule in memory. For 100M rows, that is ~12,000 notes, so March queries scan only 1,633 of 12,280 blocks. DuckDB stores min/max per column chunk, enabling similar skipping. Even adding an aggressive index to Postgres would not close the gap.

Where Row-Based Databases Strike Back 🔍

• Single-row lookup by ID: Postgres uses a B-tree index and returns in 2ms. ClickHouse, lacking an ID index, scans all blocks and reconstructs the row from eight column files, taking 168ms. DuckDB also returns in 2ms, but only because IDs were inserted in order; shuffled IDs would force a full scan.

Update and Concurrency Trade-offs ⚠️

• Row update: Postgres 5ms; ClickHouse 5.8s; DuckDB tens of milliseconds.
• ClickHouse treats updates as table mutations because its files are immutable, rewriting entire column chunks. DuckDB allows in-place changes but uses a single-file database, limiting concurrent writes.

Choosing the Right Tool

ClickHouse is a hosted server, similar to Postgres in deployment. DuckDB is an embedded library, like SQLite, with the whole database in one file. Postgres offers a rich plugin ecosystem and is better for transactional workloads, while columnar databases excel at analytics.

Key Takeaway

Columnar databases are not universally faster; they are specialized for analytical aggregation and range scans. For point lookups and frequent updates, a row-oriented database like Postgres remains superior. The right choice depends on workload, scalability, and concurrency needs.