Write paths

The durability contract behind writePath "wal" and "lake". Group commit, WAL ordering, the flusher, and what survives a crash.

OpenOptions.writePath (or LAKEFRONT_WRITE_PATH for the serving process) chooses the write path a given engine commits through, uniformly across every surface that writes: the embedded engine, the HTTP client, and the router all mean the same contract by the same setting.

"wal" vs "lake"

"wal" (default)

A mutation appends to lakefront_wal, a plain table in the catalog Postgres, inside a group-committed transaction, and acks the instant that Postgres transaction commits.

At that point the write is durable: it is a real, fsynced Postgres commit, survives this node dying outright, and is readable from any other node. But it is durable only in the WAL. Replicas sync from the WAL directly, so a reader can observe the write immediately; the lake converges separately, whenever the flusher next runs.

That shift moves further than durability. Watermarks and minWatermark tokens become WAL sequence numbers rather than lake snapshot ids, so a replica file built under one scheme cannot be adopted under the other; it is stamped with which scheme built it and rebuilt if the stamp doesn’t match.

Inside engine.transaction, scalar no longer sees the transaction’s own writes the way get does, and upsert ordering follows WAL order rather than version stamps. Both are consequences of committing to an append log first and a queryable table second.

"lake"

A mutation commits straight into DuckLake, synchronously, as one DuckLake transaction. There is no WAL to drain and nothing to converge later: what’s committed is immediately the lake’s own state.

That simplicity is the whole case for it: fewer moving parts, no flush lag, no distinction between “acked” and “landed.”

The cost is that a DuckLake commit is a full catalog transaction, and the catalog serializes commits globally across every writer touching it. See the performance numbers for the measured throughput gap between the two paths.

Choose "lake" when a write path’s simplicity matters more than its ceiling, or when a workload doesn’t need the WAL’s contracts: an infrequent bulk load, an operational script, a single-writer job where there’s no concurrency to amortize a commit across in the first place.

Group commit

Concurrent writers to the same board share a commit rather than each paying for their own. groupCommit (default on) queues arriving writes while a commit is in flight and lands everything that queued together as one transaction when it lands. This mirrors PostgreSQL’s own group commit.

No timer, and retry one at a time

• No timer. An idle system’s first writer commits immediately rather than waiting to see if company arrives; a busy system’s queue widens automatically to whatever the in-flight commit’s duration allows.
• Retry one at a time on failure. A batch is one transaction, so one bad operation in it would otherwise roll back every innocent write queued alongside it. On failure the group is retried operation by operation instead, so the failure lands on the operation that actually caused it and the rest still commit.

Transactions never batch together

Batching only ever applies to standalone writes, never to engine.transaction. Merging independent transactions into one physical commit would make each one’s uncommitted writes visible to the others’ reads inside that shared transaction: a real isolation violation. A standalone write performs no reads of its own, so folding several together is safe in a way that folding transactions together is not.

Write ordering

Outbox gap

A WAL sequence number is allocated the moment a row is inserted but only becomes visible to another transaction at commit. Without care, that gap is exploitable: a reader could observe seq 100 committed while seq 99’s transaction is still open, record its watermark as 100, and skip 99 forever. It will never come back around to look for it.

Per-board advisory lock

The fix is a per-board Postgres advisory lock (pg_advisory_xact_lock, keyed by a hash of the board id), taken by every append before it inserts and by every read before it reads.

Two same-board appends taking the lock in turn means they commit in seq order, so no committed row can ever be followed by an earlier uncommitted one.

And a reader taking the same lock means that once it holds it, every existing row for that board is guaranteed committed and visible, and anything appended after gets a seq strictly above whatever last_value was read, which is what makes “watermark := last_value read under the lock” a safe thing to hand back as a sync boundary.

Correctness under concurrent writers

Single-writer tests do not exercise this ordering failure. Concurrent writers require the advisory lock to preserve commit order.

Lock keys are sorted before acquisition specifically so that a multi-board append can never deadlock against another multi-board append touching the same boards in a different order; a hash collision between two unrelated boards merely over-serializes them, which costs throughput but never correctness.

Round-trip cost

The append and the read against the WAL are both single plpgsql functions executed server-side, not a client-driven BEGIN / lock / statement / COMMIT sequence. Each of those steps is its own network round trip, and most of a write’s latency lives there rather than in Postgres actually doing the work. Collapsing it into one function call means the whole operation, locks included, is one round trip and its own implicit transaction, which is exactly the scope the advisory locks need to hold.

Flusher

Nothing on the write path waits for the flusher; it runs on its own clock (flushIntervalMs, default 30s, or purely on-demand via flush() when the interval is 0) and moves batches of WAL records into DuckLake in one DuckLake commit per batch. Because durability already lives in the WAL, flush lateness only bounds how long a cold rebuild’s replay tail gets, never whether a write is safe.

Idempotence

Idempotence is the property the flusher is built around: the WAL sequence a flush has drained up to is recorded inside the same DuckLake transaction as the flushed rows, so a crash between the lake commit and trimming the WAL leaves a watermark that already covers the batch. The retried flush re-reads that watermark and reduces to a no-op rather than double-applying.

Two nodes racing to flush the same board collide at the DuckLake commit itself; the loser’s retry re-reads the winner’s watermark and finds nothing left to do. The competing flush does extra work but does not reapply the batch.

Tombstones

Tombstones are how a drop or a truncate reaches every node through the same channel as ordinary mutations, rather than through some separate side channel that could itself go missing. A drop or truncate is appended to the WAL as its own record kind; a tombstone voids every record before it for that board, so both the flusher and a syncing replica discard everything up to the last tombstone and only replay what comes after it.

That is also what lets a board be dropped and recreated under the same id and have every node converge on the recreation rather than getting confused about which incarnation’s records they’re looking at.

Crash behavior

A write is durable once its Postgres transaction commits, even if the process dies before returning an acknowledgment. Recovery uses the WAL rather than the node’s replica file.

A crash before commit leaves no partial write. If the caller did not receive an acknowledgment, it can retry safely with an idempotency key.

replicaWalWrites: DuckDB’s own WAL on replica files

Separately from any of the above, replicaWalWrites (default false) controls whether a replica file keeps DuckDB’s own internal write-ahead log turned on. Off by default, because a replica file is a disposable cache: durability is the lake’s and the WAL’s job. Enabling it adds disk writes to preserve a cache that can be rebuilt.

The real cost of leaving it off: a replica commit is not durable across a crash until DuckDB’s next checkpoint, so a crash can discard a commit that already appeared to succeed locally.

That is safe in this architecture specifically because the replica re-syncs and converges on its next use. It is still a genuine durability difference for that file, which is why the option exists rather than being hardcoded off.