Choosing between ClickHouse and DuckDB often comes down to a simple question: how much infrastructure do you want to manage? ClickHouse scales across multiple nodes for massive datasets and high concurrency, while DuckDB runs embedded in your application on a single machine with zero operational overhead.
The difference isn't just about raw performance or dataset size. It's about whether your workload needs distributed storage and parallel processing across a cluster, or whether a fast in-process database can handle everything on one machine. This article explains when each architecture makes sense, how node count affects performance and cost, and what the migration path looks like when you outgrow a single-node setup.
ClickHouse vs DuckDB at a glance
ClickHouse is a distributed columnar database designed for large-scale analytical workloads across multiple servers. DuckDB is an in-process analytical database that runs embedded within applications on a single machine. The fundamental difference comes down to architecture: ClickHouse scales horizontally across clusters, while DuckDB operates as a library within your application process.
Storage and execution model
ClickHouse stores data across multiple nodes with distributed execution, splitting tables into shards and replicating data for fault tolerance. DuckDB runs embedded within applications on single machines, storing data in local files or memory without any network layer or cluster coordination.
Typical deployment footprint
ClickHouse typically requires multiple servers or cloud instances, each running the ClickHouse server process with network communication between nodes. DuckDB runs as a library within existing processes, adding no separate server infrastructure or operational overhead beyond the host application itself.
Core use cases
ClickHouse handles real-time analytics at scale, serving hundreds of concurrent queries against petabytes of data with sub-second latency. DuckDB excels at interactive analysis and prototyping, providing fast analytical queries for data scientists and engineers working with datasets that fit on a single machine.
Why node count matters in OLAP databases
OLAP (Online Analytical Processing) databases process complex analytical queries that aggregate and filter large volumes of data. Node count directly impacts three dimensions: query performance through parallel processing, system availability through redundancy, and total cost through infrastructure spend.
A single node has CPU and memory constraints that limit how many queries can run simultaneously. Multiple nodes enable parallel query processing across machines, distributing both storage and computation to handle higher user concurrency and larger datasets.
Single-node deployments create a single point of failure where any hardware issue causes complete downtime. Distributed deployments with replica nodes provide backup copies of data, allowing queries to continue running even when primary nodes fail.
More nodes mean higher infrastructure spend through additional servers, network bandwidth, and storage capacity. This cost trades off against operational complexity, where managing distributed systems requires more complex monitoring and troubleshooting than single-node setups.
When a single node DuckDB is enough
DuckDB runs on one machine by design, with no clustering or distribution capabilities. This limitation becomes an advantage in scenarios where data volumes fit comfortably on a single machine and the operational simplicity of an embedded database outweighs the benefits of horizontal scaling.
Interactive notebooks and ad hoc work
Data scientists using Jupyter notebooks for exploratory analysis benefit from DuckDB's zero-setup approach. The database runs in the same Python process as the notebook, reading data directly from local Parquet files or CSV exports without any network latency or server configuration. For teams wanting ClickHouse performance in a similar embedded architecture, chDB provides an embedded ClickHouse engine for Python with zero-copy DataFrame integration.
Small production workloads under 100 GB
Applications with limited data volumes that fit in memory or on fast local storage can achieve fast query performance with DuckDB.
A SaaS dashboard serving a few hundred users with daily aggregations over a few gigabytes of event data might see sub-100ms query times without any cluster infrastructure, and DuckDB can aggregate 1 billion rows (~50 GB) on a MacBook Pro with 16 GB RAM, demonstrating larger-than-memory processing capabilities.
Edge and embedded analytics
IoT devices, mobile applications, or desktop software requiring local analytical capabilities can embed DuckDB to provide SQL analytics without external dependencies. This pattern works well for offline-first applications or scenarios where data privacy requirements prevent sending data to external servers.
When ClickHouse needs more than one node
Single-node ClickHouse deployments can handle substantial workloads, often serving billions of rows and hundreds of gigabytes of data with good performance. Certain scale and availability requirements push deployments toward multi-node clusters.
High-ingest real-time pipelines
Streaming data sources generating continuous writes at rates exceeding 100,000 rows per second can overwhelm single nodes. Distributed tables with multiple shards spread write load across nodes, allowing each shard to handle a portion of the incoming data stream independently.
Hundreds of concurrent queries
Multiple users or applications querying simultaneously cause single nodes to become bottlenecks under heavy query loads. Additional nodes allow ClickHouse to distribute queries across replicas, with each node handling a subset of the concurrent workload.
Datasets larger than RAM
When data volumes exceed single-machine memory capacity, query performance degrades as ClickHouse reads from disk more frequently. Distributed storage across multiple nodes provides more aggregate memory and disk I/O bandwidth, keeping more data in memory across the cluster.
ClickHouse data warehouse benchmarking on one vs many nodes
Performance comparisons between single-node and multi-node ClickHouse deployments reveal predictable scaling patterns. Understanding how queries and writes behave across different node counts helps you decide when to add capacity.
Read performance scale-out results
Query response times improve roughly linearly with additional nodes for queries that scan large portions of the dataset. A query scanning 1TB of data might take 10 seconds on a single node but drop to 2-3 seconds across a four-node cluster, assuming the query can be parallelized effectively.
Queries with small result sets or those hitting only a single shard see minimal improvement from additional nodes. A point lookup query retrieving a single row by primary key performs similarly on one node or many, since only one shard contains the requested data.
Write throughput per core
Ingestion rates per CPU core remain relatively constant in single versus distributed setups, with each core handling similar write volumes regardless of cluster size. A single 16-core node might handle 200,000 inserts per second, while a four-node cluster with 16 cores each scales to roughly 800,000 inserts per second.
The scaling pattern for writes is more predictable than for reads, since distributed tables with multiple shards naturally partition write load without complex query planning. Each incoming batch gets hashed to a specific shard, allowing independent parallel writes across all nodes.
DuckDB vs ClickHouse speed trade-offs
DuckDB's single-node simplicity can outperform distributed ClickHouse overhead for smaller datasets that fit entirely in memory. For datasets under 10GB, DuckDB often executes analytical queries faster than ClickHouse because it avoids network communication, distributed query planning, and cluster coordination overhead.
Once datasets grow beyond single-machine memory capacity, ClickHouse's distributed architecture provides better performance through parallel processing across nodes. In fact, DuckDB on a single machine completed TPC-H in 1 minute 16 seconds while Spark on a 32-node cluster took around 8 minutes, illustrating how single-node efficiency can outperform distributed overhead for certain workloads. The crossover point typically occurs between 50GB and 500GB, depending on query patterns, concurrency requirements, and available hardware.
How node count affects cost, ops, and failure modes
Practical considerations beyond pure performance influence node count decisions. Real-world implications around cost management, operational complexity, and system reliability often matter more than raw query speed.
Cloud spend elasticity
Cloud providers charge for multi-node clusters based on the total number of running instances, with costs scaling linearly per node. A three-node cluster on AWS using r6i.2xlarge instances costs roughly $1,500 per month in compute alone, before storage and network transfer charges.
Auto-scaling capabilities in managed services can reduce costs by shrinking clusters during low-traffic periods. ClickHouse clusters typically take several minutes to add or remove nodes safely, making auto-scaling more suitable for predictable traffic patterns than sudden spikes.
Operational complexity curve
DevOps overhead increases non-linearly with node count, as distributed systems introduce failure modes that don't exist in single-node deployments. Monitoring for replication lag, distributed query debugging, and procedures for handling split-brain scenarios where network partitions cause nodes to lose contact become necessary.
Configuration management also becomes more involved, with settings for shard distribution, replica placement, and quorum requirements affecting both performance and availability. A single misconfigured setting can cause queries to fail or data to be written to the wrong nodes.
Disaster recovery considerations
Backup strategies differ substantially between single and multi-node deployments. Single-node backups typically involve full snapshots of the data directory, while multi-node clusters can back up individual shards or replicas, allowing faster recovery times.
Distributed backups introduce consistency challenges, where you need to ensure all shards are backed up at the same logical point in time. ClickHouse provides tools for coordinated snapshots across replicas, but coordinating backups requires careful planning.
Sizing guide for a ClickHouse cluster
Practical recommendations for different deployment scenarios follow a progressive scaling approach. Starting small and adding capacity based on observed bottlenecks works better than over-provisioning upfront.
Starter single-replica two-shard setup
A minimal distributed configuration for testing distributed features while maintaining simplicity uses two nodes, each acting as a single shard with no replication. This setup costs less than a fully replicated cluster but provides experience with distributed tables and query routing. Note that DuckDB v1.4 LTS can handle ≈100 TB of CSV data on a single machine with 1.5 TB RAM, demonstrating that single-node solutions can scale further than traditionally expected.
This configuration works well for development environments or proof-of-concept projects where data loss is acceptable. You can test distributed query patterns and understand performance characteristics before committing to a production-ready topology.
Growth three-replica multi-shard setup
A production-ready configuration balancing performance, availability, and operational complexity typically uses three or four nodes with two shards and two replicas per shard. This topology survives single-node failures while distributing both reads and writes across multiple machines.
A four-node cluster might configure two shards with each shard replicated across two nodes, providing redundancy and allowing half the cluster to fail without data loss. This setup handles most production workloads up to several terabytes of data and thousands of queries per second.
Enterprise tiered-storage autoscale setup
Advanced configurations with hot/cold storage tiers separate frequently accessed data from archival data. Hot tiers use fast NVMe SSDs for recent data, while cold tiers use cheaper object storage like S3 for historical data that's queried less frequently.
Autoscaling rules can add read replicas during peak traffic periods and remove them during off-hours, optimizing cost while maintaining performance. This approach requires sophisticated orchestration and works best with managed services that handle the complexity of adding and removing nodes safely.
Local prototype to distributed migration path
Transitioning from development to production environments often involves moving from DuckDB prototyping to ClickHouse production deployments. Understanding the migration path helps you build prototypes that translate easily to production systems.
Sharing SQL lineage between engines
Both DuckDB and ClickHouse support standard SQL for most analytical queries, with common functions like GROUP BY, JOIN, and window functions working identically. You can develop and test queries locally with DuckDB, then run the same SQL against ClickHouse with minimal modifications.
Some ClickHouse-specific features like distributed tables, materialized views, and specialized functions won't work in DuckDB. Keeping prototype queries focused on standard SQL constructs maximizes portability, and you can add ClickHouse-specific optimizations after migrating to production.
Data migration techniques
Moving data from local DuckDB files to distributed ClickHouse clusters typically involves exporting to Parquet or CSV format, then using ClickHouse's file-based ingestion tools. For larger datasets, you can parallelize the export by partitioning data into multiple files, then loading each file into the appropriate ClickHouse shard.
Tinybird's data source connectors simplify this process by accepting NDJSON, Parquet, or CSV files directly, handling the distribution and replication automatically. This approach eliminates manual configuration of sharding rules or data placement across nodes.
Observability hand-off
Monitoring and alerting differ substantially between single-node and distributed deployments. DuckDB's embedded nature means monitoring focuses on host application metrics, while ClickHouse clusters need dedicated observability infrastructure.
ClickHouse provides system tables like system.replicas and system.mutations for monitoring cluster health, but interpreting these metrics requires understanding distributed systems concepts. Managed services like Tinybird abstract these details, providing application-level metrics like query latency and error rates without exposing underlying cluster complexity.
The Tinybird managed route to zero-node ops
Managed ClickHouse services eliminate node management complexity by handling infrastructure provisioning, scaling, and maintenance automatically. Developers can focus on building applications instead of operating database clusters.
Tinybird abstracts away cluster management while providing ClickHouse performance, automatically distributing data across nodes and scaling capacity based on workload. Developers define data sources and SQL queries, and Tinybird handles shard placement, replication, and query routing without requiring any cluster configuration.
The platform also provides managed ingestion for streaming data sources and hosted API endpoints that expose ClickHouse queries as REST APIs. This eliminates the need to build custom API layers or manage connection pooling, reducing the time from data to production API from weeks to hours.
Sign up for a free Tinybird plan to get started with managed ClickHouse without any infrastructure setup.
FAQs about ClickHouse vs DuckDB node sizing
How do I estimate memory requirements per ClickHouse node?
Calculate based on working set size and concurrent query count, where working set refers to the data actively accessed by queries. Generally allocate memory equal to your most frequently accessed data plus query processing overhead, which typically ranges from 20-50% of the working set size depending on query complexity.
Can I use DuckDB for development and ClickHouse for production?
Yes, both support standard SQL for most analytical queries, allowing you to test query logic locally with DuckDB before deploying to ClickHouse clusters. This approach works well for validating business logic and prototyping dashboards, though you'll want to test performance and concurrency characteristics in a ClickHouse environment before production deployment.
What happens when a ClickHouse replica fails in a two-node cluster?
The remaining node continues serving queries but loses fault tolerance, as no backup copy of the data exists. Replacing failed nodes quickly restores redundancy, typically within hours rather than days, to minimize the window of vulnerability to additional failures.
Does DuckDB support high availability configurations?
No, DuckDB runs as a single-process library without built-in replication or clustering capabilities. Applications requiring high availability need to implement their own redundancy at the application layer, such as running multiple instances with separate DuckDB databases and coordinating writes across them.
How does streaming ingestion performance change as I add ClickHouse shards?
Additional shards distribute write load across more nodes, increasing total ingestion throughput roughly linearly with shard count. A single shard might handle 100,000 rows per second, while four shards scale to approximately 400,000 rows per second, assuming balanced data distribution and sufficient network bandwidth.
/