Support index configuration like dbt-postgres

[jwills]

DuckDB has indexes like Postgres does, and I suspect that we will discover some use cases and pipelines where the ability to add them in helps significantly with performance.

dbt-postgres includes built-in support for creating indexes on relations, and I suspect that we will want to add the same to dbt-duckdb at some point, so I created a branch that includes the config and tests from the dbt-postgres indexing config ported over to dbt-duckdb.

I'm going to leave it out of the main branch right now though because the current limitations around DuckDB indexes mean that the Postgres-style index configuration doesn't work for a) incremental models or b) seeds. When those issues are fixed in DuckDB (and the corresponding tests pass), we can revisit including this functionality.

[jwills]

For the time being, assuming your use case and materialization strategy supports it, I recommend using hooks to drop/add indexes from any models in DuckDB that need them.

[PadenZach]

Worth calling out that this is also helpful for attached databases such as sqlite/postgres. Would it make sense to release this feature but gate it to tables/models whose database_type may support these without the same limitations?