OpenIVM

A DuckDB extension for incremental view maintenance (IVM). Create materialized views pipelines with standard SQL, then refresh them incrementally — without recomputing the entire query.

Based on the OpenIVM paper (SIGMOD 2024).

Quick start

OpenIVM is currently under testing. We hope to release it as a community extension by the end of 2026. Issues and feature requests are welcome.

OpenIVM currently requires a source build with DuckDB v1.5.4. Follow the build and setup guide first; the built CLI already loads OpenIVM.

LOAD 'openivm'; -- No-op in the source-built CLI (statically linked); needed only in other DuckDB clients.

-- Create a base table and a materialized view
CREATE TABLE sales (region VARCHAR, product VARCHAR, amount INT);
INSERT INTO sales VALUES ('US', 'Widget', 100), ('EU', 'Gadget', 200);

CREATE OR REPLACE MATERIALIZED VIEW regional_totals REFRESH EVERY '5 minutes' AS
    SELECT region, SUM(amount) AS total, COUNT(*) AS cnt FROM sales GROUP BY region;

-- Insert new data
INSERT INTO sales VALUES ('US', 'Bolt', 50), ('JP', 'Gear', 300);

-- Refresh manually at any time
PRAGMA refresh('regional_totals');

SELECT * FROM regional_totals ORDER BY region;
-- EU  | 200 | 1
-- JP  | 300 | 1
-- US  | 150 | 2

Run these statements at the prompt or from a SQL file, not as one -c argument or one multi-statement client call: DuckDB expands PRAGMA refresh before the preceding CREATE MATERIALIZED VIEW in the same batch has run, so the view is not found yet.

Views with REFRESH EVERY are maintained automatically by a background daemon. See automatic refresh.

Base table schema changes (ADD, DROP, RENAME COLUMN) are propagated by OpenIVM. Delta tables are synced, referenced renames update MV metadata, and referenced drops are blocked with an error. See schema evolution.

SQL syntax

CREATE [OR REPLACE] MATERIALIZED VIEW [catalog.][schema.]name [REFRESH EVERY '<interval>'] AS <query>;
ALTER MATERIALIZED VIEW [catalog.][schema.]name SET REFRESH EVERY '<interval>';
ALTER MATERIALIZED VIEW [catalog.][schema.]name SET REFRESH MANUAL;
DROP VIEW [catalog.][schema.]name;  -- removes the MV, its internal tables and metadata

CREATE MATERIALIZED VIEW IF NOT EXISTS and DROP MATERIALIZED VIEW are not supported. See parser and limitations.

Data pipelines and DuckLake

Materialized views can be stacked into pipelines, including over DuckLake tables. DuckLake's snapshot-based time travel enables delta computation through native change tracking, avoiding storage duplication. See DuckLake IVM integration.

INSTALL ducklake;
LOAD ducklake;
ATTACH ':memory:' AS dl (TYPE ducklake);

CREATE TABLE dl.orders (id INT, product VARCHAR, region VARCHAR, amount INT);
INSERT INTO dl.orders VALUES (1, 'Widget', 'US', 500), (2, 'Gadget', 'EU', 200), (3, 'Widget', 'EU', 800);

-- First MV: aggregate by product
CREATE MATERIALIZED VIEW dl.product_totals REFRESH EVERY '5 minutes' AS
    SELECT product, SUM(amount) AS total, COUNT(*) AS cnt FROM dl.orders GROUP BY product;

-- Second MV: built on top of the first
CREATE MATERIALIZED VIEW dl.top_products REFRESH EVERY '10 minutes' AS
    SELECT product, total FROM dl.product_totals WHERE total > 1000;

-- Cascade modes (controls automatic refresh propagation):
SET openivm_cascade_refresh = 'downstream';  -- default: refreshing product_totals also refreshes top_products
SET openivm_cascade_refresh = 'upstream';    -- refreshing top_products first refreshes product_totals
SET openivm_cascade_refresh = 'both';        -- refresh in both directions
SET openivm_cascade_refresh = 'off';         -- no cascade, each view refreshes independently

Note: without cascading refresh, views refreshing independently may see stale upstream data — results are consistent but not fresh until the next ordered refresh. See pipelines.

Supported operators

MVs can be created using any SQL construct. Unsupported operators automatically fall back to full refresh.

Operator	Strategy	Documentation
SELECT ... FROM, WHERE, expressions	Incremental	Projection & filter
GROUP BY + SUM, COUNT, AVG	Incremental	Grouped aggregates
STDDEV, VARIANCE (all variants)	Incremental	Grouped aggregates
MIN, MAX	Incremental (insert-only) / group-recompute	Grouped aggregates
HAVING	Incremental	Grouped aggregates
Ungrouped aggregates	Incremental	Ungrouped aggregates
INNER JOIN, CROSS JOIN, arbitrary join predicates	Incremental	Inner join
LEFT JOIN, RIGHT JOIN	Incremental	Left join
FULL OUTER JOIN	Incremental (MERGE + recompute)	Full outer join
SEMI JOIN, ANTI JOIN, EXISTS, NOT EXISTS	Aux-state incremental for supported projection shapes	Semi & anti join
UNION ALL	Incremental	Union all
DISTINCT	Incremental	Distinct
ORDER BY ... LIMIT k (top-k)	Incremental; the limit is applied when the view is read	Top-k
Window functions (ROW_NUMBER, RANK, etc.)	Partition-level recompute	Window functions
LIST aggregates	Incremental	List aggregates
WITH (CTEs), decorrelated subqueries, scalar correlated subqueries	Incremental when the lowered plan uses supported operators; scalar SINGLE delim shapes use affected-key recompute	CTEs & subqueries

Settings

Setting	Type	Default	Description	Documentation
openivm_cascade_refresh	VARCHAR	downstream	Cascade mode: off, upstream, downstream, both	Pipelines
openivm_refresh_mode	VARCHAR	incremental	full forces full recompute; incremental (or auto) uses the strategy chosen at creation	Refresh strategies
openivm_adaptive_refresh	BOOLEAN	false	Experimental: pick incremental or full refresh per run with the learned cost model	Refresh strategies
openivm_cost_decay	DOUBLE	0.9	Decay factor (0.0–1.0) for the learned cost model; higher adapts more slowly	Cost model
openivm_adaptive_backoff	BOOLEAN	true	Auto-increase refresh interval when refresh takes longer than interval	Automatic refresh
openivm_disable_daemon	BOOLEAN	false	Do not start the background refresh daemon at extension load	Automatic refresh
openivm_profile_refresh	BOOLEAN	false	Record per-step refresh timings in openivm_refresh_profile	Automatic refresh
openivm_profile_retention_days	BIGINT	31	Delete profile rows older than this many days when profiling writes new rows	Automatic refresh
openivm_metadata_schema	VARCHAR	empty	Schema for OpenIVM control metadata in each native catalog. Empty keeps existing metadata where it is (main for new metadata); a schema name moves existing metadata there on the next CREATE MATERIALIZED VIEW or refresh	Metadata placement
openivm_internal_schema	VARCHAR	empty	Schema for internal maintenance tables (backing, publication, auxiliary-state and delta tables) within each data catalog. Empty keeps them beside the MV and its sources; applies to objects created while set	Metadata placement
openivm_files_path	VARCHAR	—	Directory for compiled SQL reference files (off when unset)	Build: inspect SQL
openivm_archive_compiled_sql	BOOLEAN	false	Store executed CREATE and refresh programs in metadata tables; independent of file export	Compiled SQL archive
openivm_compiled_sql_retention	BIGINT	10	Latest distinct archived programs per view and operation; at least 1	Compiled SQL archive
openivm_metadata_catalog	VARCHAR	""	Database-wide attached catalog for OpenIVM metadata (native DuckDB, or PostgreSQL via TYPE postgres); empty keeps metadata next to each native view catalog	Metadata placement
openivm_metadata_lease_seconds	BIGINT	600	Refresh lease for clients sharing a PostgreSQL metadata catalog	Metadata placement
openivm_explain_initial_load	BOOLEAN	false	Print the CREATE MATERIALIZED VIEW initial-load SQL and its EXPLAIN plans	Build: inspect SQL
openivm_explain_initial_load_only	BOOLEAN	false	With openivm_explain_initial_load, print the diagnostic without creating the MV	—
openivm_input_dialect	VARCHAR	duckdb	Dialect of incoming CREATE MATERIALIZED VIEW bodies: duckdb or spark (e.g. VERSION AS OF)	Parser

Optimization and compilation toggles

Setting	Type	Default	Description	Documentation
openivm_skip_empty_deltas	BOOLEAN	true	Skip refresh work or join terms when deltas are empty	Empty delta skip
openivm_compact_deltas	BOOLEAN	true	Compact raw delta rows into net Z-set deltas before refresh	Delta consolidation
openivm_skip_aggregate_delete	BOOLEAN	true	Skip the zero-row DELETE for grouped aggregates when deltas are insert-only	Append-only
openivm_skip_projection_delete	BOOLEAN	true	Skip DELETE and consolidation for projections when deltas are insert-only	Append-only
openivm_minmax_incremental	BOOLEAN	true	Use GREATEST/LEAST for MIN/MAX when deltas are insert-only	Append-only
openivm_fk_pruning	BOOLEAN	true	Prune inclusion-exclusion join terms using FK constraints	FK-aware pruning
openivm_ducklake_nterm	BOOLEAN	true	Use N-term telescoping for DuckLake joins instead of 2^N−1 inclusion-exclusion	DuckLake
openivm_having_merge	BOOLEAN	true	Use MERGE for HAVING views (store all groups, filter in the view) instead of group-recompute	Limitations
openivm_left_join_merge	BOOLEAN	true	Use incremental MERGE for supported LEFT/RIGHT JOIN aggregates instead of group-recompute	Left join
openivm_full_outer_merge	BOOLEAN	true	Use incremental MERGE for FULL OUTER JOIN aggregates instead of group-recompute	Full outer join
openivm_distinct_aux_state	BOOLEAN	false	Use aux-state maintenance for supported inner-DISTINCT-under-aggregate shapes	Distinct
openivm_stateful_auxstate	BOOLEAN	false	Use persisted aux state for stateful aggregates such as COUNT(DISTINCT) instead of group-recompute	—
openivm_running_window_incremental	BOOLEAN	false	Extend cumulative running-window aggregates for append-only suffix batches	—
openivm_enable_data_dependent_optimizers	BOOLEAN	true	Optimize the executed refresh plan using current delta statistics	—
openivm_regular_nterm	BOOLEAN	true	Use N-term telescoping for eligible regular-table inner joins compiled for external engines	Inner join
openivm_regular_nterm_left	BOOLEAN	true	Extend compile-only N-term telescoping to LEFT JOIN projection views	—
openivm_emit_spark_hints	BOOLEAN	false	Emit Spark optimizer hints in target_dialect=spark compiled refresh SQL	Spark

Pragmas and functions

Call	Description	Documentation
PRAGMA refresh('view_name')	Refresh a materialized view; accepts schema.view, catalog.view, or catalog.schema.view	Refresh strategies
PRAGMA refresh_options(catalog, schema, view_name)	Refresh with explicit catalog/schema	—
PRAGMA refresh_pipeline('view_name', ...)	Refresh one or more MVs and their cascade dependencies as one ordered run	Pipelines
PRAGMA refresh_status('view_name')	Show refresh interval, last/next refresh, daemon status, and refresh strategy	Automatic refresh
PRAGMA refresh_cost('view_name')	Show incremental refresh vs full recompute cost estimate (static + calibrated)	Cost model
PRAGMA refresh_history('view_name')	Show refresh execution history (for learned cost model)	Refresh strategies
PRAGMA refresh_start_daemon	(Re)start the background refresh daemon; not allowed inside a transaction	—
PRAGMA openivm_files('view_name')	Show paths and status of the compiled SQL reference files	Build: inspect SQL
PRAGMA openivm_declare_rely_fk(child_table, child_columns, parent_table, parent_columns)	Declare a trusted (RELY) foreign key used by FK-aware join pruning	—
openivm_compile_with_facts(view_name, facts_json)	Table function: compile a refresh program as rows without executing it	Build: compiled SQL

Schemas, metadata, and compiled SQL

• Can schemas contain same-named views or source tables? Yes. Use PRAGMA refresh('dl.observation.product_summary') to identify a view explicitly. A short name works when it identifies one MV; ambiguous names produce an error.
• Where are internal tables stored? Control metadata (openivm_views, openivm_delta_tables, history, profile, hooks and dependency tables) lives in the native catalog that owns the view — the native frontend database for DuckLake views — in the schema set by openivm_metadata_schema (empty keeps the existing schema; new metadata starts in main). MV backing, publication, auxiliary-state and MV delta tables live in the MV's schema; native source deltas live with their source. SET openivm_internal_schema = 'openivm_internal' puts newly created internal tables in that schema of each data catalog instead (source deltas get schema-qualified names there). Changing the metadata schema never moves them. A small main.openivm_source_deltas routing table stays in each native source catalog so change capture works with the metadata catalog detached.
• Why are DuckLake delta tables empty? DuckLake source changes come from snapshots. Internal MV delta tables are still created for lifecycle handling and can be empty. Leave them in place.
• Can metadata stay outside DuckLake? It already does. Start the built CLI with ./build/release/duckdb openivm_frontend.duckdb, then attach your lake. Reopen that frontend and reattach the same lake under the same name in later sessions. Use openivm_metadata_catalog and openivm_metadata_schema for custom metadata placement, including PostgreSQL.
• Can the frontend run without a local database file? Yes, with persistent metadata in an attached catalog. Reattach the lake and metadata catalog and select the metadata location on every startup. See the startup sequence.
• Can control metadata use its own schema? Yes: SET openivm_metadata_schema = 'openivm'. Existing metadata is found where it lives and moved automatically by the next CREATE MATERIALIZED VIEW or refresh; do not move OpenIVM tables by hand. See metadata placement.
• Where is the generated SQL? File export is opt-in. Create an output directory and set openivm_files_path before CREATE or refresh. Then call PRAGMA openivm_files('dl.observation.product_summary') to inspect exact paths. This pragma reports files; it does not generate them.
• Can compiled SQL be stored in a table? Set openivm_archive_compiled_sql = true before CREATE or refresh to record executed programs in openivm_compiled_programs and openivm_compiled_statements. Recording defaults to off; when enabled, openivm_compiled_sql_retention keeps the latest 10 distinct programs per view and operation by default (set it to 1 for latest-only history). File export stays independent. Compiled SQL contains state-specific cutoffs; it is an inspection record, not a reusable refresh procedure.

See schema and metadata examples, exporting and inspecting SQL files, the compiled SQL archive, and saving compiled SQL as rows.

Documentation

• DuckLake integration — IVM over DuckLake tables with native change tracking
• Spark support — Spark input dialect (VERSION AS OF), compile-only Spark output, Spark hints, and add_months
• Operators — How each SQL operator is incrementalized
• Refresh — Refresh strategies, automatic refresh, pipelines, and refresh hooks
• Optimizations — Delta consolidation, FK pruning, empty-delta skip, append-only, indexing
• Internals — Delta tables, parser, concurrency, cost model, schema evolution
• Concurrency and operations — What concurrent readers, writers and refreshes can rely on; running the daemon, upgrading, troubleshooting
• Limitations — Unsupported operators, known restrictions
• Build — Building, testing, benchmarks