Is it in your plan to make postgres and duckdb work together?

[gaobaiming]

I'm glad to learn this pg_duckdb extension. I'm wondering if it's in your plan to make duckdb and postgres work toegher, for example, FACT_TAB is a columnar duckdb table, while DIM1 ~ DIM9 are 9 regular postgres tables, and the following statement (p.s. note that this is a simplified example, real world usage would be much more complex) would work:

with h_dim1 as (select * from DIM1),
h_dim2 as (select * from DIM2),
...
h_dim9 as (select * from DIM9),
fact_test as (select * from FACT_TAB)
select h_dim1.a1, h_dim2.a2, ..., h_dim9.a9, fact_test.a_fact
from fact_test, h_dim1, ... , h_dim9
where fact_test.d1 = h_dim1.d1 and ... and fact_test.d9 = h_dim9.d9

[mkaruza]

Hi @gaobaiming currently we don't write or use local duckdb columnar storage. If FACT_TAB is parquet file (either local or remote) it can be used to join with native postgres tables (DIM1~DIM9).

[gaobaiming]

Hi @mkaruza , then the question turns into: can FACT_TAB be stored in duckdb columnar storage? Right now for my use case FACT_TAB is a postgres table and contains big number of data records, it takes time to scan the table, and the star-join (p.s. postgres does not have specialized support for star-join as a matter of fact) of the provided SQL is time consuming too.

[mkaruza]

Well no, right now we don't have ability to read/write duckdb columnar storage. Since this is still early project that is something that we would like to support in future (in one way or another).