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

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

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

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

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.

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