Is it possible to use the duckdb TAM on a persistent local duckdb?

[turntable-justin]

From the docs, it seems like it is only available today via temp tables or Motherduck.

This would be incredibly helpful for analytical use cases where you want duckdb columnar storage and performance.

[JelteF]

Nope, that's currently not possible. There are two main problems:

1. Having multiple postgres processes access the local DuckDB at the same time (Postgres is multi-process, but duckdb does not support opening the same file from multiple processes).
2. To have this work with backups and HA, you'd need to write to stuff the Postgres WAL whenever you write to DuckDB. MotherDuck can live without that because it stores all duckdb data on a different machine, and temporary tables work around that by not storing data persistently.

[turntable-justin]

Thanks for getting back to me so quickly!

On 1., wouldn't Motherduck have that same problem though?
On 2., if I didn't care about this, is there a way to hack it up? (e.g. create a FDW server with time 'duckdb' or something like that)

As a less compelling alternative, it is possible to attach a local duckdb file (i.e. using duckdb attach) in read-only mode and use that via pg_duckdb?

[ggam]

As for point 2, excuse me for repeating myself but unlogged tables are perfectly suitable for some use cases (#385) ;). I understand point 1 poses real difficulties though.

[turntable-justin]

@JelteF -- will it be possible to use the newly annonced ducklake with the above. The problems above should no longer be an issue.