Code example for creating custom postgres indexes via post-hook

[dbeatty10]

Contributions

• I have read the contribution docs, and understand what's expected of me.

Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/reference/resource-configs/postgres-configs#indexes

What part(s) of the page would you like to see updated?

Would be good to put the following advanced example from dbt-labs/dbt-core#9185 in an example box or put in a Discourse post and link to the Index section within the Postgres page.

In dbt-labs/dbt-core#9185 the poster gave this code example for using the gin or gist index types from the pg_trgm extension module using the gin_trgm_ops operator class (opclass):

{{
    config(
        indexes=[
            {"columns": ["my_regular_column"], "type": "btree"},
        ],
        post_hook=[
            "create index my_1st_ext_index on {{ this }} using gin (my_1st_ext_column gin_trgm_ops);",
            "create index my_2nd_ext_index on {{ this }} using gist (my_2nd_ext_column gist_trgm_ops);",
            "create index my_3rd_ext_index on {{ this }} using gist (my_3rd_ext_column gist_trgm_ops(siglen=32));",
        ]
    )
}}

Warning

I didn't actually test out the code example above. I also didn't give much thought to the name of the example columns and indexes. The cde should be tested for correctness and the names should be aligned with relevant style guides.

Additional information

No response