Add index to dbt model column?

Question

We are considering using dbt to manage models in our PostgreSQL data warehouse. Since dbt models are SQL select statements, there doesn't seem to be an obvious, or documented, way to specify that a particular column should have an index.

How can we specify column indexes on dbt models?

Concierge

Answer 1

From dbt docs:

{{ config(
    materialized = 'table',
    indexes=[
      {'columns': ['column_a'], 'type': 'hash'},
      {'columns': ['column_a', 'column_b'], 'unique': True},
    ]
)}}

select ...

Cot

Answer 2

I ran into this exact issue myself and made the following package for this use case:

dbt-postgres-utils

1. Installation

Include the following in your packages.yml file:

packages:
  - package: sgoley/postgres_utils
    version: 0.2.0

then you can run dbt deps and the dbt package manager will setup the related macros in your project locally under project_dir/dbt_modules/postgres_utils.

2. Usage

After that, table models can have an index or unique index built with a posthook like:

{{
config({
    "post-hook": [
      "{{ postgres_utils.index(this, 'id')}}",
    ],
    })
}}

Of course if you add this to a view model, your dbt run will throw an error.

Feel free to make additional requests, submissions, or more here on the project repo:

github: sgoley/dbt-postgres-utils

Additional features I'm working on are:

• convert syntax of index and uindex functions to "create or replace"
• specify index types (btree, hash, etc.)

Laundromat

For what it's worth, they are discussing adding indexing to dbt-postgres directly: github.com/fishtown-analytics/dbt/issues/804 – Concierge

Here is the pull request that intends to add indexing to dbt-postgres: github.com/fishtown-analytics/dbt/pull/3106 – Concierge

It might be good to contribute index-related changes from dbt-postgres-utils directly to dbt-postgres :-) – Concierge

Yes, I'm following that issue closely but have much more in the works than just index macros: trello.com/b/jG0KfNzl/dbt-postgres-utils – Laundromat

Answer 3

It looks like indexing a manual job:

Make sure to create indexes for columns that are commonly used in joins or where clauses.

Sclerodermatous

Thanks Frank. I hope index definitions will be included in future releases, as we would like to include index definitions under revision control. – Concierge

Ah, found the related feature request: github.com/fishtown-analytics/dbt/issues/804 – Concierge

I first searched StackOverflow and the DBT Discourse for any suggestions. – Concierge