The release of DuckDB 0.6.0 is now available. It combines features of SQLite, such as compactness, the ability to connect as an embedded library, storing the database in a single file, and a convenient CLI interface, with tools and optimizations for executing analytical queries that cover a significant portion of stored data, such as aggregating all table contents or merging multiple large tables. The project's code is distributed under the MIT license. Development is still in the experimental release stage as the storage format is not yet stable and changes from version to version.
DuckDB offers an extended dialect of SQL, including additional capabilities for processing very complex and lengthy queries. It supports the use of complex types (arrays, structures, unions) and allows for arbitrary and nested correlated subqueries. It also supports concurrent execution of multiple queries and the ability to execute queries directly from CSV and Parquet files. There is an option to import from PostgreSQL databases.
In addition to the SQLite shell code, the project utilizes a separate library parser from PostgreSQL, the Date Math component from MonetDB, its own implementation of window functions (based on the Segment Tree Aggregation algorithm), a regular expression handler based on the RE2 library, its own query optimizer, a multi-version concurrency control (MVCC) mechanism for handling concurrent task execution, and a vectorized query execution engine based on the Hyper-Pipelining Query Execution algorithm, allowing for large sets of values to be processed in a single operation.
Among the changes in the new release:
- Work has continued on improving the storage format. An optimistic disk writing mode has been implemented, which allows large data sets to be compressed and streamed directly to the database file during a single transaction without waiting for the transaction confirmation by the COMMIT command. When the COMMIT command is issued, the data is already written to disk, and if ROLLBACK is executed, the changes are discarded. Previously, data was fully saved in memory before being written to disk upon committing.
- Support has been added for parallel data loading into separate tables, significantly increasing loading speed on multi-core systems. For instance, in the previous release, loading a 150 million row database on a 10-core CPU took 91 seconds, whereas in the new version, this operation is completed in 17 seconds. Two modes of parallel loading are provided: with preserved order of records and without preserving order.
- For data compression, the FSST (Fast Static Symbol Table) algorithm has been used, which allows data to be packed within strings using a shared dictionary of typical matches. The application of the new algorithm reduced the size of the test database from 761MB to 251MB.
- Algorithms Chimp and Patas have been proposed for compressing floating point numbers (DOUBLE and FLOAT). Compared to the previously used Gorillas algorithm, Chimp offers a higher compression level and faster unpacking. The Patas algorithm lags behind Chimp in compression ratio but is significantly faster in unpacking, with speeds nearly identical to reading uncompressed data.
- An experimental feature for loading data from CSV files in multiple parallel streams has been added (SET experimental_parallel_csv=true), significantly reducing the loading time of large CSV files. For example, with this option enabled, the loading time of a 720MB CSV file decreased from 3.5 seconds to 0.6 seconds.
- The ability to run operations for creating and managing indexes in parallel has been implemented. For example, the execution time of the CREATE INDEX operation for a column with 16 million records has been reduced from 5.92 seconds to 1.38 seconds.
- Parallel processing of aggregation operations in queries containing the expression "COUNT(DISTINCT col)" has been ensured.
- SQL has been enhanced with support for the UNION type, allowing multiple types to be bound to a single element (e.g., "UNION(num INT, error VARCHAR)").
- In SQL, it is now possible to create queries that start with the word "FROM" instead of "SELECT". In this case, it is implied that the query starts with "SELECT *".
- SQL has added support for the COLUMNS expression, which allows operations to be performed on multiple columns without duplicating the expression. For example, "SELECT MIN(COLUMNS(*)) FROM obs;" will execute the MIN function for each column in the obs table, and "SELECT COLUMNS('val[0-9]+') FROM obs;" for columns named with "val" followed by digits.
- Support for operations on lists has been added, such as "SELECT [x + 1 for x in [1, 2, 3]] AS l;".
- Memory consumption has been optimized. By default, the jemalloc library is used for memory management on Linux platforms. The performance of hash join operations has been significantly improved under limited memory size.
- A new output mode " .mode duckbox" has been added to the command-line interface, which discards the middle columns based on the width of the terminal window (suitable for quickly visually assessing results of queries with a large number of columns, such as "SELECT * FROM tbl", which usually wrap into several lines). The parameter ".maxrows X" can additionally limit the number of rows displayed.
- The CLI now supports context-aware input auto-completion (completing the input of keywords, table names, functions, column names, and file names).
- By default, the CLI includes a progress indicator for query execution.
Source: opennet.ru
