DuckLake with Postgres and S3: a hands-on setup

DuckLake with a Postgres catalog and S3 storage is the setup most people mean when they say "a lakehouse without the cluster". Postgres holds the table metadata. The bucket holds the Parquet files. DuckDB, on any machine that can reach both, reads and writes the tables.

This post sets it up step by step. I ran every command below against DuckDB 1.5.6 and Postgres 16 in a throwaway Docker container, with the data on local disk. The S3 part is from the docs, and I label it where it starts.

What goes where

Three pieces, each doing one job:

  DuckDB (any number of processes, any machine)
     │                           │
     │ catalog: snapshots,       │ data: Parquet files
     │ schemas, file lists,      │ and delete files
     │ small inlined rows        │
     ▼                           ▼
  Postgres database          s3://bucket/lake/

Postgres is not storing your data, apart from small inlined writes. It stores the facts about your data: which files belong to which table at which snapshot, with their statistics. That is why it can stay small. In my 50-commit test the catalog grew by 80 KB.

Step 1: an empty Postgres database

DuckLake needs Postgres 12 or newer and a database it can create tables in.

-- in psql, as an admin
CREATE ROLE ducklake LOGIN PASSWORD '...';
CREATE DATABASE ducklake_catalog OWNER ducklake;

Making the role the database owner matters on Postgres 15 and newer, where ordinary roles can no longer create tables in public by default. (In the lab I connected as the superuser.) Keep this database separate from your application's database. It has a different backup and access story.

Step 2: secrets and ATTACH

You can put the connection string straight into ATTACH. Secrets are tidier, because the password stays out of your SQL files and logs:

INSTALL ducklake; INSTALL postgres;

CREATE SECRET pg_cat (
    TYPE postgres, HOST 'db.internal', PORT 5432,
    DATABASE 'ducklake_catalog', USER 'ducklake', PASSWORD '...'
);

CREATE SECRET lake_secret (
    TYPE ducklake,
    METADATA_PATH '',
    DATA_PATH 'data/',
    METADATA_PARAMETERS MAP {'TYPE': 'postgres', 'SECRET': 'pg_cat'}
);

ATTACH 'ducklake:lake_secret' AS lake;
USE lake;

This is the pattern from DuckLake's connecting docs, and it worked as written. On the first ATTACH, DuckLake created 29 tables in ducklake_catalog. Use CREATE PERSISTENT SECRET if you want DuckDB to remember them between sessions. That stores the password on disk, so decide whether you want it.

The direct form, without secrets, is one line:

ATTACH 'ducklake:postgres:dbname=ducklake_catalog host=db.internal' AS lake
    (DATA_PATH 'data/');

Step 3: tables, and where small writes go

CREATE TABLE orders (id BIGINT, placed_at TIMESTAMP, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, now(), 19.99), (2, now(), 5.00), (3, now(), 42.10);
UPDATE orders SET amount = 6.00 WHERE id = 2;
DELETE FROM orders WHERE id = 3;

After those four statements, the data folder held zero Parquet files. Then I inserted 1,000 rows, and one file appeared. The snapshot log shows why:

SELECT snapshot_id, changes FROM ducklake_snapshots('lake');

0  {schemas_created: [main]}
1  {tables_created: [main.orders]}
2  {inlined_insert: [1]}
3  {inlined_insert: [1], inlined_delete: [1]}
4  {inlined_delete: [1]}
5  {tables_inserted_into: [1]}

The first three writes were inlined: stored as rows in Postgres, not as files. The UPDATE shows up as an inlined delete plus an inlined insert. Only the 1,000-row insert went to Parquet.

How inlining avoids small files

Small writes are what kill a lake. A webhook that lands five rows a minute makes 1,440 tiny Parquet files a day. Each one costs a request to list and open, and the folder needs compaction before it is pleasant to query.

DuckLake 1.0 inlines any write below a threshold, 10 rows by default. Readers see inlined rows straight away, merged with the Parquet data. In a separate run I made 50 inserts of 5 rows each: no files appeared, and each commit took about 4 ms on the Postgres catalog.

The rows still need to become Parquet eventually. Two ways to do it:

CALL ducklake_flush_inlined_data('lake');  -- just the flush
CHECKPOINT;                                -- flush plus the other maintenance

When I ran CHECKPOINT on the table above, it wrote the inlined rows to Parquet. The snapshot log gained {flushed_inlined: [1]}, and the table then read from two files: the 1,000-row file and the flushed one. It returned 1,002 rows, which is right.

Can several DuckDB processes write at once?

This is the reason to want DuckLake over a plain DuckDB file. With a plain file, a second process cannot even open it while a writer holds it. Mine got IO Error: Could not set lock on file.

With DuckLake, I started two processes. Each made 25 commits of 100 rows to the same table at the same time:

Catalog	Commits landed	Errors my code saw	Time
Postgres 16	50 of 50	0	0.37 s
SQLite	50 of 50	2, retried	2.31 s

The SQLite failures read "Failed to commit DuckLake transaction". A retry fixed both. That matches the docs: SQLite works for a few processes on one machine. Postgres is the catalog built for many clients, possibly remote. A DuckDB-file catalog is single-client.

So: SQLite if one box does the writing and you want nothing else to run. Postgres the moment a second machine writes, or commits regularly overlap. Two writers is not a stress test, though. Test with your real write rate before you rely on it.

Step 4: put the data on S3

From here the commands come from the docs. I did not run them against a bucket for this post.

Swap the local DATA_PATH for a bucket prefix, and give DuckDB an S3 secret. The S3 secret syntax is DuckDB's own (httpfs S3 docs):

-- Use the standard AWS credential chain (env vars, profile, instance role)
CREATE SECRET s3_creds (TYPE s3, PROVIDER credential_chain);

-- Or explicit keys
CREATE SECRET s3_creds (
    TYPE s3, PROVIDER config,
    KEY_ID '...', SECRET '...', REGION 'eu-west-1'
);

CREATE SECRET lake_secret (
    TYPE ducklake,
    METADATA_PATH '',
    DATA_PATH 's3://my-bucket/lake/',
    METADATA_PARAMETERS MAP {'TYPE': 'postgres', 'SECRET': 'pg_cat'}
);
ATTACH 'ducklake:lake_secret' AS lake;

The FAQ lists S3 and compatible stores: Azure Blob Storage, Google Cloud Storage and Cloudflare R2. For MinIO or another S3-compatible server, add ENDPOINT and URL_STYLE 'path' to the S3 secret.

Three things to get right on day one:

1. Choose the data path once. The data path is stored in the catalog. When I attached again with a different path, DuckDB refused unless I passed OVERRIDE_DATA_PATH true. The docs say changing the stored path is not supported yet.
2. Every writer needs both credentials. Postgres to commit, the bucket to write files. Readers need read access to both.
3. Lifecycle rules can destroy a lake. Never let a bucket rule expire objects under the lake prefix. Old files belong to snapshots, and only ducklake_cleanup_old_files knows which ones are safe to delete.

Maintenance you will need

Inlining handles trickles. Batches still make a file each, and deletes make delete files. The routine is the same on any storage:

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

Or run CHECKPOINT, which the maintenance docs recommend running regularly. In a 500-commit test, 500 one-batch files merged into one in 0.05 s, and a full-table sum went from 15.7 ms to 4.3 ms. One catch: files with deletes attached were not merged until I ran ducklake_rewrite_data_files, whose default only rewrites files with more than 95% of rows deleted.

What can go wrong

• Losing the catalog. The Parquet files without the catalog are an unordered pile with no snapshot history. Back up Postgres with point-in-time recovery, like any primary database.
• Pointing other tools at the folder. A raw read_parquet('data/**/*.parquet') ignores delete files and old snapshots. In my test it returned 50,000 rows for a table that held 33,333. Every reader goes through DuckLake.
• Mixed versions. Pin DuckDB and the extension on every machine that writes. DuckLake promises backward compatibility from 1.0, and 2.0 is planned for 21 October 2026, a date the calendar calls tentative.

How DuckDB and Postgres split the rest of the work is in DuckDB vs Postgres. Memory limits, temp directories and containers are in running DuckDB on your own infrastructure. Those apply here unchanged.

So: should you run it?

If you already run Postgres and want several processes writing analytical tables to cheap storage, this is the smallest setup I know that does it: one extra database, one bucket, and a DuckDB extension. My lab numbers are small and local. Run your own write rate through it before you move a production table.

Local-First Analytics doesn't cover DuckLake. It covers the parts this setup is built from: running DuckDB as your warehouse (Chapter 5) and adding MinIO for S3-compatible storage with Docker Compose (Chapter 9). Chapter 1 is free to read; the rest is on Amazon.

If you want someone to check whether this fits your write pattern before you build on it, a Data Platform Audit is a week and a written roadmap you keep.

More in this series: DuckLake vs Iceberg, lakehouse vs data warehouse, Iceberg vs Parquet, Postgres and Iceberg and Iceberg catalogs explained.