dbt-postgres Adapter
Relevant source files
The following files were used as context for generating this wiki page:
The dbt-postgres Adapter is the foundation SQL adapter within the dbt ecosystem, enabling dbt to interact with PostgreSQL databases. It facilitates the "Transform" layer of ELT by allowing data analysts and engineers to manage PostgreSQL relations—such as tables, views, and materialized views—using software engineering best practices.
As a core component of the dbt-adapters monorepo, the Postgres adapter serves as the base for other major adapters, most notably dbt-redshift. It implements the SQLAdapter interface, which provides standardized SQL patterns while allowing for Postgres-specific optimizations like unlogged tables and index configurations.
Sources: dbt-postgres/README.md, CLAUDE.md:120-122, dbt-adapters/README.md:16-20
Architecture and Components
The adapter architecture follows a modular pattern inherited from the base framework. It consists of connection management, dialect translation, and relation metadata handling.
Core Modules
| Module | Purpose |
|---|---|
connections.py | Manages thread-safe connection pooling using psycopg2. |
impl.py | Contains the PostgresAdapter implementation and core logic. |
relation.py | Represents database objects (tables, views) and handles quoting. |
column.py | Handles PostgreSQL-specific column metadata and type casting. |
Sources: CLAUDE.md:124, dbt-adapters/README.md:27-38
Relationship with Base Framework
The Postgres adapter extends the SQLAdapter class, which in turn extends BaseAdapter. This inheritance chain ensures that basic SQL functionality is shared across adapters while Postgres-specific logic is isolated.
Connection Management
Connection management is handled via psycopg2. By default, the adapter installs psycopg2-binary for ease of setup, though production environments often swap this for psycopg2 compiled from source for performance gains.
Credential Configuration
The adapter requires specific configuration parameters in the dbt profiles.yml to establish connections.
| Parameter | Default | Description |
|---|---|---|
host | N/A | Database server address |
port | 5432 | Connection port |
user | N/A | Authentication username |
password | N/A | Authentication password |
database | N/A | The logical database name |
schema | N/A | The target schema |
sslmode | N/A | SSL connection mode (e.g., require) |
search_path | N/A | Postgres search path for name resolution |
Sources: dbt-postgres/README.md:25-30, CLAUDE.md:138-140
Connection Lifecycle
The connection manager acquisition flow ensures thread safety and efficient resource usage through a pool.
PostgreSQL Specific Features
The dbt-postgres adapter supports several platform-specific configurations that leverage PostgreSQL's native capabilities.
Indexes and Unlogged Tables
Users can define indexes and unlogged table properties directly in the model configuration:
- Indexes: Supports various types such as
btreeanduniqueindexes across multiple columns. - Unlogged Tables: Tables that do not write to the Write-Ahead Log (WAL), increasing performance at the cost of durability.
{{ config(
materialized='table',
unlogged=true,
indexes=[
{'columns': ['user_id'], 'type': 'btree'},
{'columns': ['email'], 'unique': true}
]
) }}
Sources: CLAUDE.md:129-136
Constraints
The adapter supports standard SQL constraints. If a constraint is defined that the adapter cannot enforce, dbt-adapters provides a warning system.
- Supported:
CHECK,NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY. - Warning Events:
ConstraintNotEnforced(E048) andConstraintNotSupported(E049).
Sources: CLAUDE.md:126, dbt-adapters/src/dbt/adapters/events/types.py:328-350
Testing and Development
Testing the adapter involves both unit tests and functional integration tests.
Functional Testing Fixtures
Integration tests require a live database and typically inherit from Base classes in dbt-tests-adapter. These tests verify behaviors like:
- Materializations (Table, View, Incremental).
- Snapshot behavior (including
hard_deletes). - Documentation generation.
For local development, a Docker-based PostgreSQL instance is recommended:
docker run -d --name postgres-test -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=dbt_test -p 5432:5432 postgres:14
Sources: CLAUDE.md:143-145, dbt-tests-adapter/src/dbt/tests/adapter/basic/test_docs_generate.py:195-210, AGENTS.md:55-70
Code Quality Tools
Development within the dbt-postgres directory utilizes hatch for environment management and quality checks.
- Linting:
hatch run code-quality(Black, Flake8, MyPy). - Unit Tests:
hatch run unit-tests. - Integration Tests:
hatch run integration-tests(requirestest.env).
Sources: AGENTS.md:38-50, CLAUDE.md:41-52
Summary
The dbt-postgres adapter is a foundational piece of the dbt ecosystem. It implements the essential SQLAdapter protocols using psycopg2 while providing Postgres-specific features like unlogged tables and index management. Its role as the base for the Redshift adapter highlights its importance in maintaining a consistent development pattern for Postgres-compatible data warehouses.