OpenIVM: a SQL-to-SQL Compiler for Incremental Computations

TL;DR

Given a base table $T$ and a view $V=Q(T)$, the problem is to compute $\Delta V$ from $\Delta T$ so that $V$ is kept up to date without full recomputation. OpenIVM addresses this with a SQL-to-SQL compiler built around the DBSP framework, using differentiation $\mathscr{D}$ and integration $\mathcal{I}$ to transform queries into incremental forms and generate delta tables that update the materialized view $V$. Key contributions include a portable architecture that uses an intermediate DuckAST to translate to dialect-specific SQL, a DuckDB extension that realizes IVM inside the engine, and demonstrations of cross-system HTAP with SQL-based delta propagation between PostgreSQL and DuckDB, plus baseline benchmarks. The work enables seamless cross-system IVM and provides a foundation for exploring richer materialization strategies and optimizations in existing DBMS ecosystems.

Abstract

This demonstration presents a new Open Source SQL-to-SQL compiler for Incremental View Maintenance (IVM). While previous systems, such as DBToaster, implemented computational functionality for IVM in a separate system, the core principle of OpenIVM is to make use of existing SQL query processing engines and perform all IVM computations via SQL. This approach enables the integration of IVM in these systems without code duplication. Also, it eases its use in cross-system IVM, i.e. to orchestrate an HTAP system in which one (OLTP) DBMS provides insertions/updates/deletes (deltas), which are propagated using SQL into another (OLAP) DBMS, hosting materialized views. Our system compiles view definitions into SQL to eventually propagate deltas into the table that materializes the view, following the principles of DBSP. Under the hood, OpenIVM uses the DuckDB library to compile (parse, transform, optimize) the materialized view maintenance logic. We demonstrate OpenIVM in action (i) as the core of a DuckDB extension module that adds IVM functionality to it and (ii) powering cross-system IVM for HTAP, with PostgreSQL handling updates on base tables and DuckDB hosting materialized views on these.

Figures (3)

• Figure 1: Our IVM engine implementation, consisting of a SQL-to-SQL compiler wrapped around DuckDB. Users can specify the expected optimization strategies through flags.
• Figure 2: Our IVM optimizer extension, interacting with the DuckDB core engine.
• Figure 3: Our cross-system IVM demo, showcasing an HTAP workload with DuckDB and PostgreSQL.