Support DuckDB storage on local tables

[ggam]

DuckDB Table AM is only available with MotherDuck and for temporary tables:

create table xx(id numeric) using duckdb;

SQL Error [XX000]: ERROR: Only TEMP tables are supported in DuckDB if MotherDuck support is not enabled

I'd like to use it for storing unlogged tables.

At my company we have a Postgres based data warehouse built with dbt. The process is the following:

• Years of raw data are stored in a dedicated schema.
• Intermediate tables are created from the raw data, filtered for a specific period.
• Reporting tables are updated with the data from the intermediate layer, which were already date-filtered.

Intermediate tables are only queried to create other intermediate tables during dbt execution. We don't need those tables to survive a crash and so we mark them unlogged to avoid WAL overhead.

I understand supporting DuckDB storage on normal tables would be difficult as they'd need to provide the same crash recovery features as normal tables. But unlogged tables are more lax and having them would allow us to benefit from columnar and vectorized execution without any overhead.

[JelteF]

So the main difficulty here is that DuckDB only supports opening a duckdb database in one process if you want to do writes to the database: https://duckdb.org/docs/connect/concurrency.html#handling-concurrency

Since postgres uses a one process for each of your connections, having multiple connections write to the same database is not possible without additional logic.

There definitely are options to work around this duckdb limitation: One possibility would be to create a database per schema, and once a query is done writing to it it would re-open the database in read-only mode, and thus allow other connections to open it in read-only mode too.

[ggam]

Thanks for such a quick response! Your proposal would definitely work for my specific use case, as db creates and populates each table with just one connection.

I understand that the table (DuckDB database underneath) would be exclusively locked while one process is being written, which can be a huge limitation on other use cases. But again, it's just another tool in your box, as are temporary tables.

Edit: just to clearly state my point, I'm assuming you'd be able to create one DuckDB database for each Postgres table. That way you minimize locking constraints.