> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/build/productize/connect-engine/postgres.md).

# Postgres

Configure PostgreSQL as the engine for your Data Product.

PostgreSQL suits smaller products, prototypes, and local-first work where you want direct control over the database. It's the engine behind the `orders-analytics` example used throughout this guide. Use the `postgres` adapter to connect. For the full reference, see the [Postgres engine manual](https://v2.dataos.info/references/engine-guide/postgres) in References.

## Before you start

* A PostgreSQL server (version 12 or higher recommended).
* A database user with the required permissions.
* Network connectivity from Vulcan to the server, and the connection details: host, port, database, user, password.
* `connection.database` must already exist before `vulcan plan`: Postgres has no catalog tier, and Vulcan doesn't create the database itself.
* DataOS access: a `roles:id:data-dev`-equivalent role tag, `depot:rw:<postgres-depot-name>` (or `depot:r` for read-only), **Can Use** on Compute/Depots/Secrets, and a git-sync Secret.

## Permissions

| Permission                                       | Required for                     |
| ------------------------------------------------ | -------------------------------- |
| `CREATE` on the database                         | Creating schemas                 |
| `CREATE` on schemas                              | Creating tables and views        |
| `SELECT`, `INSERT`, `UPDATE`, `DELETE` on tables | Reading and writing model output |
| `USAGE` on schemas                               | Accessing schemas Vulcan uses    |

Three roles are required — don't collapse them into one: a superuser/DBA **admin** (creates the database, installs extensions, grants privileges), the **Vulcan service role** (runs models, creates and modifies schemas/tables), and a **consumer role** (read-only on Data Product tables). Grant the service role as a superuser or database owner:

```sql
GRANT CONNECT ON DATABASE warehouse TO vulcan;
GRANT CREATE ON DATABASE warehouse TO vulcan;
GRANT USAGE ON SCHEMA raw TO vulcan;
GRANT SELECT ON ALL TABLES IN SCHEMA raw TO vulcan;
```

Grant consumers read-only access the same way, with `CONNECT` / `USAGE` / `SELECT` instead. Verify with `\dn` and `\dp raw.*` in `psql`.

## Connection options

| Option                                                           | Required | Description                            |
| ---------------------------------------------------------------- | :------: | -------------------------------------- |
| `type`                                                           |    Yes   | Must be `postgres`                     |
| `host`                                                           |    Yes   | Server hostname                        |
| `port`                                                           |    Yes   | Server port                            |
| `database`                                                       |    Yes   | Database name                          |
| `user`                                                           |    Yes   | Connection username                    |
| `password`                                                       |    Yes   | Connection password                    |
| `sslmode`                                                        |    No    | SSL mode. Use `require` in production. |
| `connect_timeout`, `keepalives_idle`, `role`, `application_name` |    No    | Tune as your environment needs         |

### Authentication methods

* **Username and password** (required): set `user` and `password`.
* **SSL mode** (optional): set `sslmode: require` or stricter for secure connections in production.

## Example

```yaml
gateways:
  postgres:
    connection:
      type: postgres
      host: <postgres-host>
      port: 5432
      database: <database-name>
      user: <username>
      password: "{{ env_var('PG_PASSWORD') }}"
      sslmode: require
```

## Supported model kinds

`SEED`, `FULL`, `VIEW`, `INCREMENTAL_BY_TIME_RANGE`, `INCREMENTAL_BY_UNIQUE_KEY`, `INCREMENTAL_BY_PARTITION`, `INCREMENTAL_UNMANAGED`, `SCD_TYPE_2`, `SCD_TYPE_2_BY_TIME`, `SCD_TYPE_2_BY_COLUMN`, and `EMBEDDED`. `FULL` drops and recreates the table each run; `INCREMENTAL_BY_TIME_RANGE` deletes the `time_column` window then inserts; `INCREMENTAL_BY_UNIQUE_KEY` uses a native `MERGE` on Postgres 15+ (logical merge fallback on earlier versions); `INCREMENTAL_BY_PARTITION` uses Vulcan's generic delete-plus-insert fallback, not native partition DDL. For the full mapping, see the [Postgres engine manual](https://v2.dataos.info/references/engine-guide/postgres).

> Postgres folds unquoted identifiers to lowercase (the opposite of Snowflake and Spark's UPPERCASE convention). Author model names, columns, semantic references, and metric references in lowercase, or quote identifiers consistently everywhere.

Wrap DDL that should run only on real execution in `pre_statements` / `post_statements`, guarded with `@IF(@runtime_stage = 'evaluating', ...)`, or `vulcan plan` executes it. Raw PL/pgSQL the parser can't interpret (anonymous `DO $$ ... $$` blocks, `CREATE FUNCTION ... LANGUAGE plpgsql`) still runs, but logs a harmless "could not be semantically understood" warning — silence it with `VULCAN_IGNORE_WARNINGS=1` or `vulcan --ignore-warnings`.

## Limits and performance

A 1–2 vCPU, 2–4 GiB instance is usually enough for dev. Start `concurrent_tasks` at 2 for daily incrementals, and size `max_connections` for Vulcan workers, API replicas, and BI sessions together — each API replica consumes its own connections, so pair replica growth with PgBouncer or added connection capacity. Index the incremental `time_column` / `unique_key`, and for the semantic API and concurrent BI, add a pooler or read replica and watch the connection count.

If concurrency degrades, fix in order: remove cross-join-shaped queries, add missing indexes on hot filter/join columns, add a connection pooler such as PgBouncer, add a read replica for query traffic, then raise instance size last. On a 50M-row PostgreSQL 15.17 reference run, a primary-key lookup returned in \~49ms, a 3-way join aggregate took \~35s, and `COUNT(DISTINCT ...)` took \~28s — reproduce on your own instance before quoting numbers like these.

## Troubleshooting

| Symptom                                  | Likely cause                                  | Fix                                                          |
| ---------------------------------------- | --------------------------------------------- | ------------------------------------------------------------ |
| `database "warehouse" does not exist`    | Target database missing or wrong name         | Create the database; fix `connection.database`.              |
| `relation raw.raw_orders does not exist` | Missing table, grant, or external declaration | Grant `SELECT`; add to `inputs.yaml`.                        |
| Semantic `column does not exist`         | Casing mismatch                               | Use lowercase identifiers, or quote consistently everywhere. |
| `unacceptable schema name "pg_..."`      | `pg_` prefix reserved                         | Rename the schema.                                           |
| `vulcan plan` runs DDL                   | Lifecycle DDL not guarded                     | Wrap it in `@IF(@runtime_stage = 'evaluating', ...)`.        |
| Too many connections for role            | Workers + replicas + BI exceed budget         | Lower concurrency/replicas; add PgBouncer.                   |
| `psycopg2` fails to build from source    | Local build lacks `pg_config` / libpq headers | Use the wheel/binary supplied by the Vulcan distribution.    |

For the full failure-mode table, health-check queries, and recovery procedures, see [Troubleshooting](https://v2.dataos.info/references/engine-guide/postgres/troubleshooting) in the Postgres engine manual. That manual also covers full-text search and row-level security recipes.

Once `vulcan info` succeeds, continue to [Assets](https://v2.dataos.info/build/productize/assets.md).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/build/productize/connect-engine/postgres.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
