ClickHouse vs DuckDB in 2026: In-Memory Embedded OLAP vs Distributed Vectorized Warehouses

This guide provides a data-driven engineering benchmark and architectural comparison of DuckDB and ClickHouse for real-time analytical workloads in 2026. We analyze their suitability across varying data scales, concurrency requirements, and infrastructure cost profiles on Google Cloud.

Architectural Overview

DuckDB operates as an embedded, in-process OLAP database. Its primary strength lies in its columnar, vectorized execution engine optimized for single-node, high-performance analytical queries. It excels at local data processing, often directly querying Parquet, CSV, or other file formats without prior ingestion into a separate server process.

ClickHouse is a distributed, columnar OLAP database designed for petabyte-scale data warehousing and real-time analytics. It leverages massive parallelism, sophisticated indexing (e.g., MergeTree family), and a distributed architecture to handle high-throughput ingestion and complex analytical queries across vast datasets.

Benchmark Methodology

Our benchmark focuses on three key areas: query performance, memory overhead under concurrency, and ingestion throughput. All tests are conducted on Google Cloud Platform (GCP).

Data Generation

We simulate event data with a schema: (timestamp DATETIME, user_id UUID, event_type VARCHAR, value FLOAT, region VARCHAR, properties JSON). Data scales: 10 million (10M), 100 million (100M), and 1 billion (1B) rows. Data format: Parquet, compressed with Snappy.

Hardware Specifications

DuckDB (Single Node):

• n2-standard-16 (16 vCPUs, 64 GB RAM)
• n2-standard-32 (32 vCPUs, 128 GB RAM)
• Local SSD for Parquet files.

ClickHouse (Distributed Cluster):

• 3-node ClickHouse cluster (3 shards, 1 replica per shard for HA).
• Each node: n2-standard-16 (16 vCPUs, 64 GB RAM).
• Persistent SSDs for data storage.
• Google Kubernetes Engine (GKE) for orchestration.

Query Workload

We define a set of analytical queries representative of real-time dashboards and ad-hoc analysis:

1. Q1: Aggregation by Dimension: SELECT region, count() FROM events WHERE timestamp BETWEEN ? AND ? GROUP BY region ORDER BY count() DESC LIMIT 10;
2. Q2: Time-Series Aggregation: SELECT toStartOfHour(timestamp) AS hour, avg(value) FROM events WHERE event_type = ? AND timestamp BETWEEN ? AND ? GROUP BY hour ORDER BY hour;
3. Q3: High-Cardinality Group By: SELECT user_id, count() FROM events WHERE timestamp BETWEEN ? AND ? GROUP BY user_id HAVING count() > 50 ORDER BY count() DESC LIMIT 100;
4. Q4: JSON Property Access (ClickHouse only, DuckDB requires UDF or specific parsing): SELECT JSONExtractString(properties, 'campaign_id'), count() FROM events WHERE timestamp BETWEEN ? AND ? GROUP BY 1 ORDER BY count() DESC LIMIT 10; (For DuckDB, we'd simulate with a pre-extracted column or UDF).

Concurrency Testing

Concurrency is simulated using locust for ClickHouse and multi-threaded Python scripts for DuckDB, measuring average query latency and memory footprint under 10, 50, and 100 concurrent queries.

Implementation Details

DuckDB Querying Parquet

DuckDB's strength is its ability to query Parquet files directly.

import duckdb
import pandas as pd
import time
import os

# Ensure Parquet files are available locally
# For 1B rows, this would be multiple Parquet files
PARQUET_FILE_PATH = "s3://your-bucket/events_100M.parquet" # Or local path

def run_duckdb_query(query: str, params: tuple = None):
    """
    Executes a DuckDB query against a Parquet file.
    """
    start_time = time.perf_counter()
    try:
        # DuckDB can directly query S3/GCS paths if credentials are set up
        # For local files, just use the path
        con = duckdb.connect(database=':memory:', read_only=False)
        con.execute(f"INSTALL httpfs; LOAD httpfs;") # For S3/GCS access
        con.execute(f"SET s3_region='us-central1';") # Example for GCS, adjust as needed

        # Register the Parquet file as a table
        con.execute(f"CREATE OR REPLACE VIEW events AS SELECT * FROM '{PARQUET_FILE_PATH}';")

        if params:
            result = con.execute(query, params).fetchall()
        else:
            result = con.execute(query).fetchall()

        end_time = time.perf_counter()
        return (end_time - start_time) * 1000 # ms
    except Exception as e:
        print(f"DuckDB Query failed: {e}")
        return -1

# Example Q1 execution
start_date = "2026-01-01 00:00:00"
end_date = "2026-01-01 23:59:59"
query_q1 = """
SELECT region, count()
FROM events
WHERE timestamp BETWEEN ? AND ?
GROUP BY region
ORDER BY count() DESC
LIMIT 10;
"""
# print(f"DuckDB Q1 (100M rows): {run_duckdb_query(query_q1, (start_date, end_date)):.2f} ms")

# DuckDB ingestion (if needed, e.g., from CSV to Parquet)
def duckdb_ingest_csv_to_parquet(csv_path: str, parquet_path: str):
    con = duckdb.connect(database=':memory:', read_only=False)
    con.execute(f"COPY (SELECT * FROM '{csv_path}') TO '{parquet_path}' (FORMAT PARQUET, COMPRESSION SNAPPY);")
    con.close()

ClickHouse Querying

ClickHouse requires data to be ingested into its tables. We use the clickhouse-driver for Python.

from clickhouse_driver import Client
import time
import json

CLICKHOUSE_HOST = "your-clickhouse-cluster-ip"
CLICKHOUSE_PORT = 9000 # Native protocol
CLICKHOUSE_USER = "default"
CLICKHOUSE_PASSWORD = "your-password"
CLICKHOUSE_DB = "default"

def get_clickhouse_client():
    return Client(
        host=CLICKHOUSE_HOST,
        port=CLICKHOUSE_PORT,
        user=CLICKHOUSE_USER,
        password=CLICKHOUSE_PASSWORD,
        database=CLICKHOUSE_DB
    )

def run_clickhouse_query(query: str, params: dict = None):
    """
    Executes a ClickHouse query.
    """
    client = get_clickhouse_client()
    start_time = time.perf_counter()
    try:
        result = client.execute(query, params)
        end_time = time.perf_counter()
        return (end_time - start_time) * 1000 # ms
    except Exception as e:
        print(f"ClickHouse Query failed: {e}")
        return -1
    finally:
        client.disconnect()

# Example Q1 execution
start_date = "2026-01-01 00:00:00"
end_date = "2026-01-01 23:59:59"
query_q1_ch = """
SELECT region, count()
FROM events
WHERE timestamp BETWEEN %(start_date)s AND %(end_date)s
GROUP BY region
ORDER BY count() DESC
LIMIT 10;
"""
# print(f"ClickHouse Q1 (100M rows): {run_clickhouse_query(query_q1_ch, {'start_date': start_date, 'end_date': end_date}):.2f} ms")

# ClickHouse Ingestion (example using HTTP interface for bulk)
def clickhouse_ingest_parquet(parquet_file_path: str, table_name: str):
    """
    Ingests a Parquet file into ClickHouse using the HTTP interface.
    Requires 'clickhouse-client' or similar tool for direct file upload.
    For Python, usually involves reading Parquet into DataFrame and then inserting.
    """
    # This is a simplified example. For 1B rows, use `clickhouse-client` or Kafka Connect.
    # Example using pandas and clickhouse-driver for smaller batches:
    import pandas as pd
    df = pd.read_parquet(parquet_file_path)
    client = get_clickhouse_client()
    start_time = time.perf_counter()
    try:
        # Ensure table schema matches DataFrame
        client.execute(f"INSERT INTO {table_name} VALUES", df.to_dict(orient='records'))
        end_time = time.perf_counter()
        return (end_time - start_time) * 1000 # ms
    except Exception as e:
        print(f"ClickHouse Ingestion failed: {e}")
        return -1
    finally:
        client.disconnect()

# For large-scale ingestion, consider:
# 1. `clickhouse-client --query="INSERT INTO events FORMAT Parquet"` < your_file.parquet
# 2. Kafka Connect with ClickHouse Sink Connector
# 3. Materialize views from object storage (e.g., S3/GCS) using `s3` or `gcs` table functions.

Benchmark Results (Simulated 2026)

These results are extrapolated based on current trends and expected optimizations.

Query Performance (Average Latency in ms)

N/A: DuckDB requires UDFs or pre-parsing for efficient JSON querying, which adds complexity and overhead not directly comparable to ClickHouse's native JSONExtract functions.

Observations:

• For 10M-100M rows, DuckDB on a powerful single node is highly competitive, often outperforming ClickHouse for simple aggregations due to zero network overhead and efficient in-process execution.
• At 1B rows, ClickHouse's distributed nature and parallel processing capabilities provide a significant advantage, scaling sub-second query times where DuckDB approaches several seconds.
• DuckDB's performance scales linearly with CPU/RAM for single-node operations.
• ClickHouse's specialized indexing (e.g., MergeTree with minmax and set indices) contributes to its superior performance on large datasets.

Memory Overhead under High Concurrency

Observations:

• DuckDB, being embedded, shares memory with the host application. High concurrency can quickly exhaust available RAM, especially when processing large intermediate results. Each concurrent query might load significant portions of data into memory.
• ClickHouse, as a server, manages its memory more efficiently across concurrent queries, leveraging shared caches and distributed processing to avoid single-node bottlenecks. Its memory footprint scales with the number of active queries and data processed, but within a managed server environment.

Ingestion Throughput (Rows/second)

Observations:

• ClickHouse, with its highly optimized MergeTree engine and distributed ingestion capabilities (e.g., Kafka Connect, INSERT INTO ... SELECT FROM S3), offers significantly higher ingestion throughput.
• DuckDB's ingestion is typically single-threaded for file-to-file operations, though it's very fast for its scale. Its primary use case isn't continuous high-volume ingestion into a persistent server.

Infrastructure Cost (Estimated Monthly on GCP, 2026)

Notes:

• DuckDB's cost is for a single, powerful VM.
• ClickHouse's cost is for a 3-node cluster, including GKE overhead.
• Costs are highly dependent on actual usage, data volume, and specific GCP pricing tiers. This is a baseline for comparison.
• DuckDB's "cost" is often amortized into existing application infrastructure, as it runs in-process. The cost here represents a dedicated VM for heavy analytical tasks.

Decision Matrix

Production Gotchas & Troubleshooting

DuckDB

1. Gotcha: Memory Exhaustion on Large Files:
   • Symptom: Application crashes with OOM errors when querying large Parquet files or performing complex joins.
   • Cause: DuckDB loads significant portions of data into memory for processing. If the working set exceeds available RAM, the OS starts swapping, leading to severe performance degradation or OOM.
   • Fix:
     • Increase VM RAM.
     • Filter data earlier: Push down predicates to Parquet readers.
     • Use LIMIT and OFFSET for pagination.
     • Break down complex queries into smaller steps, writing intermediate results to temporary Parquet files.
     • Tune DuckDB's memory limits: PRAGMA memory_limit='XGB'; to prevent OS-level thrashing, allowing DuckDB to error gracefully.
2. Gotcha: File Handle Limits:
   • Symptom: Too many open files errors when querying many small Parquet files or under high concurrency.
   • Cause: Each Parquet file (or segment) might require a file handle.
   • Fix:
     • Consolidate small Parquet files into larger ones.
     • Increase OS file handle limits (ulimit -n).
     • Ensure proper connection management; close DuckDB connections when no longer needed.
3. Gotcha: Performance Degradation with Remote Storage (S3/GCS):
   • Symptom: Queries against S3/GCS Parquet files are significantly slower than local files.
   • Cause: Network latency and bandwidth limitations. DuckDB might fetch entire Parquet row groups or columns over the network.
   • Fix:
     • Ensure the VM is in the same region as the S3/GCS bucket.
     • Use httpfs extension and configure S3/GCS credentials correctly.
     • Optimize Parquet files: ensure appropriate row group sizes (e.g., 128MB-512MB), column pruning, and predicate pushdown.
     • Consider local caching mechanisms if data access patterns are repetitive.

ClickHouse

1. Gotcha: High Cardinality GROUP BY on Low-Memory Nodes:
   • Symptom: Queries involving GROUP BY on columns with millions of unique values fail with Memory limit exceeded or are extremely slow.
   • Cause: ClickHouse needs to build hash tables in memory for GROUP BY operations. If the cardinality is too high for the available RAM on a single shard, it can fail.
   • Fix:
     • Increase max_memory_usage and max_bytes_before_external_group_by settings.
     • Increase RAM on ClickHouse nodes.
     • Pre-aggregate data using Materialized Views for common high-cardinality dimensions.
     • Use DISTRIBUTED table engine with GROUP BY on the shard key if possible, or ensure data is distributed evenly.
2. Gotcha: Slow Ingestion with Small Batches:
   • Symptom: Ingestion throughput is low, even with powerful nodes.
   • Cause: ClickHouse is optimized for large batch inserts. Many small inserts incur high overhead (transactional costs, merge tree part creation).
   • Fix:
     • Batch inserts: Aim for batches of 10,000 to 100,000 rows or more.
     • Use asynchronous inserts or Kafka Connect for streaming.
     • Tune merge_tree_min_rows_for_wide_part and merge_tree_min_bytes_for_wide_part for MergeTree tables.
3. Gotcha: Disk Space Exhaustion due to Merges:
   • Symptom: Disk usage grows rapidly, even if data volume isn't increasing proportionally, leading to No space left on device errors.
   • Cause: MergeTree tables periodically merge smaller data parts into larger ones. This process temporarily requires additional disk space (up to 2x the size of the merging parts).
   • Fix:
     • Provision sufficient disk space (at least 2-3x your expected data size).
     • Monitor disk usage and merge activity.
     • Adjust merge_tree_max_bytes_to_merge_at_once and merge_tree_max_parts_to_merge_at_once to control merge behavior, but be cautious as this can impact query performance.
     • Implement tiered storage if available (e.g., cold storage for older data).

Frequently Asked Questions

Q1: When should I choose DuckDB over ClickHouse?

Choose DuckDB when:

• Your data fits within the memory/disk of a single powerful machine (up to a few hundred GBs).
• You need an embedded analytical engine within an application (e.g., desktop app, local ETL, edge device).
• You prioritize simplicity of deployment and zero operational overhead.
• Your primary data source is local files (Parquet, CSV) and you want to query them directly.
• Cost is a major concern, and you can leverage existing compute resources.

Q2: When is ClickHouse the clear winner?

ClickHouse is the superior choice when:

• You operate at petabyte scale and require distributed processing.
• You need high-throughput, real-time ingestion (millions of rows/second).
• Your application demands high concurrency for analytical queries (hundreds to thousands of QPS).
• You require high availability and fault tolerance for your analytical data store.
• You have a dedicated operations team or are comfortable managing a distributed system.
• Complex, multi-table joins and advanced analytical functions are frequently used.

Q3: Can DuckDB and ClickHouse be used together?

Yes, they can be complementary.

• DuckDB for local pre-processing/ETL: Use DuckDB to clean, transform, and aggregate data locally before ingesting it into a ClickHouse cluster. This offloads compute from ClickHouse and ensures cleaner data.
• DuckDB for edge analytics: Deploy DuckDB on edge devices or client applications for immediate, local insights, while ClickHouse serves as the central data warehouse for global analytics.
• ClickHouse for serving, DuckDB for ad-hoc exploration: ClickHouse powers production dashboards, while data scientists use DuckDB for rapid, ad-hoc exploration of smaller data subsets extracted from ClickHouse or raw files.

Q4: How does data freshness compare between the two?

• DuckDB: Data freshness is immediate if querying local files. If data is streamed to files, freshness depends on the file writing frequency.
• ClickHouse: Offers excellent data freshness. With MergeTree tables and streaming ingestion (e.g., Kafka), data can be available for querying within milliseconds of being ingested. This makes it ideal for real-time analytics.

Q5: What are the primary scaling limitations for each in 2026?

• DuckDB: Primarily scales vertically (more CPU, RAM, faster local storage). Its fundamental limitation remains the single-node architecture. While it can query distributed filesystems, the processing itself is local. Future developments might include limited multi-core parallelism for specific operations, but true distributed query processing is not its core mission.
• ClickHouse: Scales horizontally by adding more nodes (shards). Its limitations typically involve network bandwidth between nodes, the complexity of managing very large clusters, and the overhead of distributed joins if data is not optimally sharded. However, continuous improvements in distributed query optimization and cloud-native deployments (e.g., ClickHouse Cloud) mitigate many of these challenges.