DuckDB vs ClickHouse: Local vs Managed Analytics

DuckDB and ClickHouse are both fast columnar databases. Both are designed for analytical workloads. But they're designed for fundamentally different deployment models — and choosing the wrong one for your use case means either over-engineering a simple problem or under-engineering a complex one.

Here's the practical comparison.

The Core Difference#

DuckDB is an embedded, in-process database. It runs inside your application or script. No server, no network, no cluster. One file, one process.

ClickHouse is a server-based distributed database. It runs as a separate service, scales across multiple nodes, and handles concurrent queries from many users simultaneously.

This single difference drives almost every other trade-off.

Feature Comparison#

Query Performance#

Both are fast. The performance story is nuanced:

DuckDB wins on:

• Complex analytical queries on a single machine
• Ad-hoc analysis against files (CSV, Parquet)
• Queries that benefit from vectorized CPU execution
• Small-to-medium datasets (up to hundreds of GB)

ClickHouse wins on:

• High-throughput ingestion (millions of rows/second)
• Concurrent queries from dozens of users
• Extremely large datasets (terabytes to petabytes)
• Time-series and event data with high cardinality

On a single-machine benchmark with a 100GB dataset, DuckDB and ClickHouse are comparable. DuckDB sometimes wins due to better SQL optimization; ClickHouse sometimes wins due to its primary key-based pruning.

When to Choose DuckDB#

Choose DuckDB when:

1. You're building an embedded analytics feature — analytics inside an app, not a separate service
2. You need local-first analytics — data stays on the user's machine (privacy, latency, offline)
3. You're a single analyst — one person or a small team doing ad-hoc analysis
4. Your dataset fits on one machine — under a few hundred GB
5. You want zero infrastructure — no servers to manage, no ops overhead
6. You need SQL on files — query CSV/Parquet directly without loading into a database

Real examples:

• DenchClaw's CRM database (per-user, local, embedded)
• A data scientist analyzing a 10GB dataset
• An ETL pipeline in a Lambda function
• A startup's internal analytics dashboard where one process writes and a dashboard reads

When to Choose ClickHouse#

Choose ClickHouse when:

1. You need to ingest millions of events/second — ClickHouse's columnar MergeTree engine handles this natively
2. You have multiple applications writing simultaneously — ClickHouse supports concurrent writes
3. You need multi-user analytics — dozens of analysts querying simultaneously
4. Your dataset is hundreds of GB or terabytes — ClickHouse scales horizontally
5. You need real-time dashboards — ClickHouse supports materialized views that update on insert
6. You're running a SaaS analytics feature — ClickHouse Cloud is designed for multi-tenant analytics backends

Real examples:

• Cloudflare's DNS analytics (trillions of rows)
• Sentry's error tracking (billions of events/day)
• A SaaS product embedding analytics (PostHog uses ClickHouse)
• A fintech company's transaction analytics pipeline

Migrating Between Them#

If you start with DuckDB and outgrow it, migration to ClickHouse is straightforward via Parquet:

-- DuckDB: export to Parquet
COPY (SELECT * FROM events) TO 'events.parquet' (FORMAT PARQUET);
 
-- ClickHouse: import from Parquet
INSERT INTO events SELECT * FROM file('events.parquet', Parquet);

The SQL dialect differences are manageable — most standard SQL works in both.

The DuckDB + ClickHouse Architecture#

For teams that need both, a common pattern:

Raw Events → ClickHouse (ingest + storage)
                  ↓ (export to Parquet nightly)
             DuckDB (analyst workbench, ad-hoc queries)

ClickHouse handles the ingest and serves real-time dashboards. DuckDB handles the ad-hoc analysis, ML feature engineering, and local exploration by data scientists. Each tool does what it's best at.

DenchClaw's Choice#

DenchClaw uses DuckDB, not ClickHouse. The reason is simple: DenchClaw is local-first, single-user, and embedded. ClickHouse's strengths — distributed ingest, multi-user concurrency, managed cloud — aren't relevant to that use case.

For a personal CRM running on your laptop, DuckDB's zero-setup, zero-cost, zero-ops design is exactly right. When DenchClaw Cloud scales to thousands of concurrent users, the architecture will evolve — but per-user databases in DuckDB remain the right primitive.

Frequently Asked Questions#

Is ClickHouse harder to operate than DuckDB?#

Significantly. DuckDB requires zero operational overhead. ClickHouse requires server management, schema design for its MergeTree engine, shard planning, and backup configuration.

Can I use both in the same project?#

Yes. A common pattern: ClickHouse for production ingest and real-time queries, DuckDB for data science workloads and ad-hoc analysis.

Does ClickHouse support ACID transactions?#

ClickHouse has limited transaction support. It guarantees atomicity at the block level but doesn't support full ACID transactions across tables. DuckDB has full ACID compliance.

Which has better SQL compatibility?#

DuckDB follows the PostgreSQL dialect closely and supports most standard SQL features. ClickHouse has a custom SQL dialect with some differences. DuckDB generally has broader SQL compatibility.

What's the managed ClickHouse cost?#

ClickHouse Cloud starts around $50/month for small workloads and scales to thousands per month for large deployments. DuckDB is free.

Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →