Does it support Remote Postgresql Based Ducklake Catalog too?

[alimardon123]

Hello everyone, first of all thank you to all the contributors for your hard work and dedication on this project. I liked the idea of making things simple and easy with our common db and really appreciate your work.

My question: Does it (or will it) support using/connecting Remote Postgresql db as main catalog server too?
Currently, I think it will only create ducklake schema in my currently duckdb extension attached db and use it as catalog( correct me if I am wrong). And my concern is that it can put some pressure to this db when using pg_duckdb in which it should work as catalog too and should really be highly available for concurrent connections too.

So I was thining of using 2 Postgresql db in our use case:

• DB1: for mainly for Ducklake cataloging with high availability for concurrent connections/transactions and no other work going on in it.
• DB2 (or maybe many other prod DBs too): for compute and consuming the tables with pg_ducklake by connecting to DB1 Ducklake catalog. DB1 can have pg_ducklake on it too to do occasionaly maintainance on demand.

So that we basically have kind of distributed consuming between our dbs if needed.
Can you let me know if it is feasible?

[qsliu2017]

Thanks for your attention!

Yes, we do have plan to offload the compute heavy job from main PG. We have two options now:

1. Use a read-only PG replica for OLAP workload. I have not tried this approach yet, but it supposes to work now.
2. Send partial SQL to an external DuckDB server. The idea comes from MotherDuck. In this approach, we will have more control over scaling, isolation, and audit etc. I have a demo but still got a lot to work.

Let me know if these work for your use case :)

[alimardon123]

1 option can be close but analysts might need to write to Ducklake too.

[qsliu2017]

FYI, we have just introduced IMPORT FOREIGN SCHEMA for ducklake_fdw (#122), which allows to import ducklake from other PG.

[alimardon123]

FYI, we have just introduced IMPORT FOREIGN SCHEMA for ducklake_fdw (#122), which allows to import ducklake from other PG.

@qsliu2017 Thanks. This is what I was expecting. Is it possible to show this feature in main readme file too.

Regarding this, do you have a plan to enable write option too in the future?
And It would honestly be much more better to auto refresh local tables state when remote catalog schema changes.

[qsliu2017]

Thanks for the suggestions!

Regarding this, do you have a plan to enable write option too in the future?

yes, we do plan to support this. Opened #137 to track it.

And It would honestly be much more better to auto refresh local tables state when remote catalog schema changes.

this is not something we plan to implement at this time. Neither postgres_fdw nor mysql_fdw support auto-refresh; IMPORT FOREIGN SCHEMA is a one-shot operation across the FDW ecosystem. You can re-run it to pick up remote changes.

[LoicRaillon]

Hello everyone.

I have already a dedicated PostgreSQL instance for the DuckLake metadata catalog and based on this conversation I can use IMPORT FOREIGN SCHEMA from another PostgreSQL instance using pg_ducklake.

I have a few questions :

• Does it mean that I can create a view of the data (from the dedicated DuckLake catalog) in the instance running pg_ducklake ?
• Do I need to attach the DuckLake catalog in every query ?
• I think your solution could potentially bridge the gap between Power BI and DuckLake using the native PostgreSQL connector on PostgreSQL instance with pg_ducklake. Did anybody try this setup ?

Thanks for your impressive work. I think it can unlock so many things.