Jonathan Wrede

pgfederate: one PostgreSQL endpoint over many data sources

A gateway that speaks the PostgreSQL protocol and runs joins in an embedded DuckDB, so Metabase and Grafana can query PostgreSQL, MySQL, files and REST APIs through their normal PostgreSQL driver.

The problem

A BI tool usually talks to one database at a time. Requests to change this are among the most popular in Metabase's tracker: join across multiple databases has 2110 reactions and has been open since 2016, and a REST API driver has 889. Metabase's own guide lists a data warehouse, foreign data wrappers, views and federated query engines as workarounds. These work, but they need either a Trino cluster or a PostgreSQL server you may change, plus setup per source.

Projects that put a PostgreSQL protocol in front of DuckDB exist as well. PostHog discontinued duckgres in October 2026, and buenavista has not changed since December 2024. The case I wanted to cover is small: one process, started next to Metabase by a team that has no data platform.

How it works

pgfederate reads one YAML file. Each source becomes a schema: PostgreSQL, MySQL and SQLite are attached through DuckDB's extensions, files are read at query time, and JSON APIs are loaded on a schedule with pagination, retries and flattening of nested objects. A relations entry is shown to clients as a foreign key, so Metabase's query builder offers joins across sources without SQL.

Three parts took most of the work:

  • The server implements PostgreSQL protocol 3: SCRAM login, prepared statements, portals, cancel requests, and results in text and binary format.
  • Clients discover tables through pg_catalog and information_schema. DuckDB's own views describe DuckDB, so pgfederate keeps separate tables that show one database with one schema per source, and rebuilds them after each refresh.
  • Every statement is parsed with sqlglot before it runs. Only reads of exposed tables pass, DuckDB functions such as read_csv and query are refused, and DuckDB itself runs with external access disabled.

Example

The quick start joins a SQLite database and a CSV file through psql:

pgfederate=> select c.name, sum(i.amount) as billed
             from shop.customers c join billing.invoices i on i.customer_id = c.id
             group by c.name order by billed desc;
  name  | billed
--------+--------
 Acme   |   30.5
 Globex |   5.25
(2 rows)

This is the output of the end-to-end run in CI, where the real Metabase talks to the gateway through its PostgreSQL driver:

ok: metabase synced every source
ok: metabase sees the cross-source foreign key
ok: native query joins Postgres, MySQL and REST: [['Acme', 53.5, 2], ['Globex', 50.0, 1], ['Initech', 9.5, 0]]
ok: query builder join across MySQL and Postgres: {'Acme': 4, 'Globex': 2, 'Initech': 1}
ok: month bucketing: [['2026-03-01T00:00:00Z', 2], ['2026-04-01T00:00:00Z', 2]]
ok: filter with a bound parameter

How it is tested

The test suite runs psycopg in text and binary mode, asyncpg and psql against a real server, and replays the catalog queries of Metabase, pgjdbc and Grafana, which I took from their source code. About 35 tests try writes and escapes such as reading files or attached databases directly. PostgreSQL and MySQL sources run against real servers in CI, including a check that filters appear in the scan plan. A second CI job starts a compose stack and drives Metabase and Grafana through their HTTP APIs.

The first CI run found no gateway bug, only a mistake in my test query, which multiplied rows through a join. A local run with a wider set of queries then found a real one: substring(x from 'pattern') is a regex search, and Metabase binds the pattern as a parameter. About 80 query builder cases now run in CI.

Limits

pgfederate is read-only and runs in one process, so it does not replace Trino on large data. Filters are pushed down to PostgreSQL and MySQL, aggregations are not, and a cross-source join pulls the rows it needs into DuckDB. REST data is a snapshot from the last refresh. SQL that DuckDB cannot run fails even if it is valid PostgreSQL, and Metabase's host and domain expressions fail because they use regex lookaround. DECLARE and FETCH are refused, so named cursors in psycopg do not work. There are no per-user query quotas. Only the clients named above are tested.

The code is at github.com/Jwrede/pgfederate.