Verbund (German for "federation"; in German relational-algebra textbooks Verbund also means JOIN) is a DuckDB optimizer extension for federated query pushdown.
DuckDB's database connectors (PostgreSQL, MySQL, SQL Server, quack) push down per-table filters and projections. Verbund goes further: after DuckDB's cost-based optimizer has produced the final logical plan, it finds plan subtrees whose leaves all read from the same attached remote database, deparses each subtree into that source's SQL dialect, and replaces it with a single scan over the connector's query function.
ATTACH 'host=... dbname=sales' AS pg (TYPE postgres);
SELECT c.region, sum(o.amount) AS total
FROM pg.orders o
JOIN pg.customers c ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.region
ORDER BY total DESC
LIMIT 10;Without Verbund both tables are transferred and joined/aggregated locally. With Verbund the whole branch executes at the source — DuckDB receives ten rows:
-- what Verbund sends through postgres_query('pg', ...)
WITH t0 AS (SELECT customer_id, amount FROM public.orders WHERE status = 'paid'),
t1 AS (SELECT id, region FROM public.customers)
SELECT t1.region AS c0, sum(t0.amount) AS c1
FROM t0 JOIN t1 ON t0.customer_id = t1.id
GROUP BY t1.region ORDER BY c1 DESC LIMIT 10- Analyze — a post-optimization
OptimizerExtensionpass annotates every plan node with its data source (via the attached catalog type) and checks shippability against a per-dialect whitelist. - Deparse — maximal single-source subtrees containing a join, aggregate, ORDER + LIMIT, or DISTINCT are deparsed into the source dialect (CTE composition; leaf SELECTs are delegated to the connector where a rendering contract exists).
- Replace — the subtree is replaced by a
LogicalGetbound to the connector's query function (postgres_query/mysql_query/mssql_scan/quack_query), wrapped in a cast projection to preserve exact result types.
Design guarantees (see the constitution):
- Semantics first — a collapse must never change query results; anything with divergent remote semantics is compensated, flag-gated, or not shipped.
- The core optimizer stays in charge — Verbund runs after join ordering and collapses only what the optimizer arranged.
- Bare scans are never touched — connectors keep doing native, parallel per-table pushdown; Verbund only collapses subtrees where it reduces transferred data.
- Observable — the generated remote SQL is visible in
EXPLAIN, and every "did not ship" decision is diagnosable.
| Backend | Query function | Status |
|---|---|---|
| quack (remote DuckDB over HTTP) | quack_query(uri, sql) |
M0 — reference & CI backend |
| PostgreSQL | postgres_query(db, sql) |
M1 |
| MySQL 8+ | mysql_query(db, sql) |
M3 |
| SQL Server 2017+ | mssql_scan(ctx, sql) |
M3 |
Roadmap: M0 skeleton + analyzer + quack harness → M1 join pushdown (quack, postgres) → M2 aggregates, EXISTS/semi-joins, TopN → M3 MySQL + SQL Server dialects, function-mapping registry → M4 DISTINCT, UNION ALL, scalar subqueries → M5 window functions, complex LATERAL (CTE deparse), partial aggregation.
The full architecture design lives in docs/design.md.
Design phase / M0 (skeleton). See future-specs/1-skeleton.md.
Based on the DuckDB extension template.
git clone --recurse-submodules [email protected]:hugr-lab/verbund.git
cd verbund
make # builds ./build/release/duckdb with the extension loaded
make test # runs sqllogictests from test/sql/For faster incremental builds: GEN=ninja make (requires ninja and ccache).
Spec-driven: forward specifications live in future-specs/, active feature work flows
through spec-kit (/speckit-specify,
/speckit-plan, /speckit-tasks, /speckit-implement). Project principles are defined
in .specify/memory/constitution.md.
MIT — see LICENSE.