Ducklake support

[turntable-justin]

It would be awesome if pg_duckdb could query from a ducklake, now that that's been announced.

Two potential ways to support:

• an ATTACH (as with duckdb)
• Using the duckdb TAM (as pgduckdb currently supports motherduck)

Is this possible?

[YuweiXiao]

DuckLake operates similarly to pg_mooncake v0.1, but has more implemented features, such as schema evolution and compaction. It would be beneficial to have a native columnstore support in pg_duckdb by integrating ducklake.

[JelteF]

This is definitely something we're considering to add support for, but it's not a small feature to implement.

[YuweiXiao]

Hi @JelteF, I've got a working PoC for integrating DuckLake into pg_duckdb. The code incorporates DuckLake as a submodule (requiring no modifications) and hooks several classes (like metadata_manager) by registering a new storage extension (pgducklake_storage).

DuckLake currently uses the PostgreSQL extension to connect to PostgreSQL and fetch metadata. By intercepting the metadata_manager, we can perform scans/insertion directly using PG APIs.

I can submit a merge request if we agree this "columnstore" integration is suitable to land on pg_duckdb. We may also need to establish feature milestones for it.

The usage looks like:

create table t1(id int) using ducklake;
insert into t1 values(1); -- executed by DuckDB
select * from t1; -- executed by DuckDB

[turntable-justin]

Wow @YuweiXiao ! This is way faster than I thought it would happen!

One small implementation note: I've seen for vanilla pgduckdb that sometimes that postgres seems to keep the duckdb connection open indefinitely, long after your query has been run, which hogs memory after a large query. Would it be possible to put a ttl on that (e.g. x minutes after the last completed query) in the code you have somewhere, and expose that as a pg setting to the user?

[JelteF]

As always, feel free to open a (draft) PR so we can have such discussions. But I likely won't have time to look into this in the near future.

[chris-gooch]

I’m interested in having this feature for sure. Fits my requirements perfectly

[YuweiXiao]

The PR (#830) has been opened. This initial version supports basic operations such as creating tables, inserting data, and querying DuckLake tables.

Features we may want to have on DuckLake + pg_duckdb:

1. Columnar storage
2. Time-travel and Change data feed
3. Client interoperability - tables written by Postgres can be read by other DuckLake clients like the DuckDB CLI