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