Skip to main content

Postgres configurations

Incremental materialization strategies​

In dbt-postgres, the following incremental materialization strategies are supported:

  • append (default when unique_key is not defined)
  • merge
  • delete+insert (default when unique_key is defined)
  • microbatch

Performance optimizations​

Unlogged​

"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.

Table models, incremental models, seeds, snapshots, and materialized views 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

Materialized views​

The Postgres adapter supports materialized views with the following configuration parameters:

ParameterTypeRequiredDefaultChange Monitoring Support
on_configuration_change<string>noapplyn/a
indexes[{<dictionary>}]nononealter
dbt_project.yml
models:
  <resource-path>:
    +materialized: materialized_view
    +on_configuration_change: apply | continue | fail
    +indexes:
      - columns: [<column-name>]
        unique: true | false
        type: hash | btree

The indexes parameter corresponds to that of a table, as explained above. It's worth noting that, unlike tables, dbt monitors this parameter for changes and applies the changes without dropping the materialized view. This happens via a DROP/CREATE of the indexes, which can be thought of as an ALTER of the materialized view.

Learn more about these parameters in Postgres's docs.

0
Edit this page