Presented by Gabor Szarnyas at OSA Con 2023
DuckDB is an analytical database management system. It runs in-process, which makes its configuration trivial and eliminates any overhead between the client application and the database. DuckDB is open-source and highly portable, with integrations for Python, R, Java, Julia, and 10+ other languages. DuckDB has top-notch support for data formats (CSV, Parquet, JSON, Iceberg) and data sources (https, s3, gcs, etc.). This talk will introduce the DuckDB system, explain its key design decisions, and demonstrate how it is able to scale even on a single laptop.
DuckDB is aimed at analytical workloads and designed for easy installation and deployment. Inspired by traditional databases like MySQL, DuckDB brings the database server into the client application to eliminate configuration, authentication, and client protocol bottlenecks. Written in C++11 and fully open-source under the MIT license, DuckDB supports an in-memory database and a single file format for persistence. It can be installed and running in less than 15 seconds on various platforms and supports multiple programming languages and operating systems. DuckDB is known for its speed, with a load time of over one gigabyte per second and roughly three times compression over the original CSV data. The system can even be compiled to run within a browser using web assembly. DuckDB's column-based storage and vectorized execution model allow for efficient processing of large datasets without running out of memory. Its indexing system, called zone maps, creates minimum and maximum indexes for each row group, and it supports larger-than-memory execution. DuckDB's extension mechanism allows for the definition of new types, functions, data formats, operators, and SQL syntax. The system is used by many people to save money and is often deployed as a building block for various use cases. However, it is not suitable for all workloads and has limitations, such as single-node execution and read-only mode for multiple processes due to caching reasons.
00:00:00 Overview of DuckDB: The motivation behind DuckDB's creation is the increasing power of end-user devices, such as laptops, which can now handle complex data processing tasks. Traditional database systems, with their client-server architecture and expensive servers, are not optimized for this new era. DuckDB's solution is to bring the database server into the client application, eliminating the need for configuration, authentication, and the client protocol, which is a major bottleneck for analytical data workloads. DuckDB is written in C++11, fully open-source under the MIT license, and supports an in-memory database and a single file format for persistence. The speaker is a former academic and now a developer relations advocate at Duck DB Labs.
00:05:00 Gabor discusses DuckDB, a unique database system that targets analytical workloads and is designed for fast installation and deployment. DuckDB was inspired by popular databases like MySQL but differs in its deployment model and target workload. It aims to be portable and can be installed and running in less than 15 seconds on various platforms, including Mac OS, Python, Windows, and R Studio. DuckDB supports multiple programming languages and operating systems and is known for its speed due to its zero external dependencies and pure C++ codebase. The system can even be compiled to run within a browser using web assembly. DuckDB is also fast in terms of data processing, with a load time of over one gigabyte per second and roughly three times compression over the original CSV data. The speaker then proceeds to demonstrate DuckDB's functionality in practice using a Jupyter Notebook.
00:10:00 Gabor demonstrates the ease of importing and querying large CSV files. He also shows how to use DuckDB's "describe" command to confirm that the database correctly assumed the schema. DuckDB quickly loads more than half a billion rows without requiring the user to specify the data format.
00:15:00 Gabor demonstrates the pivot operation in DuckDB, which turns a long table into a wide table in just 28 milliseconds.
00:20:00 Gabor discusses the efficiency and fast processing of DuckDB. DuckDB is cache and pipelining friendly, allowing for skipping most random accesses, resulting in fast processing.
00:25:00 Gabor discusses the benefits and limitations of the database system. DuckDB is an easy-to-install system that is open standard compliant and does not require configuration or a DBA for maintenance. However, it is not suitable for all workloads, particularly those that are right-heavy or require distributed execution.