Faster DuckDB Queries with Vortex

DuckDB 2.0 has just been released! It brings a new storage format, asynchronous I/O, and improvements to query execution.

If you use Vortex with DuckDB, you might be able to get even faster query performance!

DuckDB supports querying external files (like CSV or Parquet), so we can keep our data in a different format without first loading it into DuckDB's native storage. Vortex is one such file format. Similar to Parquet, it is an open columnar file format built for analytical workloads, but Vortex can also work on data while it is still compressed, which mitigates some of the significant performance costs of decompression.

Vortex is available as a DuckDB core extension on Linux and macOS, so you can read from and write to Vortex files directly inside DuckDB.

Compression can reduce how much data a query reads from disk or object storage, but decompression still takes valuable CPU time. If we can perform an operation before decompressing the data, we can avoid much of that work.

Vortex ships with a highly optimized reader alongside its file format, with code for reading and computing over compressed data. An engine can delegate and "push down" supported operations to this reader, so Vortex can serve DuckDB as well as other database query engines.

DuckDB is responsible for plannig the SQL queries and handles joins and aggregates, while the Vortex reader scans the requested columns and evaluates supported filters. Vortex and DuckDB both work with batches of columnar values, and Vortex can often zero-copy pass the data up to DuckDB.

For example, ClickBench runs 43 analytical queries over hits, a single table of web analytics data. Query 20 counts rows whose URL contains google:

SELECT COUNT(*)
FROM hits
WHERE "URL" LIKE '%google%';

Vortex can search strings while they're still compressed, and DuckDB counts the matching rows, so we can avoid the work of reconstructing full URLs that the query never returns.

In our ClickBench results, DuckDB + Vortex runs Q20 about 3x faster than both DuckDB + Parquet and DuckDB's native storage. Across all 43 queries, DuckDB + Vortex is about 2x faster than both by geometric mean.

Every ClickBench query finishes in under a second with all three formats, so these differences might not matter much on their own. But we also run benchmarks at larger scales, where DuckDB with Vortex is faster than DuckDB with Parquet on local NVMe.

Suppose we already have our data in a Parquet file like hits.parquet. After loading the Vortex extension, we can use DuckDB to read that Parquet file and write hits.vortex, then run the same query against the new file:

INSTALL vortex;
LOAD vortex;

COPY (SELECT * FROM 'hits.parquet')
TO 'hits.vortex' (FORMAT vortex);

SELECT COUNT(*)
FROM 'hits.vortex'
WHERE "URL" LIKE '%google%';

On local storage, you should expect most analytical queries to run faster with Vortex than with Parquet.¹ If you want to compare the formats, simply run the same SELECT against hits.parquet (the only difference is the file name in the FROM clause)!

The DuckDB integration guide covers other ways to read and write Vortex files, including files in object storage.

Faster queries are one reason to store data in Vortex. Another reason is that your data can be accessible outside of DuckDB. By keeping it in .vortex files instead of DuckDB's native storage, we can use another engine with Vortex support without having to export the dataset. Parquet is the usual choice for this kind of data, but as the ClickBench results above show, DuckDB can query Vortex faster than Parquet, so we don't have to trade speed for portability.

For example, we can explore hits.vortex in DuckDB but use DataFusion to query the same file from a service, so we don't need a separate copy of the data for each engine or a pipeline to keep those copies in sync. Vortex also works with Apache Arrow, the in-memory columnar format that many data systems share, so the same files work in PyArrow workflows as well as SQL queries.

Sharing files between engines works well for a single dataset, but many teams have to manage large datasets as tables in an open table format such as Apache Iceberg. Iceberg tables keeps track of many data files, along with the table's schema and history. In practice, those data files are almost always Parquet, which means Iceberg tables inherit Parquet's limits: Parquet can be slower to query, and it is hard to extend with new types or encodings.

We want Iceberg users to have a faster, more extensible option, so we've been working on using Vortex files as the data files in Iceberg tables. That technical work is complete, and we're now seeking acceptance from the Iceberg community.

Put together, Vortex can give DuckDB faster queries on data that other engines can still read. If you already use DuckDB, trying Vortex only takes INSTALL vortex;, LOAD vortex;, and a COPY to write your data as a .vortex file. Later posts in this series will go into more technical detail about how DuckDB and Vortex work together.