A New TRY() expression for safer queries

If you’re ingesting messy data sources or writing resilient data pipelines, the TRY () function offers more graceful handling for bad data by returning NULL values instead of errors on problematic rows.

Pushdown of inequality conditions into joins

A huge win for incremental dbt models and other workloads that rely on join conditions, DuckDB and MotherDuck users can expect much better performance when filtering.

Pushdown of arbitrary expressions into scans

DuckDB can now push down more types of filter expressions directly into scans, reducing the amount of data that needs to be processed downstream to deliver up to 30X faster queries in these scenarios.

Late materialization

DuckDB now defers fetching columns until absolutely necessary, resulting in 3–10x faster reads for queries with LIMIT.

~15% average speedup on reads

General read performance is significantly improved due to new efficiency scan and filter improvements, even without late materialization.

30%+ faster write throughput

Major improvements to multithreaded Parquet export performance result in even faster writes.

Better compression for large strings

Large strings can now be dictionary-compressed, resulting in reduced file sizes and performance boosts.

Smarter rowgroup combining

Smaller rowgroups from multiple threads are now merged at the time of write, resulting in more efficient Parquet files.

Here are 12 performance highlights that caught our attention:

• 2x faster Top-N for large LIMIT queries: If you’re working with up to 250K rows, Top N is now faster than sorting!

• 3x fewer memory allocations in aggregations: Improvements to string hashing and aggregation internals reduce memory pressure and lower contention, leading to more efficient execution of queries like COUNT(DISTINCT) at scale.

• ~25% faster performance for large hash table creation: The parallelism strategy has been refined to avoid excessive task splitting, leading to better memory access patterns and faster hash table initialization during large joins.

• 20x faster UNNEST and UNPIVOT for small lists: DuckDB now processes multiple lists at once and eliminates unnecessary copying to deliver better performance for common patterns like unpivoting a few columns.

• 30–40% faster RANGE based window functions: Parallelized task processing across hash groups and reduced lock contention during execution now lead to smoother, more efficient performance.

• 7x faster conversion to Python object columns: Optimized Python object conversion due to skipping intermediate steps to speed up performance for object columns and scalar UDFs.

• 5–25% faster LIKE '%text%' and CONTAINS string scans: Unified and optimized DuckDB’s implementation using memchr for early match detection to speed up substring searches across the board.

• Faster list-of-list creation: Improved performance when constructing nested lists, boosting speed for transformation pipelines that rely on complex list structures.

• Reduced memory contention in hash joins: Introduced parallel memset for initializing large join tables, eliminating single-threaded bottlenecks and improving performance on multi-core systems.

• Faster recursive CTEs and complex subqueries performance: Adopted a new top-down subquery decorrelation strategy, unlocking better optimization for nested queries and improved performance for recursive CTEs.

• Improved performance and support for JSON-heavy queries: More parallelism in UNION ALL and resolution of multiple JSON edge cases, for better handling.

• Faster decoding of short FSST compressed strings: Optimized decoding for inlined strings by skipping unnecessary copying, resulting in ~15% speedups without performance regressions on longer strings.

All these optimizations add up to one thing: even faster queries without lifting a finger.