When it comes to data engineering and analytics, no tool has garnered as much hype as DuckDB, an in-process analytical database that has earned a name for itself not only as being convenient but also as being extremely fast in most cases, even matching or beating enterprise databases in their analytical performance when running directly from your own laptop. However, how is DuckDB so fast? It turns out there is not one thing but a highly optimized architecture in which each and every component was designed for maximum speed. Learn More
The Vectorized Execution Engine
The foundation of efficiency in DuckDB lies in its vectorized approach to operations. Conventional database engines perform operations row-by-row, which may involve substantial costs due to interpretation, whereas DuckDB operates in batches called DataChunks, which consist of up to 2,048 rows and are stored as column vectors. Batch processing brings about several major advantages: it decreases the number of function invocations when processing queries, increases CPU cache hit rate because of processing data in tight loops, and allows for generation of optimized code by modern compilers thanks to auto-vectorization.
Take, for example, an aggregation query over several millions of rows. Conventional databases will have to fetch a row from a table, extract the necessary columns, and update aggregates, which will require numerous function calls and memory access operations. DuckDB will apply the same procedure to all 2,048 rows in one single go. The technique makes use of CPU pipelining and SIMD instructions and is able to achieve practically optimal performance.
Smart Storage and Zone Maps
The way data is stored in DuckDB is equally ingenious. Data is stored row-by-row within row groups, with each column segment having zone maps—metadata storing the minimum and maximum values of the segment. That tiny detail allows for an extremely efficient optimization known as query skipping.
Suppose you have a table of taxi rides, which includes a pickup date column. You are trying to find trips made in a certain week using your query. DuckDB compares each row group’s zone map for the pickup_date column against the filters of your query. If the min-max interval of the row group does not match your filters, then the whole row group is skipped—no disk I/O, no decompression, no computation. It’s like saying “If you don’t need it, don’t even look at it.”
However, things become really interesting when we combine query skipping with sorted data. If you sort your table by often-filtered columns upon loading the data, you can achieve extremely selective zone maps. If you load time-series data sorted by its timestamp and run a query, which filters out recent data, you’ll most likely hit only the latest row groups—saving hundreds of times in I/O operations.
The Sorting Revolution
Perhaps the focus of DuckDB on speed is most clearly seen through its sorting mechanism, which has been completely redesigned not one but two times in the last few years. The present strategy can be considered to be an example of systems engineering at its best since it uses many different approaches.
Key Normalization
Key normalization is the first ingenious technique. Databases that evaluate types during execution time, such as DuckDB, have to pay an additional price in the form of comparison overhead while ordering tuples of differing types. In order to circumvent this, DuckDB makes use of a function called create_sort_key. This function transforms sorting expressions into comparable BLOBs, where instead of comparing arbitrary type combinations, the engine performs binary comparisons of fixed-sized binary objects.
sql
SELECT * FROM tbl
ORDER BY create_sort_key(x, 'DESC NULLS LAST', y, 'ASC NULLS FIRST');This method removes overhead associated with interpretation during the comparison process, replacing complicated interpretation with a simple memory comparison operation.
Static Integer Optimization
However, DuckDB goes further than this. In cases where sort keys occupy less than 16 bytes, these sort keys are translated into a C++ struct of two 64-bit unsigned integers. The structure is fixed at compile time, allowing the compiler to produce very highly optimized comparison code. The comparison is done using integer comparison with no dynamic dispatch:
cpp
bool LessThan(const FixedSortKeyPayload &lhs, const FixedSortKeyPayload &rhs) {
return lhs.part0 < rhs.part0 || (lhs.part0 == rhs.part0 && lhs.part1 < rhs.part1);
}Non-Contiguous Iteration and Adaptive Algorithms
One such optimization is the memory allocation layout. In DuckDB, there are 256 KiB page allocations. In order to sort more data than the size of one page allows, there is an iterator that allows the engine to traverse over disjoint memory regions. Thus, sorted runs spanning several pages are possible, resulting in longer sorted runs to increase merge efficiency.
There is a variety of algorithms used in the implementation of sorting. There is Vergesort, which finds and sorts pre-existing runs of sorted data, and Skasort, which is a radix sort on the first 64-bit integer key. As a default fallback algorithm, there is pattern-defeating quicksort.
The Query Optimizer
The query optimizer in DuckDB, which is often neglected, is perhaps the least-understood part of this system. The optimizer performs a set of optimizations that allow naive query plans to become very efficient. Take, for example, a query where taxi information is joined against look-up tables for picking up and dropping off areas:
An unoptimized version of this query would use cross-products to compute trillions of intermediate results, taking more than 24 hours to finish.
A query with optimization takes only 0.769 seconds. Why? Because the optimizer performed three important optimizations:
- Filter Pushdown: The filters for Manhattan borough are pushed down to the table scans, thereby lowering the number of rows in the lookup tables from 256 to 45 per lookup table.
- Join Order Optimization: The order of joins is altered such that the smaller data sets (45 rows) are first joined together, greatly lowering the intermediate cardinality.
- TopN Optimization: Rather than sorting all the data and picking the top 5, the optimizer has introduced the TopN operator that sorts the top 5 rows.
The performance difference is staggering: 0.769 seconds vs. over 24 hours—a 100,000x speedup from smarter planning alone
Parallel Execution Without Overhead
Morsel-Driven Parallelism is the technique DuckDB employs for parallel query execution. This approach uses separate phases for operations that block such as sort and hash-aggregation:
- Sink: Threads accumulate data separately
- Combine: Threads merge their data after finishing
- Finalize: Called once all threads finish
- GetData: Output the data to the next pipeline
This approach enables efficient parallel execution without excessive synchronization overhead. Each thread processes its own morsel (chunk) of data, and the system balances work dynamically.
Practical Performance in Numbers
It is clear from benchmarking performance that these optimizations work well. For instance, TPC-H (a popular analytical benchmarking tool) shows that DuckDB performs query operations in milliseconds, whereas other databases perform them in seconds. One example is Query 6, where a simple aggregation operation takes only 0.3 ms to perform on the lineitem table—260 times faster than other competing tools for small-scale factors.
Beyond the Basics
Even more performance stories await us from DuckDB. The recently introduced Selective Late Materialization could possibly yield another performance boost by letting each attribute of the query decide on its materialization phase individually. Such an approach was able to deliver an average speedup of 14.7% over early materialization on the Join Order benchmark suite, with some queries experiencing up to 76.7% gains in speed.
DuckDB is great at processing large datasets thanks to its single-file storage system combined with Adaptive Radix Trees (ART). DuckDB stores data in blocks (256 KB each) and manages those blocks via an ART index.
When DuckDB Excels
Knowing about the architecture of DuckDB will help to identify where it performs well:
- Analytic loads: Complicated aggregates, joins, and window functions performed on large data sets
- Embarrassingly parallel queries: Queries that can be distributed across row groups
- In-process tasks: Queries executed within the application itself without the need for a server
- Data preparation: ETL pipelines process, where efficiency is crucial
DuckDB is not efficient for high-concurrency transactional loads where traditional databases have the upper hand. Learn More
Conclusion
The efficiency of DuckDB comes from carefully planned engineering. Vectorized execution serves as a fast engine. Zone maps and sorting make it possible to skip unnecessary data. Sorting algorithms are flexible and adjust to the characteristics of data. Query optimization works aggressively in search of an optimal execution plan. Parallel execution works efficiently across multiple cores.
All of these elements work together to form an entity much more powerful than its separate parts. This results in a database that can process billions of rows on a laptop, which is a requirement for today’s data analysis. It is crucial to be able to ask questions fast rather than work with large amounts of data.
Explore Our Programming Category
