DuckDB's preview post for version 2.0 promised triggers with "BEFORE and AFTER triggers, FOR EACH ROW and FOR EACH STATEMENT". I downloaded the alpha build and tried all eight combinations against INSERT, UPDATE and DELETE. Five of them don't work yet, and the one that's missing most completely is BEFORE, which is the half of the feature most people reach for first, to validate or reject a row before it lands.
DuckDB has never had triggers. Version 2.0, codenamed Cyanoptera, is still an alpha, but the CLI binary is a public download, and CREATE TRIGGER already parses and runs for a meaningful subset of cases. I ran the stable 1.5 development build alongside it on the same machine, same data, three repeats per measurement.
$ ./duckdb -version
v2.0.0-alpha46057 (Cyanoptera) b15358c371
$ ./duckdb-1.5 -version
v1.5.7-dev2 (Development Version) 7def715258
What the trigger matrix actually supports
I created the same trigger, varying only BEFORE/AFTER, ROW/STATEMENT, and the event, against a throwaway table:
BEFORE INSERT FOR EACH ROW -> Not implemented: BEFORE FOR EACH ROW triggers are not yet supported
BEFORE UPDATE FOR EACH ROW -> Not implemented: BEFORE FOR EACH ROW triggers are not yet supported
BEFORE DELETE FOR EACH ROW -> Not implemented: BEFORE FOR EACH ROW triggers are not yet supported
AFTER INSERT FOR EACH ROW -> works
AFTER UPDATE FOR EACH ROW -> Not implemented: UPDATE FOR EACH ROW triggers are not yet supported
AFTER DELETE FOR EACH ROW -> works
BEFORE * FOR EACH STATEMENT -> works, all three events
AFTER * FOR EACH STATEMENT -> works, all three events
Every BEFORE row-level trigger fails with the identical error, regardless of event, which reads like the whole BEFORE/ROW combination is simply not wired up yet rather than being event-specific. AFTER row-level triggers fire correctly on INSERT and DELETE, with real NEW and OLD values, but UPDATE gets its own distinct "not yet supported" message. Statement-level triggers work everywhere I tried them, for both BEFORE and AFTER.
I also hit two restrictions that aren't in the preview post at all. You can't mix a FOR EACH STATEMENT trigger and a FOR EACH ROW trigger on the same table:
$ duckdb -c "CREATE TRIGGER trg_stmt AFTER INSERT ON t REFERENCING NEW TABLE AS n FOR EACH STATEMENT ...
CREATE TRIGGER trg_row AFTER INSERT ON t FOR EACH ROW ..."
Not implemented: Mixing FOR EACH STATEMENT and FOR EACH ROW triggers on the same table is not yet supported
And DROP TRIGGER trg_audit;, exactly as written in DuckDB's own blog post, is a syntax error on this build. It needs DROP TRIGGER trg_audit ON t;. Small, but it's the first command a reader would copy-paste and watch fail.
What a trigger costs on a bulk insert
I built three identical tables and inserted 500,000 rows into each with a single INSERT INTO ... SELECT ... FROM range(500000), fastest of three runs:
| Setup | Time | vs. no trigger |
|---|---|---|
| No trigger | 0.073s | — |
AFTER INSERT, FOR EACH ROW, logs NEW.id
|
0.217s (range 0.22-0.36s) | 3.0x |
| AFTER INSERT, FOR EACH STATEMENT, logs the whole batch via a transition table | 0.095s | 1.3x |
The row-level trigger does the same logical work as the statement-level one, one row inserted into a log table per row inserted into the source, but it does it 500,000 times through the trigger machinery instead of once as a set operation. The gap is real and it's the kind of thing an audit-log trigger on a hot table would hit immediately. The statement-level version, which instead references the whole batch at once with REFERENCING NEW TABLE AS n and runs a single INSERT INTO log SELECT id FROM n, is close enough to free that I'd default to it unless I specifically needed per-row logic.
The row-level timings also had the widest spread of anything I measured, 0.22s to 0.36s across three runs against a tight 0.073-0.080s for no trigger at all. Five hundred thousand individual trigger invocations seem to be more sensitive to whatever else the machine is doing than a single set-based statement is, which is its own small argument for preferring statement-level triggers when you can.
I checked that the transition table actually behaves like the docs describe, because the whole cost argument depends on it: does a 10,000-row bulk insert give the statement trigger one transition table with 10,000 rows, or does it somehow get invoked per row anyway?
INSERT INTO t SELECT i, i*2 FROM range(10000) t(i);
INSERT INTO t SELECT i, i*2 FROM range(3) t(i);
SELECT * FROM summary;
| n:BIGINT |
|---|
| 3 |
| 10000 |
One row per statement, with the right count each time. That's what makes the statement-level cost close to flat regardless of batch size.
The one safety check that does work
I tried to get a trigger to fire itself: an AFTER INSERT trigger on t that inserts into t.
CREATE TRIGGER trg AFTER INSERT ON t FOR EACH ROW INSERT INTO t VALUES (NEW.id + 1);
INSERT INTO t VALUES (1);
Not implemented: Recursive trigger chains are not yet supported (trigger cycle detected through trigger "trg" on table "t")
That's a real cycle check, not a stack overflow or a hang. Of everything "not yet supported" in this build, this is the one I'd rather see land unfinished than missing, since the alternative failure mode is a lot worse than an error message.
A claim from the same release that held up better than the trigger matrix
The preview post also claims a rewritten recursive CTE engine, citing a reachability benchmark that went from 4.90s to 0.12s between v1.5.4 and the v2.0 preview. I tried to reproduce something in that shape.
My first attempt was a graph of 100,000 nodes and 1,000,000 random edges, reachability from node 0 via a recursive UNION CTE. It ran in about 0.09s on the 1.5 build. No measurable difference at all, which given the headline claim should have been the first thing I doubted about my own setup, not DuckDB's.
A uniformly random graph with that many edges per node is nearly a complete graph after a handful of hops, so the recursive query converges in a handful of iterations regardless of which engine is running it. It wasn't exercising the part of the engine that changed. I rebuilt the test as a 40,000-node chain, 0 -> 1 -> 2 -> ... -> 39999, which forces the recursion through 40,000 sequential steps and is close to a worst case for any engine that re-evaluates the growing result set on every iteration:
| Engine | Fastest of 3 runs |
|---|---|
| v1.5.7-dev2 | 18.57s (18.57-19.51s) |
| v2.0.0-alpha46057 | 0.41s (0.41-0.58s) |
About 45x, on a harder topology and at a quarter the node count of their own benchmark. I can't confirm their exact 1M-edge number, but the direction and the rough order of magnitude both check out independently.
What I got wrong on the way
The reachability test above is the mistake, left in rather than quietly fixed. I had a result that said "no difference" and very nearly wrote that down as a finding about the recursive CTE rewrite before checking whether my test actually stressed anything. A graph dense enough to reach everywhere in three hops will look the same on any reasonable query engine; the interesting case is a graph that forces real iteration, and I only got there by being suspicious of a number that was too convenient.
What it costs on disk
v2.0's default storage format moved from version 68 to version 69, and it's a one-way street on this build:
$ duckdb-1.5 v2-database.db -c "select 1"
IO Error: Trying to read a database file with version number 69, but we can only read versions between 64 and 68.
COPY ... TO '...parquet' (FORMAT PARQUET) and reading it back with read_parquet() on the older binary works fine as a workaround, which is how I got the same dataset into both versions for the benchmarks above. Worth knowing before anyone attaches a 2.0 alpha database to data they also need 1.5 to read.
Run it yourself
No Docker needed, both are static CLI binaries:
curl -sL -o v2.tar.gz https://artifacts.duckdb.org/staged/v2.0-cyanoptera/duckdb-cli-linux-amd64.tar.gz
tar xzf v2.tar.gz # -> ./duckdb
curl -sL -o v15.zip https://artifacts.duckdb.org/v1.5-variegata/duckdb-binaries-linux-amd64.zip
unzip -o v15.zip duckdb_cli-linux-amd64.zip && unzip -o duckdb_cli-linux-amd64.zip # -> ./duckdb
The trigger cost comparison:
./duckdb /tmp/none.db -c "
CREATE TABLE t(id INTEGER, val INTEGER);
INSERT INTO t SELECT i, i*2 FROM range(500000) t(i);"
./duckdb /tmp/row.db -c "
CREATE TABLE t(id INTEGER, val INTEGER);
CREATE TABLE log(id INTEGER);
CREATE TRIGGER trg AFTER INSERT ON t FOR EACH ROW INSERT INTO log VALUES (NEW.id);
INSERT INTO t SELECT i, i*2 FROM range(500000) t(i);"
./duckdb /tmp/stmt.db -c "
CREATE TABLE t(id INTEGER, val INTEGER);
CREATE TABLE log(id INTEGER);
CREATE TRIGGER trg AFTER INSERT ON t REFERENCING NEW TABLE AS n FOR EACH STATEMENT INSERT INTO log SELECT id FROM n;
INSERT INTO t SELECT i, i*2 FROM range(500000) t(i);"
Time each with time on the command, or wrap it in a shell loop; wrapping the whole duckdb ... -c "..." call is enough since the binary exits when the script finishes.
The chain reachability benchmark:
./duckdb /tmp/chain.db -c "CREATE TABLE edges AS SELECT i AS src, i+1 AS dst FROM range(40000) t(i);"
./duckdb /tmp/chain.db -c "
WITH RECURSIVE reachable(node) AS (
SELECT 0
UNION
SELECT e.dst FROM edges e, reachable r WHERE e.src = r.node
)
SELECT count(*) FROM reachable;"
What to do with this
This is an alpha, so "half the trigger matrix throws Not implemented" isn't a defect report, it's a status update, and most of these gaps will probably close before 2.0 ships. What's worth taking from it now: if you're evaluating triggers for an audit-log or denormalization pattern, default to FOR EACH STATEMENT with transition tables rather than FOR EACH ROW, both because it's roughly 2x cheaper on a bulk load today and because BEFORE row-level triggers, which is where most validation logic naturally goes, aren't available at all yet. And if you're touching the recursive CTE engine for anything that currently needs a manual iterative workaround to stay fast, that part looks like it's already doing what the release notes say, which is reason enough to pull the alpha down and point your own worst-case query at it rather than guessing from a blog post.
Top comments (0)