> 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/references/engine-guide/postgres.md). # Postgres PostgreSQL is an open-source relational database that works with Vulcan. Use it for smaller projects, development environments, or when you want full control over your database infrastructure. This page is the full manual: connection and authentication, grants, how each model kind materialises, the local Docker setup, indexing and concurrency guardrails, and the failure modes you hit in real projects. Use the [Connect engine → Postgres](https://v2.dataos.info/build/productize/connect-engine/postgres) Build page for the quick connection steps. {% hint style="info" %} Engine adapter type: `postgres`. Model dialect: `postgres`. Queries execute in the PostgreSQL server itself; there is no separate warehouse to start, stop, or resize. Tested Vulcan image: `tmdcio/vulcan-postgres:0.228.1.23` against PostgreSQL 14 to 16 on the Draco 1.38 series. {% endhint %} ## When to use Postgres Choose PostgreSQL for smaller Data Products, development, or when you want full control of the database. There is no warehouse cold-start penalty, but there is also no elastic scale-out: performance is shaped by instance size, IOPS, indexes, and data volume. PostgreSQL is a full relational database management system (RDBMS), so many native features (indexes, constraints, row-level security, materialized views, triggers, federation) are reachable through guarded raw DDL. ### 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`. Python models materialise an in-process Pandas DataFrame through the adapter and do not support `VIEW`, `EMBEDDED`, `SEED`, or `MANAGED` kinds. ## Connection options Set these under `gateways..connection`. | Option | Description | Type | Required | | ------------------ | --------------------------------------------------- | ------ | -------- | | `type` | Engine type name. Must be `postgres`. | string | Yes | | `host` | The hostname of the Postgres server. | string | Yes | | `user` | The username for authentication. | string | Yes | | `password` | The password for authentication. | string | Yes | | `port` | The port number. | int | Yes | | `database` | The database to connect to. Must already exist. | string | Yes | | `keepalives_idle` | Seconds between keepalive packets. | int | No | | `connect_timeout` | Seconds to wait for the connection (default 10). | int | No | | `role` | The role for authentication. | string | No | | `sslmode` | Connection security level (for example, `require`). | string | No | | `application_name` | The application name for the connection. | string | No | ### Authentication methods * Username and password (required): set `user` and `password`. * SSL mode (optional): set `sslmode: require` or stricter for secure connections in production. {% hint style="warning" %} Always inject the password from an environment variable: `password: {{ env_var('POSTGRES_PASSWORD') }}`. {% endhint %} ## Permissions and grants Three roles are required, each with a distinct scope. | Role | Who holds it | Purpose | | ------------------- | -------------------------------------- | ---------------------------------------------------------------------------- | | Admin role | PostgreSQL superuser / DBA | Creates databases, installs extensions, grants privileges. | | Vulcan service role | The role whose credentials Vulcan uses | Runs models, creates and modifies schemas and tables in the target database. | | Consumer role | BI users, endpoint consumers | Read-only access to Data Product tables. | Vulcan needs `CREATE` on the target database (for schemas), `CREATE` on schemas (for tables and views), `USAGE` on schemas, and `SELECT`, `INSERT`, `UPDATE`, `DELETE` on tables. Run minimum grants as a superuser or database owner: ```sql -- Target database. Vulcan materialises here; the database must already exist. GRANT CONNECT ON DATABASE warehouse TO vulcan; GRANT CREATE ON DATABASE warehouse TO vulcan; -- Source data landed by Nilus, seeds, or another ingestion path. GRANT USAGE ON SCHEMA raw TO vulcan; GRANT SELECT ON ALL TABLES IN SCHEMA raw TO vulcan; ALTER DEFAULT PRIVILEGES IN SCHEMA raw GRANT SELECT ON TABLES TO vulcan; ``` Consumer role (read-only): ```sql GRANT CONNECT ON DATABASE warehouse TO consumer; GRANT USAGE ON SCHEMA sales TO consumer; GRANT SELECT ON ALL TABLES IN SCHEMA sales TO consumer; ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO consumer; ``` Verify grants in `psql`: ```sql SELECT current_user, current_database(); \dn \dp raw.* ``` {% hint style="warning" %} `connection.database` must exist before `vulcan plan`. PostgreSQL has no catalog tier: use `database.schema.table`, and schemas referenced by models are created automatically when privileges allow. {% endhint %} ### DataOS permissions Your DataOS operator must provision `roles:id:data-dev` (or equivalent), workspace access, `depot:rw:`, `depot:r:`, and Git repository access. Run `dataos-ctl get depot` to confirm the PostgreSQL depot appears before you start. ## Example gateway config Minimum local connection: ```yaml gateways: default: connection: type: postgres host: "{{ env_var('WAREHOUSE_HOST', 'localhost') }}" port: "{{ env_var('WAREHOUSE_PORT', '5432') }}" database: "{{ env_var('WAREHOUSE_DATABASE', 'warehouse') }}" user: "{{ env_var('WAREHOUSE_USER', 'vulcan') }}" password: "{{ env_var('WAREHOUSE_PASSWORD', 'vulcan') }}" default_gateway: default model_defaults: dialect: postgres start: '2024-01-01' ``` For a local starter project, Vulcan can use the default PostgreSQL gateway connection. For TLS, add `sslmode: require` or stricter. ## Local development with Docker PostgreSQL is the one engine you can run end to end on your laptop. Create the Docker network once, then bring up a Postgres container. ```bash docker network create vulcan # if it already exists, continue ``` Save the following as `docker/docker-compose.warehouse.yml`: ```yaml # Central Warehouse - PostgreSQL for project data # Access: postgresql://vulcan:vulcan@localhost:5433/warehouse x-images: postgres: &postgres_image "postgres:17-alpine" volumes: warehouse: driver: local networks: vulcan: external: true services: warehouse: image: *postgres_image environment: POSTGRES_DB: warehouse POSTGRES_USER: vulcan POSTGRES_PASSWORD: vulcan POSTGRES_HOST_AUTH_METHOD: trust ports: - "5433:5432" volumes: - warehouse:/var/lib/postgresql/data healthcheck: test: ["CMD-SHELL", "pg_isready -U vulcan -d warehouse"] interval: 5s timeout: 5s retries: 5 restart: unless-stopped networks: - vulcan ``` ```bash docker compose -f docker/docker-compose.warehouse.yml up -d ``` Use the matching connection (note port `5433` on the host, and DuckDB for local state): ```yaml gateways: default: connection: type: postgres host: localhost port: 5433 database: warehouse user: vulcan password: vulcan state_connection: type: duckdb database: ./.state/vulcan.db default_gateway: default model_defaults: dialect: postgres ``` Validate the connection: ```bash vulcan --log-to-stdout plan ``` {% hint style="info" %} `--log-to-stdout` skips creating a `.logs/` directory, which avoids `PermissionError` when the working directory is not writable. {% endhint %} ## Production deployment Production usually uses a depot instead of a direct password in the project config. Apply resources in this order: ``` PostgreSQL password instance secret → PostgreSQL depot → Git secret → config.yaml → Vulcan resource ``` Two wiring styles are supported. Use a depot gateway (`type: depot`, `address: dataos://`), or bind an instance secret into the workflow with `envFrom` and let `config.yaml` read `WAREHOUSE_*` through `env_var()`. Pick one style per project; do not mix a depot gateway with conflicting environment variables. Depot purposes are `rw` (Vulcan workflow), `scan` (metadata scanner: reads `information_schema` and `pg_catalog`), and `query` (consumer read-only). ## Materialisation behaviour per model kind SQL models compile to PostgreSQL SQL and execute directly on the server. | Model kind | PostgreSQL operation | | ----------------------------------- | ----------------------------------------------------------------------------------------------------------------- | | `SEED` | `COPY` / batched `INSERT` from CSV into a table. | | `FULL` | `DROP TABLE`, `CREATE TABLE`, `INSERT` (rebuilds from scratch each run). | | `VIEW` | `CREATE OR REPLACE VIEW`. | | `INCREMENTAL_BY_TIME_RANGE` | DELETE by `time_column` window, then INSERT. | | `INCREMENTAL_BY_UNIQUE_KEY` | Native `MERGE` (UPSERT) on PostgreSQL 15+, logical merge fallback on earlier versions. | | `INCREMENTAL_BY_PARTITION` | Partition-key replacement through Vulcan's generic delete-plus-insert fallback (not native partition DDL). | | `INCREMENTAL_UNMANAGED` | Append/unmanaged pattern; Vulcan does not manage updates or deletes. | | `SCD_TYPE_2` / `SCD_TYPE_2_BY_TIME` | History table with validity windows tracked by time. | | `SCD_TYPE_2_BY_COLUMN` | History table with validity windows tracked by checked columns. | | `EMBEDDED` | Not materialised; inlined into downstream models. | | Python | In-process Pandas DataFrame written through the adapter (not valid for `VIEW`, `EMBEDDED`, `SEED`, or `MANAGED`). | ### Lowercase identifiers {% hint style="warning" %} PostgreSQL folds unquoted identifiers to lowercase. Author model names, columns, semantic references, metric references, and DQ SQL in lowercase unless you intentionally use quoted identifiers everywhere. This is the opposite of the Snowflake and Spark UPPERCASE rule. {% endhint %} ### Lifecycle DDL and raw PL/pgSQL Wrap DDL in `pre_statements` / `post_statements` when it should run only during real execution, or a dry-run plan executes it: ```sql @IF(@runtime_stage = 'evaluating', CREATE INDEX IF NOT EXISTS ix_orders_customer ON sales.orders (customer_id) ); ``` PostgreSQL-native DDL that the parser cannot interpret (anonymous `DO $$ ... $$` blocks, `CREATE FUNCTION ... LANGUAGE plpgsql`, trigger bodies) logs a harmless warning such as `'DO $$ ...' could not be semantically understood`. The block still runs verbatim; silence the console noise with `VULCAN_IGNORE_WARNINGS=1` or `vulcan --ignore-warnings`. Objects created inside PL/pgSQL are structural but opaque to SQL lineage. ### External source declarations Declare non-owned source tables in `external_models.yaml` so linting and lineage resolve them. The physical table must exist only if a model actually selects from it. ```yaml - name: '"warehouse"."raw_external"."country_lookup"' columns: country_code: VARCHAR country_name: VARCHAR region: VARCHAR ``` ### Semantic models and metrics Use `{semantic_model.column}` braces for measure references and declare joins for multi-entity queries; cross-join-shaped semantic queries are one of the easiest ways to saturate a single instance. Metrics use the current `kind: metric` shape with `ts` and `granularity`; some older example projects use an aggregate `metrics:` map with `time:`, which is legacy syntax. Two runtime behaviours matter: heavy `COUNT(DISTINCT ...)` over wide tables is a primary cost driver (pre-aggregate or add supporting indexes), and indexing matters more than on elastic warehouses (add an index on a slow filter/join column through guarded `post_statements`). Native constraints (`NOT NULL`, `CHECK`, `PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`) are enforced at write time and complement Vulcan audits. ## Endpoints Every deployed Data Product gets REST, GraphQL, and MySQL-wire endpoints, all pushing down to the same PostgreSQL gateway. Large result sets are buffered before streaming; size `api.resource.limit.memory` for expected payloads, with 3 GiB a practical starting point for high-row responses. ## Metadata scanning and catalog DataOS scans `information_schema` (databases, schemas, tables, columns, grants) and `pg_catalog` (constraints, indexes, comments, view definitions). PostgreSQL has no warehouse-style query-history table, so lineage comes from parsed definitions and Vulcan's model graph. Use a read-only scanner role separate from the Vulcan service role: ```sql GRANT CONNECT ON DATABASE warehouse TO scanner; GRANT USAGE ON SCHEMA sales, raw TO scanner; GRANT SELECT ON ALL TABLES IN SCHEMA sales, raw TO scanner; ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO scanner; ``` Scanner cadence is commonly every 6 to 12 hours. If lineage is missing after `vulcan apply`, verify `external_models.yaml`, then wait for the next scan. ## Engine-native feature support | PostgreSQL feature | Vulcan pattern | | --------------------------- | ----------------------------------------------------------------------------------------------- | | Database | `connection.database` (must exist before apply). | | Schema | Referenced by model names; created automatically when the role has privilege. | | Regular / incremental table | `FULL` and incremental models. | | View | `kind VIEW`. | | SCD history | `SCD_TYPE_2_BY_TIME` / `SCD_TYPE_2_BY_COLUMN`. | | Indexes | `CREATE INDEX` in guarded `post_statements`. | | Row-level security | `ENABLE ROW LEVEL SECURITY` plus `CREATE POLICY`. | | Triggers / functions | Guarded PL/pgSQL DDL (parser warning expected). | | Materialized view | Raw `CREATE MATERIALIZED VIEW` / `REFRESH` (`kind VIEW materialized true` does not create one). | | Full-text search | Generated `tsvector` plus GIN index. | | FDW federation | `postgres_fdw` extension plus foreign tables (DBA-level setup). | Boundaries: `MANAGED` model kind (Snowflake Dynamic Tables only), real materialized view from `kind VIEW materialized true`, elastic scale-out / auto-suspend, and cross-database queries in one statement (land data into the same database). Treat the server lifecycle, database creation, extensions, replication, and instance parameters as DBA boundaries. ## Operational boundaries ### Compute sizing A 1 to 2 vCPU, 2 to 4 GiB instance is usually enough for dev. For first backfills, temporarily raise `work_mem` / `maintenance_work_mem` and ensure disk headroom. Index the incremental `time_column` / `unique_key` and size IOPS to write volume. For the semantic API and concurrent BI, add a pooler or read replica and watch the connection count. ### Concurrency and API replicas Start with `concurrent_tasks: 2` for daily incrementals; raise it only if plan/run duration is the bottleneck and the instance has connection and I/O headroom. The API is stateless, but each replica consumes PostgreSQL connections, so pair replica increases with PgBouncer or higher connection capacity. Size `max_connections` for Vulcan workers, API replicas, and BI sessions together. ### Scheduling and latency floor Schedule after upstream lands, set `timezone: UTC`, set `endOn` 1 to 2 years out, and use `concurrencyPolicy: Forbid` (prevents overlapping windows and connection pile-ups). The latency floor: typically no cold start on provisioned PostgreSQL (serverless or auto-paused deployments may have wake-up latency); connection establishment is sub-second when warm; queries on unindexed filters pay sequential-scan cost. ## Performance reference Measure active connections versus `max_connections`, plan/run duration per model, semantic query wall time, rows touched per incremental window, and the sequential-scan versus index-scan ratio (`EXPLAIN`). When 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. Measured results from a 50M-row local run (2026-06-12) against a managed PostgreSQL 15.17 instance show the shape clearly: a point lookup by primary key returns in about 49 ms (index scan), while a 3-way join aggregate over 50.1M orders runs about 35 seconds (sequential scan ×3), and a `COUNT(DISTINCT customer_id)` runs about 28 seconds. `statement_timeout` enforcement is confirmed. A Vulcan-orchestrated full-refresh backfill of the same data (three `FULL` models in parallel: plan, snapshot, CTAS, audits) completed in about 2 minutes 28 seconds wall clock. Reproduce on your own instance before quoting these. ## Failure modes and troubleshooting | Symptom | Likely cause | Fix | | ------------------------------------------- | --------------------------------------------- | ---------------------------------------------------------------------- | | `database "warehouse" does not exist` | Target database missing or wrong name | Create the database; fix `WAREHOUSE_DATABASE` / `connection.database`. | | PostgreSQL connection refused | Infra not running or wrong host/port | Start PostgreSQL; use `host.docker.internal` from containers. | | Password authentication failed | Wrong password or role | Fix the password; confirm the role exists and can log in. | | `relation raw.raw_orders does not exist` | Missing table, grant, or external declaration | Grant `SELECT`; add to `external_models.yaml`. | | Plan succeeds but `psql` shows empty schema | Different role/database used for debugging | Use the same database and a role with `USAGE`. | | Semantic `column does not exist` | Casing mismatch | Use lowercase identifiers or quote consistently. | | `Depot not resolvable` on DataOS | Depot name mismatch | Match `dataos://?purpose=rw` to the depot manifest. | | `PermissionError: '.logs'` | CWD not writable | Run `vulcan --log-to-stdout ...` or make the directory writable. | | `unacceptable schema name "pg_..."` | `pg_` prefix reserved | Rename the schema. | | `vulcan plan` runs DDL | Lifecycle DDL not guarded | Wrap in `@IF(@runtime_stage = 'evaluating', ...)`. | | Incremental reprocesses history | Missing/wrong `time_column` or `unique_key` | Verify the model kind config matches actual columns. | | Endpoint OOM on large result | API memory too low | Raise API memory and limit result size. | | Too many connections for role | Workers plus replicas plus 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. | ### Health-check queries ```sql SELECT current_user, current_database(), current_schema(); \dn SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name LIMIT 50; SELECT count(*) AS conns, state FROM pg_stat_activity WHERE datname = current_database() GROUP BY state; ``` ### Recovery procedures | Situation | Procedure | | --------------------------------- | ----------------------------------------------------------------------------------------- | | Incremental run failed mid-window | Re-run; time-range incrementals reprocess the affected window. | | Table dropped manually | `vulcan plan` detects and recreates; use a rebuild command if needed. | | Wrong database applied | Fix `WAREHOUSE_DATABASE`, verify with `SELECT current_database()`, re-run plan/apply. | | Credentials leaked | Rotate the password; update `.env` / DataOS instance secret. | | Connection exhaustion | Lower concurrency/replicas, restart the pooler, investigate idle-in-transaction sessions. | ## Related * [Connect engine → Postgres](https://v2.dataos.info/build/productize/connect-engine/postgres) for the quick connection steps. * [Engine guide overview](/references/engine-guide/engine-guide.md) for the cross-engine rules. --- # 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 dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://v2.dataos.info/references/engine-guide/postgres.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. 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.