Add index to dbt model column?

user_1191545 0
Viewed 2811

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?

dbt postgresql
3 Answers

From dbt docs:

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

select ...
user_35170250

It looks like indexing a manual job:

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

user_2719590

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.

  1. 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.)
user_30733400
Related