Hands-on test finds DuckDB 2.0 alpha triggers mostly unimplemented and 3x slower per row

Most of the trigger matrix isn't wired up yet

DuckDB has never shipped triggers, and version 2.0 — codenamed Cyanoptera, currently at v2.0.0-alpha46057 — is set to change that. The release preview promises BEFORE and AFTER triggers in both FOR EACH ROW and FOR EACH STATEMENT form. A hands-on test published on dev.to by alexgeorgiev17, who downloaded the public alpha CLI and tried every combination against INSERT, UPDATE and DELETE, reports that five of the eight don't work yet.

The results, per the author:

• Every BEFORE ... FOR EACH ROW trigger fails with the identical "not implemented" error regardless of event, which reads like the whole BEFORE/ROW combination simply isn't built yet rather than being event-specific.
• AFTER ... FOR EACH ROW works for INSERT and DELETE, with real NEW and OLD values, but UPDATE returns its own "not yet supported" message.
• Statement-level triggers work in both timings and for all three events.

The BEFORE row-level gap is the most conspicuous one, because validating or rejecting a row before it lands is the use most people reach for first.

Restrictions the preview post doesn't mention

Two further limitations surfaced during testing that aren't in the preview post at all. Mixing a FOR EACH STATEMENT trigger and a FOR EACH ROW trigger on the same table throws a "not yet supported" error. And DROP TRIGGER trg_audit; — the exact form written in DuckDB's own blog post — is a syntax error on this build; it needs DROP TRIGGER trg_audit ON t; instead. As the author points out, that's the first command a reader would copy from the docs and watch fail.

Row-level triggers cost three times more

To measure cost, the author built three identical tables and inserted 500,000 rows into each with a single INSERT ... SELECT, taking the fastest of three runs on the same machine, with the stable v1.5.7-dev2 build alongside. The baseline insert with no trigger took 0.073 seconds. An AFTER INSERT FOR EACH ROW trigger logging NEW.id took 0.217 seconds — 3.0x — with runs spanning 0.22 to 0.36 seconds. An AFTER INSERT FOR EACH STATEMENT trigger logging the whole batch through a transition table took 0.095 seconds, or 1.3x.

The two triggers do the same logical work — one log row per inserted row — but the row-level version goes through the trigger machinery 500,000 times instead of once as a set operation. Its timings also showed the widest spread of anything measured, against a tight 0.073–0.080s for no trigger at all, which the author reads as half a million individual invocations being more sensitive to machine noise than a single set-based statement.

The statement-level case depends on transition tables behaving as documented, so the author verified it: a 10,000-row bulk insert gives the statement trigger one transition table with 10,000 rows, and a 3-row insert one with 3. One invocation per statement is what keeps statement-level cost close to flat regardless of batch size, and the practical takeaway is to default to statement-level triggers unless per-row logic is genuinely needed — the kind of gap an audit-log trigger on a hot table would hit immediately.

Recursion is caught, not crashed

Trying to make a trigger fire itself — an AFTER INSERT row trigger on a table that inserts back into that table — produces a proper cycle-detection error rather than a hang or a stack overflow. Of everything marked "not yet supported" in this build, the author argues this is the one worth shipping unfinished, since the alternative failure mode is far worse than an error message.

The recursive CTE claim holds up

The release also advertises a rewritten recursive CTE engine, citing a reachability benchmark that dropped from 4.90 seconds to 0.12 seconds between v1.5.4 and the v2.0 preview. The author's first reproduction attempt — 100,000 nodes with 1,000,000 random edges — showed no measurable difference, running in about 0.09 seconds even on the 1.5 build. That turned out to be a flawed test: a dense random graph converges in a handful of hops on any engine. A rebuilt worst case, a 40,000-node chain forcing 40,000 sequential recursion steps, ran in 18.57 seconds on v1.5.7-dev2 versus 0.41 seconds on the alpha — roughly 45x faster. The exact headline number couldn't be confirmed, but the direction and order of magnitude check out independently.

Separately, the alpha's default storage format moves from version 68 to 69, which the author describes as a one-way street on this build.

Why it matters

Triggers are a frequent porting blocker for teams moving audit-heavy workloads off Postgres-style engines, and DuckDB's first cut of them is explicitly incomplete: no BEFORE row triggers, no row-level UPDATE triggers, no mixing granularities on one table, and documented DROP TRIGGER syntax that fails. Anyone planning to adopt them should treat statement-level triggers with transition tables as the default — they're near-free relative to row-level ones — and hold off on per-row validation logic until BEFORE support lands. The gaps at least fail loudly with explicit errors rather than misbehaving quietly, and the recursive CTE rewrite looks like a genuinely large win for deep-recursion queries. But this is an alpha: plan against the tested matrix, not the preview post's promise.