DuckDB and When You Do Not Need a Cluster

Q: How big does data need to be before a single node stops working?

Far larger than most people assume, and the honest answer depends on your scanned working set rather than your stored volume. Rough guidance by the data a typical query actually reads after projection and predicate pushdown: under 1GB is trivially fast on any modern machine. Between 1GB and 50GB, a single node is comfortable and usually beats a cluster outright, because coordination overhead dominates at that scale. Between 50GB and 500GB, a single node still works well given adequate RAM, and out-of-core spilling handles the cases that exceed it. Between 500GB and 2TB it is feasible on a large instance but needs deliberate attention to layout and memory. Above roughly 2TB of genuine working set, distribution becomes the right answer. The measurement that matters is not total storage. A 5TB Parquet lake where a typical query filters to one month and selects six columns out of two hundred may scan under 20GB. Columnar formats read only the columns named, and row-group statistics let the engine skip blocks whose min and max values exclude the predicate. Size the platform against that number. Teams routinely provision against the 5TB and never measure the 20GB. Two things extend the ceiling further than expected. Out-of-core execution means exceeding RAM degrades performance rather than failing the query, and with NVMe reading at several GB per second the degradation is gentler than intuition suggests. And cloud instances with several hundred GB of RAM and dozens of cores are ordinary now, not exotic. The practical test is empirical rather than theoretical: take your slowest real query, run it on one large instance against real data with a sensible file layout, and compare. That answer is worth more than any threshold in an article.

Q: Why is a cluster sometimes slower than one machine on the same query?

Because a distributed engine pays a fixed coordination cost on every query, and that cost is only worth paying when the parallel work it enables is large enough to dwarf it. On a small dataset the overhead is most of the runtime. The fixed costs are concrete. The job must be scheduled and admitted. Executors must be running or started, and cold-start on an autoscaling cluster can take tens of seconds by itself. The query plan is serialised and distributed to workers. Data is partitioned and, for joins and aggregations, shuffled across the network — which is frequently the dominant cost, since network bandwidth is far below memory bandwidth. Partial results are collected and merged. Workers coordinate through a driver that can itself become a bottleneck. Against a 40GB working set, a single machine reads at memory and NVMe speed with no network hop, no serialisation, and no scheduling. It starts executing in milliseconds. The cluster is still distributing the plan while the single node is finishing. There is a second effect worth naming: shuffles are where distributed queries fail as well as where they slow down. A join whose key distribution is skewed sends most rows to one worker, which is both slow and a common source of out-of-memory failures. A single node has no shuffle and therefore no skew problem. None of this argues against distribution at real scale, where parallel processing genuinely wins and the fixed cost amortises across a large amount of work. It argues that the crossover point is much higher than the industry defaults suggest, and that most teams sit below it while paying above it.

Q: Should we replace our data warehouse with DuckDB?

Usually not, and framing it as a replacement is the fastest way to make a bad decision. The far more common good outcome is adding it as a layer alongside what you have. Five places it fits well without displacing anything. Local development against real production-shaped data, so engineers stop testing against fixtures that do not resemble reality. CI running genuine data tests against real files rather than mocks, which catches an entire class of bug earlier. Ad-hoc analysis that runs immediately rather than queueing behind a scheduler. Pipeline transformation steps that were round-tripping through a warehouse purely because that is where the engine lived. And embedded analytics inside an application, where an in-process engine avoids an external dependency altogether. Where a warehouse remains the right answer: high-concurrency serving for many simultaneous users, which is a serving problem rather than a size problem; multi-tenant isolation with per-tenant resource limits, which an embedded engine simply does not provide; continuous ingestion with transactional guarantees; centralised governance, access control and audit; and working sets that genuinely exceed a large instance. The strategic point is that this is not either-or, because everything speaks the same open formats. Parquet on object storage can be read by your warehouse, your Spark jobs and a single-node engine without duplication, so adding one is additive rather than a migration. And if your warehouse currently meets its SLAs and its cost is not a problem anyone has raised, migrating to save money nobody asked to save is churn with migration risk attached. The case for adopting should come from a specific pain — slow iteration, scheduler queues, cost on a particular workload — not from the technology being interesting.

Q: What actually makes DuckDB fast compared to older single-node databases?

Four design decisions, and they are why a modern single-node engine is not comparable to the single-node databases people remember from a decade ago. Columnar storage means data for one column sits contiguously, so a query naming six of two hundred columns reads only those six. Row-oriented databases must read entire rows and discard most of each one, which is why they were genuinely unsuited to analytical scans and why the distributed era began. Vectorised execution processes batches of values — typically around two thousand at a time — in tight loops rather than row-at-a-time through an interpreter. This keeps working data in CPU cache, amortises per-operation overhead across the batch, and lets the compiler emit SIMD instructions. The gain over row-at-a-time interpretation is large enough to change which architecture a workload needs. Direct querying of Parquet with pushdown means no import step. The engine reads Parquet in place, uses row-group statistics to skip blocks the predicate excludes, and applies projection so unnecessary columns never leave storage. Eliminating the load step removes both time and an entire class of pipeline. Zero-copy interchange through Arrow means results move to pandas, Polars or another Arrow consumer without serialisation. In pipelines that previously spent significant time converting between formats, this is often the single largest speedup. Two operational properties compound these. In-process execution means no server, no connection pool and no network hop — the engine runs inside your Python process or CLI. And out-of-core spilling means memory is a performance boundary rather than a correctness one, so exceeding RAM slows the query instead of failing it.

Q: How do I measure my real working set rather than my stored data?

Measure bytes actually read per query, and if your engine reports it, this takes an afternoon rather than a project. Most query engines expose scanned bytes in their query profile or execution metrics. Collect that figure across a representative sample of your real production queries over a normal week — not the largest query anyone ever ran, and not a synthetic benchmark. Look at the distribution: the median tells you what your platform normally handles, and the 95th percentile tells you what it must handle without falling over. Those two numbers, not your storage total, should drive engine choice. If your engine does not report scanned bytes, approximate it. Take the columns each query selects as a fraction of total columns, multiply by the fraction of partitions the filters touch, and apply that to total size. It is rough, but it lands in the right order of magnitude, which is all the decision requires. Two things to check while measuring. First, whether pushdown is actually working — if queries are scanning far more than their column and partition selection implies, your file layout is defeating it, and fixing that improves your existing platform regardless of what engine you end up on. Unpartitioned files, tiny row groups, or partitioning that does not match how people filter are the usual causes. Second, measure concurrency alongside size: peak simultaneous queries and their arrival pattern, because that axis independently determines whether a single node is viable at all. The common and expensive mistake is provisioning against total storage. A team with 10TB in object storage assumes they need a distributed platform, when their median query scans 8GB and their peak scans 60GB. That is a single-node workload being served by a cluster, and the measurement is what reveals it.

Q: What are the real limitations of single-node analytics?

Five, and they are architectural boundaries rather than gaps that will close with a future release. Concurrency. An embedded engine is designed for one process running queries, not for hundreds of simultaneous users. If you are serving a BI tool used across the company, you need a system built for concurrent serving with connection management and workload isolation. This is independent of data size — a 10GB dataset serving three hundred concurrent dashboard users is a warehouse workload. Multi-tenancy and resource isolation. There is no per-tenant memory limit, no query governor, no way to stop one expensive query starving everything else in the process. In a shared environment that matters a great deal. Continuous ingestion with transactional guarantees. Single-node analytical engines are built for querying files, not for high-rate concurrent writes with ACID semantics across many writers. That is a different system. Genuine scale. Above roughly a couple of terabytes of working set, distribution is the correct answer and no amount of vectorisation changes that. High availability. One process on one machine has no failover story. If analytical queries are on a critical path with an uptime commitment, you need something with redundancy built in. There is also an organisational limitation worth naming honestly. Warehouses provide centralised governance, access control, audit logging and lineage. Distributing analysis across many local processes can undermine those, and in a regulated environment that is a real cost rather than a bureaucratic one. The resolution most teams land on is a split: warehouse for shared serving, governance and genuine scale, single-node for development, CI, pipeline transformation and ad-hoc work. Open formats make that split cheap to maintain.

Q: Does file layout matter more than engine choice?

Frequently, yes — and it is the first thing to fix because it improves whatever engine you are running, which makes any subsequent comparison honest rather than a measurement of your layout. Four layout decisions dominate. Format: Parquet or another columnar format is not optional for analytical work. Querying JSON or CSV at volume defeats every optimisation any engine has, and teams comparing engines over row-oriented text files are measuring parsing rather than execution. Partitioning aligned to how people actually filter: if most queries restrict by date, partition by date, and the engine skips entire directories rather than reading and discarding. Partitioning on a column nobody filters by adds directory overhead and gains nothing. Row-group sizing: too small and per-group metadata overhead dominates while parallelism suffers; too large and statistics become coarse so skipping stops working. Somewhere in the region of a hundred to a few hundred megabytes is a common sensible default. Sort order within files, which is the most underused lever available — sorting by a frequently filtered column tightens the min and max statistics per row group, so far more blocks can be skipped. The effect on scanned bytes can be dramatic. Two anti-patterns worth checking for specifically. The small-file problem, where a streaming or per-batch writer produces thousands of tiny files and metadata overhead swamps the actual work; compaction is the fix and it often produces a larger speedup than any engine change. And over-partitioning, where partitioning on a high-cardinality column creates so many directories that listing them costs more than scanning would have. Fix the layout first. Then benchmark. Teams that skip this step regularly conclude that engine A beats engine B when what they measured was one engine reading well-organised files and the other reading badly-organised ones.

Q: How does this change now that AI agents are writing the queries?

It shifts the workload profile in a direction that favours fast-starting single-node engines, and it is a genuinely new consideration rather than a restatement of the old debate. Natural-language analytics and agentic data exploration generate far more queries than human analysts ever did, and those queries have a different shape: they are numerous, exploratory, frequently small, unpredictable in arrival, and latency-sensitive because an agent may issue a sequence of dependent queries where each one blocks the next. That profile is poorly matched to a system with a fixed per-query coordination cost. If an agent issues thirty exploratory queries to answer one question, thirty scheduling and startup overheads are paid, and the user waits through all of them. An engine that starts in milliseconds and has no scheduler queue changes the interaction from sluggish to immediate. Three practical implications. Sub-second startup matters more than peak throughput for this traffic, which inverts the usual benchmarking priority. Cost per query matters more when query volume rises by an order of magnitude, and a cluster billed continuously is a poor fit for bursty exploratory load. And isolation matters, because an agent generating an accidentally expensive query should not affect other users — which cuts both ways, since a separate embedded process per session provides natural isolation but no resource governance. The sensible architecture that is emerging looks like a split rather than a replacement: a governed semantic layer and warehouse for shared, audited, high-concurrency serving, with fast local engines handling the exploratory tail. Both read the same open formats, so there is no duplication, and the query router sends work to whichever is appropriate for its shape.