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.
Related projects
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:
| Platform | Architectures |
|---|---|
| Linux, glibc or musl (Alpine) | x86-64, ARM64 |
| macOS | Intel, Apple Silicon |
| Windows | x86-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 version | Python wheel | Built | Commit | Direct install |
|---|---|---|---|---|
v1.5.6 | duckdb==1.5.6 | 2026-11-16 | 86c1a991f181 | file |
v2.0.0-alpha43763 | duckdb==2.0.0.dev2610011535 | 2026-10-14 | e1d6eb40dc18 | file |
v2.0.0-alpha43385 | duckdb==2.0.0.dev2609250715 | 2026-09-29 | 271c57b707dc | file |
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
postgresand 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_ONLYfor 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_extensionfrom a release or from the build table, andLOADit.
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 oncache_local_fileswhere it exists, so the external file cache servespgdb://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), andextension/build.shhad not been givenpgdb_filesystem.cpp. A failed nightly build now prints the compiler’s error instead of only docker’s. just ext-nightlypasses--hostname github.comtogh, so it works for a developer whoseghdefaults 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) andPGVFS_DB_READAHEAD_SLOTS(8). New counters:db_readahead_hits,db_readahead_bytes.- Block size is documented as a knob (
BLOCK_SIZEat 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 everypgdb_refresh_intervalseconds (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()addsdb_reads,db_read_bytes,db_pieces,db_cache_bytesanddb_publishes.- Tests and benchmarks:
just bench-db(pgdb://against a local DuckDB file),just sqllogic(DuckDB’s own SQL tests onpgdb://), and apgdb://storage contract and end-to-end test injust 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_Ssets the grace (600 seconds by default); a read that outlives it names the setting in its error. - Credentials per volume. A
postgressecret namedpgvfs_<volume>gives that volume its own database; each database has its own connection pool. DuckDB keeps noSCOPEon postgres secrets, so the name is what routes a path. - Build memory is bounded.
tantivy_indexreserves each open build’smemory_budget(plus about 10 MB) againstmemory_limitand fails before any build starts that would exceed it. Indexing threads are DuckDB’sthreadsshared 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
globlists only the literal prefix before its first*,?or[. pgvfs_drop_volumeworks from any writer.- Build:
just extbuilds 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, andsite.pymoved totools/. - Benchmarks: cold passes evict PostgreSQL’s files from the OS page cache;
ROUNDSrepeats restart, eviction and passes;PG_DEVICEwithPG_READ_IOPSandPG_READ_BPSthrottles 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.reapwith 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_limitnow fail. Each open build reservesmemory_budgetplus about 10 MB, so at the default 256 MB budget a query can have at mostmemory_limit/ 266 MB groups. Lowermemory_budgetor build fewer groups per query. - A
postgressecret namedpgvfs_<volume>now takes precedence overpgvfs_secretfor that volume. Rename any secret that already has that name. - A SELECT-only role’s
pgvfs_drop_volumefails 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’sread_blob, so every filesystem works. - Fast-field-only hit projection,
tantivy_count, andtantivy_aggregatewith 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_mergecompacts segments natively, optionally removing excluded documents.tantivy_indexaccepts 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) andignore_unmapped; count optionslimit(stop afterlimit + 1) anddistinct(the exact number of distinct values).limitwas an unknown option before. - Query DSL: paths inside JSON fields in
term,terms,match,match_phrase,range,existsandmulti_match;existson an indexed text field without a fast column falls back to its terms. - Query DSL:
prefix,wildcard,regexpandfuzzyon paths inside JSON fields (the path’s strings only; the path-skipping automaton is adapted from Quickwit, Apache-2.0), andcase_insensitiveonterm,prefix,wildcardandregexp. tantivy_search:sortby a fast field (numbers, dates, text; missing values last;scoreisNULL), andhighlight(a fourth column: HTML snippets of stored text fields). Query DSL:more_like_thisfor a text.bench/search.pyandbench/index_memory.py: timings, compaction cost and build memory (documented withmemory_budget; aTMPDIRon 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 showedNULLfor all butpgvfs_stats). pgvfs_drop_volumeremoves 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_searchsearches a split (laterally, many);tantivy_dropremoves 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. Thetantivy_create_indexandtantivy_match_bm25macros work like DuckDB’s fts. Which splits make up an index is up to SQL: the docs show a split per DuckLake commit, usingtable_changes,rowidandsnapshot_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_statshas a function description, soduckdb_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 bloband registering it withducklake_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
SELECTand work on streaming standbys (a writer there is refused). - Credentials from a
postgressecret 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 JOINso existing keys stay put. Later keys stop following alphabetical order, so rebuild and re-key if a query relies onORDER BY station_idmeaning alphabetical. ENUMis 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_BYTESrotation 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):
| Layout | Read on the cold pass | Cold q/s | Warm q/s | Warm p50 |
|---|---|---|---|---|
| LZ4, V1 | 681 MiB | 193 | 307–318 | 25–27 ms |
| zstd, V2 | 434 MiB | 157–162 | 205–243 | 33–37 ms |
| zstd, V1 | 446 MiB | 138–175 | 186–228 | 33–44 ms |
ClickBench hits (100M rows, 105 columns, per-site dashboard queries):
| Load | Load time | Cold | Warm | New parameters |
|---|---|---|---|---|
| Source order | 62 s | 16 q/s, p50 337 ms | 40 q/s, p50 140 ms | 25 q/s |
| Sorted, key-range batches | 139 s | 30 q/s, p50 163 ms | 76 q/s, p50 46 ms | 36 q/s |
| … with 64K-row groups | 213 s | 40 q/s, p50 134 ms | 89 q/s, p50 47 ms | 47 q/s |
File size (the sorted table, 3 alternating runs each, same load time):
| Files | Cold p50 | Warm p50 | New parameters p50 |
|---|---|---|---|
| 512 MB (40 files) | 229–250 ms | 62–76 ms | 149–169 ms |
| 64 MB (223 files) | 176–192 ms | 52–65 ms | 121–142 ms |
Maintenance (Overture Houston, 1M rows, the recommended setup, 2 runs
each; LOAD_ARGS="--variant ..." just bench city):
| Layout | Files | Snapshots | Warm q/s | Warm p50 | Cold p50 |
|---|---|---|---|---|---|
| One sorted load | 4 | 7 | 389–398 | 20 ms | 29–36 ms |
| 100 small inserts | 200 | 205 | 77–82 | 144–153 ms | 133–153 ms |
| … then merged and cleaned up | 4 | 1 | 284–288 | 30–31 ms | 61–66 ms |
| 10% of rows deleted¹ | 4 + 4 delete files | 9 | 299–308 | 30 ms | 44–50 ms |
| … then rewritten and cleaned up | 2 | 1 | 319–348 | 24–27 ms | 43–48 ms |
| Hot and cold columns split | 3 + cold tables | 9 | 380–399 | 19–20 ms | 35–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):
| Layout | Data | Lookup p50 / p99 |
|---|---|---|
| zstd, 122,880-row groups | 281 MB | 122 / 142 ms |
| zstd, 8,192-row groups | 298 MB | 57 / 70 ms |
| LZ4, 8,192-row groups | 470 MB | 55 / 70 ms |
| LZ4, 32,768-row groups | 450 MB | 50 / 59 ms |
Storage format (in pgvfs; one 500K-parcel stream registered as finished files; reads are 1-client p50 with positional deletes):
| Compression, Parquet version | Files | Load | Building lookup | Bounding-box page |
|---|---|---|---|---|
| zstd, V1 | 819 MiB | 7.5 s | 56 ms | 403 ms |
| zstd, V2 | 785 MiB | 8.7 s | 56 ms | 398 ms |
| snappy, V1 | 1,400 MiB | 8.8 s | 58 ms | 414 ms |
| snappy, V2 | 1,361 MiB | 10.3 s | 59 ms | 405 ms |
Delete files (the same 2.9M deleted rows in 26 files):
| Format | Size | Building lookup | Bounding-box page |
|---|---|---|---|
| Parquet positions (default) | 11.9 MB | 56 ms | 398 ms |
| Puffin deletion vectors | 1.9 MB | 106 ms | 907 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
ATTACHanywhere fails at once (“attached for writing by another process”, or “in this process”). A read-onlyATTACHin 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()showspgdb://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_intervalseconds (10 by default,0off), 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 file | pgdb:// | |
|---|---|---|
CREATE TABLE AS 30M rows | 6.9 s | 8.6 s (1.24×) |
| Primary key and an index | 7.6 s | 10.6 s (1.40×) |
| Commit (one-row insert) | 0.6 ms | 2.2 ms |
| 13 readers, warm: queries/s | 74.5 | 69.1 (93%) |
| … point lookup p50 | 1.7 ms | 11.8 ms |
| … aggregate over the table p50 | 249 ms | 263 ms |
| 13 readers, first pass: queries/s | 71.0 | 51.7 (73%) |
| Cold point lookup | 3.8 ms | 21.6 ms |
| Cold lookup of 300 scattered rows | 82 ms | 512 ms |
| Cold scan of an incompressible column | 118 ms | 375 ms |
| A reader’s refresh, then one query of each kind | 150 ms, 2 MB from PostgreSQL | |
| … without the external file cache | 433 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):
| before | with readahead | |
|---|---|---|
| Full scan, 16 KiB blocks | 2,603 ms | 703 ms |
| Full scan, 16 KiB blocks, 8 threads | 438 ms | 163 ms |
| Full scan, 256 KiB blocks | 738 ms | 645 ms |
| Full scan, 256 KiB blocks, 8 threads | 116 ms | 126 ms |
| 200 point lookups, 16 KiB blocks | 812 ms | 458 ms |
| 200 point lookups, 256 KiB blocks | 745 ms | 695 ms |
| Scan of 1% of the table, 16 KiB blocks | 79 ms | 43 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 table | 16 KiB | 256 KiB (default) |
|---|---|---|
| Bytes a cold point lookup moves | ~60 KB | ~630 KB |
| Read calls (200 point lookups) | 745 | 443 |
| Full scan, one thread (with readahead) | 703 ms | 645 ms |
| Full scan, 8 threads (with readahead) | 163 ms | 116 ms |
| Database size on disk | 1.03 GB | 754 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 atemp_directoryunder it fails. - No listing or globbing of
pgdb://paths. To copy a database in or out, useCOPY 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_intervalsetting is database-wide (SETapplies 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:
| Alternative | Why not |
|---|---|
Mutable rows in pgvfs.files | Breaks 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 guard | Silently 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 reaper | Invisible 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 refresh | NOTIFY 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 size | Block 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 = offon 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_bm25 | macros 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 in | merge | write out | total | re-index the 1.6M live documents from source | |
|---|---|---|---|---|---|
| local files | 0.6 s | 11.5 s | 0.3 s | 12.7 s | 21.6 s |
| pgvfs, PostgreSQL on the same host | 4.4 s | 10.9 s | 1.7 s | 17.2 s | 20.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.
schemais a tantivy schema as JSON: an array of field entries. Each has aname, atype(text,i64,u64,f64,bool,date,facet,bytes,json_objectorip_addr) andoptions(indexing, tokenizer,stored,fast,coerceand so on).docis a row/STRUCT(just passt; it is cast to JSON, which needs DuckDB’sjsonextension, autoloaded in standard builds), or a JSON object as text. Fields not in the schema andnulls are skipped; an array gives a field several values.coerceon a text field indexes numbers as text.options, as JSON:tokenizers: named analyzers to use in the schema, besides tantivy’s built-indefault,raw,en_stemandwhitespace. Each is atokenizer("simple","whitespace","raw",{"ngram": {"min": 2, "max": 3, "prefix_only": false}}, or{"regex": "..."}) and a list offilters. 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") anddocstore_blocksize.memory_budget: the indexing memory in bytes (default 256 MB).merge: merge into one segment (defaulttrue), 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):
| groups | 256 MB (default) | 64 MB | 16 MB |
|---|---|---|---|
| 1 | 300 MB | 120 MB | |
| 4 | 780 MB | 320 MB | |
| 16 | 1,680 MB | 1,140 MB | 390 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:
| Option | Default | |
|---|---|---|
top_k | every hit | hits to return across the whole list of splits; use a bound on large indexes |
offset | 0 | hits 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 |
collapse | none | return 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 |
sort | by score | order 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 |
highlight | none | {"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 |
fields | every indexed text field | the fields to search for terms that name none |
conjunctive | false | every term must match |
strict | false | fail on query syntax errors instead of dropping what tantivy cannot parse |
fast | stored fields | build doc only from these fast fields, without reading stored documents; [] returns {} |
exclude_field | none | an i64 or u64 fast field whose values are in exclude; exactly one value on every document |
global_stats | false | use combined BM25 statistics; needs the same schema across splits. As in tantivy, statistics include excluded (deleted) documents until the splits are merged |
ignore_unmapped | false | in 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 | |
|---|---|
limit | stop 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 |
distinct | the 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:
| Query | Supported parameters |
|---|---|
bool | must, should, must_not, filter, minimum_should_match |
match | query, operator (and/or), minimum_should_match, fuzziness, fuzzy_transpositions |
match_phrase, match_phrase_prefix | query; slop for phrases, max_expansions for phrase prefixes |
multi_match | named fields (with ^boost), best_fields, most_fields, phrase, phrase_prefix; applicable match parameters, tie_breaker |
term, terms | exact typed values, without analysis; term takes case_insensitive for text |
prefix, wildcard, regexp | text value, case_insensitive; wildcard *, ?, \ escapes; a regular expression matches a whole term |
fuzzy | text value, fuzziness, transpositions |
more_like_this | like: 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) |
exists | field: a fast field, or an indexed text field (slower: any term in its dictionary), or a JSON field (any path in it) |
range | gt, gte, lt, lte |
constant_score, dis_max | filter; queries and tie_breaker, respectively |
match_all, match_none | |
query_string, simple_query_string | query, 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:
| Parameter | Default | |
|---|---|---|
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_accents | true | fold accents (café matches cafe) |
lower | true | lowercase |
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’sthreads(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 toindexas 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
threadsthreads (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.pymeasures 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.
| Probes | Recall@10, separate blobs | Recall@10, overlapping blobs | Bytes read, share of exact |
|---|---|---|---|
| 1 | 0.998 | 0.51 | 2.5–2.7% |
| 2 | 1.0 | 0.59 | 5–7% |
| 4 | 1.0 | 0.69 | 10–14% |
| 8 | 1.0 | 0.79 | 20–28% |
| 16 | 0.88 | 39% | |
| 32 | 0.98 | 69% |
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_idwith one binaryCOPY, and publishes the path in the same transaction when the file is closed. Overwriting a path publishes a newfile_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 withSELECT-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_Sseconds (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 exampleSELECT pgvfs.reap(interval '1 hour');, to matchPGVFS_REAP_GRACE_S. - Vacuum. Reaping deletes whole files’ rows, so new databases set
pgvfs.chunksto 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: sethot_standby_feedback = onon 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_sizeandcheckpoint_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
VACUUMdoes not return space to the operating system.
Caching
- DuckDB’s file cache.
file_idis 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 shouldSET parquet_metadata_cache = false. - External file cache. DuckDB 2.0 caches only files that look remote
(
http://,s3://, …); loading pgvfs also turns oncache_local_filesthere, sopgdb://reads are cached (they go throughpgdbc://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:
- A
postgressecret namedpgvfs_<volume>, which gives that volume its own database:CREATE SECRET pgvfs_lake (TYPE postgres, ...)forpgvfs://lake/. DuckDB’s postgres secrets keep noSCOPE, so the name is what routes a path. - The
postgressecret named bySET pgvfs_secret = 'name'. Pair it withATTACH ... (META_SECRET 'name'). - Otherwise the
PGVFS_URLenvironment variable (a URL orkey=valuestring). - Otherwise the unnamed default
postgressecret, 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:
USAGEon schemapgvfsandSELECTon its tables.ALTER DEFAULT PRIVILEGEScan grant these before the schema exists. - Writers: the same, plus
INSERT,UPDATEandDELETEon its tables andUSAGEon its sequences. The first write also needsCREATEon 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
| Setting | Default | |
|---|---|---|
pgvfs_<volume> postgres secret | none | credentials for one volume’s paths |
SET pgvfs_secret = 'name' | the default postgres secret | credentials for all other paths |
PGVFS_URL | credentials, if no secret is named | |
PGVFS_REAP_GRACE_S | 600 | seconds a deleted file’s rows (and a superseded pgdb:// version) stay readable |
SET pgdb_refresh_interval = s | 10 | seconds between pgdb:// readers’ checks for a newer version (0 off) |
PGVFS_POOL_MAX | max(8, 2 × DuckDB threads) | PostgreSQL connections per DuckDB database |
PGVFS_POOL_MIN | 4 | connections kept open |
PGVFS_IO_THREADS | DuckDB threads | I/O threads per DuckDB database |
PGVFS_OPEN_CACHE_S | 10 | seconds to remember a path’s file (0 off) |
PGVFS_DB_CA_FILE | extra CA certificates (PEM) | |
PGVFS_DB_ALLOW_PLAINTEXT | false | allow 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.
| Pass | Queries/s | p50 | p95 | PostgreSQL busy |
|---|---|---|---|---|
| cold | 68.1 | 23.6 ms | 120.0 ms | 41% |
| warm (median) | 116.3 | 13.4 ms | 72.3 ms | 13% |
| new params | 100.1 | 18.1 ms | 71.9 ms | 27% |
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/s | p50 | p95 | 100M hits warm q/s | p50 | p95 |
|---|---|---|---|---|---|---|
| 13 × 1 | 371 | 21 ms | 88 ms | 76 | 46 ms | 440 ms |
| 6 × 2 | 346 | 11 ms | 40 ms | 49 | 36 ms | 202 ms |
| 4 × 3 | 302 | 9 ms | 28 ms | |||
| 1 × 13 | 137 | 6 ms | 11 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
COPYacross 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::Readis 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, withinmemory_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.sql | the storage layout, and the functions writers keep current |
src/store.rs | reads, the COPY writer, the advisory-locked install, reaping |
src/pg.rs | connection pool and TLS |
src/lib.rs | the C interface used by the extension, the open cache, pgvfs_stats() |
dbschema.sql, src/db.rs | pgdb://: 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.toml | how the two C headers in extension/src/include/ are generated from the Rust FFI |
deny.toml | supply-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) |
justfile | every 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 pinnedduckdbsubmodule (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 extfails if the submodule is any other release, and writestarget/ext/release/with the wheel (DUCKDB_PY) and the version (DUCKDB_VERSION) thatbenchand the release recipes read.make testrunstest/sql/. TheDistributionworkflow 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’sMainCI 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 loginorGH_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 indescription.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-releaseduckdb-<version>(every stable build and the last 4 dev builds are kept);just bench cityappended to thebenchpre-release’shistory.jsonl. - Pages (
pages.yml, weekly and on docs changes): this site, assembled from the releases bytools/site.py, is also theINSTALL ... FROMrepository.