• In-process database for edge analytics.
• New version with additional features and performance optimisations.

DuckDB has announced the release of version 1.2 of its database software, with several feature upgrades and performance improvements.

Code-named Histrionicus (after the magnificent Harlequin duck’s Latin name), version 1.2.0 is the result of over 5,000 commits by more than 70 contributors since version 1.1.3.

Convenient, lightweight databases

Staging a full SQL-compatible database is a fairly trivial task, but many developers need something simpler and lightweight that doesn’t necessarily warrant the full client-server model. The best known solution for a standalone, single database file solution is SQLite, a handy piece of software to be found in literally billions of instances all over the planet.

Produced in the Dutch public sector and later spun off into a full, open-source database technology, DuckDB takes the spirit of simplicity and ease of use that millions of SQLite users love, and adds capabilities that make it the ideal choice for analytics workloads running close to data sources. Edge environments where there’s a need for fast data analysis of IIoT data would be one example, or in places where embedded analytics are required to run on modest hardware.

Where SQLite excels at real-time data processing (transactional workloads), DuckDB is optimised for OLAP workloads (as opposed to OLTP). It users a vectorised, interpreted execution engine, storing fixed-length data types (such as integer values) as native arrays

Like SQLite, it’s designed for use in-process, so ephemeral DBs can be created, queried and dropped as part of larger codebases, and both are ACID compliant (Atomic, Consistent, Isolated, and Durable).

Selected new features

Full release notes are on GitHub and run to over 700 fixes, improvements, new features and tweaks, but some of the highlights include:

• optional compression, not yet enabled by default to ensure backwards compatibility,
• simple conversion of older format DuckDB databases.
• better .csv options and optimisations, including the ability to recognise up to four-byte delimiters (the project maintainers showcase a duck glyph in place of boring old commas).
• prefix aliases for better readability. Expression and table aliases can be specified before they’re referred to, rather than using the AS syntax, which can put the derived results deep in a mess of Boolean logic.
• overall optimisation that improves performance, measured by the maintainers at a 13% boost on a laptop.
• a CLI Safe Mode (activated with the -safe flag), preventing the client from accessing external files other than the file initially connected to.
• use of 64-bit random numbers for random().

DuckDB can process large data sets with limited hardware without compromising on performance. With smaller data sets, it performs as well as its direct analogue, SQLite, but the larger the data set, SQLite can begin to slow because of its row-based execution model.

As with any software choice, it’s a case of horses for courses. The user base for SQLite is vast, and it’s a proven technology. DuckDB is perhaps not as well known, but its use-cases are deliberately different and its design ethos makes it a solution for particular settings and requirements.