| name | merge-ivm-reference |
| description | Reference for SQL MERGE statement and its application to IVM upsert patterns. Auto-loaded when discussing upserts, delta application, MERGE, INSERT OR REPLACE, or the CompileAggregateGroups/CompileSimpleAggregates/CompileProjectionsFilters functions. |
SQL MERGE for IVM Upsert Operations -- Reference
Comprehensive reference on the SQL MERGE statement and its application to
Incremental View Maintenance (IVM) upsert patterns, written in the context of
the OpenIVM DuckDB extension.
Note (post-fc6dab9 Z-set refactor): OpenIVM's canonical aggregate consolidation is now
SUM(_duckdb_ivm_multiplicity * col) — _duckdb_ivm_multiplicity is a signed INTEGER
Z-set weight (+1 = insert, −1 = delete). Many templates in this skill still show the
earlier SUM(CASE WHEN _duckdb_ivm_multiplicity = false THEN -col ELSE col END) form
— the two are mathematically equivalent and the BOOLEAN form is preserved here for
historical context, but new code should use the integer-weight form. Similarly,
WHERE _duckdb_ivm_multiplicity = true / = false is now > 0 / < 0.
1. The MERGE Statement (SQL Standard)
1.1 Overview
MERGE is a single DML statement that can perform INSERT, UPDATE, and DELETE
operations on a target table based on the results of a join with a
source table (or subquery). It was introduced in SQL:2003 and refined in
SQL:2008 and SQL:2016.
The key abstraction: the database joins source to target once, then for each
resulting row it decides which action to take based on declarative WHEN clauses.
1.2 Full Syntax (SQL Standard, as implemented across major databases)
[ WITH cte_name AS (...) [, ...] ]
MERGE INTO target_table [ [ AS ] target_alias ]
USING source_table_or_subquery [ [ AS ] source_alias ]
ON join_condition
{ WHEN MATCHED [ AND condition ] THEN
{ UPDATE SET col = expr [, ...]
| DELETE
| DO NOTHING }
} [ ... ]
{ WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN
{ INSERT [ ( col [, ...] ) ] VALUES ( expr [, ...] )
| INSERT DEFAULT VALUES
| DO NOTHING }
} [ ... ]
{ WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN
{ UPDATE SET col = expr [, ...]
| DELETE
| DO NOTHING }
} [ ... ]
[ RETURNING ... ]
1.3 Clause Semantics
| Clause | Meaning | Available Actions |
|---|
WHEN MATCHED | Row exists in both source and target (join succeeds) | UPDATE, DELETE, DO NOTHING |
WHEN NOT MATCHED [BY TARGET] | Row in source has no target match | INSERT, DO NOTHING |
WHEN NOT MATCHED BY SOURCE | Row in target has no source match | UPDATE, DELETE, DO NOTHING |
Evaluation order: WHEN clauses are evaluated top-to-bottom; the first
clause whose condition is satisfied fires. Only ONE action per row.
Critical constraint: Each target row must match at most one source row.
If multiple source rows match the same target row, the database raises an error
(this is mandated by the standard, though enforcement varies).
1.4 Conditional Actions
WHEN MATCHED AND target.status = 'active' THEN
UPDATE SET balance = target.balance + source.amount
WHEN MATCHED AND target.status = 'closed' THEN
DELETE
WHEN MATCHED THEN
DO NOTHING
Multiple WHEN MATCHED clauses with different AND conditions allow branching
logic within a single statement.
1.5 The Source/Target Model
- Target: The table being modified (the materialized view in IVM context)
- Source: A table, subquery, or CTE providing the change data (the delta
in IVM context)
- ON clause: Defines how source rows map to target rows (typically a
primary/group key equality)
1.6 How MERGE Differs from Other Upsert Mechanisms
| Feature | INSERT OR REPLACE | INSERT ON CONFLICT | MERGE |
|---|
| Requires PK/unique constraint | Yes | Yes | No (arbitrary ON condition) |
| Can UPDATE subset of columns | No (replaces entire row) | Yes | Yes |
| Can DELETE | No | No | Yes |
| Multiple conditional actions | No | No | Yes |
| Source can be a subquery | N/A | N/A | Yes |
| Single-statement insert+update+delete | No | No | Yes |
| NULL key handling | Breaks (NULLs don't conflict) | Breaks | Works (IS NOT DISTINCT FROM in ON) |
| Handle "not matched by source" | No | No | Yes |
2. DuckDB MERGE Support
2.1 Availability
MERGE INTO was introduced in DuckDB v1.4.0 (September 2025, "Andium" LTS
release). OpenIVM currently builds against DuckDB main, which tracks ahead of
v1.4.0, so MERGE is available.
2.2 Supported Syntax
MERGE INTO target_table
USING source_table_or_subquery AS source_alias
ON (match_condition)
MERGE INTO target USING source USING (id)
WHEN MATCHED [ AND condition ] THEN UPDATE [ SET col = expr, ... ]
WHEN MATCHED [ AND condition ] THEN DELETE
WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN INSERT [ (cols) VALUES (vals) ]
WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN INSERT BY NAME
WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN UPDATE SET ...
WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN DELETE
RETURNING merge_action, *;
2.3 DuckDB-Specific Features
INSERT BY NAME: Inserts using column name matching rather than
positional. DuckDB extension, not in the SQL standard.USING (column_name) shorthand: When the join column has the same name
in source and target, this shorthand avoids repeating ON source.id = target.id.RETURNING merge_action: Returns a string column indicating which
action was taken for each affected row.- CTE support in USING: CTEs defined with
WITH can be used as the source
in the USING clause.
2.4 Known Limitations (as of v1.4.x)
- MERGE cannot appear inside a CTE:
WITH result AS (MERGE INTO ...) SELECT ...
produces a parser error. This is a known missing feature (GitHub Discussion #19451).
- RETURNING cannot reference source columns:
RETURNING merge_action, src.*
fails with a binder error. Only target columns are accessible.
- No duplicate detection in source: If the source contains duplicate rows
matching the same target row, DuckDB does not raise an error (unlike the
standard), which can produce undefined behavior.
- Memory usage: Large MERGE operations can consume significant memory
(GitHub Issue #19958).
- CTEs are materialized by default (since v1.4.0): This is generally
beneficial for correctness but may affect performance of complex CTEs used
as MERGE sources.
2.5 DuckDB MERGE Examples
Basic upsert:
MERGE INTO people
USING (SELECT 3 AS id, 'Sarah' AS name, 95000.0 AS salary) AS upserts
ON (upserts.id = people.id)
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSERT;
Conditional delete:
WITH deletes(item_id, delete_threshold) AS (VALUES (10, 3000))
MERGE INTO stock
USING deletes
USING (item_id)
WHEN MATCHED AND balance < delete_threshold THEN DELETE
RETURNING merge_action, *;
Full sync (insert + update + delete):
MERGE INTO target
USING (SELECT 1 AS id) source
USING (id)
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED BY SOURCE THEN DELETE
RETURNING merge_action, *;
3. MERGE for IVM Upsert Operations
3.1 Current OpenIVM Upsert Patterns
OpenIVM currently generates SQL strings to apply deltas to materialized views.
The patterns differ by view type. Source: src/upsert/openivm_compile_upsert.cpp.
3.1.1 AGGREGATE_GROUP (Current Pattern)
Three separate statements executed sequentially:
Statement 1: UPDATE (add delta to existing groups)
WITH ivm_cte AS (
SELECT group_key,
SUM(CASE WHEN _duckdb_ivm_multiplicity = false THEN -agg ELSE agg END) AS agg
FROM delta_mv WHERE <ts_filter>
GROUP BY group_key
)
UPDATE mv SET agg = mv.agg + d.agg
FROM ivm_cte d
WHERE mv.group_key IS NOT DISTINCT FROM d.group_key;
Statement 2: INSERT (new groups not yet in MV)
WITH ivm_cte AS ( ... same CTE ... )
INSERT INTO mv SELECT d.group_key, d.agg
FROM ivm_cte d
WHERE NOT EXISTS (SELECT 1 FROM mv v WHERE v.group_key IS NOT DISTINCT FROM d.group_key);
Statement 3: DELETE (groups that zeroed out)
DELETE FROM mv WHERE agg = 0;
Problems with this pattern:
- Three separate passes over the data
- The CTE is duplicated in UPDATE and INSERT (though DuckDB materializes it)
- The DELETE is a full table scan checking all rows, not just affected groups
- Race conditions possible between UPDATE and INSERT (though run in single TX)
3.1.2 SIMPLE_AGGREGATE (Current Pattern)
Single UPDATE statement with correlated subqueries:
UPDATE mv SET
col = COALESCE(col, 0)
- COALESCE((SELECT col FROM delta_mv WHERE _duckdb_ivm_multiplicity = false AND <ts>), 0)
+ COALESCE((SELECT col FROM delta_mv WHERE _duckdb_ivm_multiplicity = true AND <ts>), 0);
This pattern works well since there is always exactly one row. MERGE would
add complexity without benefit here.
3.1.3 SIMPLE_PROJECTION (Current Pattern)
Two statements using EXCEPT ALL for bag-arithmetic consolidation:
WITH net_dels AS (
SELECT cols FROM delta_mv WHERE multiplicity = false
EXCEPT ALL
SELECT cols FROM delta_mv WHERE multiplicity = true
)
DELETE FROM mv WHERE EXISTS (SELECT 1 FROM net_dels WHERE <match>);
WITH net_ins AS (
SELECT cols FROM delta_mv WHERE multiplicity = true
EXCEPT ALL
SELECT cols FROM delta_mv WHERE multiplicity = false
)
INSERT INTO mv SELECT cols FROM net_ins;
3.2 MERGE-Based Replacement for AGGREGATE_GROUP
This is the highest-value target for MERGE. The three-statement pattern
(UPDATE + INSERT + DELETE) collapses into a single MERGE:
WITH delta AS (
SELECT group_key,
SUM(CASE WHEN _duckdb_ivm_multiplicity = false
THEN -agg_col ELSE agg_col END) AS agg_col
FROM delta_mv
WHERE _duckdb_ivm_timestamp >= '<last_update>'
GROUP BY group_key
)
MERGE INTO mv
USING delta AS d
ON (mv.group_key IS NOT DISTINCT FROM d.group_key)
WHEN MATCHED AND (mv.agg_col + d.agg_col) = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET agg_col = mv.agg_col + d.agg_col
WHEN NOT MATCHED BY TARGET THEN
INSERT (group_key, agg_col)
VALUES (d.group_key, d.agg_col);
Advantages:
- Single statement, single pass over both tables
- Consolidation (the CTE) is computed once
- DELETE of zeroed-out groups happens in the same pass (no full table scan)
- Correct ordering: DELETE check before UPDATE (clause evaluation is top-down)
- NULL group keys handled correctly via IS NOT DISTINCT FROM in ON clause
Multi-column example
For a view like:
CREATE MATERIALIZED VIEW mv AS
SELECT group_key, SUM(value) AS total, COUNT(*) AS cnt
FROM base_table GROUP BY group_key;
The MERGE becomes:
WITH delta AS (
SELECT group_key,
SUM(CASE WHEN _duckdb_ivm_multiplicity = false
THEN -total ELSE total END) AS total,
SUM(CASE WHEN _duckdb_ivm_multiplicity = false
THEN -cnt ELSE cnt END) AS cnt
FROM delta_mv
WHERE _duckdb_ivm_timestamp >= '<last_update>'
GROUP BY group_key
)
MERGE INTO mv
USING delta AS d
ON (mv.group_key IS NOT DISTINCT FROM d.group_key)
WHEN MATCHED AND (mv.total + d.total) = 0 AND (mv.cnt + d.cnt) = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET total = mv.total + d.total,
cnt = mv.cnt + d.cnt
WHEN NOT MATCHED BY TARGET THEN
INSERT (group_key, total, cnt)
VALUES (d.group_key, d.total, d.cnt);
Deletion condition note: For correctness, a group should be deleted when
cnt reaches 0 (COUNT(*) = 0 means no rows contribute to the group). The
total = 0 check is not strictly necessary but acts as a safety check.
In practice, cnt = 0 is the authoritative signal.
3.3 MERGE-Based Replacement for SIMPLE_PROJECTION
WITH net_delta AS (
SELECT cols, true AS _is_insert FROM (
SELECT cols FROM delta_mv WHERE _duckdb_ivm_multiplicity = true
EXCEPT ALL
SELECT cols FROM delta_mv WHERE _duckdb_ivm_multiplicity = false
)
UNION ALL
SELECT cols, false AS _is_insert FROM (
SELECT cols FROM delta_mv WHERE _duckdb_ivm_multiplicity = false
EXCEPT ALL
SELECT cols FROM delta_mv WHERE _duckdb_ivm_multiplicity = true
)
)
MERGE is NOT well-suited for SIMPLE_PROJECTION because:
- Projection views have no natural key to join on
- Rows are bags (duplicates allowed), and MERGE requires at-most-one match
- The EXCEPT ALL consolidation pattern is already clean and correct
- Forcing a MERGE would require synthesizing row IDs, adding complexity
Recommendation: Keep the current EXCEPT ALL pattern for SIMPLE_PROJECTION.
3.4 MERGE-Based Replacement for SIMPLE_AGGREGATE
Not recommended. The current single-UPDATE pattern is already optimal:
- There is always exactly one row in the MV
- MERGE overhead (join logic, clause evaluation) is unnecessary
- The correlated subquery pattern is clear and efficient
3.5 Summary: Where MERGE Helps
| View Type | Current Pattern | MERGE Benefit | Recommendation |
|---|
| AGGREGATE_GROUP | UPDATE + INSERT + DELETE (3 stmts) | High: collapses to 1 stmt, single pass, no full-scan DELETE | Adopt MERGE |
| SIMPLE_AGGREGATE | Single UPDATE | None: already optimal | Keep current |
| SIMPLE_PROJECTION | EXCEPT ALL + DELETE + INSERT | Low: no natural key for MERGE | Keep current |
4. MERGE vs Alternative Upsert Strategies
4.1 INSERT OR REPLACE (SQLite / DuckDB)
INSERT OR REPLACE INTO mv SELECT ...
| Aspect | Assessment |
|---|
| Mechanism | Deletes conflicting row, inserts new one |
| Requires | PRIMARY KEY or UNIQUE constraint |
| Pros | Simple syntax, single statement |
| Cons | Replaces entire row (can't do partial update like mv.agg + delta.agg); triggers DELETE+INSERT rather than UPDATE; NULL keys never conflict (silently inserts duplicates); loses row identity |
| IVM suitability | Poor. Cannot express "add delta to existing aggregate." Would need to pre-compute the final value in the source query, defeating the purpose of incremental maintenance. |
4.2 INSERT ON CONFLICT DO UPDATE (PostgreSQL UPSERT)
INSERT INTO mv (group_key, total, cnt)
VALUES (...)
ON CONFLICT (group_key) DO UPDATE SET
total = mv.total + EXCLUDED.total,
cnt = mv.cnt + EXCLUDED.cnt;
| Aspect | Assessment |
|---|
| Mechanism | Attempts INSERT; on unique violation, runs UPDATE |
| Requires | UNIQUE constraint or unique index on conflict columns |
| Pros | Single statement for upsert; EXCLUDED pseudo-table is convenient; handles concurrent inserts well |
| Cons | Cannot DELETE in the same statement; NULL keys don't conflict (unique indexes allow multiple NULLs); DuckDB supports ON CONFLICT but it requires a PK/unique index; no conditional branching |
| IVM suitability | Moderate. Handles UPDATE+INSERT but needs a separate DELETE for zeroed-out groups. NULL group keys are problematic. OpenIVM previously used this but switched away due to NULL key issues. |
4.3 MERGE (SQL Standard)
MERGE INTO mv USING delta ON (mv.key IS NOT DISTINCT FROM delta.key)
WHEN MATCHED AND ... THEN DELETE
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
| Aspect | Assessment |
|---|
| Mechanism | Joins source to target, executes declarative actions per match status |
| Requires | Nothing (arbitrary ON condition) |
| Pros | Single statement for INSERT+UPDATE+DELETE; arbitrary join conditions (IS NOT DISTINCT FROM for NULLs); multiple conditional clauses; no PK required; clean semantics |
| Cons | More complex SQL generation; source must not produce duplicate matches to same target row; some databases report MERGE as slightly slower than separate statements for simple cases |
| IVM suitability | Excellent for AGGREGATE_GROUP. Handles all three operations (update existing, insert new, delete zeroed) in one pass. IS NOT DISTINCT FROM solves the NULL key problem. |
4.4 Separate INSERT + UPDATE + DELETE
UPDATE mv SET agg = mv.agg + d.agg FROM delta d WHERE mv.key = d.key;
INSERT INTO mv SELECT ... FROM delta d WHERE NOT EXISTS (...);
DELETE FROM mv WHERE agg = 0;
| Aspect | Assessment |
|---|
| Mechanism | Three independent statements |
| Requires | Nothing special |
| Pros | Each statement is simple and independently tunable; easy to debug; clear semantics |
| Cons | Three passes over the data; CTE duplication (for UPDATE and INSERT); DELETE scans full MV table; potential for subtle ordering bugs |
| IVM suitability | This is the current OpenIVM approach for AGGREGATE_GROUP. It works correctly but is suboptimal. |
4.5 DELETE then INSERT
DELETE FROM mv WHERE key IN (SELECT key FROM delta);
INSERT INTO mv SELECT * FROM (full_recompute_for_affected_keys);
| Aspect | Assessment |
|---|
| Mechanism | Remove affected rows, recompute and reinsert |
| Requires | Access to the original view query for recomputation |
| Pros | Correct by construction; handles MIN/MAX (non-invertible aggregates) |
| Cons | Requires re-executing the original query (partially); loses and recreates rows; more I/O |
| IVM suitability | This is OpenIVM's current approach for MIN/MAX aggregates (has_minmax path). Appropriate for non-invertible aggregates but wasteful for SUM/COUNT. |
4.6 EXCEPT ALL Consolidation (OpenIVM Current for Projections)
WITH net_dels AS (dels EXCEPT ALL ins)
DELETE FROM mv WHERE EXISTS (SELECT 1 FROM net_dels WHERE ...);
WITH net_ins AS (ins EXCEPT ALL dels)
INSERT INTO mv SELECT * FROM net_ins;
| Aspect | Assessment |
|---|
| Mechanism | Bag-arithmetic cancellation of matching insert/delete pairs |
| Requires | Nothing special |
| Pros | Correct for bag semantics (handles duplicates properly); essential when inclusion-exclusion join delta rules produce redundant pairs |
| Cons | Two passes; DELETE uses EXISTS which may not use indexes efficiently |
| IVM suitability | Ideal for SIMPLE_PROJECTION where there is no key and rows form a bag. MERGE cannot easily replace this. |
5. Detailed MERGE Pattern for Grouped Aggregates
5.1 Setup
CREATE MATERIALIZED VIEW mv AS
SELECT group_key, SUM(value) AS total, COUNT(*) AS cnt
FROM base_table
GROUP BY group_key;
5.2 The Complete MERGE Statement
WITH ivm_cte AS (
SELECT
group_key,
SUM(CASE WHEN _duckdb_ivm_multiplicity = false THEN -total ELSE total END) AS total,
SUM(CASE WHEN _duckdb_ivm_multiplicity = false THEN -cnt ELSE cnt END) AS cnt
FROM delta_mv
WHERE _duckdb_ivm_timestamp >= '<last_update_ts>'
GROUP BY group_key
)
MERGE INTO mv
USING ivm_cte AS d
ON (mv.group_key IS NOT DISTINCT FROM d.group_key)
WHEN MATCHED AND (mv.cnt + d.cnt) = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
total = mv.total + d.total,
cnt = mv.cnt + d.cnt
WHEN NOT MATCHED BY TARGET THEN
INSERT (group_key, total, cnt)
VALUES (d.group_key, d.total, d.cnt);
5.3 Multi-Column Group Keys
When the group key is composite (e.g., region, product), the ON clause uses
AND for each key column:
ON (mv.region IS NOT DISTINCT FROM d.region
AND mv.product IS NOT DISTINCT FROM d.product)
5.4 Handling the Multiplicity Column
The _duckdb_ivm_multiplicity column is a signed INTEGER carrying the Z-set weight
(post-fc6dab9 refactor):
+1 = this delta row represents an INSERT (positive contribution)-1 = this delta row represents a DELETE (negative contribution)- multiplicities >1 are encoded by repeated rows in practice
The CTE consolidation is now direct weight arithmetic — no CASE WHEN round-trip:
SUM(_duckdb_ivm_multiplicity * agg)
This is the current OpenIVM template; older code paths used
SUM(CASE WHEN _duckdb_ivm_multiplicity = false THEN -agg ELSE agg END) and have been
replaced. The MERGE pattern is unchanged otherwise.
5.5 Deletion of Zeroed-Out Groups
Yes, MERGE can handle this in one statement. The key is clause ordering:
WHEN MATCHED AND (mv.cnt + d.cnt) = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET ...
Because WHEN clauses are evaluated top-to-bottom, the DELETE condition is
checked before the UPDATE. If cnt reaches zero, the row is deleted; otherwise
it is updated. This eliminates the need for a separate DELETE FROM mv WHERE cnt = 0
that scans the entire table.
5.6 Comparison with Current OpenIVM Approach
| Aspect | Current (UPDATE + INSERT + DELETE) | MERGE |
|---|
| Number of statements | 3 | 1 |
| CTE duplication | CTE repeated in UPDATE and INSERT | CTE used once |
| DELETE scope | Full table scan (WHERE agg = 0) | Only matched rows |
| NULL key handling | IS NOT DISTINCT FROM (manual) | IS NOT DISTINCT FROM (in ON) |
| Passes over data | 3 | 1 |
| Code complexity | ~80 lines of C++ string building | ~40 lines (estimated) |
| Correctness risk | Ordering between UPDATE/INSERT matters | Single atomic operation |
| DuckDB version required | Any | >= 1.4.0 |
5.7 List Mode (PAC Counters)
For the PAC integration where aggregates are LIST, the MERGE pattern
adapts the SET clause:
WHEN MATCHED THEN
UPDATE SET
total = list_transform(
list_zip(mv.total, d.total),
lambda x: x[1] + x[2])
The CTE consolidation similarly uses list operations:
list_reduce(
list(CASE WHEN _duckdb_ivm_multiplicity = false
THEN list_transform(total, lambda x: -x)
ELSE total END),
lambda a, b: list_transform(
list_zip(a, b), lambda x: x[1] + x[2])
) AS total
5.8 MIN/MAX Aggregates
MERGE does not help with MIN/MAX (non-invertible) aggregates. The current
DELETE+recompute strategy remains necessary. The has_minmax code path should
be kept as-is.
6. Performance Considerations
6.1 MERGE vs Separate Statements
Theoretical advantage of MERGE:
- Single pass: the database joins source (delta) to target (MV) once, then
applies all actions in that single pass
- Single execution plan: the optimizer sees the full picture and can choose
optimal join strategies
- Single transaction overhead: one statement = one implicit transaction
Empirical findings (from SQL Server benchmarks, not DuckDB-specific):
- SQL Server benchmarks show MERGE can be ~19-28% slower than separate
INSERT/UPDATE for simple cases, due to the overhead of the more complex
execution plan
- However, for IVM the workload is different: we need INSERT+UPDATE+DELETE
together, and the separate approach requires 3 passes. MERGE's single-pass
advantage likely dominates.
- DuckDB's columnar engine and vectorized execution may handle MERGE
differently than row-store databases like SQL Server
Expected IVM-specific benefits:
- The delta CTE is computed exactly once (vs. twice in the current approach)
- The DELETE check is scoped to matched rows (vs. full table scan)
- Single plan means the join between delta and MV is done once
- For small deltas (common in IVM), the MERGE overhead is negligible
6.2 Index Usage During MERGE
- DuckDB uses ART indexes for primary keys and unique constraints
- The ON clause in MERGE can leverage indexes on the target table's group key
- OpenIVM already creates an index (
<view_name>_ivm_index) on group keys
for AGGREGATE_GROUP views -- this index will benefit MERGE's ON clause
- No index changes are needed when adopting MERGE
6.3 Batch MERGE (Many Deltas at Once)
MERGE naturally handles batch deltas. The CTE consolidation aggregates all
pending delta rows per group, and the MERGE applies them all in one pass.
This is superior to row-by-row approaches.
For chained materialized views (where delta_mv rows accumulate from multiple
refresh rounds), the timestamp filter in the CTE ensures only relevant deltas
are processed:
WHERE _duckdb_ivm_timestamp >= '<last_update_ts>'
6.4 Memory Considerations
DuckDB's MERGE implementation may consume significant memory for large delta
sets (GitHub Issue #19958). For IVM, delta sets are typically small relative
to the full table, so this is unlikely to be a practical concern. If memory
becomes an issue, the adaptive cost model (ivm_adaptive_refresh setting) can fall
back to full recompute.
7. Other Database MERGE Implementations
7.1 PostgreSQL MERGE (PG 15+)
Introduced in PG 15 after years of development.
Syntax (PG 18 current):
[ WITH cte ... ]
MERGE INTO [ ONLY ] target [ * ] [ AS alias ]
USING source ON condition
WHEN MATCHED [ AND cond ] THEN { UPDATE SET ... | DELETE | DO NOTHING }
WHEN NOT MATCHED BY SOURCE [ AND cond ] THEN { UPDATE SET ... | DELETE | DO NOTHING }
WHEN NOT MATCHED [ BY TARGET ] [ AND cond ] THEN { INSERT ... | DO NOTHING }
[ RETURNING [ WITH ( { OLD | NEW } AS alias ) ] ... ]
PG-specific features:
WHEN NOT MATCHED BY SOURCE added in PG 17RETURNING clause added in PG 17, with OLD/NEW aliasesmerge_action() function in RETURNINGDO NOTHING action (standard extension)- Cannot target materialized views, foreign tables, or tables with rules
- CTE support in USING clause
- MERGE can appear inside CTEs (DuckDB cannot do this yet)
pg_ivm (PostgreSQL IVM extension):
pg_ivm uses AFTER triggers with transition tables. It does NOT use MERGE
internally; it uses separate INSERT/UPDATE/DELETE with transition table data.
This is partly because pg_ivm predates PG15 MERGE support.
7.2 Oracle MERGE
Oracle was the first major database to support MERGE (Oracle 9i, 2001).
Unique Oracle feature -- DELETE clause within WHEN MATCHED:
MERGE INTO target USING source ON (condition)
WHEN MATCHED THEN
UPDATE SET col = val
DELETE WHERE delete_condition;
The DELETE clause in Oracle is a sub-clause of UPDATE: it first updates the
row, then deletes it if the DELETE WHERE condition is met. This is different
from the standard where DELETE is a separate WHEN MATCHED action.
Important Oracle limitation: The DELETE clause only affects rows that were
updated by the MERGE. It cannot delete rows that were not matched and updated.
7.3 SQL Server MERGE
SQL Server has had MERGE since SQL Server 2008.
SQL Server-specific features:
WHEN NOT MATCHED BY SOURCE (SQL Server was the first to support this)OUTPUT clause (similar to RETURNING)$action in OUTPUT (returns 'INSERT', 'UPDATE', 'DELETE')
Known issues:
- Multiple documented bugs and race conditions in concurrent scenarios
- Microsoft's own guidance has at times recommended against MERGE for
high-concurrency workloads
- No guaranteed ordering of UPDATE vs INSERT operations
7.4 Snowflake MERGE
Snowflake uses MERGE extensively for its Dynamic Tables feature, which
is Snowflake's approach to incremental materialized views:
- Dynamic tables support three refresh modes:
AUTO, INCREMENTAL, FULL
- In incremental mode, Snowflake creates lightweight streams (change data
capture) on base tables, then uses MERGE to apply changes
- The system automatically decides between incremental and full refresh
based on query complexity and cost estimation
- Dynamic tables in incremental mode cannot be downstream from full-refresh
dynamic tables
Relevance to OpenIVM: Snowflake's Dynamic Tables validate the approach of
using MERGE for IVM delta application. Their architecture (streams for CDC +
MERGE for application) is analogous to OpenIVM's (delta tables for CDC +
upsert queries for application).
7.5 Cross-System Comparison for IVM
| Feature | DuckDB | PostgreSQL | Oracle | SQL Server | Snowflake |
|---|
| MERGE available | v1.4.0+ | PG15+ | 9i+ | 2008+ | Always |
| NOT MATCHED BY SOURCE | Yes | PG17+ | No | Yes | Yes |
| RETURNING/OUTPUT | Yes | PG17+ | No | Yes (OUTPUT) | No |
| CTE in USING | Yes | Yes | Yes | Yes | Yes |
| MERGE inside CTE | No | Yes | No | Yes (OUTPUT INTO) | No |
| DO NOTHING | Yes | Yes | No | No | No |
| IS NOT DISTINCT FROM in ON | Yes | Yes | No (use DECODE) | No (use ISNULL) | Yes (IS) |
| DELETE as separate WHEN action | Yes | Yes | Only sub-clause of UPDATE | Yes | Yes |
8. Implementation Roadmap for OpenIVM
8.1 Phase 1: AGGREGATE_GROUP Only
Replace CompileAggregateGroups() in src/upsert/openivm_compile_upsert.cpp
for the non-minmax, non-list-mode case.
Changes needed:
- Generate a single MERGE statement instead of UPDATE + INSERT + DELETE
- Keep the CTE consolidation logic (it maps directly to the MERGE USING clause)
- The ON clause uses IS NOT DISTINCT FROM (already used in current code)
- WHEN MATCHED AND ... = 0 THEN DELETE replaces the separate DELETE statement
- Keep the
has_minmax path unchanged (DELETE+recompute)
Estimated code reduction: The function shrinks from ~100 lines to ~50 lines.
8.2 Phase 2: List Mode
Adapt the MERGE SET clause for LIST element-wise operations.
This is a straightforward syntax change within the WHEN MATCHED THEN UPDATE SET.
8.3 Phase 3: Verify Companion Row Logic
The companion row insertion (for downstream AGGREGATE_GROUP views) runs
separately from the upsert. Verify that the MERGE-based upsert does not
interfere with companion row generation. The companion logic reads from
delta_mv and mv, and the MERGE modifies mv, so execution order matters.
The companion query should run BEFORE the MERGE (it currently runs before
the upsert in openivm_upsert.cpp).
8.4 DuckDB Version Gate
Since MERGE requires DuckDB >= 1.4.0, add a version check or feature flag.
If OpenIVM needs to support older DuckDB versions, keep the old code path
behind a conditional.
9. Concrete Code Sketch
Below is a sketch of how CompileAggregateGroups() would look with MERGE
(non-minmax, non-list-mode case):
string cte = "WITH ivm_cte AS (\n SELECT ";
for (auto &key : keys) cte += key + ", ";
for (auto &agg : aggregates) {
cte += "SUM(CASE WHEN " + MULTIPLICITY_COL + " = false THEN -" + agg
+ " ELSE " + agg + " END) AS " + agg + ", ";
}
cte.pop_back(); cte.pop_back();
cte += "\n FROM " + delta_name;
if (!ts_filter.empty()) cte += " WHERE " + ts_filter;
cte += "\n GROUP BY ";
for (auto &key : keys) cte += key + ", ";
cte.pop_back(); cte.pop_back();
cte += "\n)\n";
string merge = cte + "MERGE INTO " + view_name + "\n"
+ "USING ivm_cte AS d\n"
+ "ON (";
for (size_t i = 0; i < keys.size(); i++) {
if (i > 0) merge += " AND ";
merge += view_name + "." + keys[i] + " IS NOT DISTINCT FROM d." + keys[i];
}
merge += ")\n";
merge += "WHEN MATCHED AND ";
for (size_t i = 0; i < aggregates.size(); i++) {
if (i > 0) merge += " AND ";
merge += "(" + view_name + "." + aggregates[i] + " + d." + aggregates[i] + ") = 0";
}
merge += " THEN\n DELETE\n";
merge += "WHEN MATCHED THEN\n UPDATE SET ";
for (size_t i = 0; i < aggregates.size(); i++) {
if (i > 0) merge += ", ";
merge += aggregates[i] + " = " + view_name + "." + aggregates[i] + " + d." + aggregates[i];
}
merge += "\n";
merge += "WHEN NOT MATCHED BY TARGET THEN\n INSERT (";
for (auto &key : keys) merge += key + ", ";
for (auto &agg : aggregates) merge += agg + ", ";
merge.pop_back(); merge.pop_back();
merge += ")\n VALUES (";
for (auto &key : keys) merge += "d." + key + ", ";
for (auto &agg : aggregates) merge += "d." + agg + ", ";
merge.pop_back(); merge.pop_back();
merge += ");\n";
return merge;
Sources
