The release of the DBMS DuckDB 0.10.0 has been announced, combining properties of SQLite such as compactness, the ability to connect as an embedded library, storing the database in a single file, and a user-friendly CLI interface, with tools and optimizations for executing analytical queries that cover a significant portion of the stored data, for example, performing aggregations of all table contents or merging several large tables. The project code is distributed under the MIT license. Development is still in the experimental release stage, as the storage format is not yet stabilized 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:
- Significantly improved performance in parsing CSV data. For instance, reading a CSV file with 11 million lines in the new version has reduced from 2.6 seconds to 1.15 seconds, while executing the 'SELECT COUNT(*)' operation on a CSV file has dropped from 1.8 seconds to 0.3 seconds.
- Support for fixed-size arrays has been added, resembling lists containing a fixed number of elements ('CREATE TABLE vectors(v DOUBLE[3]);').
- Support for connecting to MySQL, PostgreSQL, and SQLite DBMSs has been added, allowing data to be loaded into DuckDB from external DBMSs and transferred between different systems. Access to external databases is performed using typical tables. ATTACH āpostgres:dbname=postgresscannerā AS postgres; SELECT title, release_year, length FROM postgres.film LIMIT 5;
- Support for the 'COMMENT ON' expression has been added to save comments about objects in the database.
- Support for the 'COPY FROM DATABASE' expression has been added for copying all content from one database to another.
- Support for the 'ALL' modifier in EXCEPT and INTERSECT expressions has been added.
- The type '
Source: opennet.ru
