Postgres configurations

Performance Optimizations

Unlogged

▶Changelog

"Unlogged" tables can be considerably faster than ordinary tables, as they are not written to the write-ahead log nor replicated to read replicas. They are also considerably less safe than ordinary tables. See Postgres docs for details.

my_table.sql

{{ config(materialized='table', unlogged=True) }}

select ...

dbt_project.yml

models:
  +unlogged: true

Indexes

While Postgres works reasonably well for datasets smaller than about 10m rows, database tuning is sometimes required. It's important to create indexes for columns that are commonly used in joins or where clauses.

▶Changelog

Table models, incremental models, seeds, and snapshots may have a list of indexes defined. Each Postgres index can have three components:

• columns (list, required): one or more columns on which the index is defined
• unique (boolean, optional): whether the index should be declared unique
• type (string, optional): a supported index type (B-tree, Hash, GIN, etc)

my_table.sql

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

select ...

If one or more indexes are configured on a resource, dbt will run create index DDL statement(s) as part of that resource's materialization, within the same transaction as its main create statement. For the index's name, dbt uses a hash of its properties and the current timestamp, in order to guarantee uniqueness and avoid namespace conflict with other indexes.

create index if not exists
"3695050e025a7173586579da5b27d275"
on "my_target_database"."my_target_schema"."indexed_model" 
using hash
(column_a);

create unique index if not exists
"1bf5f4a6b48d2fd1a9b0470f754c1b0d"
on "my_target_database"."my_target_schema"."indexed_model" 
(column_a, column_b);

You can also configure indexes for a number of resources at once:

dbt_project.yml

models:
  project_name:
    subdirectory:
      +indexes:
        - columns: ['column_a']
          type: hash