Skip to main content

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