ClickHouse vs DuckDB (2026): When to Use Each for OLAP Workloads

Your product manager just dropped a new requirement: a real-time analytics dashboard for user engagement metrics, directly in the application. The dataset is currently 500GB of event logs in Parquet files on S3 and is growing by 10GB daily. The C-suite wants sub-second query latency on interactive charts. Your team is now at a critical architectural crossroads. Do you reach for DuckDB, the lightning-fast in-process engine promising zero-copy reads on your Parquet data? Or do you deploy ClickHouse, the distributed columnar powerhouse built for high-throughput ingestion and massive scale? This isn't just a choice between two tools; it's a fundamental decision between two distinct philosophies of data processing: embedded, single-node parallelism versus distributed, real-time persistence. Choosing incorrectly can lead to spiraling operational costs, missed latency targets, and a painful re-architecture down the line.

Core Architectural Showdown

At a surface level, both ClickHouse and DuckDB are columnar, vectorized analytical databases. They both use Single Instruction, Multiple Data (SIMD) instructions to process data in batches, avoiding the row-by-row overhead that cripples traditional OLTP databases in analytical workloads. But this is where the similarities end. Their design goals, execution models, and storage engines are fundamentally different, optimized for entirely separate corners of the data engineering universe.

Philosophy & Use Case Spectrum

DuckDB: The SQLite for Analytics DuckDB is an in-process OLAP database. It's not a server you connect to; it's a library (libduckdb) you link against your application. There's no separate process to manage, no network overhead for local queries, and no complex configuration. Its primary design goals are:

• Portability & Simplicity: Zero external dependencies. pip install duckdb is all you need.
• Performance on a Single Node: Maximizes utilization of a single machine's CPU and RAM.
• Direct Data Format Execution: First-class citizen for querying Parquet, Arrow, CSV, and JSON files without a mandatory import step.

This makes DuckDB a titan in interactive data analysis (the replacement for Pandas' slower operations), embedded application analytics (powering a dashboard within a Node.js or Python app), and efficient ETL/data transformation tasks on a single node.

ClickHouse: The Distributed Real-Time OLAP Engine ClickHouse is a full-fledged, distributed database management system (DBMS) that runs as a separate server process. It was built at Yandex to power their real-time web analytics platform, a use case that demands:

• Massive, Real-Time Ingestion: Capable of ingesting millions of rows per second from disparate sources like Kafka.
• Horizontal Scalability: Scales to petabytes of data across hundreds of servers using sharding and replication.
• Low-Latency Queries on Big Data: Designed to answer aggregation queries over huge datasets in milliseconds.
• Durability and High Availability: Production features like replication, backups, and fault tolerance are built-in.

ClickHouse is the weapon of choice for large-scale log analytics, real-time business intelligence, user-facing analytical dashboards, and any scenario involving terabyte-to-petabyte scale datasets with high concurrency requirements.

Data Storage & Indexing: MergeTree vs. Adaptive Columnar Blocks

The most profound difference lies in how they store data on disk.

ClickHouse: The Log-Structured Merge-Tree (LSM-Tree) Colossus

ClickHouse's power comes from its MergeTree family of storage engines. This isn't your standard B-Tree. It's a variation of a Log-Structured Merge-Tree, optimized for columnar data and bulk inserts.

When you insert data, ClickHouse writes it into a new "data part" on disk - an immutable set of columnar files. A background process continuously merges these small parts into larger, more optimized ones.

The magic is the sparse primary index. When you define a table, you specify an ORDER BY clause. This is not just for sorting results; it is the physical sort key for your data on disk.

CREATE TABLE user_events (
    `event_time` DateTime,
    `user_id` UInt64,
    `event_type` String,
    `payload` Map(String, String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time);

Here, data is physically sorted by user_id, then event_time. ClickHouse creates a sparse primary index on this key. It doesn't index every row; instead, it records the key value for the start of every Nth block of data (a "granule," typically 8,192 rows).

When you run a query like WHERE user_id = 123, ClickHouse does a binary search on the small primary index file to find the few granules that could contain this user_id. It then reads only those granules from disk, skipping terabytes of irrelevant data. This is why ClickHouse is unbelievably fast for queries that filter on its sort key, especially time-series data.

DuckDB: Optimized Blocks and Direct Parquet Execution

DuckDB's native storage format consists of columnar data blocks. For each column, it stores a set of blocks on disk. The key innovation is its use of Zone Maps for each block. A zone map is lightweight metadata that stores the minimum and maximum value for that data chunk.

When a query contains a predicate like WHERE column_a > 100, DuckDB first checks the zone maps. If a block's zone map indicates its maximum value is 99, the entire block is skipped without ever being read from disk. This is a form of predicate pushdown.

Furthermore, DuckDB builds adaptive Radix Tree (ART) indexes on primary keys or for internal join operations, providing fast point lookups.

But DuckDB's killer feature is its ability to apply these same principles directly to external data formats. When you run SELECT COUNT(*) FROM 'data.parquet' WHERE ..., DuckDB doesn't naively scan the file. It leverages the internal metadata within the Parquet file itself - specifically, the row group statistics (which are essentially zone maps) - to skip massive sections of the file. This makes ad-hoc analysis on existing data lakes incredibly fast.

Query Execution: Morsel-Driven Parallelism vs. Classic Vectorized Pipeline

Both engines are vectorized, but they parallelize execution very differently on a single machine.

ClickHouse: Pipeline Parallelism

ClickHouse uses a traditional pull-based vectorized model, heavily influenced by the Volcano/MonetDB paradigm. Each query is transformed into a pipeline of operators (e.g., Scan -> Filter -> Aggregate). During execution, the top-level operator requests a block of data from its child, which in turn requests from its child, and so on.

Parallelism is achieved by duplicating this pipeline for multiple worker threads. The data source is split, and each thread works on its own segment of the data, processing full blocks at each stage. This is highly efficient for simple, straight-line queries but can introduce synchronization overhead and load imbalance in more complex plans with joins and high-cardinality aggregations.

DuckDB: Morsel-Driven Parallelism

DuckDB implements a more modern and flexible model called "Morsel-Driven Parallelism."

1. Pipeline Breaking: The query plan is broken into multiple pipelines at synchronization points (e.g., an aggregation hash table build).
2. Morsels: The data itself is processed in small, fixed-size chunks called "morsels" (around 100k rows).
3. Dynamic Scheduling: A global task scheduler assigns morsels to available worker threads. When a thread finishes processing a morsel through a pipeline, it goes back to the scheduler and requests another one.

This approach provides significant advantages on a single node:

• Cache Locality: Processing smaller morsels fits better within CPU caches.
• Load Balancing: Fast threads can process more morsels than slow ones, naturally balancing the workload.
• Pipelining: It allows for full query pipelining, where results from one operator are immediately available to the next without materializing the entire intermediate result set, even for complex parallel queries.

This is a key reason why DuckDB often outperforms other engines on complex TPC-H queries on a single powerful machine.

Production Benchmarks: TPC-H SF100

Talk is cheap. Let's benchmark these engines on a common analytical workload. We use a TPC-H dataset at Scale Factor 100 (~100GB of raw data) on an AWS m6i.8xlarge instance (32 vCPU, 128 GiB RAM, NVMe storage).

The Queries:

• Q1: A heavy aggregation query with multiple SUM, AVG, COUNT operations and a GROUP BY. Tests raw aggregation and scanning speed.
• Q6: A simple full-table scan with a WHERE clause. Tests pure scan speed and predicate pushdown.

Scenario 1: Raw File Querying (DuckDB's Home Turf)

Here, we query the 100GB of TPC-H data stored as Parquet files directly from disk. ClickHouse uses its s3 table function, while DuckDB queries the files natively.

# DuckDB Direct Parquet Query
import duckdb

con = duckdb.connect()
# For multi-file datasets
con.execute("SET a_fancy_option=42;")
result = con.execute("""
    SELECT
        l_returnflag,
        l_linestatus,
        SUM(l_quantity) AS sum_qty,
        SUM(l_extendedprice) AS sum_base_price,
        SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        AVG(l_quantity) AS avg_qty,
        AVG(l_extendedprice) AS avg_price,
        AVG(l_discount) AS avg_disc,
        COUNT(*) AS count_order
    FROM 'lineitem.parquet'
    GROUP BY l_returnflag, l_linestatus
    ORDER BY l_returnflag, l_linestatus;
""").fetchall()

-- ClickHouse S3 Table Function Query
SELECT
    l_returnflag,
    l_linestatus,
    ... -- same aggregations as above
FROM s3('https://my-bucket.s3.amazonaws.com/tpc-h-100/lineitem.parquet', 'Parquet')
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

Analysis: DuckDB is the undisputed champion of ad-hoc queries on Parquet files. Its deep integration with the Parquet format, allowing it to push down predicates and skip entire row groups based on file metadata, gives it a massive edge. ClickHouse's s3 function is powerful but is treated more like a foreign data wrapper; it can't match the native performance of DuckDB in this scenario.

Scenario 2: Native Storage Querying (ClickHouse's Home Turf)

Now, we first ingest the data into each database's native format. This is how you would use these systems in a production application where data is persistently managed.

• ClickHouse: Data is loaded into a MergeTree table, ORDER BY (l_shipdate).
• DuckDB: Data is loaded into a native DuckDB table using CREATE TABLE lineitem AS SELECT * FROM 'lineitem.parquet'.

Analysis: The results are now far more nuanced.

• Ingestion: ClickHouse's MergeTree structure is purpose-built for absorbing high-velocity streams of data, and it easily wins the ingestion benchmark.
• Q1 (Heavy Aggregation): DuckDB retains its lead. On a single, powerful node, its morsel-driven parallelism and superior cache utilization for complex aggregations and joins are hard to beat.
• Q6 (Selective Scan): ClickHouse demonstrates its architectural genius. Because our table is sorted by l_shipdate, the Q6 query (WHERE l_shipdate >= '1994-01-01' AND l_shipdate < '1995-01-01') can use the sparse primary index to locate and scan only the relevant data parts and granules. It reads a fraction of the data from disk, resulting in a sub-200ms query time on a 100GB dataset. DuckDB, while still fast due to zone maps, must scan more data.

Scenario 3: High Concurrency

This test is more conceptual. We simulate 100 concurrent clients running a simple aggregation query (SELECT COUNT(*) FROM lineitem WHERE <random_filter>).

• DuckDB: As an embedded library, managing concurrency is the application's responsibility. You can achieve it with multiple threads pointing to the same database file (in read-only mode) or by spinning up multiple processes. However, resource management (CPU, RAM) becomes a complex application-level concern. A single "heavy" query from one user can starve all others in the same process.
• ClickHouse: As a server, this is its bread and butter. It handles each of the 100 connections in separate threads, manages a query queue, and enforces per-user memory and CPU limits. It provides stable, predictable performance under high concurrent load, whereas the embedded DuckDB model would require significant custom engineering to achieve the same robustness.

The Verdict: When to Use Which?

This is not a competition where one engine is "better." They are specialists designed for different tasks on the data processing spectrum.

Choose DuckDB when:

1. Interactive Analysis: You're a data scientist or analyst working in a Python/R notebook. DuckDB provides an order-of-magnitude speedup over Pandas for data manipulation and aggregation on datasets up to ~100s of GB.
2. Embedded Analytics: Your application needs to provide fast analytical queries, but you don't want the operational overhead of a separate database server. A web server can use DuckDB to power a dashboard directly from a Parquet file on disk.
3. Lightweight ETL: Your data transformation pipeline runs on a single machine and needs to efficiently process Parquet, CSV, or JSON files. DuckDB can act as a powerful SQL-based transformation engine.

# A typical DuckDB use case: data wrangling in Python
import duckdb
import pandas as pd

# Zero-copy read from Pandas DataFrame
df = pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6]})
con = duckdb.connect()

# Query the DataFrame directly using SQL
result_df = con.execute("""
    SELECT a, b * 2 AS b_doubled
    FROM df
    WHERE a > 1
""").fetch_df()

print(result_df)

# Or perform a complex aggregation on a massive Parquet file
# that would crash Pandas
con.execute("SELECT COUNT(DISTINCT user_id) FROM 'events.parquet'")

Choose ClickHouse when:

1. Large-Scale Real-Time Analytics: You are building a system to ingest and analyze streams of data (logs, metrics, IoT events, user clicks) at scale. Your data is >1TB and growing.
2. User-Facing Dashboards: You need to power a BI tool or an application dashboard that will be used by many concurrent users, demanding consistently low query latencies (sub-second).
3. A Centralized Analytical Warehouse: You require a durable, replicated, and scalable "source of truth" analytical database for your organization, separate from your application logic.

-- A typical ClickHouse use case: real-time analytics table
-- DDL for a table to store website page views
CREATE TABLE page_views (
    `view_time` DateTime,
    `url` String,
    `user_id` String,
    `device_type` LowCardinality(String), -- Special optimization for few distinct values
    `response_ms` UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(view_time)
ORDER BY (device_type, url, view_time); -- Sort key for fast filtering

-- Example Query for a dashboard: Get 95th percentile response time by device
SELECT
    device_type,
    quantile(0.95)(response_ms) AS p95_response
FROM page_views
WHERE view_time >= now() - INTERVAL 1 DAY
GROUP BY device_type;

Final Thoughts & Further Reading

The rise of both ClickHouse and DuckDB signals a paradigm shift in data analytics. The one-size-fits-all database is dead. We are now in an era of specialized tools, and knowing the architectural trade-offs is paramount for any senior engineer.

• DuckDB is a masterpiece of in-process query execution. It perfects the art of bringing the computation to the data, eliminating overhead and maximizing single-node efficiency for interactive and embedded use cases.
• ClickHouse is a reflection of distributed systems design. It perfects the art of bringing data to a specialized, scalable computation engine, providing unmatched performance for real-time analytics at a massive scale.

The intelligent data engineer doesn't see them as competitors. They see them as two essential, complementary tools in a modern data stack. You might use DuckDB in your dbt transformation pipeline to process Parquet files on S3, and then load the final, cleaned-up data models into ClickHouse to power your company's fastest and most critical dashboards.

Related Technical Reading:

• ClickHouse MergeTree Engine Documentation
• The DuckDB "Morsel-Driven Parallelism" Paper
• Architecture of the MonetDB System (A key influence on modern vectorized engines)
• Comparing Columnar Formats (Parquet, Arrow, ORC)

Test Your Knowledge