[CT-3439] [Feature] Support for postgres index extensions

[scottgigante]

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

Developers should easily be able to select indices from installed extensions -- e.g. I would like to use a gin index from pg_trgm.

Describe alternatives you've considered

This is the clearest workaround, but it's not obvious to new users and it's counterintuitive to do something different for this index than for other regular indices.

{{
    config(
        indexes=[
            {"columns": ["my_regular_column"], "type": "btree"},
        ],
        post_hook=[
            "create index my_extension_index on {{this}} using gin (my_extension_column gin_trgm_ops);"
        ]
    )
}}

Who will this benefit?

This will benefit users of Postgres who wish to add indices that are not supported out of the box.

Are you interested in contributing this feature?

Unfortunately I don't have the time.

Anything else?

No response

[dbeatty10]

Thanks for proposing this @scottgigante !

Something we'll often say that we want dbt to "make easy things easy and hard things possible". Since this proposal involves an optional postgres exension, we would put this in the "hard" category rather than the "easy" category. So our aspiration would be to make this at least possible (even if we can't / won't make it easy).

If we were to implement this, the syntax might look like this:

{{
    config(
        indexes=[
            {"columns": ["my_regular_column"], "type": "btree"},
            {"columns": ["my_1st_ext_column"], "type": "gin", "opclass": "gin_trgm_ops"},
            {"columns": ["my_2nd_ext_column"], "type": "gist", "opclass": "gist_trgm_ops"},
            {"columns": ["my_2nd_ext_column"], "type": "gist", "opclass": "gist_trgm_ops(siglen=32)"}
        ]
    )
}}

But since it is possible right now via post-hooks and not a priority for us to implement ourselves or accept an open source contribution, I'm going to close this as "not planned".

p.s.
Agreed that the workaround is not obvious to new users and it's counterintuitive to do something different for trigram-based indexes like gist (Generalized Search Tree) or gin (Generalized Inverted Index) from the pg_trgm extension module. So we should supplement the documentation for Postgres indexes with code examples similar to what you provided.

Here's the issue I've opened for that supplemental documentation: dbt-labs/docs.getdbt.com#4568.

[scottgigante]

Thanks @dbeatty10, that explanation makes a lot of sense and the proposed docs addition solves 90% of the problem. Thank you!

[philippemnoel]

Would love to get support for pg_bm25 in dbt as well! https://github.com/paradedb/paradedb

[aibunny]

Would love to get support for pg_bm25 in dbt as well! https://github.com/paradedb/paradedb

I also want this too, looking to use dbt in parade db and want my tables as parquets