Downloads 724this week

GitHub stars 4

Extension descriptor (YAML)

DuckLake and DuckDB databases on PostgreSQL - pgvfs:// stores data files as PostgreSQL rows for many writers and readers; pgdb:// stores a DuckDB database for one writer and many readers

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL pgvfs FROM community;
LOAD pgvfs;

Example

-- One secret serves both the DuckLake catalog and the pgvfs data files.
CREATE SECRET (TYPE postgres, HOST 'db', DATABASE 'lake', USER 'lake', PASSWORD '...');
ATTACH 'ducklake:postgres:' AS lake (DATA_PATH 'pgvfs://lake/');

-- Once per lake, before the first insert: small row groups and files for fast
-- lookups, zstd to keep PostgreSQL storage small (LZ4 reads faster, stores more).
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
    SELECT range % 1000, DATE '2026-01-01' + (range % 365)::INTEGER, random() FROM range(1000000);

-- Readers, in other processes: SELECT-only roles, read replicas welcome.
-- ATTACH 'ducklake:postgres:' AS lake (READ_ONLY);
SELECT count(*) FROM lake.events WHERE site_id = 42 AND day = DATE '2026-02-12';

-- Counters for one process: opens, reads, bytes, time in pgvfs.
SELECT pgvfs_stats()::JSON;

About pgvfs

pgvfs is a DuckDB filesystem, pgvfs://, that stores DuckLake's Parquet data files as rows in PostgreSQL. With the DuckLake catalog in the same database, one PostgreSQL is the whole lake: one secret, one backup, one set of roles, and no object store or gateway.

• Many writers and readers. Readers need only SELECT, take no locks and work on streaming read replicas. Any process whose PostgreSQL role can write may write, with no lock between writers; roles decide who writes.
• Fast lookups. Files are immutable, so DuckDB caches them without revalidating, and reads are primary-key range queries straight into DuckDB's buffers.
• Plain PostgreSQL 14+, including Amazon Aurora. No PostgreSQL extensions or superuser. The first write installs pgvfs's schema.
• Native DuckDB databases (experimental). ATTACH 'pgdb://lake/sales.duckdb' stores a whole DuckDB database, with indexes, keys and constraints, in the same PostgreSQL: one writer, any number of readers (standbys too) that follow new commits by themselves.
• Full-text search. Tantivy indexes built and searched from SQL (tantivy_index, tantivy_search, tantivy_count, tantivy_aggregate, tantivy_merge, and fts-style macros): OpenSearch-style queries, sorting, highlights, exclusion of deleted rows, splits per DuckLake commit, on pgvfs or any DuckDB filesystem.

It suits lakes that fit comfortably in one PostgreSQL, with many concurrent readers doing lookups and dashboard-style queries. For scans over large lakes in object storage, a plain DuckLake on S3 is the better fit.

Layout decides most of the read speed: set the lake options above before the first insert, declare a sort order that matches your filters, and load big tables in key-range batches. Credentials resolve per path: a postgres secret named pgvfs_<volume>, else the one named by SET pgvfs_secret, else $PGVFS_URL, else the default postgres secret. Remote servers need SSLMODE 'require'.

Documentation: install, loading data, databases, full-text search, vector search, how it works and performance.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings