Databases & Storage
OLAP Analytics: ClickHouse vs DuckDB vs PostgreSQL
Benchmarking analytical OLAP queries on 1 Billion records comparing columnar databases ClickHouse and DuckDB against PostgreSQL.
Test Environment
CPUAWS i4i.8xlarge
RAM256GB
OSUbuntu 22.04 LTS
ToolTPC-H
Metrics Comparison
Complex Aggregation Time
Unit: SecondsClickHouse
0.8★
DuckDB
1.2
PostgreSQL
145.0
Detailed Analysis
Columnar datastores like ClickHouse and DuckDB execute analytical queries 100x faster than traditional row-oriented PostgreSQL.
Columnar storage only reads necessary columns from disk and vectorizes execution, drastically reducing I/O and CPU time for aggregations.
Disk Read
Fetching data into memory
Bottleneck: Row-stores fetch entire rows even when computing a single column sum.
Architectural Recommendations
- Use DuckDB for embedded local analytics
- Use ClickHouse for distributed real-time analytics
Frequently Asked Questions
Can I use Postgres for OLAP?
Yes, but for massive datasets, you need columnar extensions like Citus or specialized tools.