DuckDB Adds WAL Group Commit And ART Batch Index Scans

DuckDB took 221 commits on main in the last seven days, shortly after the 2.0 branch was merged back into it. Two changes affect how DuckDB behaves as an embedded store behind a pipeline. Concurrent commits now share WAL fsyncs, and IN list lookups on ART indexes run as one batch. The rest is profiler metrics, an optimizer guard, and compatibility work for extensions built on the old C API.

Until now the commit path handled one commit at a time, fsync included. Concurrent committers queued behind each other, and throughput stayed near 1 / fsync_latency no matter how many connections wrote. The WAL group commit splits the sync in two. FlushMarker writes a WAL_FLUSH marker and hands the buffer to the OS while the WAL lock is held. SyncUpTo(offset) runs after the commit locks are released, and one caller syncs every offset requested so far.

Snapshots stay safe. A commit that waits for its sync stays in the active transaction list, and StartTransaction caps new snapshots below the first commit that is not yet durable. When COMMIT returns, the data is on disk and every later transaction sees it. A commit with no other open transaction still syncs under the transaction lock, as before.

The PR benchmark ran single row commits on an Apple M4 Pro with an injected fsync delay, measured in commits per second:

  • 1 thread, 456 to 480 at 1 ms and 71 to 68 at 10 ms
  • 4 threads, 584 to 1278 at 1 ms and 74 to 148 at 10 ms
  • 8 threads, 588 to 2334 at 1 ms and 74 to 287 at 10 ms

With the sync skipped, the numbers did not move. The gain comes only from shared fsyncs, so expect it on slow durable storage such as network volumes. A follow up keeps recently_committed_transactions ordered. Commits that share one sync leave the active set in the order their threads wake up, but the cleanup sweep stops early and assumes commit order.

An IN list on an indexed column used to become separate equality lookups, each with its own scan state and key generation. The ART batch scan work gives the scan state three modes: equality, range, and batch equality. The commits split the predicate into range and equality, add a constructor for batch equality scans, and drop the unused FULL scan. The table scan puts all probe values into one DataChunk, encodes the keys in one pass, and collects row IDs into one set. The row ID limit still applies. Past it, the scan drops the collected IDs and falls back to a table scan.

Most of the change sits in art.cpp, touched by 22 commits. Internal search helpers now return an ARTSearchResult with COMPLETED and CAPACITY_EXCEEDED instead of a bool. Numbers from the PR, on 100 million row tables with unique indexes:

  • BIGINT IN with 50 keys, 0.828 ms to 0.749 ms
  • BIGINT IN with 2,048 keys, 19.585 ms to 16.229 ms
  • INNER JOIN against 50 BIGINT keys, 0.602 ms to 0.511 ms
  • BIGINT range id < 200, 0.447 ms to 0.466 ms, about 4 percent slower

Full domain ENUM IN simplification rewrites a filter that lists every value of an ENUM into constant_or_null. A new ConstantOrNullSimplification pass then folds it to true or false when the column is proven not null. A second commit extends this to ENUM typed constants such as 'sad'::mood, not only VARCHAR literals.

Before merge, a correctness fix closed a wrong result. The not null proof came from table statistics, which cover committed row groups only. Rows appended earlier in the same transaction live in local storage. The scan sees them. The statistics do not.

CREATE TABLE t(i INTEGER);
BEGIN;
INSERT INTO t VALUES (1), (2), (3);
INSERT INTO t VALUES (NULL);
SELECT count(*) FROM t WHERE i * 2 <> 5;  -- returned 4 on the branch, expected 3

The fix lives in NotNullExpressionAnalyzer, which now refuses the proof when the transaction holds uncommitted appends for that table. Deliminator and PushdownMarkJoin use the same analyzer and get the same guard. Folding to a constant stays disabled for plans with side effects.

The profiler already counted bytes and operations. Read and write time tracking adds io.total_read_time and io.total_write_time in seconds. FileHandle times each read and write when I/O tracking is on. That helps tell an I/O bound query apart from one that just moves many bytes.

PRAGMA enable_profiling = 'json';
SET tracked_metrics = ['io.total_bytes_read', 'io.total_read_time'];
SELECT sum(id) FROM read_parquet('events.parquet');

In storage, generic delta decoding now starts its unrolled loop at index 0 with a carry variable. The old loop started at 1, so every block also ran the scalar tail. Across five runs of the bitpacking_read_dfor micro benchmark, the average fell from 22.7 ms to 20.6 ms, about 9 percent.

In 2.0, a scalar function must call SetFallible() before it may raise an execution error. C API V1 has no such call, so odbc-scanner failed with an INTERNAL Error from odbc_bind_params. The C API V1 change marks every scalar function registered through V1 as fallible. Those extensions stay binary compatible with DuckDB 1.2.0 and later, including v1.4 LTS, with no port to V2.

C API V2 gained parse time statement metadata. duckdb_v2_sql_statement_get_type, duckdb_v2_sql_statement_get_text, and the parameter count and name calls need no catalog and no bind. A PRAGMA reports PRAGMA even when execution rewrites it.

MATCH_RECOGNIZE also merged. Follow ups accepted four SQL:2016 spellings found by JSqlParser fixtures and taught the new SQL exporter about row patterns. That exporter, bound_expression_sql_exporter.cpp, got 23 commits. Separately, the formatted bytes parser behind parse_formatted_bytes() and memory settings stopped casting an out of range double, which was undefined behavior.

  • MATCH_RECOGNIZE is now a reserved keyword. An unquoted identifier with that name will stop parsing after upgrade.
  • Group commit changes the failure mode. A lone commit whose fsync fails still rolls back. A failed shared sync invalidates the database, and that WAL refuses further syncs until reopened. debug_wal_fsync_sleep_ms and debug_force_wal_fsync_failure let you test this before rollout.
  • Public ART::Scan still returns a bool. The PR marks the switch to the new result type as a follow up that will need patches in extensions.