DBSP for DuckDB

August 1, 2026 · View on GitHub

Real-time incrementally maintained materialized views for DuckDB, based on Database Stream Processing (DBSP) theory.

Overview

Traditional materialized views recompute entirely when underlying data changes. DBSP-powered views update incrementally in O(delta) time - only processing the changes, not the entire dataset.

Traditional:  INSERT 1 row → Recompute 1M rows → O(n)
DBSP:         INSERT 1 row → Update affected aggregates → O(delta)

Key Features

Incremental Updates: Views update in O(delta) time, not O(n)
SQL Syntax: Define views using familiar SQL
Cascading Views: Views can reference other views
Automatic CDC: Change Data Capture with sync detection
Delta read-back: dbsp_changes('view') returns the last sync's output delta with signed weights
Attached catalogs: views can source tables in ATTACHed databases (... FROM m.orders); tables are keyed by canonical catalog.schema.table
Persistence: Save/restore views across sessions
Auto-persist: views survive a clean connection reopen with no explicit save/load calls (dbsp_autopersist, on by default)
Zero Dependencies: Pure C++ header-only core library
Bounded Memory: optional disk-backed state (dbsp_spill)
Parallel Updates: optional multi-core sync, propagation, and join probing (dbsp_parallel)

Quick Start

Basic Example

-- Load the extension
LOAD 'dbsp';

-- Create a table
CREATE TABLE orders (id INT, customer VARCHAR, amount DECIMAL);

-- Create an incrementally maintained view. That's it — the source
-- table is tracked automatically and the view keeps itself current.
CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer, SUM(amount) as total
FROM orders
GROUP BY customer;

-- Insert data — the view updates on commit, no sync call needed
INSERT INTO orders VALUES (1, 'Alice', 100), (2, 'Bob', 200), (3, 'Alice', 150);

-- Query the view (instant — no recomputation)
SELECT * FROM dbsp_query('customer_totals');
-- Returns: Alice: 250, Bob: 200

INSERT INTO orders VALUES (4, 'Alice', 50);
SELECT * FROM dbsp_query('customer_totals');
-- Returns: Alice: 300, Bob: 200

Bulk loading? Turn the automatic refresh off while you load, then sync once:

SELECT * FROM dbsp_auto_sync(false);
-- ... millions of inserts ...
SELECT * FROM dbsp_sync();          -- one scan-and-diff
SELECT * FROM dbsp_auto_sync(true);

Alternative Syntax (table functions):

-- Create view using table function API
SELECT * FROM dbsp_create_view('customer_totals',
    'SELECT customer, SUM(amount) FROM orders GROUP BY customer');
    
-- Query using table function
SELECT * FROM dbsp_query('customer_totals');

Advanced Examples

Filtering aggregates with HAVING:

CREATE MATERIALIZED VIEW high_value_customers AS
SELECT customer, SUM(amount) as total, COUNT(*) as order_count
FROM orders
GROUP BY customer
HAVING SUM(amount) > 200;

Recursive queries for graph traversal:

CREATE TABLE edges (src INT, dst INT);
SELECT * FROM dbsp_track('edges');

CREATE MATERIALIZED VIEW reachable AS
WITH RECURSIVE reach AS (
    SELECT src, dst FROM edges
    UNION
    SELECT e.src, r.dst FROM edges e JOIN reach r ON e.dst = r.src
)
SELECT * FROM reach;

See examples/ for more comprehensive demos.

Installation

Building from Source

./build.sh

This will:

Download DuckDB source (if not present)
Apply the engine patches from patches/ (the patch files are the fork — stock DuckDB lacks the transaction-callback symbols the extension needs; idempotent, fails loudly if the tree has drifted from the patch)
Build the DBSP extension (uses ccache and Ninja automatically when installed; parallelism capped at -j 8)
Output dbsp.duckdb_extension

Any change to DuckDB engine sources must land as an updated patch file in patches/ in the same commit — a fresh clone builds only from the patches.

Loading the Extension

LOAD '/path/to/dbsp.duckdb_extension';

Testing

Running Tests

# Build and run the full suite (unit + integration)
cd test/build_test
cmake .. && make -j8
ctest

# Benchmarks (built but not part of ctest)
make bench_planner_eval soak_differential
./bench_planner_eval
SOAK_ROUNDS=60 ./soak_differential "[soak]"

Test Coverage

Unit tests: Core DBSP library, native views, CDC manager
Integration tests: All extension functions, CDC, cascading views, persistence
Benchmarks: O(delta) performance validation

See docs/TESTING.md for details.

Documentation

API Reference - Complete function reference
Theory - DBSP mathematical foundations
Examples - Usage examples
Architecture - Internal design

SQL Functions

Table Tracking

Function	Description
`dbsp_track(table)`	Pre-track a table (optional — view creation auto-tracks its sources)
`dbsp_sync(table)`	Manually sync one table (needed only with auto-sync off)
`dbsp_sync()`	Manually sync all tracked tables
`dbsp_tables()`	List all tracked tables

View Management

Function	Description
`dbsp_create_view(name, sql)`	Create view with SQL syntax
`dbsp_replace_view(name, sql)`	Redefine a view, rebuilding only it and its dependents
`dbsp_query(view)`	Query a materialized view
`dbsp_views()`	List all views with stats
`dbsp_drop(view)`	Drop a view
`dbsp_drop_cascade(view)`	Drop view and dependents
`dbsp_deps(view)`	Show view dependencies

Persistence

Function	Description
`dbsp_save()`	Save view definitions to the `_dbsp_views` table (in the database file)
`dbsp_load()`	Load view definitions from `_dbsp_views`
`dbsp_save('views.json')`	Save view definitions to a JSON file
`dbsp_load('views.json', 'json')`	Load view definitions from a JSON file

Persistence covers definitions, not materialized state — loading rebuilds views from current table data. Table-form persistence lives in the database file, so file copies/backups carry the views. JSON file paths must be relative to the working directory — absolute paths are rejected.

dbsp_save()/dbsp_load() are no longer something a caller has to remember: with auto-persist on (the default), a clean connection close saves automatically and the next session's first DBSP call loads automatically. See dbsp_autopersist below.

Manual CDC

Function	Description
`dbsp_notify_insert(table, ...)`	Notify of row insertion
`dbsp_notify_delete(table, ...)`	Notify of row deletion

Automatic CDC & Diagnostics

Function	Description
`dbsp_auto_sync(bool)`	Toggle automatic sync on commit (default ON; turn off for bulk loads)
`dbsp_autopersist(bool)`	Toggle auto-save-on-close + auto-load-on-reopen (default ON; turn off for bulk loads)
`dbsp_autopersist_interval(n)`	Piggyback a circuit-state checkpoint every `n` commits (default 0 = off)
`dbsp_lazy_restore(bool)`	Toggle lazy per-view checkpoint restore: each view decodes on first need instead of eagerly at load (default ON)
`dbsp_parallel(bool)`	Toggle parallel multi-table sync + same-level view propagation
`dbsp_spill(bool)`	Toggle disk-backed state: baselines, join indexes, top-K windows, big aggregate groups
`dbsp_use_planner([bool])`	No-op since Phase C (planner is the only frontend); kept for script compatibility

Error Handling

duckDBSP uses a structured error code system (DBSP-Exxx) with helpful error messages:

Clear descriptions of what went wrong
SQL highlighting showing exactly where the error occurred
Workarounds for unsupported features
Documentation links for detailed guidance

See Error Handling Guide for details.

Supported SQL Features

✅ Currently Supported

DDL Syntax:

CREATE MATERIALIZED VIEW name AS SELECT ...
CREATE OR REPLACE MATERIALIZED VIEW name AS SELECT ... - redefine a view, rebuilding only it and its transitive dependents
DROP MATERIALIZED VIEW name [CASCADE]
REFRESH MATERIALIZED VIEW name (no-op with auto-refresh)

Query Operations:

SELECT * FROM table / SELECT columns FROM table
SELECT ... WHERE condition with complex predicates
SELECT ... GROUP BY column
SELECT ... HAVING condition - filter aggregated results
SELECT DISTINCT ... - incremental deduplication
SELECT ... FROM t1 JOIN t2 ON ... - bilinear incremental joins
- Multi-column equality joins
- Complex JOIN predicates (non-equi conditions)
WITH RECURSIVE ... - transitive closures and recursive queries

Aggregate Functions:

SUM, COUNT, AVG, MIN, MAX - all with O(log n) incremental updates
DISTINCT and FILTER (WHERE ...) modifiers, incrementally maintained
ROLLUP / CUBE / GROUPING SETS with GROUPING() - one incremental aggregate branch per grouping set
STRING_AGG / ARRAY_AGG with in-aggregate ORDER BY (sorted per-group state, re-rendered on change)
MEDIAN, QUANTILE_CONT, QUANTILE_DISC, MODE, MAD (holistic, over the sorted per-group multiset; mode ties break by smallest value)
Window functions over expressions (auto-projected below the window)

Circuit Optimization:

Automatic filter pushdown through JOINs
Projection pruning to minimize data movement
Operator fusion for reduced overhead
Shared join arrangements: N views joining the same table share one index (one update per delta instead of N)

Advanced Features:

Cascading views (views on views with dependency tracking)
NULL-aware operations (SQL semantics for GROUP BY, JOINs, aggregates)
Incremental recursive query evaluation

Planner Frontend (the only frontend since Phase C — the bespoke SQL parser was deleted):

View SQL planned by DuckDB's own binder/planner; scan/filter/projection, GROUP BY aggregation (incl. exact SUM over DECIMAL), inner and outer joins (LEFT/RIGHT/FULL; equi + residual predicates), cross joins, IN/NOT IN and scalar subqueries (correlated included), EXISTS, DISTINCT, DISTINCT ON, set operations, window functions, non-recursive CTEs, WITH RECURSIVE (multi-table recursive steps), and ORDER BY/LIMIT/OFFSET translate directly to circuit nodes with full DuckDB expression coverage (function calls, mixed AND/OR predicates, multi-aggregate GROUP BY, expression group/join keys, HAVING, global aggregates). A circuit-IR optimizer combines filters, pushes them below joins, and fuses filter+project into one node. Unsupported plans (unordered string_agg, USING KEY recursion, ...) fail with a DBSP-E110 error naming the operator.

📋 Not yet supported

WITH RECURSIVE ... USING KEY
Non-constant (expression) LIMIT — percentage LIMIT works
Window ORDER BY / PARTITION BY over expressions (project first)
string_agg / array_agg without ORDER BY inside the aggregate (ordered forms are supported)

How It Works

DBSP (Database Stream Processing) treats database operations as streams of changes:

Z-Sets: Data represented as element → weight mappings
- Weight +1 = insertion
- Weight -1 = deletion
- Weight 0 = no change
Incremental Operators: Each SQL operator has an incremental version
- Filter^Δ: Only process changed rows matching predicate
- Join^Δ: Δa × b + a × Δb (bilinear formula)
- Aggregate^Δ: Update running totals with deltas

Change Propagation: Changes flow through the view graph

orders (Δ) → filter_view (Δ) → aggregate_view (Δ)

For the mathematical foundations, see Theory.

Performance Benchmarks

Metric	Result
Incremental filter/projection	~970,000 rows/s
Incremental aggregation	~2,200,000 rows/s
Incremental join (100k delta vs 100k index)	~460,000 rows/s
Delta propagation, 3-level view chain	~13 µs/row
Captured-delta commit (explicit INSERT txn)	~0.3 ms
Captured UPDATE/DELETE commit (1M-row table, single row)	~1.5 ms
Captured autocommit INSERT (1M-row table)	~1.0 ms
Full scan-and-diff sync (50k rows, 3 views)	~41 ms

Apple M-series, release build (test/build_test), 100k-row deltas unless noted; reproduce with bench_planner_eval / bench_write_capture. Explicit INSERT-only transactions and whitelisted UPDATE/DELETE statements (including autocommit) commit O(Δ) via captured deltas; other writes pay the scan-and-diff sync (docs/DESIGN_WRITE_CAPTURE.md).

Project Structure

duckDBSP/ ├── include/ # Header-only implementation │ ├── dbsp_zset.hpp # Z-set data structure │ ├── dbsp_stream.hpp # Stream operators │ ├── dbsp_circuit.hpp # Dataflow graph │ ├── dbsp_plan_translator.hpp # Planner frontend (circuit translation) │ ├── dbsp_cdc.hpp # CDC manager + shared arrangements │ ├── dbsp_duckdb_types.hpp # Native DuckDB type integration │ └── dbsp_context_state.hpp # Transaction hooks (auto-CDC) ├── src/ # Extension source │ ├── dbsp_extension.cpp # Extension entry point │ └── dbsp_recovery.cpp # Replay-based crash recovery ├── build.sh # Build script ├── test/ # Unit/integration tests, benchmarks ├── docs/ # Documentation └── examples/ # Usage examples

Contributing

Contributions are welcome! Please see CONTRIBUTING.md for guidelines.

Development Setup

# Clone the repository
git clone https://github.com/yourusername/duckDBSP.git
cd duckDBSP

# Build the core library tests
mkdir build && cd build
cmake ..
make

# Run tests
./dbsp_tests

The DBSP theory was developed by Mihai Budiu, Tej Chajed, Frank McSherry, Leonid Ryzhyk, and Val Tannen
DuckDB team for the excellent embeddable database