Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

pgvfs

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. No object store, no S3 credentials, no gateway.
  • Many writers and readers. Readers need only SELECT, take no locks and can run on read replicas. Any process whose role can write may write, so loaders run in parallel; PostgreSQL roles decide who may.
  • Fast reads. Files are immutable, so DuckDB caches them without revalidating. Reads are primary-key range queries straight into DuckDB’s buffers: a block read takes about 0.1–0.2 ms, and with one reader a lookup on a well-laid-out lake has a median under 10 ms (see Performance).
  • Search. Tantivy full-text indexes, built and searched from SQL and stored in the lake (or on any DuckDB filesystem), and vector search as plain SQL over a clustered lake.
  • Plain PostgreSQL. No extensions and no superuser needed, so managed services work too (14 or later; see install); tested on Amazon Aurora PostgreSQL (with password authentication; RDS IAM tokens aren’t supported).

It suits lakes that fit comfortably in one PostgreSQL (tested to 100M rows, 14 GB) with many concurrent readers doing lookups and dashboard-style queries: map layers, per-customer analytics, APIs over DuckLake.

INSTALL pgvfs FROM community;
LOAD pgvfs;

Signed builds need no -unsigned flag. Start with Install for a runnable DuckLake example, then Loading data: how a lake is laid out decides most of its read speed.

pgvfs keeps the lake inside PostgreSQL and reads it from DuckDB. To go the other way, querying Parquet or Iceberg files in object storage from PostgreSQL clients, use one of these instead:

  • Aurora PostgreSQL direct querying (aurora_analytics): DuckDB inside Aurora, reading S3, S3 Tables and Glue or Iceberg REST catalogs as foreign tables.
  • pg_duckdb: DuckDB inside any PostgreSQL, reading Parquet, Iceberg and Delta files.
  • pg_lake: Iceberg tables and data lake files in PostgreSQL, using DuckDB to execute queries.

They suit scans and analytics over large lakes, but not millisecond lookups. We did not test the projects above. Each reads object storage, where a request to S3 Standard takes tens of milliseconds; S3 Express One Zone, which is faster, was not tested. Aurora’s announcement likewise recommends copying lake data into native tables when a query needs single-digit-millisecond latency. Against an S3 gateway on the same PostgreSQL, pgvfs was faster on ClickBench (Performance).

Source: https://github.com/adonm/pgvfs (Apache-2.0). Beta, storage layout v2; see the changelog and layout compatibility policy.

Install

For DuckDB 1.5.6, install the signed community extension. No -unsigned flag or allow_unsigned_extensions setting is needed:

INSTALL pgvfs FROM community;
LOAD pgvfs;

DuckDB selects the build matching its version and platform:

PlatformArchitectures
Linux, glibc or musl (Alpine)x86-64, ARM64
macOSIntel, Apple Silicon
Windowsx86-64

WebAssembly and MinGW/rtools are not supported. Storage needs PostgreSQL 14 or later; the contract also passes on 11 to 13, which are end-of-life upstream. No PostgreSQL extensions are required. pgvfs is in beta, on storage layout v2.

Python

Install the matching DuckDB package (python -m pip install duckdb==1.5.6), then use a normal connection:

import duckdb
con = duckdb.connect()
con.execute("INSTALL pgvfs FROM community")
con.execute("LOAD pgvfs")

Run the same quick-start SQL below with con.execute(...).

Quick start

The PostgreSQL database must already exist. Use a writer role that can create schemas and DuckLake catalog tables there; a database owner role suffices. The first write creates pgvfs’s schema automatically; see roles for production permissions.

For an optional throwaway local demo, start PostgreSQL with Docker:

docker run --name pgvfs-demo --rm -d -p 127.0.0.1:54329:5432 \
  -e POSTGRES_DB=lake -e POSTGRES_USER=lake -e POSTGRES_PASSWORD=lake postgres:18

Wait for PostgreSQL to be ready (docker exec pgvfs-demo pg_isready -h 127.0.0.1 -U lake -d lake; without -h it can report ready before TCP connections work). This demo uses local-only credentials and loses its data when stopped. For your own server, replace the host, port, database, user and password below.

After installing and loading pgvfs, run this once to initialize a new lake:

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

No local data file is needed. To load your own Parquet files instead, follow Loading data, keeping the options and sort order set before the first insert. For a remote PostgreSQL server, add SSLMODE 'require' to the secret. Certificates are verified; private CAs and Alpine’s ca-certificates package are covered under Credentials.

Reconnect or read from another process

Installation is cached, but LOAD and the temporary CREATE SECRET must be repeated in each new DuckDB session. Do not recreate the table or run the initialization again. The data path and lake options are stored in the catalog, so an existing lake needs no DATA_PATH on attach.

After loading the three extensions and creating the secret, readers use:

ATTACH 'ducklake:postgres:' AS lake (READ_ONLY);
SELECT count(*) FROM lake.events; -- 3

The reader role needs USAGE on both the DuckLake catalog and pgvfs schemas, and SELECT on their tables. The same attach works on a streaming read replica. A process whose role can write attaches without READ_ONLY; several such processes may write at once, and roles say which roles those are.

Switch from an unsigned installation

INSTALL alone does not replace a cached extension. In a fresh DuckDB process, switch an older installation from this site’s repository to the signed community build:

FORCE INSTALL pgvfs FROM community;
LOAD pgvfs;

Start DuckDB normally, without -unsigned. This changes the installed extension, not the data in PostgreSQL. Check the layout compatibility policy if upgrading an older lake.

To check the installed source:

SELECT extension_version, installed_from
FROM duckdb_extensions() WHERE extension_name = 'pgvfs';

installed_from should be community. Its version may be displayed as the source commit rather than a release tag.

DuckDB 2.0 dev builds

Until community builds are available for 2.0, recent dev versions use the unsigned, Linux x86-64 glibc-only builds at https://pgvfs.adonm.dev. These are published weekly and must match the exact DuckDB build:

-- only for dev builds: start with duckdb -unsigned
INSTALL pgvfs FROM 'https://pgvfs.adonm.dev';
LOAD pgvfs;

In Python, only these unsigned builds need duckdb.connect(config={"allow_unsigned_extensions": True}).

Available unsigned builds

This table lists this site’s builds, including older stable builds, not the signed community binaries. Prefer FROM community for stable DuckDB.

DuckDB versionPython wheelBuiltCommitDirect install
v1.5.6duckdb==1.5.62026-11-1686c1a991f181file
v2.0.0-alpha43763duckdb==2.0.0.dev26100115352026-10-14e1d6eb40dc18file
v2.0.0-alpha43385duckdb==2.0.0.dev26092507152026-09-29271c57b707dcfile

Version history and release notes: Releases.

Troubleshooting

  • Extension not found: check your DuckDB version and platform against the supported builds above. A 2.0 dev wheel needs its exact matching build.
  • Missing PostgreSQL credentials: load postgres and create its secret before attaching the lake. Secrets are temporary unless made persistent.
  • Permission denied on a write: the role has no write privileges on pgvfs’s tables. Attach with READ_ONLY for a reader, or grant the writer privileges as shown in roles.

Releases

Every version is a pre-release on GitHub (all releases), and every DuckDB build is published separately as duckdb-<duckdb version>, so a build can be updated without a new pgvfs version. Three ways to install:

  • INSTALL pgvfs FROM community — DuckDB’s own signed build of the current release. Available for stable DuckDB once the update below is merged; Install has the details.
  • INSTALL pgvfs FROM 'https://pgvfs.adonm.dev' — this site’s build for your DuckDB version, including 2.0 dev wheels (builds).
  • A file — download pgvfs.duckdb_extension from a release or from the build table, and LOAD it.

Release notes for each version are on its release page; the changelog below is the same text, newest first.

Changelog

pgvfs is in beta: the SQL interface and configuration are expected to stay, but storage layout changes are still possible (see “Storage layout” in the docs). Each release lists its layout version.

0.3.2

Storage layout: v2 (unchanged).

  • DuckDB 2.0 caches pgdb:// reads again. 2.0 caches only paths that look remote, and loading pgvfs now turns on cache_local_files where it exists, so the external file cache serves pgdb:// reads (1.5.6 is unaffected: it has no such rule and no such setting).
  • The nightly build works again: the container had not copied dbschema.sql (needed since 0.3.0), and extension/build.sh had not been given pgdb_filesystem.cpp. A failed nightly build now prints the compiler’s error instead of only docker’s.
  • just ext-nightly passes --hostname github.com to gh, so it works for a developer whose gh defaults to another host.
  • The contract’s PostgreSQL allows 500 connections (the pgdb end-to-end test uses more than the default 100 under 2.0) and the Python end-to-end test runs with PGVFS_POOL_MIN=1 PGVFS_POOL_MAX=8, as the Rust contract does.

0.3.1

Storage layout: v2 (unchanged). No FFI or table changes.

  • pgdb:// reads ahead. A read that continues the previous one also fetches the blocks after it, so scans stop costing a round trip per block (2.6 s to 0.70 s for a cold 1.2 GB scan with 16 KiB blocks, 438 ms to 163 ms with 8 threads) and range scans nearly halve. A read after a gap fetches exactly its own bytes, so point lookups are unchanged. Knobs: PGVFS_DB_READAHEAD_KB (1 MiB) and PGVFS_DB_READAHEAD_SLOTS (8). New counters: db_readahead_hits, db_readahead_bytes.
  • Block size is documented as a knob (BLOCK_SIZE at creation): Databases.
  • Comments trimmed across the Rust and C++ sources; the house style is in CONTRIBUTING.md.

0.3.0

Storage layout: v2 (unchanged). pgdb:// adds five tables (dbschema.sql), created by the first database writer; the pgvfs:// tables do not change.

  • pgdb://: native DuckDB databases in PostgreSQL (experimental). ATTACH 'pgdb://<volume>/<name>' is a real DuckDB database (ART indexes, primary and foreign keys, constraints, sequences, WAL) stored as versioned 8 KB chunks. One writer, refused at attach if another holds it and fenced on every write; any number of readers, on standbys too, each reading one published version and re-attached at the newest every pgdb_refresh_interval seconds (running transactions keep theirs). Readers cache through DuckDB’s external file cache under keys a new version leaves valid. Experimental: its tables may still change, with a migration. See Databases for the design, limits and benchmarks: against a local file, warm readers run at 93% of the throughput, loads at 1.24x the time, commits at about 2 ms; cold scattered lookups are the slow case.
  • Concurrent searches of one tantivy split read in parallel, through up to four handles instead of one under a lock.
  • Secrets are found for database-level I/O (no client context), through the file opener.
  • pgvfs_stats() adds db_reads, db_read_bytes, db_pieces, db_cache_bytes and db_publishes.
  • Tests and benchmarks: just bench-db (pgdb:// against a local DuckDB file), just sqllogic (DuckDB’s own SQL tests on pgdb://), and a pgdb:// storage contract and end-to-end test in just contract.

Upgrading from beta.6: nothing to do. Readers of pgdb:// need SELECT on the new tables, writers INSERT, UPDATE and DELETE (see Databases).

0.2.0-beta.6

Storage layout: v2 (unchanged). The functions moved to functions.sql, which writers keep current; the tables did not change.

  • Any process whose role can write may write. The writer lease, its dedicated connection and the “another writer holds the lock” error are gone. Concurrent writers publish independently. Two writers publishing one path: the second fails with a clear error and rolls back. A SELECT-only role’s write, drop or reap fails with PostgreSQL’s permission error.
  • Reaping runs in every writer, under a try-lock, so concurrent reaps skip. Each reap deletes a bounded range of rows per file. PGVFS_REAP_GRACE_S sets the grace (600 seconds by default); a read that outlives it names the setting in its error.
  • Credentials per volume. A postgres secret named pgvfs_<volume> gives that volume its own database; each database has its own connection pool. DuckDB keeps no SCOPE on postgres secrets, so the name is what routes a path.
  • Build memory is bounded. tantivy_index reserves each open build’s memory_budget (plus about 10 MB) against memory_limit and fails before any build starts that would exceed it. Indexing threads are DuckDB’s threads shared among the open builds (at most 8 each), with one merge thread. Earlier versions started tantivy’s default of about 21 threads per build.
  • Listing streams one primary-key range query, and glob lists only the literal prefix before its first *, ? or [.
  • pgvfs_drop_volume works from any writer.
  • Build: just ext builds the stable extension locally from the DuckDB 1.5.6 submodule. The container build is for DuckDB 2.0 dev builds. Every script is a justfile recipe; scripts/ is gone, and site.py moved to tools/.
  • Benchmarks: cold passes evict PostgreSQL’s files from the OS page cache; ROUNDS repeats restart, eviction and passes; PG_DEVICE with PG_READ_IOPS and PG_READ_BPS throttles PostgreSQL’s disk reads.
  • PostgreSQL 11 to 13 are end-of-life upstream. The contract still passes on them; use 14 or later.

Upgrading from beta.5:

  • The first write by the database owner (or a superuser) replaces pgvfs.reap with the new one. Until then, writers that do not own the functions keep the beta.5 reap, which has no try-lock and deletes whole files at once.
  • Builds that would exceed memory_limit now fail. Each open build reserves memory_budget plus about 10 MB, so at the default 256 MB budget a query can have at most memory_limit / 266 MB groups. Lower memory_budget or build fewer groups per query.
  • A postgres secret named pgvfs_<volume> now takes precedence over pgvfs_secret for that volume. Rename any secret that already has that name.
  • A SELECT-only role’s pgvfs_drop_volume fails with PostgreSQL’s permission error, not the old “writer” error.

0.2.0-beta.5

Storage layout: v2 (unchanged).

  • Exclude dead IDs before tantivy top-k, exact counts and aggregations, using portable 32/64-bit roaring BLOBs or BIGINT[] on a fast integer key. Bitmap paths use DuckDB’s read_blob, so every filesystem works.
  • Fast-field-only hit projection, tantivy_count, and tantivy_aggregate with tantivy’s Elasticsearch-shaped requests, including terms and cardinality.
  • OpenSearch query-DSL subset: fuzzy, prefix, exists, boolean clauses, minimum-should-match and non-scoring filters; unsupported parameters fail.
  • List-of-splits search, counts and aggregation merging; optional combined BM25 statistics. Hits now also include their split path.
  • tantivy_merge compacts segments natively, optionally removing excluded documents. tantivy_index accepts rows/structs directly.
  • Prepared index plans own their execution resources correctly: repeated execution and retries after errors neither crash nor keep partial documents. A failed build open under a parallel GROUP BY (a bad schema) is a clean error in every thread; before, other threads reused the failed entry and crashed. Rust panics at the tantivy C boundary become errors; oversized top-k is clamped.
  • Exclusion is now an alive bitset per segment, built once per split and set and kept (the last four), as tantivy’s own deletes are, instead of a lookup per candidate: with 60% of 4 million documents excluded, a count of 2.8 million matches takes 9 ms (was 61), a top 10 takes 6 ms (was 64).
  • A list of splits is searched in parallel, up to DuckDB’s threads, and the hits’ documents are read in parallel too. Equal scores order the same at any thread count.
  • Search options offset (a page reads documents only for its own hits), collapse (the best hit of each value of a fast field) and ignore_unmapped; count options limit (stop after limit + 1) and distinct (the exact number of distinct values). limit was an unknown option before.
  • Query DSL: paths inside JSON fields in term, terms, match, match_phrase, range, exists and multi_match; exists on an indexed text field without a fast column falls back to its terms.
  • Query DSL: prefix, wildcard, regexp and fuzzy on paths inside JSON fields (the path’s strings only; the path-skipping automaton is adapted from Quickwit, Apache-2.0), and case_insensitive on term, prefix, wildcard and regexp.
  • tantivy_search: sort by a fast field (numbers, dates, text; missing values last; score is NULL), and highlight (a fourth column: HTML snippets of stored text fields). Query DSL: more_like_this for a text.
  • bench/search.py and bench/index_memory.py: timings, compaction cost and build memory (documented with memory_budget; a TMPDIR on a tmpfs counts as memory).
  • Every function registers a description and an example, so DuckDB’s duckdb_functions() and the community docs page list them (they showed NULL for all but pgvfs_stats).
  • pgvfs_drop_volume removes one volume through the writer and invalidates caches. Directory removal unpublishes a key prefix.
  • DuckDB 2.0 scalar error declarations and filesystem access checks, including cached splits. SQL and PostgreSQL tests cover snapshot liveness and native compaction; changes inserted then deleted within one range are not indexed.

0.2.0-beta.4

Storage layout: v2 (unchanged).

  • Full-text search with tantivy, in the same extension. tantivy_index (an aggregate) builds an immutable split, one file, from any query’s rows, one per group; tantivy_search searches a split (laterally, many); tantivy_drop removes one. They use only DuckDB’s filesystem, so splits can live on pgvfs, local disk or object storage. Schemas, documents, options and queries are tantivy’s own. The tantivy_create_index and tantivy_match_bm25 macros work like DuckDB’s fts. Which splits make up an index is up to SQL: the docs show a split per DuckLake commit, using table_changes, rowid and snapshot_id, and range compaction.
  • Docs: vector search as plain SQL over a lake clustered by nearest centroid (IVF), with a synthetic check (bench/ivf.py).
  • The extension grows by about 5 MB (tantivy, without its zstd feature). Built against DuckDB 2.0 (nightly), which embeds much less of DuckDB, it is about 21 MB instead of 39 MB.

0.2.0-beta.3

Storage layout: v2 (unchanged).

  • Restore Intel and Apple Silicon macOS targets in the distribution workflow and community submission.
  • Document signed community installation and a self-contained DuckLake quick start, with separate instructions for unsigned 2.0 dev builds.
  • Docs: tested on Amazon Aurora PostgreSQL; a “Related projects” section (Aurora direct querying, pg_duckdb, pg_lake) and how pgvfs’s millisecond lookups compare.
  • Docs: the recommended layout is now zstd and Parquet V2. LZ4 stores 55–58% more but serves 25–35% more queries/s to CPU-bound readers; the guide covers when to pick it. Loading data adds registering pre-written files for big loads, commit sizing, JSON over VARIANT payloads, snapshot retention, why to keep positional delete files, and how to check pruning.
  • pgvfs_stats has a function description, so duckdb_functions() and the community extension page describe it.
  • Benchmarks load with zstd and Parquet V2 (was LZ4, V1). Weekly results drop accordingly (Houston warm throughput about 30% lower on the same machine) and are not comparable with earlier runs. Each benchmark record now includes the lake’s layout.
  • The end-to-end test covers copying finished Parquet into pgvfs with FORMAT blob and registering it with ducklake_add_data_files.

0.2.0-beta.2

Storage layout: v2 (unchanged).

  • Builds for Linux musl (Alpine), amd64 and arm64.
  • macOS left out of the multi-platform build for now.

0.2.0-beta.1

Storage layout: v2.

First beta. Highlights since the project split out of pgvs3:

  • Install from https://pgvfs.adonm.dev for DuckDB 1.5.6 and recent 2.0 dev builds (linux_amd64); DuckDB’s extension pipeline also builds it for Linux arm64 and Windows. macOS builds but is not published yet.
  • One writer, many readers: an advisory-lock writer lease; readers need only SELECT and work on streaming standbys (a writer there is refused).
  • Credentials from a postgres secret shared with DuckLake’s catalog.
  • Performance defaults: Parquet footer cache on load, a 10 s open cache, I/O threads and pool sized from DuckDB’s threads; documented lake layout (8K-row groups, LZ4, 64 MB files, a declared sort order).
  • Reliability: reads retry once on a lost connection; interrupted writes publish nothing; a lost writer lock stops writes; the writer reaps garbage when it takes the lock.
  • Compatibility: PostgreSQL 11–18, no extensions or superuser; TLS with certificate verification.
  • pgvfs_stats() for monitoring.

Loading data

How a lake is laid out decides most of its read speed, so spend the effort at load time. A query’s cost is the files and row groups it cannot rule out from their min/max statistics, times what it must decode in the rest. The steps below are in order of impact; the measurements are at the end.

1. Set the lake options before the first insert

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');

They are stored in the catalog and apply to files written afterwards. DuckLake takes no defaults from extensions, so set them on every lake.

  • 8,192-row row groups. A query reads whole row groups, so their size sets lookup latency: a 1,000-row lookup took 41 ms with DuckDB’s default of 122,880 rows, 10 ms with 8,192, and 12.5 ms with 2,048. For large tables mostly scanned by long key ranges, 32K–64K rows gives more throughput (the option can be set per table).
  • zstd. Every byte of a pgvfs lake is PostgreSQL storage, WAL and backup, and cold reads fetch it from PostgreSQL. LZ4 stored 55–58% more than zstd, and snappy (DuckDB’s default) 70% more with no faster reads. LZ4 decodes faster, though: when readers are CPU-bound it served 25–35% more queries per second (13 readers on Houston), while one client’s sequential lookups were only 4% faster. Choose LZ4 for a read-heavy lake whose storage is cheap; the option can be set per table.
  • Parquet V2 encodings made files about 4% smaller than V1, with about the same read speed. Writing them was about 15% slower.
  • 64 MB files instead of the 512 MB default. DuckLake skips whole files using statistics in its catalog before opening any, so smaller files leave less to read in the ones a query does open.

Set these, and each table’s sort order (below), once when the lake is created, not in every load. One 200 GB load that re-declared its sort orders inside each batch’s transaction slowed from 34 s to 102 s per batch over its first 84 batches.

2. Declare a sort order that matches your filters

ALTER TABLE lake.events SET SORTED BY (site_id, day);

DuckLake then sorts every insert, merge and rewrite by it. Put the column queries filter on most first. This was the biggest single win measured: 2× read throughput on 100M rows.

For geometry, sort by a space-filling curve so an area of interest touches few row groups:

ALTER TABLE lake.buildings SET SORTED BY (ST_Hilbert(geometry, {'min_x': -95.65, 'min_y': 29.60, 'max_x': -95.15, 'max_y': 29.95}::BOX_2D));

Sorting on one coordinate was about 15% slower. Data that already arrives spatially clustered, like Overture’s, needs no re-sort.

3. Load big tables in key-range batches

One INSERT of 100M wide rows sorts them all at once, which can exhaust memory. Instead, insert disjoint ranges of the leading sort key, in order, about 5M rows at a time:

SET memory_limit = '4GiB';            -- bigger sorts spill to temp_directory
SET temp_directory = '/fast/disk/duckdb-temp';
SET preserve_insertion_order = false;
INSERT INTO lake.events FROM 'events.parquet' WHERE site_id >= 0 AND site_id < 1000;
INSERT INTO lake.events FROM 'events.parquet' WHERE site_id >= 1000 AND site_id < 2000;
-- ...

Each sort stays small, and ranges don’t overlap across files, so a filtered query opens one or two files instead of all of them. bench/hits.py shows the pattern.

4. Keep columns typed and narrow

Convert dates, timestamps and numbers from strings or epochs at load time (make_date, epoch_ms, casts). Statistics on typed columns prune; on strings they mostly don’t. DuckDB quantifies the rest in its performance guide: one timestamp column aggregated as TIMESTAMP took 0.9 s and 3.3 GB, the same values as VARCHAR 3.9 s and 5.2 GB, and a join on a VARCHAR key ran 1.8× slower than on a BIGINT.

Parquet stores a fixed-width type at its declared width whatever the values are, so use the narrowest type that holds them: INTEGER and DATE shrink the file as well as the memory and the comparisons. That is worth more here than DuckDB’s note suggests, because DuckDB’s own file format bitpacks small values into wide columns while Parquet does not.

There are no keys or indexes to declare: DuckLake supports only NOT NULL, so PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK are rejected outright.

Store nested payloads as JSON (text in Parquet), with the fields queries filter on as typed columns beside them. On DuckDB 1.5.6, shredded VARIANT was 14–19× slower than JSON both for reading payloads whole and for filtering or aggregating on one field inside them, at the same storage, and JSON loaded about 35% faster. A 2.0 nightly through DuckLake was still 5–7× slower.

Group on a narrow key, not a long string

Grouping on a long, repeated string hashes and compares every byte of it for every row, and copies it into the hash table. DuckDB’s write-up on dimension tables moves those values into a small dimension table keyed by a narrow integer, aggregates on the key, and joins the strings back once the result is small:

-- the distinct strings, once, keyed in string order
CREATE TABLE lake.station_names AS
    SELECT station_name,
           (row_number() OVER (ORDER BY station_name))::USMALLINT AS station_id
    FROM (SELECT DISTINCT station_name FROM lake.stops WHERE station_name IS NOT NULL);

-- the fact table then stores station_id in place of station_name

Size the key to the cardinality: UTINYINT holds 255 distinct values, USMALLINT 65,535, UINTEGER about 4.3 billion. Keep the ORDER BY, so ORDER BY station_id still means alphabetical and a rebuild of the same data assigns the same keys.

  • The dimension table is small enough to stay cached, so its join costs almost nothing. The win is in the fact table’s column: fewer bytes to fetch from PostgreSQL, and a key that sorts and prunes like any other integer, where a string column’s Parquet statistics are truncated prefixes.
  • A small, dense key range skips hashing. The optimizer then uses a perfect hash aggregate, indexing by the key (perfect_ht_threshold).
  • Add unseen values with an ANTI JOIN so existing keys stay put. Later keys stop following alphabetical order, so rebuild and re-key if a query relies on ORDER BY station_id meaning alphabetical.
  • ENUM is the built-in form of this, but its values are fixed when the type is created, so a lake that keeps taking new values wants the dimension table.

Skip it when the strings are short (up to 12 bytes are stored inline anyway), the column is nearly unique, the data is read once, or nothing groups on it. Narrower keys also do not reduce the number of groups: every thread aggregates into its own hash table, so an aggregation whose groups approach the rows each thread sees wants fewer threads instead (see Sizing). Not measured here.

5. For big or continuous loads, write files elsewhere and register them

When the loading process is the bottleneck, move sorting and encoding out of it. Any number of DuckDB processes, anywhere, write finished Parquet files in the lake’s layout. One process with a role that can write to pgvfs only copies their bytes into pgvfs and registers them in the catalog, without decoding them:

-- each producer: one sorted, contiguous key range per file
CREATE TEMP TABLE batch AS
    SELECT *, ntile(8) OVER (ORDER BY site_id, day) - 1 AS bucket FROM 'new_events.parquet';
COPY (SELECT * EXCLUDE (bucket) FROM batch WHERE bucket = 0 ORDER BY site_id, day)
TO 'bucket-0000.parquet' (FORMAT parquet, COMPRESSION zstd, PARQUET_VERSION V2, ROW_GROUP_SIZE 8192);
-- ... one COPY per bucket

-- the registering process: copy the bytes, then register the files in one transaction
COPY (SELECT content FROM read_blob('bucket-0000.parquet'))
TO 'pgvfs://lake/main/events/bucket-0000.parquet' (FORMAT blob);
BEGIN;
CALL ducklake_add_data_files('lake', 'events', ['pgvfs://lake/main/events/bucket-0000.parquet']);
COMMIT;
  • The files must already be in the lake’s layout. DuckLake neither sorts nor rewrites registered files, and their schema must match the table’s. Pick the bucket count so each file is about 64 MB. Don’t rely on FILE_SIZE_BYTES rotation instead: it interleaves key ranges across files, so none of them prune.
  • Copy in parallel. Each connection writes its own files, so several connections of one DuckDB copy at once: one streamed about 300 MiB/s, four 536 MiB/s; six and eight were slower (PostgreSQL on 8 cores). Separate processes may copy too, since there is no writer lock; their throughput was not measured.
  • Commit about 2 GB of files at a time. Each commit has fixed catalog work and makes a snapshot. A 3.2 GiB load took 40.6 s in 256 MB commits, 35.9 s in 2 GB commits and 36.5 s in 8 GB commits.
  • Give the loading process threads; cap its memory with memory_limit. The same load took 77 s on 1 thread and 33 s on 6, with the same 9.5 GiB peak, so fewer threads only made it slower.

Registering new rows this way ran at 160–190 MiB/s of Parquet, copy and commit included. Updates and deletes still cost per changed row, because they must find the rows they replace.

6. Keep it compact

Every insert writes its own files and a catalog snapshot, and files from separate inserts overlap, so statistics prune less. A lake fed by 100 small inserts was 5× slower than one sorted load. After trickle inserts:

CALL ducklake_merge_adjacent_files('lake');
CALL ducklake_expire_snapshots('lake', older_than => now() - INTERVAL 1 HOUR);
CALL ducklake_cleanup_old_files('lake', older_than => now() - INTERVAL 1 HOUR);

Merging recovered most of the loss, but merged files keep each insert’s rows together rather than globally sorted. For all of it back, reload into a fresh sorted table.

Expiring snapshots ends time travel to them, and cleanup then deletes their files. If anything reads old versions (AT (VERSION => ...), change feeds, history built on snapshot ids), merge and rewrite but don’t expire those snapshots.

7. Rewrite after deletes and updates

Deletes go into delete files that every later scan merges in; 10% of rows deleted cost 15–23%. Rewrite, then expire and clean up as above:

CALL ducklake_rewrite_data_files('lake', delete_threshold => 0.05);

Without delete_threshold, a file is only rewritten once 95% of its rows are deleted.

Keep DuckLake’s default delete files (Parquet row positions). Its experimental write_deletion_vectors option writes Puffin bitmaps instead: 84% smaller, but point and bounding-box reads through pgvfs were about 2× slower.

Check what a query reads

EXPLAIN ANALYZE shows each DUCKLAKE_SCAN’s Total Files Read, so you can see whether a filter prunes before measuring anything. For bytes and round trips, diff SELECT pgvfs_stats() before and after a query.

Not worth it

  • Splitting hot and cold columns into separate tables: Parquet is columnar, so a query decodes only the columns it reads anyway. No change measured.
  • Partitioning except on low-cardinality columns (a month, a region) that nearly every query filters on. Each partition value gets its own files; a sort does the same job without many small files.
  • Turning off sorting to load faster: ingest went from 7.4 s to 7.3 s, and reads got both faster and slower. When registering pre-sorted files, sorting doesn’t run anyway.
  • skip_stats_columns (drop statistics on wide unfiltered columns) could shrink the catalog, but it isn’t in the DuckLake builds for DuckDB 1.5 or the current 2.0 dev. Untested.

Measurements

13 readers, PostgreSQL on 3 cores, 16-core laptop; just bench city and just bench hits. The tables below were measured with LZ4, before the benchmarks moved to zstd and Parquet V2.

Compression (Houston, 2 alternating rounds each; LOAD_ARGS="--compression lz4 --parquet-version 1" just bench city; warm is the median warm pass):

LayoutRead on the cold passCold q/sWarm q/sWarm p50
LZ4, V1681 MiB193307–31825–27 ms
zstd, V2434 MiB157–162205–24333–37 ms
zstd, V1446 MiB138–175186–22833–44 ms

ClickBench hits (100M rows, 105 columns, per-site dashboard queries):

LoadLoad timeColdWarmNew parameters
Source order62 s16 q/s, p50 337 ms40 q/s, p50 140 ms25 q/s
Sorted, key-range batches139 s30 q/s, p50 163 ms76 q/s, p50 46 ms36 q/s
… with 64K-row groups213 s40 q/s, p50 134 ms89 q/s, p50 47 ms47 q/s

File size (the sorted table, 3 alternating runs each, same load time):

FilesCold p50Warm p50New parameters p50
512 MB (40 files)229–250 ms62–76 ms149–169 ms
64 MB (223 files)176–192 ms52–65 ms121–142 ms

Maintenance (Overture Houston, 1M rows, the recommended setup, 2 runs each; LOAD_ARGS="--variant ..." just bench city):

LayoutFilesSnapshotsWarm q/sWarm p50Cold p50
One sorted load47389–39820 ms29–36 ms
100 small inserts20020577–82144–153 ms133–153 ms
… then merged and cleaned up41284–28830–31 ms61–66 ms
10% of rows deleted¹4 + 4 delete files9299–30830 ms44–50 ms
… then rewritten and cleaned up21319–34824–27 ms43–48 ms
Hot and cold columns split3 + cold tables9380–39919–20 ms35–38 ms

¹ Measured before pgvfs cached file opens. DuckDB re-opens delete files on every query to check them, and the cache now answers those checks from memory; that took about 10% off this row’s warm latency.

A change-history lake

From an API built on pgvfs: parcels, buildings and roof parts with every captured version, synthetic data seeded from Overture. DuckDB 1.5.6; for the pgvfs runs, one loading process with 6 threads and 8 GB and PostgreSQL 18 on 8 cores and 8 GB. Single runs, so treat small differences as noise.

Compression and row groups (local files, 3 batches of 100K parcels, 100 sequential ID lookups):

LayoutDataLookup p50 / p99
zstd, 122,880-row groups281 MB122 / 142 ms
zstd, 8,192-row groups298 MB57 / 70 ms
LZ4, 8,192-row groups470 MB55 / 70 ms
LZ4, 32,768-row groups450 MB50 / 59 ms

Storage format (in pgvfs; one 500K-parcel stream registered as finished files; reads are 1-client p50 with positional deletes):

Compression, Parquet versionFilesLoadBuilding lookupBounding-box page
zstd, V1819 MiB7.5 s56 ms403 ms
zstd, V2785 MiB8.7 s56 ms398 ms
snappy, V11,400 MiB8.8 s58 ms414 ms
snappy, V21,361 MiB10.3 s59 ms405 ms

Delete files (the same 2.9M deleted rows in 26 files):

FormatSizeBuilding lookupBounding-box page
Parquet positions (default)11.9 MB56 ms398 ms
Puffin deletion vectors1.9 MB106 ms907 ms

Databases (pgdb://)

pgdb:// stores a native DuckDB database in PostgreSQL: the same file DuckDB would write to disk, with ART indexes, primary and foreign keys, CHECK constraints, sequences, views, macros and the WAL. One process writes; any number of DuckDB processes read it, on the primary or on streaming standbys, and follow new commits by themselves. Nothing else runs beside PostgreSQL.

LOAD pgvfs;
CREATE SECRET (TYPE postgres, HOST 'localhost', PORT 54329,
               DATABASE 'lake', USER 'lake', PASSWORD 'lake');

-- the one writer
ATTACH 'pgdb://lake/sales.duckdb' AS sales;
USE sales;
CREATE TABLE customers (id BIGINT PRIMARY KEY, email VARCHAR UNIQUE NOT NULL);
CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT REFERENCES customers(id),
    amount      DECIMAL(12, 2) CHECK (amount >= 0));
INSERT INTO customers VALUES (1, 'ada@example.com');
INSERT INTO orders VALUES (10, 1, 42.00);
-- any other process, any number of them
ATTACH 'pgdb://lake/sales.duckdb' AS sales (READ_ONLY);
SELECT c.email, sum(o.amount) FROM sales.orders o JOIN sales.customers c
    ON o.customer_id = c.id GROUP BY ALL;

Paths are pgdb://<volume>/<name>; credentials and volumes work as for pgvfs:// (credentials). It is the same PostgreSQL database that can hold a DuckLake on pgvfs:// too: a session can attach both and join across them.

One writer, many readers

  • One writer per database. A second read-write ATTACH anywhere fails at once (“attached for writing by another process”, or “in this process”). A read-only ATTACH in the writer’s own process reads published versions, as any reader does. The writer holds a PostgreSQL advisory lock for as long as the database is attached, and every write it makes checks that it still owns the database. A writer that loses its connection fails rather than diverging.
  • Every commit is durable in PostgreSQL before DuckDB reports it: DuckDB’s fsync publishes everything written since the last one as a new version, in one statement. A commit costs one round trip to PostgreSQL (about 1.5 ms on a local server), so batch small writes as you would on any remote disk.
  • Readers see a version, never a mix. Attaching resolves the path to the newest version (duckdb_databases() shows pgdb://lake/sales.duckdb@42~3: version 42, and a token telling this attach from others in the process), and every read is of that version. A version is a set of the writer’s fsyncs: exactly a state DuckDB’s own crash recovery would accept, so a reader replays the WAL as it would after a crash.
  • Readers follow new commits. Every pgdb_refresh_interval seconds (10 by default, 0 off), each database attached read-only by its plain path is attached again at the newest version, in place (ATTACH OR REPLACE). A running transaction keeps the version it started with; the next one sees the new version. So readers lag by at most the interval, and are never inconsistent.
  • Pin a version by attaching pgdb://lake/sales.duckdb@42: it is never refreshed. Workers of one distributed query can attach the same version.
  • Old versions are reaped once they have been superseded for the reap grace (PGVFS_REAP_GRACE_S, 10 minutes by default). A reader of a version older than that gets an error telling it to attach again, never wrong bytes. Refreshing readers never get there; only a single transaction longer than the grace on a superseded version can.
  • A writer that dies leaves the versions it published. The next writer removes anything it wrote but never published, then DuckDB replays the WAL as after any crash.

Performance

Readers cache through DuckDB’s external file cache (counted in memory_limit, visible in duckdb_external_file_cache(), off with SET enable_external_file_cache = false). A cached range is keyed by the version that last wrote it, so a refresh keeps every unchanged range cached: moving to a new version costs the bytes that changed, plus reading the catalog and replaying the WAL written since the last checkpoint. The external cache does not survive a restart; PostgreSQL’s own buffers are the next tier.

DuckDB batches the reads of adjacent blocks, and pgvfs splits large reads into 8 MB range queries that run in parallel.

just bench-db compares a pgdb:// database with the same database in a local file: 30M rows (about 1 GB), PostgreSQL 18 in a container on 3 of a 16-core laptop’s cores, 13 single-threaded readers (2 GiB memory_limit each) on the rest, three runs (medians). Cold passes restart PostgreSQL and evict its files from the OS page cache, and evict the local file.

local filepgdb://
CREATE TABLE AS 30M rows6.9 s8.6 s (1.24×)
Primary key and an index7.6 s10.6 s (1.40×)
Commit (one-row insert)0.6 ms2.2 ms
13 readers, warm: queries/s74.569.1 (93%)
… point lookup p501.7 ms11.8 ms
… aggregate over the table p50249 ms263 ms
13 readers, first pass: queries/s71.051.7 (73%)
Cold point lookup3.8 ms21.6 ms
Cold lookup of 300 scattered rows82 ms512 ms
Cold scan of an incompressible column118 ms375 ms
A reader’s refresh, then one query of each kind150 ms, 2 MB from PostgreSQL
… without the external file cache433 ms, 337 MB from PostgreSQL

Where it costs: each block a query misses in memory is a round trip to PostgreSQL, so scattered lookups on a cold or larger-than-memory working set are several times a local SSD’s; each reader process caches for itself, where local readers share the OS page cache. Scans and aggregates are close to local. Writes go to PostgreSQL by binary COPY (blocks new to a version) and upserts (blocks rewritten in one version), on one connection.

Readahead

DuckDB’s block manager asks for one block at a time, so a scan used to pay a round trip per block. A read that continues the previous one now also fetches the blocks after it (PGVFS_DB_READAHEAD_KB, 1 MiB by default; 0 turns it off, PGVFS_DB_READAHEAD_SLOTS caps the buffers per process at 8). A read after a gap fetches exactly its own bytes, so point lookups pay nothing.

Cold, one reader thread, on a 20M-row (1.2 GB) database, PostgreSQL restarted and its files evicted before each run, two runs (medians):

beforewith readahead
Full scan, 16 KiB blocks2,603 ms703 ms
Full scan, 16 KiB blocks, 8 threads438 ms163 ms
Full scan, 256 KiB blocks738 ms645 ms
Full scan, 256 KiB blocks, 8 threads116 ms126 ms
200 point lookups, 16 KiB blocks812 ms458 ms
200 point lookups, 256 KiB blocks745 ms695 ms
Scan of 1% of the table, 16 KiB blocks79 ms43 ms

It fetches ahead about 171 MB for a 160 MB scan (the reads would have happened anyway) and about 3 MB for the 200 lookups. A scan that stops costs at most the window that was already fetched.

Block size

ATTACH 'pgdb://lake/sales.duckdb' (BLOCK_SIZE 16384) creates a database with 16 KiB blocks; readers and later writers use whatever the file was created with. The trade is bytes against round trips:

Same 20M-row table16 KiB256 KiB (default)
Bytes a cold point lookup moves~60 KB~630 KB
Read calls (200 point lookups)745443
Full scan, one thread (with readahead)703 ms645 ms
Full scan, 8 threads (with readahead)163 ms116 ms
Database size on disk1.03 GB754 MB

Lookup latency was equal within noise in these runs; the difference is the bytes, which matter for many concurrent readers and for PostgreSQL. Use the default for scan-heavy lakes, and 16 KiB blocks for lookup-heavy databases — cold point lookups are 10x cheaper in bytes there, and readahead brings their scans close to the default’s.

Readers hold a block twice while it is hot: in DuckDB’s buffer pool and in the external file cache, which share one eviction queue. Set memory_limit per reader process (several readers on one host each default to 80% of its memory).

Large reads need some PostgreSQL CPU, as for pgvfs:// (sizing). Every block written is new rows plus PostgreSQL WAL, and superseded versions stay until reaped: a checkpoint that rewrites X bytes holds about 2X for the reap grace.

Limits

  • One writer per database. A read-only attach in the writer’s own process reads published versions, like any other reader.
  • A superseded version stays readable for the reap grace only: a single transaction longer than that on an old version gets an error (attach again).
  • Spill files stay local: pgdb:// holds databases and their WAL files only, so a temp_directory under it fails.
  • No listing or globbing of pgdb:// paths. To copy a database in or out, use COPY FROM DATABASE.
  • Attaching needs a session-pooled connection to PostgreSQL (the writer’s lock is a session advisory lock); PgBouncer in transaction mode is unsupported for databases. pgvfs:// is unaffected.
  • The pgdb_refresh_interval setting is database-wide (SET applies to every connection of that DuckDB).

Storage

Five tables beside the pgvfs:// layout, created by the first database writer (dbschema.sql); the layout version does not change. Reader roles need SELECT on them, writer roles INSERT, UPDATE and DELETE and the pgvfs.db_ids and pgvfs.file_ids sequences.

  • pgvfs.dbchunks: 8,112-byte rows keyed by (file_id, chunk, version), one per 8 KB page. A write inserts a new version; nothing is updated in place, so readers of older versions are unaffected.
  • pgvfs.dbs: one row per database: the newest version, the writer epoch (fencing) and the reap horizon.
  • pgvfs.dbfiles: each file’s chunk set and size per version (the database and its WAL files).
  • pgvfs.dbgens: one row per version: when, and which chunks it wrote. Readers key their cache by it; the reaper walks it.
  • pgvfs.dbfileids: every file a database has had, for reaping and recovery.

Every read checks the reap horizon in the same statement as its rows, so a read races no reap.

Design

A pgdb:// database is the file DuckDB would write to disk, so DuckDB’s own storage, recovery and caching do almost all the work. pgvfs supplies what a local disk gives DuckDB, out of PostgreSQL primitives: durable, ordered fsyncs, one writer, and readers that each see one consistent state.

Why a version is safe to read. A version is everything written up to one of the writer’s fsyncs: exactly a state a crash could leave on a local disk, and DuckDB recovers from every such state, including “checkpointed, WAL not yet truncated”. DuckDB replays the WAL on read-only attach too. Within a version DuckDB writes new blocks only to free block ids, and frees modified blocks only after the new header is written, so a reader’s blocks stay put until it moves to a newer version. Per-statement MVCC alone would not do: a reader reads the header, the writer’s checkpoint then reuses those block ids, and block checksums verify content, not identity.

The writer. Attaching read-write takes pg_try_advisory_lock on a dedicated connection (a second writer fails at once), bumps the database’s epoch and, if the last writer died, deletes what it wrote but never published. Writes go to that connection in ordered batches of about 8 MB: binary COPY for chunks new to the version (about 2.5x an upsert’s rate), upserts for chunks rewritten in it. A chunk written in part stays in memory until it is complete or the next fsync. Every fsync publishes in one statement: the last chunks, file sizes, the chunks the version changed, and the new version number, all conditional on the epoch. A writer whose lock was lost (its connection dropped, a pooler in between) fails on its next write rather than diverging.

Readers and paths. Attaching the plain path resolves it to the newest version: pgdb://lake/sales.duckdb@42~3 (DuckDB derives the WAL path from it, so the WAL is read as of the same version). The token after ~ is unique in the process: DuckDB refuses a read-write and a read-only attach of the same path, and only the writer’s own attach may read its unpublished files. A path with an explicit @<version> is pinned.

Refresh relies on DuckDB behaviour that is public but not a documented contract, so the end-to-end test asserts it for every DuckDB build: ATTACH OR REPLACE swaps an attached database atomically and is a no-op for the same path (hence versions in the path); a running transaction keeps the database it first touched, so it finishes on its version; a replaced database closes when its last user goes; prepared statements rebind. The refresher is a thread holding only a weak reference to the DuckDB instance; a swap that fails stops following that database rather than retrying in a loop.

Cache keys that never go stale. Readers read through DuckDB’s CachingFileSystem under the key <file_id>@<version that last wrote this range>, found from the change lists in pgvfs.dbgens. The bytes under a key never change, so nothing is invalidated, the cache needs no validation, old keys age out with ordinary eviction, and the cache counts against memory_limit and shares DuckDB’s eviction queue with table data. A miss reads the key’s version, with the same reap check as any read.

Reaping. The writer deletes chunk versions superseded for longer than the reap grace, incrementally from the change lists, in batches. Every read checks the database’s reap horizon in the same statement, so a reader of an expired version gets an error, never wrong bytes. The grace protects standby readers too, without the primary knowing about them.

Chosen against:

AlternativeWhy not
Mutable rows in pgvfs.filesBreaks the rule that a file_id names the same bytes forever, which the pgvfs:// caches and reads rely on. Hence a separate scheme and tables.
A PostgreSQL snapshot per reader (pg_export_snapshot)Holds back vacuum cluster-wide for each attach’s lifetime, idle-in-transaction connections per reader, recovery conflicts on standbys.
The advisory lock as the only writer guardSilently released if its connection drops; meaningless behind a transaction pooler. The epoch check makes every publish safe regardless.
Reader pins as advisory locks, read by the reaperInvisible across standbys, 32-bit keys, a pin session per process. The grace plus the per-read horizon check covers primary and standby alike.
LISTEN/NOTIFY for refreshNOTIFY does not reach standbys; one cheap poll per interval works everywhere.
Merging partial chunks in SQL (overlay())Opaque SQL; merging in the writer is plain code, covered by the model test.
Chunks aligned to DuckDB’s block sizeBlock sizes vary (16 to 256 KiB); 8,112 bytes is the largest payload that keeps a row and its version inline in one page.

Not yet

  • Strong consistency (each transaction sees the newest version, at one round trip per transaction): readers lag by up to pgdb_refresh_interval.
  • Faster commits by synchronous_commit = off on the writer: PostgreSQL would lose only a suffix of commits, never reorder them, so every crash state is still a valid version; the last commits DuckDB reported may be lost.
  • One log instead of two: every commit is in DuckDB’s WAL and PostgreSQL’s.
  • Long-lived pinned versions beyond the reap grace (would need pins).
  • Prefetching scattered blocks: a cold index lookup reads its blocks one round trip at a time.

Full-text search

The pgvfs extension also carries tantivy search indexes: build them from any query, and search them with BM25 ranking. Stored in pgvfs, they live in PostgreSQL alongside the lake, with its secret, backup and roles, and no search cluster. They use only DuckDB’s filesystem, though, so they work as well on local files or object storage.

As in Quickwit, an index is made of splits. A split is an immutable tantivy index in a single file, at any path DuckDB can write: pgvfs://lake-fts/docs.tantivy, /data/docs.tantivy, s3://bucket/docs.tantivy. The extension builds, searches and drops splits. Everything else is SQL: which splits make up an index, when to add one (say, after each load), and when to merge them. A DuckLake table makes a good list of splits, versioned with the data it indexes.

Function
tantivy_index(index, schema, doc [, options])aggregate: builds a split from its rows
tantivy_search(index, query, ...)table: ranked hits from one split or a list of them, searched in parallel
tantivy_count(index, query, ...)scalar: exact total (or capped, or distinct values), without loading stored documents
tantivy_aggregate(index, query, aggs, ...)scalar: tantivy’s aggregations as JSON, merged across splits
tantivy_merge(splits, target, ...)scalar: native compaction, without re-indexing documents
tantivy_drop(index)scalar: removes a split
tantivy_create_index, tantivy_match_bm25macros that work like DuckDB’s fts

Quick start

Index the title and body columns of lake.docs under its key id, from a process whose role can write to pgvfs:

FROM tantivy_create_index('pgvfs://lake-fts/docs.tantivy', 'lake.docs', 'id', ['title', 'body']);

On any reader, score rows (NULL when they don’t match):

SELECT id, title, score
FROM (
    SELECT *, tantivy_match_bm25('pgvfs://lake-fts/docs.tantivy', id, 'small cats') AS score
    FROM lake.docs
)
WHERE score IS NOT NULL
ORDER BY score DESC;

or fetch only the top hits and join them to the table, which avoids scanning it:

SELECT d.*, s.score
FROM tantivy_search('pgvfs://lake-fts/docs.tantivy', 'small cats', '{"top_k": 10}') s
JOIN lake.docs d ON d.id = (s.doc->>'_key')::INTEGER
ORDER BY s.score DESC;

Keep splits outside the lake’s DATA_PATH, for example in their own volume. DuckLake’s ducklake_delete_orphaned_files deletes files under DATA_PATH that its catalog doesn’t track.

A split never changes, so to refresh this one, drop it and build it again: SELECT tantivy_drop('pgvfs://lake-fts/docs.tantivy'). Other readers see the new split within 10 s. To keep searching without a gap, build new splits instead, as below.

Keeping an index current with DuckLake

Index each load’s changes as a new split. DuckLake’s table_changes gives the rows inserted or updated between two snapshots. Each row’s rowid stays the same across updates and compaction, and its snapshot_id is the snapshot that wrote the current version. Together they let a search keep only hits on current rows. To exclude stale versions before top-k, counting and aggregation, record deletes and updates as invalidations too.

-- once: splits, invalidations, and a durable indexing watermark
CREATE TABLE lake.docs_splits (path VARCHAR, snapshot BIGINT, docs BIGINT);
CREATE TABLE lake.docs_fts_dead (rowid BIGINT, snapshot BIGINT);
CREATE TABLE lake.docs_fts_state (snapshot BIGINT);
BEGIN;
SET VARIABLE s1 = (SELECT id::BIGINT FROM lake.current_snapshot());
INSERT INTO lake.docs_splits
SELECT path, getvariable('s1'), tantivy_index(path,
    '[{"name": "rowid", "type": "i64", "options": {"stored": true, "indexed": true, "fast": true}},
      {"name": "body", "type": "text", "options": {"indexing": {"record": "position", "tokenizer": "en_stem"}}}]',
    c)
FROM (SELECT 'pgvfs://lake-fts/docs/' || getvariable('s1') || '.tantivy' AS path, rowid, * FROM lake.docs) c
GROUP BY path;
INSERT INTO lake.docs_fts_state VALUES (getvariable('s1'));
COMMIT;

-- after each load
BEGIN;
SET VARIABLE s0 = (SELECT snapshot FROM lake.docs_fts_state);
SET VARIABLE s1 = (SELECT id::BIGINT FROM lake.current_snapshot());
CREATE OR REPLACE TEMP TABLE docs_changes AS
FROM lake.table_changes('docs', getvariable('s0') + 1, getvariable('s1'));

INSERT INTO lake.docs_fts_dead
SELECT DISTINCT rowid, snapshot_id FROM docs_changes
WHERE change_type IN ('delete', 'update_preimage');

INSERT INTO lake.docs_splits
SELECT path, getvariable('s1'), tantivy_index(path, '<the same schema>', c)
FROM (
    SELECT 'pgvfs://lake-fts/docs/' || getvariable('s1') || '.tantivy' AS path, *
    FROM (
        SELECT * FROM docs_changes
        WHERE change_type IN ('insert', 'update_postimage', 'delete')
        QUALIFY row_number() OVER (PARTITION BY rowid ORDER BY snapshot_id DESC) = 1
    ) latest
    WHERE change_type IN ('insert', 'update_postimage')
) c
GROUP BY path;
UPDATE lake.docs_fts_state SET snapshot = getvariable('s1');
COMMIT;

table_changes reads only the files that changed, so a load’s split costs in proportion to the load, not the table. Choose the latest event including deletes, then discard deleted rows: otherwise a row inserted and deleted inside the range would incorrectly enter the split. The watermark advances even on delete-only loads. Retain the change feed until each range is processed.

Publish the splits, invalidations and watermark together. Files themselves are not transactional: if the SQL transaction fails after writing a split, drop the unlisted file before retrying.

Readers can search the latest indexed snapshot coherently, even while the next load is being indexed. Each split gets the IDs invalidated after its snapshot and up to the indexing watermark:

BEGIN;
SET VARIABLE r = (SELECT snapshot FROM lake.docs_fts_state);
SELECT d.*, h.score
FROM lake.docs_splits s
CROSS JOIN tantivy_search(s.path, 'small cats',
    '{"top_k": 10, "fast": ["rowid"], "exclude_field": "rowid"}',
    (SELECT list(rowid) FROM lake.docs_fts_dead x
     WHERE x.snapshot > s.snapshot AND x.snapshot <= getvariable('r'))) h
JOIN lake.docs d AT (VERSION => getvariable('r')) ON d.rowid = (h.doc->>'rowid')::BIGINT
ORDER BY h.score DESC
LIMIT 10;
COMMIT;

top_k applies per split, so it bounds each split’s work while LIMIT picks the overall best. Exclusion happens before that bound: a dead hit cannot crowd out a live one. Sum tantivy_count(s.path, query, options, dead_ids) with the same exclusions for the exact total. Without exclusions, joining live rows after top-k can underfill a page and cannot give correct totals.

Keep the indexed data snapshot available if joining its rows as above. Each split scores with its own term statistics by default. A list argument searches several splits in one call; global_stats uses their combined statistics.

Native compaction. Merge splits as they pile up. When rowids occur in old and new versions, first prune each source with its own dead set, then merge the live segments. Neither step rescans the table or re-indexes documents:

BEGIN;
SET VARIABLE r = (SELECT snapshot FROM lake.docs_fts_state);
CREATE TEMP TABLE replaced AS SELECT *, uuid()::VARCHAR AS token FROM lake.docs_splits;
CREATE TEMP TABLE cleaned AS
SELECT 'pgvfs://lake-fts/staging/' || s.token || '.tantivy' AS path,
       tantivy_merge([s.path], 'pgvfs://lake-fts/staging/' || s.token || '.tantivy',
           '{"exclude_field": "rowid"}',
           (SELECT list(rowid) FROM lake.docs_fts_dead x
            WHERE x.snapshot > s.snapshot AND x.snapshot <= getvariable('r'))) AS docs
FROM replaced s;
CREATE TEMP TABLE merged AS
SELECT 'pgvfs://lake-fts/docs/compact-' || getvariable('r') || '.tantivy' AS path,
       getvariable('r') AS snapshot,
       tantivy_merge((SELECT list(path) FROM cleaned),
           'pgvfs://lake-fts/docs/compact-' || getvariable('r') || '.tantivy') AS docs;
DELETE FROM lake.docs_splits;
INSERT INTO lake.docs_splits FROM merged;
COMMIT;
SELECT tantivy_drop(path) FROM (SELECT path FROM replaced UNION ALL SELECT path FROM cleaned);

The output must have a new path; use a unique one for another compaction at the same watermark. On object storage, delay dropping replaced files until old readers have finished. Native merging does not deduplicate keys automatically. Retain invalidations while an active split still needs them. You can instead rebuild from a longer change-feed range, using the same latest-event rule.

What a merge costs. tantivy_merge copies each source into a local temporary index (TMPDIR), merges all its segments on one thread, and writes the result. Four splits of 460 MB (4 million documents, 60% excluded), with TMPDIR on disk:

copy inmergewrite outtotalre-index the 1.6M live documents from source
local files0.6 s11.5 s0.3 s12.7 s21.6 s
pgvfs, PostgreSQL on the same host4.4 s10.9 s1.7 s17.2 s20.1 s

The copy is a quarter of the time through pgvfs at most, so reading the sources in place would not remove the cost: the merge is. It is one thread, so it is the part a busy host slows most, while indexing from source uses every core. When most of the documents are excluded, compare the two on your data (bench/search.py --merge does).

Large tables. A tantivy split holds at most 2³¹ documents, and a smaller one builds, merges and opens faster. Partition big builds: GROUP BY builds one split per group, so a path such as 'pgvfs://lake-fts/docs/' || s1 || '-' || rowid % 16 || '.tantivy' gives 16 splits per build. Merge the per-load splits into larger ranges over time, keeping a few dozen in all, since a query searches each one.

The functions

tantivy_index(index, schema, doc [, options])

An aggregate that builds a split at index, a path DuckDB can write, from one document per row, and returns the number of documents. The split is written as the aggregate finishes. The arguments are per row: with GROUP BY, each group builds its own split, and every row of a group must name the same index. No rows, no split. index must not exist yet (on pgvfs, the process needs a role that can write). A failed split write cleans up its target; already completed splits are not rolled back with the SQL statement.

  • schema is a tantivy schema as JSON: an array of field entries. Each has a name, a type (text, i64, u64, f64, bool, date, facet, bytes, json_object or ip_addr) and options (indexing, tokenizer, stored, fast, coerce and so on).
  • doc is a row/STRUCT (just pass t; it is cast to JSON, which needs DuckDB’s json extension, autoloaded in standard builds), or a JSON object as text. Fields not in the schema and nulls are skipped; an array gives a field several values. coerce on a text field indexes numbers as text.
  • options, as JSON:
    • tokenizers: named analyzers to use in the schema, besides tantivy’s built-in default, raw, en_stem and whitespace. Each is a tokenizer ("simple", "whitespace", "raw", {"ngram": {"min": 2, "max": 3, "prefix_only": false}}, or {"regex": "..."}) and a list of filters. A filter is "lowercase", "ascii_folding", "alpha_num_only", {"remove_long": 40}, {"stop_words": "english"} (or a list of words), or {"stemmer": "english"}. They are saved with the split, so readers use the same ones.
    • settings: tantivy’s index settings: docstore_compression ("lz4" or "none") and docstore_blocksize.
    • memory_budget: the indexing memory in bytes (default 256 MB).
    • merge: merge into one segment (default true), so a search reads each term once.

Memory and disk of a build. Every group of a query is built at the same time, and each takes about memory_budget of heap (256 MB by default, plus about 10 MB; less for a group with less data than that) on top of DuckDB’s own memory. Before a build starts, tantivy_index checks the reservations of the builds open (groups × (memory_budget + 10 MB)) against memory_limit, and fails with a message that says what to change: lower memory_budget, build fewer groups per query, or raise memory_limit. The check counts tantivy’s reservations only, so leave room for DuckDB’s own memory, including a materialised source table. So

peak ≈ DuckDB’s memory + groups × (memory_budget + 10 MB)

for groups bigger than the budget. Measured on 2 million documents of about 440 bytes of split each, streamed from a query (DuckDB alone: 40 MB):

groups256 MB (default)64 MB16 MB
1300 MB120 MB
4780 MB320 MB
161,680 MB1,140 MB390 MB

The default stays below groups × 266 MB when groups hold less than the budget (16 groups of 125,000 documents used 100 MB each). A smaller budget only makes more, smaller segments to merge at the end: slower for one big group (12 s to 18 s here), not for many (14 s to 13 s). To build a large table in less memory, lower memory_budget or build fewer groups per query.

Each group also keeps its index in a local temporary directory (TMPDIR) until its split is written: about the size of the split, for all groups at once. On a tmpfs (often /tmp) that is memory too, so point TMPDIR at a disk.

Threads. A build’s indexing threads are DuckDB’s threads divided among the builds open (at most 8 per build), and each build has one merge thread. Earlier versions gave every build tantivy’s own default, about 21 threads: 256 groups started 5,392.

tantivy_search(index, query [, options [, exclude]])

Searches a path (VARCHAR) or a list of paths (VARCHAR[]). Returns score DOUBLE, doc JSON, the hit’s split path VARCHAR and highlight JSON per hit, best first; doc holds the hit’s stored fields, a value each or an array for several. score is NULL when the hits are ordered by a field (sort), highlight unless asked for (highlight). All arguments may be columns, so a lateral join searches every split in a table (FROM splits s CROSS JOIN tantivy_search(s.path, ...)), or runs a query per row. A NULL index or query finds nothing. The query uses tantivy’s query language: words, "phrases", +required and -excluded terms, field:term and ranges such as id:[10 TO 20]. A query starting with { is instead OpenSearch query DSL. options, as JSON:

OptionDefault
top_kevery hithits to return across the whole list of splits; use a bound on large indexes
offset0hits to skip before top_k, across all the splits. Documents are read only for the hits returned, so a deep page costs its scoring, not its position
collapsenonereturn only the best hit of each distinct value of this fast field (text, number, boolean or date; one value per document), best first. top_k and offset then count values. Documents without a value form one group
sortby scoreorder by a fast field instead: a number, a date or text, as "price" (ascending) or {"field": "price", "order": "desc"}. Documents without a value come last either way; score is NULL. Applies across all the splits (they must agree on the field’s type), with top_k and offset; not with collapse
highlightnone{"fields": ["body"], "max_chars": 150}: for these stored text fields, an HTML snippet of where the query’s terms match, as a JSON object by field in the highlight column (<b> marks the match, the rest is escaped; a field the query does not match is left out). Replace the tag in SQL for another
fieldsevery indexed text fieldthe fields to search for terms that name none
conjunctivefalseevery term must match
strictfalsefail on query syntax errors instead of dropping what tantivy cannot parse
faststored fieldsbuild doc only from these fast fields, without reading stored documents; [] returns {}
exclude_fieldnonean i64 or u64 fast field whose values are in exclude; exactly one value on every document
global_statsfalseuse combined BM25 statistics; needs the same schema across splits. As in tantivy, statistics include excluded (deleted) documents until the splits are merged
ignore_unmappedfalsein query DSL, a field a split lacks matches nothing instead of failing the call (the other half of searching splits built with different schemas)

exclude is a portable roaring bitmap BLOB (32-bit bitmap or 64-bit treemap), or a BIGINT[] of dead IDs. NULL means no exclusions. Signed IDs use their unsigned 64-bit bit pattern in a treemap. Read a bitmap path with DuckDB itself:

FROM tantivy_search('pgvfs://lake-fts/docs.tantivy', 'roof',
    '{"top_k": 10, "exclude_field": "rowid", "fast": ["rowid"]}',
    (SELECT content FROM read_blob('pgvfs://lake-fts/dead.roaring')));

Exclusion works like tantivy’s own deletes. The first call with an exclusion reads the key column of each split once and builds an alive bitset for its segments; the split keeps the last four (by exclude_field and the set), so later calls with the same set skip dead documents as tantivy’s deletes are skipped: they never crowd out top_k, and never reach a count or an aggregation. A set that changes on every call pays that first read each time: about 20 ms per million documents of a split (splits build in parallel).

One exclusion set applies to every split in a list. If a rowid is dead in an old split but live in a new one, use per-split exclusions with lateral calls as above, or index a unique document-version key. Do not globally exclude that rowid from both versions.

Fast projection keeps grouping and typed output in SQL. For example, with roof_id and building_id declared fast in your schema:

SELECT doc->>'building_id' AS building,
       arg_max((doc->>'roof_id')::BIGINT, score) AS best_roof
FROM tantivy_search(['s3://bucket/part-1.tantivy', 's3://bucket/part-2.tantivy'],
    'roof', '{"fast": ["roof_id", "building_id"]}')
GROUP BY building;

A list of splits is searched in parallel, up to DuckDB’s threads at a time, and merged: hits by score (equal scores in list order, then document order), so a page does not depend on how many threads ran. One call over the list also gives global_stats, a merged top_k and, for deep pages, offset: calling once per split in a lateral join gives all of these up.

tantivy_count(index, query [, options [, exclude]])

The exact number of matches, as BIGINT, across one split or a list of them. Uses the same query, options and exclusions as search, but ignores top_k and reads no stored documents. A NULL index or query returns NULL; an empty list returns zero. Two options change what it counts:

Option
limitstop counting after limit + 1 matches and return that, so “more than 100” costs a hundred matches, not millions: {"limit": 100} returns 101 when there are more
distinctthe exact number of distinct values of a fast field among the matches (text, number, boolean or date; one value per document; documents without one are not counted), merged across splits. Memory is proportional to the distinct values. The cardinality aggregation is approximate but much faster and smaller; use it when an estimate will do

offset, collapse and distinct/limit belong to one call each: a search rejects limit and distinct, a count rejects offset and collapse, and limit cannot be combined with distinct.

tantivy_aggregate(index, query, aggs [, options [, exclude]])

Tantivy’s aggregation module, directly: Elasticsearch-shaped aggregation requests and results as JSON. Includes terms, cardinality, stats, ranges and histograms; fields must be fast. Intermediate results merge natively across splits. Exclusions apply first; top_k does not limit aggregations. Cardinality is approximate. Tantivy’s default memory and bucket limits apply across the request.

SELECT tantivy_aggregate(['part-1.tantivy', 'part-2.tantivy'], 'roof',
    '{"buildings": {"terms": {"field": "building_id", "size": 20}},
      "unique_roofs": {"cardinality": {"field": "roof_id"}}}');

tantivy_merge(splits, target [, options [, exclude]])

Copies the source segments into a local temporary index and merges them into one split at target, returning the documents kept. splits is a nonempty VARCHAR[]; schemas and custom tokenizer definitions must agree. The first source’s index settings are used. Sources are unchanged, and target must not exist. The temporary index needs disk for the sources and merged output.

Options: memory_budget (default 256 MB) and exclude_field. Exclusions have the same formats as search, and delete documents during compaction. They need only a fast integer key, not an indexed one. A merge does not deduplicate IDs; the caller decides which versions are live.

Query DSL

Pass the query object directly, or wrap it as {"query": ...} (not a whole OpenSearch search request with size, _source, etc.):

FROM tantivy_search('docs.tantivy',
    '{"bool": {
        "must": {"match": {"body": {"query": "roofs", "fuzziness": "AUTO"}}},
        "filter": {"term": {"building_id": "b17"}},
        "must_not": {"term": {"status": "demolished"}}
    }}', '{"top_k": 10, "fast": ["roof_id", "building_id"]}');

This is a subset, compiled to tantivy queries, not an OpenSearch server. Unsupported query types and parameters fail rather than being silently ignored:

QuerySupported parameters
boolmust, should, must_not, filter, minimum_should_match
matchquery, operator (and/or), minimum_should_match, fuzziness, fuzzy_transpositions
match_phrase, match_phrase_prefixquery; slop for phrases, max_expansions for phrase prefixes
multi_matchnamed fields (with ^boost), best_fields, most_fields, phrase, phrase_prefix; applicable match parameters, tie_breaker
term, termsexact typed values, without analysis; term takes case_insensitive for text
prefix, wildcard, regexptext value, case_insensitive; wildcard *, ?, \ escapes; a regular expression matches a whole term
fuzzytext value, fuzziness, transpositions
more_like_thislike: a text or a list of texts (not a document of the index: read its text with SQL and pass that); fields (text fields; default all), min_term_freq, max_query_terms, min_doc_freq, max_doc_freq, min_word_length, max_word_length, boost_terms, stop_words (compared with the analyzed term: cat, where the index stems)
existsfield: a fast field, or an indexed text field (slower: any term in its dictionary), or a JSON field (any path in it)
rangegt, gte, lt, lte
constant_score, dis_maxfilter; queries and tie_breaker, respectively
match_all, match_none
query_string, simple_query_stringquery, named fields, default_field, default_operator; tantivy syntax, lenient for simple_query_string

boost is supported in query parameter objects. minimum_should_match accepts integers, negative integers, percentages and negative percentages, not conditional expressions. Fuzziness is 0, 1, 2 or AUTO[:low,high]. bool.filter and must_not do not contribute scores. Numeric values, booleans, RFC 3339 dates (or epoch milliseconds), facets and IP addresses are typed by the schema. Regular expressions and scoring follow tantivy’s behaviour.

JSON fields. A path inside a json_object field is a field name: {"term": {"attributes.contractor": "acme"}}. term, terms, match (analyzed by the JSON field’s tokenizer), match_phrase, range (fast, numeric bounds typed by their JSON literal) and exists accept paths, as do multi_match fields. So do prefix, wildcard, regexp and fuzzy (and match’s fuzziness), which see only the strings at their own path. Free-form attributes then need no field per attribute in the schema, and splits with different attribute sets share one schema. match_phrase_prefix on a path fails.

Case. Term-level queries (term, terms, prefix, wildcard, regexp) are not analyzed, as in OpenSearch: on a field whose tokenizer lowercases, {"prefix": {"title": "Big"}} finds nothing. case_insensitive: true matches the indexed terms in any case. The value of a prefix or a case-insensitive term is literal; in regexp it is an expression.

What is not here. Tantivy’s tweak_score takes code, which JSON cannot carry: re-rank in SQL, over score and the fast fields fast projects.

Missing fields. A field the schema lacks is an error, so a typo cannot quietly match nothing. ignore_unmapped (a search option) makes it match nothing instead, as in OpenSearch, for splits built with different schemas.

tantivy_drop(index)

Removes the split at index, and returns whether there was one (on pgvfs, from a role that can write). A scalar function, so SELECT tantivy_drop(path) FROM ... drops many.

The fts-style macros

tantivy_create_index(index, input_table, input_id, input_values, ...) builds a split of the input_values columns of input_table, keyed by input_id (stored as _key). It returns the number of rows indexed. Named parameters:

ParameterDefault
stemmer'porter''porter' (English), another of tantivy’s 18 languages ('french', 'german', …), or 'none'
stopwords'english'a language, or 'none'. Tantivy’s English list is Lucene’s 33 words, not fts’s 571
strip_accentstruefold accents (café matches cafe)
lowertruelowercase

tantivy_match_bm25(index, input_id, query_string, fields := NULL, conjunctive := false) returns a row’s score, or NULL. fields restricts the search to some columns ('title, body'), and conjunctive requires every term. The query uses tantivy’s lenient query-string parser, or the DSL above. BM25 uses tantivy’s k1 = 1.2 and b = 0.75.

Both macros are short SQL over the functions above (CREATE_INDEX_MACRO and MATCH_BM25_MACRO in extension/src/tantivy_functions.cpp). Copy and adapt them for other schemas. DuckLake can store your own macros with the lake.

How it works

  • Builds use tantivy’s own index directory in a local temporary one (TMPDIR), with indexing threads shared out from DuckDB’s threads (see memory and disk above), so a build needs free local disk about the size of the split. The finished index (merged to one segment) is written to index as a single file: its files back to back and a small footer naming them.
  • Searches read a split through DuckDB’s filesystem, opening it once per DuckDB database and keeping its term dictionaries in memory. A search then reads query postings and the requested stored or fast fields. Counts and aggregations skip the document store. An open split is checked for a change (size, modification time, version tag) at most every 10 s, so another process’s rebuild at the same path shows up within that.
  • Parallel searches. A call over a list of splits searches them on up to threads threads (each split reads its file through its own handle), then merges. Documents of the hits are read in parallel too, one thread a split.
  • Exclusions are alive bitsets built once per split and set, as above. On 4 million documents in 4 splits, 60% of them excluded, a count of 2.8 million matches takes 9 ms (1.4 ms with nothing excluded), a top 10 takes 6 ms (5 ms), and a new set costs 25 ms the first time (bench/search.py measures these).
  • Drops remove the file. On pgvfs its rows stay for the reap grace (PGVFS_REAP_GRACE_S, 10 minutes by default), so searches already running finish; object stores delete at once, so there drop replaced splits a while after swapping them out.
  • Storage. On pgvfs a split is an ordinary file; the storage layout is unchanged.

Roadmap

What a real workload (a 12.5M-document index in 13 splits, read through pgvfs, replaced documents excluded by key) still lacks.

A faster merge. tantivy_merge is one thread (see “What a merge costs”). Merging halves of the list at once and then their results would use more cores at the price of a second pass; measure it on the real splits before building it.

A community release that includes tantivy. The signed INSTALL pgvfs FROM community build predates the search functions, so projects that need them load a local build with allow_unsigned_extensions. The next release, and its submission to the community repository, fixes that.

Vector search

Nearest-neighbour search over a large lake needs no extension: an IVF (inverted file) index is a column. Cluster the vectors once, store each row’s nearest centroid as cluster, and sort the table by it. A query then probes its few nearest centroids, and DuckLake reads only those clusters’ row groups, from their min/max statistics, before ranking them exactly.

-- centroids: k-means on a sample (bench/ivf.py does this in SQL), then
CREATE TABLE lake.centroids (c INTEGER, v FLOAT[]);

-- every row's nearest centroid, the table sorted by it
CREATE TABLE lake.vecs AS
SELECT d.id, arg_min(c.c, list_distance(d.v, c.v)) AS cluster, d.v
FROM data d, lake.centroids c
GROUP BY d.id, d.v
ORDER BY cluster;
ALTER TABLE lake.vecs SET SORTED BY (cluster);

-- the 10 nearest rows, probing the 4 nearest clusters
SELECT id, list_distance(v, $q) AS distance
FROM lake.vecs
WHERE cluster IN (SELECT c FROM lake.centroids ORDER BY list_distance(v, $q) LIMIT 4)
ORDER BY distance
LIMIT 10;

DuckLake has no fixed-size ARRAY type, so vectors are FLOAT[] lists and distances list_distance (or list_cosine_distance). New rows take their nearest centroid as they load, the same arg_min against lake.centroids. Retrain and rewrite when the data drifts far from the centroids.

How well it works

bench/ivf.py checks this on synthetic data: 200,000 vectors of 64 dimensions in 256 blobs, 64 clusters (k-means on a 20,000-row sample), 2,048-row row groups, against exact search over the same lake. Recall@10 is over 50 queries; bytes are those a cold DuckDB read from PostgreSQL.

ProbesRecall@10, separate blobsRecall@10, overlapping blobsBytes read, share of exact
10.9980.512.5–2.7%
21.00.595–7%
41.00.6910–14%
81.00.7920–28%
160.8839%
320.9869%

Reads track the probes: the subquery prunes as well as a literal IN list. Recall depends on how clustered the data is. Real embeddings are clustered, nearer the first column than the second, but measure on your own, and pick the probes for the recall you need.

Recall and bytes trade off directly: on overlapping blobs, 4 probes give 0.69 recall for 10–14% of the bytes, and 16 give 0.88 for 39%. Clusters are the unit of I/O here. A graph index (HNSW, say) visits rows one at a time, and in pgvfs every read fetches at least one 8 KB chunk, so a search that visits hundreds of rows reads far more than a few probed clusters. That is an argument from the storage design, not a measurement: we have not benchmarked a graph index. Consider one only if the probes your recall needs read more bytes than a graph search would.

At scale

Pick the cluster count so a cluster spans a row group or two: for 10 billion rows and 8,192-row groups, about a million clusters of 10,000 rows. A query then reads tens of row groups whatever the table’s size. Finding the nearest of a million centroids is itself a scan of a million vectors per query; when that matters, two levels of centroids (coarse clusters of fine ones) keep it small, still in SQL.

How it works

This page covers pgvfs://, the files behind a DuckLake. Native DuckDB databases on pgdb:// have their own tables and design: see Databases.

Storage

A file is a sequence of 8,120-byte rows in one table, pgvfs.chunks, keyed by (file_id, row number). Each row fills one 8 KB page, stored inline without TOAST. pgvfs.files maps (volume, path) to the file’s file_id and size. Paths are pgvfs://<volume>/<path>: a volume ([a-z0-9][a-z0-9._-]{0,62}) is a namespace, so one database can hold several lakes. The layout is schema.sql and functions.sql, which writers keep current. It needs no PostgreSQL extensions; install lists the supported versions.

  • Files are immutable. A write streams rows under a new file_id with one binary COPY, and publishes the path in the same transaction when the file is closed. Overwriting a path publishes a new file_id. DuckLake itself never rewrites a path.
  • Reads are primary-key range queries, up to 8 MB each; bigger reads run their pieces in parallel on pooled connections. Rows stream straight into DuckDB’s buffer.
  • Deletes unpublish the path and queue the old file_id. Its rows stay for the reap grace (PGVFS_REAP_GRACE_S, 10 minutes by default) so queries that already opened it can finish, then a writer removes them in the background.

Storage layout versions

The layout has a version, stored in pgvfs.layout; this release reads and writes v2. pgvfs refuses a database with any other version rather than misread it. Layout changes are listed in the changelog and, during 0.x, bump the minor version. There is no in-place migration yet: to move a lake to a new layout, copy its tables with DuckDB into a lake in a new database (CREATE TABLE new.t AS FROM old.t).

Writers and readers

  • Any process whose role can write may write. There is no writer lock. Each file is written in one transaction, so its rows and its path are published together when the file is closed, or not at all. Writers in different processes and connections run in parallel.
  • Two writers of one path: the second to publish fails with “published by another writer at the same time; this write was rolled back”, and nothing of its file is kept. DuckLake never rewrites a path, so a lake does not hit this.
  • Readers need only SELECT. They take no locks and run no DDL, so they work with SELECT-only roles and on read replicas. Before the schema exists they see an empty store.
  • Roles decide who writes. A SELECT-only role’s write, drop or reap fails with PostgreSQL’s permission error. See roles.
  • Each process’s first write to a database installs the schema if it is missing (for a role that may create it), and replaces the functions when their version changed and the role owns them. A transaction-scoped advisory lock serialises this, so concurrent first writes do the work once. A standby refuses writes.

Operations

  • Dropping a volume. From any writer, SELECT pgvfs_drop_volume('lake-fts'); unpublishes all files in exactly that volume and returns their count. It invalidates that database’s file and search caches; rows are reaped after the usual grace. This is destructive: DuckLake’s catalog is not removed, so do not drop an active lake’s data volume.
  • Reaping and grace. Each writer reaps at most once a minute while it writes, and the first time it connects. A try-lock serialises reaps across processes, so a reap that finds another running skips. Each reap removes up to 65,536 rows (about 512 MB) and the rest waits for the next. A deleted file’s rows stay readable for PGVFS_REAP_GRACE_S seconds (600 by default).
  • Grace bounds reads. A read still running when its file’s rows are reaped fails, with a message that names PGVFS_REAP_GRACE_S. In a run that paused a reader after an overwrite, a reader that resumed at 9 minutes (grace 10) read on, and one that resumed at 11 minutes failed on its next batch.
  • Reaping by hand. If writes stop for a long time, reap from cron or a scheduler, as a role that can write: SELECT pgvfs.reap();. It uses a grace of 10 minutes; pass another, for example SELECT pgvfs.reap(interval '1 hour');, to match PGVFS_REAP_GRACE_S.
  • Vacuum. Reaping deletes whole files’ rows, so new databases set pgvfs.chunks to autovacuum at 1% dead rows instead of 20%. Databases created before this setting existed can apply it themselves: ALTER TABLE pgvfs.chunks SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);
  • Read replicas. Readers (pgvfs and DuckLake’s catalog) work on streaming standbys; attach with READ_ONLY. Catalog and data share one database, so a replica never sees a snapshot that names files it doesn’t have yet. When the primary reaps, a long query on a standby can be cancelled by a recovery conflict: set hot_standby_feedback = on on the standby. A writer that connects to a standby is refused.
  • Connection loss. A read that hits a connection which died (a PostgreSQL restart, a network drop) retries once on a fresh one. A write that loses its connection fails and publishes nothing; the next write connects again.
  • Poolers. Connect directly, or through a pooler in session mode. Only direct connections are tested.

Storage costs

Measured on PostgreSQL 18, writing, dropping and reaping 256 MB files in a loop, with four live at a time:

  • WAL on write: about 1x the bytes. A 256 MB file wrote 262 MB of WAL.
  • WAL on reap: about 1x, once a checkpoint has passed. PostgreSQL writes a page’s full image the first time it changes after a checkpoint, and reaping changes the pages a write filled. Reaping 256 MB after a checkpoint wrote 262 MB of WAL; with no checkpoint in between, it wrote 2 MB.
  • Vacuum adds full-page images. Vacuum writes a full page the first time it touches a page after a checkpoint, so its WAL varied from 1 MB to 525 MB per cycle. Checkpoints set how often that happens: max_wal_size and checkpoint_timeout. On Aurora, which bills I/O operations, the same writes are billed too (not priced here).
  • Peak heap is live data, plus the files dropped within the grace, plus space that a long transaction holds. Freed pages are reused: with the grace set to zero, the heap stopped growing after a few cycles, at 1.25x the live data. A nonzero grace keeps dropped files’ rows until it ends, so the heap holds them meanwhile. A transaction that stays open (a long write, or a reader holding an old snapshot) stops vacuum from freeing the pages of files dropped during it. They stay in the heap afterwards, since plain VACUUM does not return space to the operating system.

Caching

  • DuckDB’s file cache. file_id is the cache’s version tag, so DuckDB keeps file bytes in memory across queries and they can never be stale. Warm queries don’t reach PostgreSQL for data at all.
  • Parquet footers. Loading pgvfs turns on DuckDB’s parquet_metadata_cache (off by default), so footers aren’t re-parsed on every query. It applies to every Parquet file the process reads: a process that rewrites local Parquet files in place while reading them should SET parquet_metadata_cache = false.
  • External file cache. DuckDB 2.0 caches only files that look remote (http://, s3://, …); loading pgvfs also turns on cache_local_files there, so pgdb:// reads are cached (they go through pgdbc:// paths) and local files are cached too. 1.5.6 has no such setting.
  • File opens. pgvfs remembers each path’s file for 10 s per DuckDB database. DuckDB re-opens files to check them (DuckLake’s delete files on every query), and this answers those checks without a round trip. This process’s own writes drop the entry at once; another process’s rewrite of a path shows up within 10 s.

Credentials

pgvfs finds PostgreSQL credentials the same way DuckDB’s postgres extension, and therefore DuckLake’s catalog, does. One secret can serve both. For each path, pgvfs uses the first of these that exists:

  1. A postgres secret named pgvfs_<volume>, which gives that volume its own database: CREATE SECRET pgvfs_lake (TYPE postgres, ...) for pgvfs://lake/. DuckDB’s postgres secrets keep no SCOPE, so the name is what routes a path.
  2. The postgres secret named by SET pgvfs_secret = 'name'. Pair it with ATTACH ... (META_SECRET 'name').
  3. Otherwise the PGVFS_URL environment variable (a URL or key=value string).
  4. Otherwise the unnamed default postgres secret, which DuckLake also uses.

Each database gets its own pool, so one DuckDB process can read and write volumes in several databases. Secrets keep the password redacted, and no setting holds one. Connection options pgvfs’s client doesn’t support (passfile, sslrootcert, service, RDS IAM) are rejected rather than ignored.

TLS: a remote server needs sslmode=require. Certificates are checked against the system CAs (on Alpine, install ca-certificates) plus PGVFS_DB_CA_FILE. PGVFS_DB_ALLOW_PLAINTEXT=true allows plaintext on an isolated network; local servers may always use it.

Roles

  • Readers: USAGE on schema pgvfs and SELECT on its tables. ALTER DEFAULT PRIVILEGES can grant these before the schema exists.
  • Writers: the same, plus INSERT, UPDATE and DELETE on its tables and USAGE on its sequences. The first write also needs CREATE on the database to install the schema, after which the role that installed it owns the functions and replaces them on upgrades. A writer that does not own them uses the ones installed. For a writer role that is not the owner:
GRANT USAGE ON SCHEMA pgvfs TO etl;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA pgvfs TO etl;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA pgvfs TO etl;

pgvfs never shares a database with the pgvs3 S3 gateway: each refuses a database holding the other’s schema.

Configuration

SettingDefault
pgvfs_<volume> postgres secretnonecredentials for one volume’s paths
SET pgvfs_secret = 'name'the default postgres secretcredentials for all other paths
PGVFS_URLcredentials, if no secret is named
PGVFS_REAP_GRACE_S600seconds a deleted file’s rows (and a superseded pgdb:// version) stay readable
SET pgdb_refresh_interval = s10seconds between pgdb:// readers’ checks for a newer version (0 off)
PGVFS_POOL_MAXmax(8, 2 × DuckDB threads)PostgreSQL connections per DuckDB database
PGVFS_POOL_MIN4connections kept open
PGVFS_IO_THREADSDuckDB threadsI/O threads per DuckDB database
PGVFS_OPEN_CACHE_S10seconds to remember a path’s file (0 off)
PGVFS_DB_CA_FILEextra CA certificates (PEM)
PGVFS_DB_ALLOW_PLAINTEXTfalseallow plaintext to a remote server

SELECT pgvfs_stats() returns this process’s counters as JSON: opens and cache hits, reads, bytes, time spent in pgvfs, and range queries sent; for pgdb://, reads and bytes sent to PostgreSQL (db_reads, db_read_bytes, db_pieces), bytes read through DuckDB’s external file cache (db_cache_bytes), versions published (db_publishes), and reads answered by readahead (db_readahead_hits) with the bytes it fetched ahead (db_readahead_bytes). They are cumulative, so diff two samples.

Performance

Weekly benchmark

Run 2026-11-16 at commit 86c1a991f181 (just bench city): Overture Houston, 3 readers each running 30 area and attribute queries per pass, on a 4-core GitHub runner; lake layout zstd V2, 8192-row groups, 64MB files. Shared hardware, so read it as a trend, not a score. Full history: history.jsonl.

PassQueries/sp50p95PostgreSQL busy
cold68.123.6 ms120.0 ms41%
warm (median)116.313.4 ms72.3 ms13%
new params100.118.1 ms71.9 ms27%

Passes: cold is the first pass after PostgreSQL restarts with its files evicted from the operating system’s page cache, in new DuckDB processes; warm repeats the same queries; new params runs fresh random queries on warm processes. ROUNDS repeats the restart, eviction and passes; the lines of each round carry its round.

Sizing

PostgreSQL: about one core per four reader cores. Per query, readers spend about 4× the CPU PostgreSQL does (decoding Parquet, building results). With DuckDB’s cache warm, PostgreSQL mostly answers DuckLake’s catalog queries. In every run here, 3 PostgreSQL cores were 3–50% busy serving 13 reader cores.

Readers: DuckDB gives a query at most one thread per row group it reads, and each thread fetches its row group’s columns one after another. So N reader cores can be N single-threaded readers (the most throughput) or fewer readers with more threads (lower latency). pgvfs sizes its I/O threads and connection pool from each DuckDB’s threads setting.

Readers × threads (13 cores)Houston warm q/sp50p95100M hits warm q/sp50p95
13 × 137121 ms88 ms7646 ms440 ms
6 × 234611 ms40 ms4936 ms202 ms
4 × 33029 ms28 ms
1 × 131376 ms11 ms

For many small lookups, 2 threads per reader halves latency for about 7% less throughput. For wide scans it costs about 35%.

Fewer threads also lower peak memory on an aggregation that would not fit: each thread aggregates into its own hash table, so a query grouping on millions of distinct values can approach threads × groups of memory (DuckDB on dimension tables). Narrower group keys shrink each entry but not the count, and data clustered by the group key helps too.

Reference results

All on one 16-core laptop, DuckDB 1.5.6, PostgreSQL 18 in a container. Loading data has the layout measurements. These results predate the page-cache eviction in the benchmark’s cold passes, so their cold numbers had the operating system’s cache warm; the weekly history is measured that way. Absolute numbers moved by up to 1.7× with the laptop’s other load (warm Houston ran at 390–660 queries/s on the same day), so compare rows within a table: those runs alternated under the same conditions.

Compression and the footer cache. LZ4 instead of snappy, plus the Parquet footer cache pgvfs now turns on, took 13 Houston readers from 292 to 415 queries/s (p50 28.5 → 18.4 ms). The benchmarks now load with zstd and Parquet V2, which read 36% fewer bytes but served 25–35% fewer queries/s than LZ4 on Houston. The weekly history records each run’s layout; the other results on this page used LZ4.

Against an S3 gateway. Full ClickBench (100M rows) through DuckDB’s httpfs and pgvs3, a PostgreSQL-backed S3 gateway, against pgvfs, with a fresh DuckDB per query: geomean 430 ms vs 371 ms, the same when warm (229 ms). Short queries gain most, because the HTTP hop and revalidation are gone. Records.

Where pgvfs’s time goes. On a cold pass pgvfs is about 30% of query time (Houston) and most of it on a 100M-row wide table; on warm passes it’s 0%. A read costs mostly its bytes: alone, 8 KB takes 0.11 ms and 115 KB 0.2 ms, of which PostgreSQL executes 0.01–0.03 ms. Under load the rest is queueing for CPU.

Schema shape. The layout decides which bytes a query fetches; column types decide what it does with them. DuckDB measures the same timestamp column at 0.9 s aggregated as TIMESTAMP against 3.9 s as VARCHAR (its schema guide), and DuckLake takes only NOT NULL, so unlike a plain DuckDB table there are no keys or indexes to declare. Replacing long repeated strings with narrow integer keys is the change that shrinks a lake’s bytes rather than just its read set (dimension tables); Loading data has the pattern. Not measured here.

Tried, and not worth it:

  • Block read-ahead (fetch 254 KB blocks, cache them): 30–38% slower cold. DuckDB already merges nearby reads, so only half the extra bytes were used.
  • Index scans for small reads: PostgreSQL’s execution was already 0.01–0.03 ms.
  • Pipelining a read’s pieces: every read measured was a single piece.

Considered, and not done:

  • Sharding one file’s COPY across connections: one stream already writes about 300 MiB/s, and more than four streams were slower on an 8-core PostgreSQL (loading). Committing shards before the publish would also leak rows from a crashed writer that nothing points at.
  • Hedged retries: tails come from CPU queueing, so a hedge adds load exactly when PostgreSQL is saturated.
  • wal_compression: it compresses full-page images only, and the pages hold already-compressed Parquet: CPU for nothing, on the scarce resource.
  • Several ranges per query: DuckDB’s FileSystem::Read is one synchronous call per range, so pgvfs never sees a batch to merge. Only the tantivy code could issue one, at the cost of tying full-text search to pgvfs.
  • A pgvfs block cache: DuckDB’s external file cache, keyed by the immutable file_id, already serves warm reads, within memory_limit.
  • full_page_writes = off: risks corrupting the whole PostgreSQL cluster on a crash.

pgdb:// databases have their own benchmark, just bench-db, against the same database in a local file: see Databases.

Running the benchmarks

just bench city                         # Overture Houston, 1M rows: about a minute
just bench hits                         # ClickBench, 100M rows: about 4 minutes (14 GB download)
MODE=profile just bench city            # where one warm query of each kind spends its time
READERS=6 just bench city               # 6 readers with 2 threads each
ROUNDS=3 just bench city                # three restarts, each with its files evicted
PG_DEVICE=/dev/nvme0n1 PG_READ_IOPS=3000 PG_READ_BPS=125mb just bench city  # gp3's baseline disk
LOAD_ARGS="--variant trickle" just bench city   # layout variants: see bench/city.py

Each run loads the data into a fresh PostgreSQL container, on a data volume that the rounds keep. Each round then starts PostgreSQL in a new container pinned to about a fifth of the cores, evicts its files from the operating system’s page cache, and runs lockstep readers: one cold pass, warm passes and a new-parameter pass. Every pass reports queries/s, latency, PostgreSQL’s CPU and pgvfs_stats(). Memory stays bounded: LOAD_MEMORY (default 4 GiB, sorts spill to disk), READER_MEMORY (2 GiB per reader) and PG_MEMORY (6 GB for PostgreSQL). PG_DEVICE with PG_READ_IOPS and PG_READ_BPS throttles PostgreSQL’s disk reads, a container setting that needs the device that holds Docker’s volumes. The bench recipe is in the justfile.

Development

mise install                              # rust, just, uv, python, mdbook, cmake, ninja
git submodule update --init --recursive   # DuckDB's source, for `just ext`
just check                    # fmt, clippy, unit and property tests, and that the C headers match the FFI
just headers                  # regenerate the C headers from the Rust FFI
just ext                      # the extension, built here from DuckDB v1.5.6 -> target/ext/release/
just ext-nightly [WHEEL]      # a DuckDB 2.0 dev wheel (default: newest), built in a container -> target/ext/nightly/
just contract [EXT]          # storage contract on disposable PostgreSQL (PG_IMAGE=postgres:11..18); with EXT, the end-to-end test too
just compat                   # the contract on every supported PostgreSQL
just e2e [release|nightly]    # build, then contract + DuckDB end-to-end
just sqllogic [PATTERN] [JOBS] # DuckDB's own SQL tests, each on a pgdb:// database (after `just ext`)
just bench [city|hits]       # benchmarks (see Performance)
just bench-db                 # pgdb:// against a local DuckDB file (see Databases)
just site                     # this site from local builds -> .tmp/site/

Every build, test, benchmark and release command is a recipe in the justfile; just --list shows them. CONTRIBUTING.md has the house style: code carries the design, comments only where they state an invariant or DuckDB/PostgreSQL behaviour that is not derivable, plain structs and free functions over stateful objects, and no globals beyond pgvfs_stats’ counters and the environment knobs. Docker is needed for the contract, the nightly build and the benchmark. just contract starts a primary, a streaming standby and a TLS-only server with a throwaway CA, and tests against all three: exact reads, concurrent writers, same-path publish conflicts, cooperative reaps, function refresh, listing ranges, readers with SELECT only and on the standby, TLS verification, lost connections, interrupted writes, readers during writes, and offsets past 4 GiB. For pgdb:// (tests/db_contract.rs, run twice: with the default write batches and with tiny ones): a randomized writer checked against an in-memory model at every version, readers keeping their version, one writer and recovery from a dead one, and reaping. With an extension, it also runs the DuckDB end-to-end test through it: a second process’s writes, a SELECT-only role’s refused writes, volumes routed to two databases by their secrets, and for pgdb:// (extension/test/pgdb.py) keys and constraints, WAL replay, refused second writers, refresh, the cache, pinned versions, a killed writer process, the standby and a SELECT-only role.

DuckDB’s own SQL tests on pgdb://. just sqllogic runs DuckDB’s test/sql through test/configs/pgvfs.json, which attaches a pgdb:// database per test on a disposable PostgreSQL and uses it, as DuckDB’s encryption config does with an encrypted file (and with its skip list: tests that use the memory catalog by name). Tests that load a database file of their own are skipped by that mechanism. Skips need a reason, and the list churns with DuckDB upgrades.

Layout

Path
schema.sql, functions.sqlthe storage layout, and the functions writers keep current
src/store.rsreads, the COPY writer, the advisory-locked install, reaping
src/pg.rsconnection pool and TLS
src/lib.rsthe C interface used by the extension, the open cache, pgvfs_stats()
dbschema.sql, src/db.rspgdb://: its tables, the versioned writer, reads, refresh, reaping
tantivy/tantivy splits (a workspace crate, linked into the same staticlib): build, bundle, search
extension/the C++ DuckDB FileSystem adapters (pgdb_filesystem.cpp: pgdb://, its cache and refresher), the tantivy SQL functions, the container build, the end-to-end tests
tests/the storage contracts, against real PostgreSQL; headers.rs checks pgvfs.h against the FFI
test/SQL tests for make test, and configs/pgvfs.json for DuckDB’s own tests on pgdb://
cbindgen.toml, tantivy/cbindgen.tomlhow the two C headers in extension/src/include/ are generated from the Rust FFI
deny.tomlsupply-chain policy for cargo deny: permissive licences, crates.io only, advisories
bench/benchmark harness (lake.py), datasets (city.py, hits.py), the IVF check (ivf.py), pgdb.py
tools/the Pages site generator (site.py)
justfileevery build, test, benchmark and release command
docs/this site (mdbook)

The storage is Rust. The extension is a thin C++ layer because DuckDB’s stable C API can use filesystems but cannot register one.

The C ABI. extension/src/include/pgvfs.h and tantivy.h are generated from the extern "C" functions in Rust (cbindgen) and checked in. A signature that differs between Rust and C++ still compiles on both sides and crashes at run time, so just check fails when a header is out of date: run just headers and commit the result. Document an FFI function in Rust; its comment becomes the header’s.

Properties. tantivy/src/search/properties.rs runs proptest over random queries, options and exclusions on four splits: the answer is the same on one thread and on many; a page is a slice of the ranking; excluded documents are the ranking without them (and never crowd out top_k); collapse keeps the best hit of each group; bytes and JSON that are not a bitmap or a query are refused, not a panic. PROPTEST_CASES=3000 cargo test -p duckdb-tantivy properties runs them longer. A failure prints a minimal case and saves its seed in tantivy/proptest-regressions/: commit that file with the fix.

Supply chain. CI runs cargo deny check licenses bans sources on every change and the RustSec advisories weekly. Dependencies must be permissively licensed and come from crates.io: no git dependencies, so nothing unreleased is linked into the extension. An accepted advisory goes in deny.toml with its reason.

Building the extension

A C++ DuckDB extension must be statically linked against the exact DuckDB build that loads it, as DuckDB’s own extensions are (Python loads DuckDB with RTLD_LOCAL, so the host’s symbols are out of reach).

  • Stable (just ext): DuckDB’s extension template compiles the pinned duckdb submodule (v1.5.6) and links the extension, with the Rust crate built by cargo through Corrosion (CMakeLists.txt, extension_config.cmake). The first build compiles DuckDB once, about 7 minutes; later builds are incremental. just ext fails if the submodule is any other release, and writes target/ext/release/ with the wheel (DUCKDB_PY) and the version (DUCKDB_VERSION) that bench and the release recipes read. make test runs test/sql/. The Distribution workflow runs this build for Linux (amd64, arm64, glibc and musl), macOS (Intel and Apple Silicon) and Windows on tags, weekly and by hand.
  • Nightly (just ext-nightly): each 2.0 dev wheel is cut by a run of DuckDB’s Main CI on its exact commit, which keeps the static libraries for 90 days. The recipe downloads them and the source tree, checks the digest, and links the extension in a container (extension/Containerfile, extension/build.sh) without compiling DuckDB, in seconds. The download needs a GitHub token (gh auth login or GH_TOKEN).

CI and releases

  • Community releases: DuckDB builds and signs pgvfs for INSTALL pgvfs FROM community. Updates are submitted to duckdb/community-extensions, pinning the release commit in description.yml. Our weekly unsigned builds remain available for 2.0 dev versions.
  • Each push (ci.yml): just check, then the stable build (DuckDB compiles once per commit; the cache falls back to an older commit’s), the contract on PostgreSQL 18 and the end-to-end test.
  • Tags, weekly and by hand (distribution.yml): the multi-platform build with DuckDB’s extension pipeline (Linux amd64 and arm64, glibc and musl; macOS Intel and Apple Silicon; Windows).
  • Weekly, Monday (weekly.yml, or by hand): the contract on PostgreSQL 11; end-to-end tests for the stable and newest 2.0 dev builds; each build published as the pre-release duckdb-<version> (every stable build and the last 4 dev builds are kept); just bench city appended to the bench pre-release’s history.jsonl.
  • Pages (pages.yml, weekly and on docs changes): this site, assembled from the releases by tools/site.py, is also the INSTALL ... FROM repository.