pgvfs: DuckLake on PostgreSQL

pgvfs:// is a DuckDB filesystem that stores DuckLake's data files as rows in PostgreSQL. Put the DuckLake catalog in the same database and one PostgreSQL is the whole lake: one secret, one backup, one set of roles. Any number of DuckDB processes read and write it directly, with no object store, gateway or HTTP. Any process whose PostgreSQL role can write may write. Works on PostgreSQL 14 or later, including Amazon Aurora.

Documentation: https://pgvfs.adonm.dev

Install

INSTALL pgvfs FROM community;
LOAD pgvfs;

Signed builds for DuckDB 1.5.6 on Linux (x86-64 and ARM64, glibc and musl), macOS (Intel and Apple Silicon), and Windows (x86-64). No -unsigned flag needed. Install covers Python, updating an older installation, and 2.0 dev builds.

Quick start

Use an existing PostgreSQL database and a writer role that can create schemas and catalog tables, or start the local demo. The credentials below are for that demo; replace them for your own server.

INSTALL ducklake;
INSTALL postgres;
LOAD ducklake;
LOAD postgres;

-- one secret serves the DuckLake catalog and the pgvfs data
CREATE SECRET (
    TYPE postgres, HOST 'localhost', PORT 54329,
    DATABASE 'lake', USER 'lake', PASSWORD 'lake'
);
ATTACH 'ducklake:postgres:' AS lake (DATA_PATH 'pgvfs://lake/');

-- once per lake, before the first insert: the fast layout
CALL lake.set_option('parquet_compression', 'zstd');
CALL lake.set_option('parquet_version', 2);
CALL lake.set_option('parquet_row_group_size', 8192);
CALL lake.set_option('target_file_size', '64MB');

CREATE TABLE lake.events (site_id INTEGER, day DATE, value DOUBLE);
ALTER TABLE lake.events SET SORTED BY (site_id, day);
INSERT INTO lake.events VALUES
    (42, DATE '2026-09-01', 1.0),
    (42, DATE '2026-09-01', 2.0),
    (7,  DATE '2026-09-01', 3.0);

SELECT count(*) AS events FROM lake.events
WHERE site_id = 42 AND day = DATE '2026-09-01'; -- 2

Run initialization once. In later sessions, load the extensions, recreate the secret, and attach the existing lake. Readers use ATTACH 'ducklake:postgres:' AS lake (READ_ONLY) with a role that can read the catalog and pgvfs tables. For remote PostgreSQL, add SSLMODE 'require' to the secret; see credentials.

A DuckDB database in PostgreSQL

pgdb:// stores a native DuckDB database (indexes, keys, constraints, WAL) in the same PostgreSQL: one writer, any number of readers that follow its commits.

ATTACH 'pgdb://lake/sales.duckdb' AS sales;               -- the one writer
ATTACH 'pgdb://lake/sales.duckdb' AS sales (READ_ONLY);   -- readers, anywhere

See Databases.

Documentation

• Install: builds, versions, Python.
• Loading data: lay out a lake for fast reads, load at scale, and keep it fast.
• Databases: native DuckDB databases on pgdb://, one writer and many readers.
• Full-text search: tantivy indexes built and searched from SQL, on pgvfs or any DuckDB filesystem.
• Vector search: nearest neighbours over a clustered lake, in plain SQL.
• How it works: storage, writers and readers, credentials, configuration.
• Performance: benchmarks and sizing.
• Development: build, test, benchmark, release.

Development

mise install          # toolchain: rust, just, uv, python, mdbook
just check            # fmt, clippy, unit tests
just e2e              # build the extension, then the storage contract and end-to-end tests
just bench city       # benchmark: Overture Houston, about a minute

Apache-2.0.