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

▶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
Edit this page