[Feature] Create indexes also without full refresh

Open aBBDnGus opened this issue 1 year ago • 2 comments

Is this your first time submitting a feature request?

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

Describe the feature

I have an incremental table or a materialized view and create a new index for this via config(indexes=[{'columns': ['column_a']}]). Then, this index is only created if the model is run with full-refresh. It would be better, if the index is created with every run. I.e, if I add an index to a model, this is created with the next run.

I'm using PostgreSQL.

Describe alternatives you've considered

Alternatives:

Do a full refresh, which can be expensive
Create an index "by hand" in SQL

Who will this benefit?

Subsequenctly adding an index for incremental models, which are expensive to fully refresh.

Are you interested in contributing this feature?

No response

Anything else?

No response

Feb 02 '24 10:02 aBBDnGus

Thanks for raising this idea @aBBDnGus 👍

Materialized views

Are you having issues updating indexes for materialized views?

Our documentation says that:

indexes can be dropped and created on the materialized view without the need to recreate the materialized view itself

So we'd want to either create a bug report or update the documentation if that is not true.

Incremental models

I can see what you are saying that incremental models will only have their indexes replaced when using full refresh.

To implement this, it looks like we could follow the pattern for updating indexes in materialized views (and re-use a lot of the logic).

It's a long trace of code end-to-end, but it might save someone time in the future:

logic for updating indexes for materialized views

https://github.com/dbt-labs/dbt-adapters/blob/154ca610f67906b0affea9a1e85d29126721cafd/dbt/include/global_project/macros/materializations/models/materialized_view.sql#L10
- https://github.com/dbt-labs/dbt-adapters/blob/154ca610f67906b0affea9a1e85d29126721cafd/dbt/include/global_project/macros/materializations/models/materialized_view.sql#L52
  - https://github.com/dbt-labs/dbt-adapters/blob/154ca610f67906b0affea9a1e85d29126721cafd/dbt/include/global_project/macros/materializations/models/materialized_view.sql#L65
    - https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L46
      - https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L47
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/describe.sql#L1
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/describe.sql#L3
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/adapters.sql#L226
      - https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L48
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/adapters/postgres/relation.py#L53
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/adapters/postgres/relation.py#L63-L65
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/adapters/postgres/relation.py#L75
        
        https://github.com/dbt-labs/dbt-adapters/blob/154ca610f67906b0affea9a1e85d29126721cafd/dbt/include/global_project/macros/materializations/models/materialized_view.sql#L71
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L1
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L18
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L25
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L33
        
        https://github.com/dbt-labs/dbt-postgres/blob/deb4e09375a182a70a525bf7a38e784f8f3abd39/dbt/include/postgres/macros/relations/materialized_view/alter.sql#L37

Summary

This is unlikely to be a high priority for us, so labeling this as "help wanted".

Feb 07 '24 02:02 dbeatty10