ADR-004: Optional DuckDB engine in front of Postgres and extra sources

Copy Markdown View Source

Status

Accepted

Date

2026-09-06

Context

Lens v1 queries PostgreSQL through Ecto/Postgrex. Analysts also want one engine that can join the host Repo with warehouse Postgres, SQLite, Parquet, and CSV without standing up another BI sidecar. DuckDB’s postgres scanner can ATTACH a running Postgres as read-only and keep additional attachments on the same connection.

Decision

  • Default engine remains PostgreSQL (direct, read-only transaction + statement_timeout).

  • Settings persist engine (postgresql | duckdb) and extra sources on the host Repo.

  • DuckDB is optional: host apps add {:duckdbex, "~> 0.4"}. Without it, Settings still render and switching explains the missing dep.
  • When DuckDB is on, one in-memory DuckDB attaches the host Repo as repo (TYPE postgres, READ_ONLY) plus user sources. Field policy still applies to result rows.
  • User SQL stays SELECT/WITH/EXPLAIN. Attach/load/install happen only inside the engine, not the notebook.

Alternatives Considered

Always-on DuckDB

  • Pros: one code path
  • Cons: NIF + extension download in every host; extra failure mode for the default Postgres notebook
  • Rejected as the default.

Separate DuckDB process / MotherDuck

  • Pros: isolation
  • Cons: another sidecar, the thing Lens exists to avoid
  • Rejected.

Consequences

  • Dummy app depends on duckdbex so /lens/settings can actually switch.
  • Catalog lists attached DuckDB tables when the engine is DuckDB.
  • Origin-column masking still uses the SELECT list; DuckDB has no Postgres RowDescription OIDs.