[CT-3407] [Feature] Support for tablespaces in PostgreSQL

[ggam]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

PostgreSQL allows you to define tablespaces and store each table and index on a different tablespace. This is pretty useful when fast disk are not available for the whole database.

It would be great if dbt allowed you to specify the tablepace at model (table) and index level. For example:

{{ config(
    materialization = 'table'
    tablespace = 'reporting_tablespace'
    indexes=[
        {
            'columns': ['id']
            'tablespace': 'index_tablespace'
        }
    ]
)}}

Describe alternatives you've considered

I'm using pre and post hooks to route reporting tables to a specific tablespace:

    intermediate:
      materialized: "table"
      schema: intermediate
      +unlogged: true

    marts:
      materialized: "table"
      schema: marts
      +pre-hook: 'set "default_tablespace" to "marts_tablespace"'
      +post-hook: 'set "default_tablespace" to default'

But now I'd like to move only the indexes to another tablespace. I can't set a "default index tablespace" on Postgres so my only option now is to create the indexes via post hooks instead of the native option.

Who will this benefit?

Everyone using tablespaces in Postgres.

Are you interested in contributing this feature?

I probably could, if you guide me a bit where to look.

Anything else?

No response

[dbeatty10]

Thanks for writing this up so well @ggam !

When adding new features to dbt-core, a big criteria for us is applicability -- whether a feature is supported across multiple databases. Another is impact -- how commonly a feature is needed. Ease of implementation is also a factor. There are other potential decision-making factors, but the last I'll mention now is possibility -- whether or not it's already reasonably possible already.

Since this feature is only specific to postgres and is relatively niche (but is still possible via hooks), I'm going to close this feature request as "not planned".

[ggam]

@dbeatty10 I agree this is a very niche case. Note though that using hooks is not (currently) an option for specyfing the index tablespace. #8999 will help with this, as I'll be able to set a known index name and alter index.

In order to implement this correctly in user land, I'd need an "index creation pre/post hook". But yes I know, that's even more niche use case so I'll hope for #8999 to be merged as a workaround.