Postgres configurations
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.
{{ config(materialized='table', unlogged=True) }}
select ...
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, 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 definedunique
(boolean, optional): whether the index should be declared uniquetype
(string, optional): a supported index type (B-tree, Hash, GIN, etc)
{{ 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:
models:
project_name:
subdirectory:
+indexes:
- columns: ['column_a']
type: hash