Dbt: Feature Request: UNLOGGED Postgres tables

[boxysean]

Feature

Feature Description

Allow for dbt developers to configure their Postgres model to be created with the UNLOGGED parameter. From the Postgres CREATE TABLE docs:

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 30), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.

An example of a Postgres CTAS statement with UNLOGGED looks like:

CREATE UNLOGGED TABLE my_table AS SELECT * FROM my_other_table;

This feature request is similar in nature to the Snowflake transient tables configuration.

Who will this benefit?

This will benefit those who use dbt on Postgres who want (1) faster write performance and (2) reduced WAL log generation (which has downstream effects on physical and logical replication).

[drewbanin]

Thanks @boxysean! I think you're right to identify that this is similar to transient configs on Snowflake.

Have you checked out how those transient tables are implemented? I think it will look _very_ similar for unlogged on pg.

Check out the create_table_as macro code here and the corresponding registration of the transient config here.

I don't think this is something we're going to prioritize on our end - we typically recommend that folks use a proper data warehouse like Redshift/Snowflake/BigQuery rather than Postgres as their analytical database. That said, I'd be happy to support this feature if you (or anyone else visiting this issue) is interested in contributing a PR!

[boxysean]

we typically recommend that folks use a proper data warehouse like Redshift/Snowflake/BigQuery rather than Postgres as their analytical database

Oh tell me about it. 😭

I've opened a PR! And heading to Casper now for the meetup. :-D

[drewbanin]

closed by #1650