Skip to content

PostgreSQL connector

PostgreSQL is reached over the Postgres wire protocol with the postgres crate. Each query is prepared first — that is where the authoritative type OIDs come from — and the values are then read as text through simple_query, which lets NUMERIC (the type SUM/AVG produce) normalize exactly without pulling in a decimal dependency.

Sessions are pinned with SET TIME ZONE 'UTC', so a TIMESTAMPTZ renders zone-less regardless of the server's own timezone setting and buckets compare equal with every other engine.

Connections are pooled (r2d2, 8 by default) and opened lazily. Gated by the exec-postgres cargo feature.

Connection profile

datasources:
  warehouse_pg:
    type: postgres
    host: pg.internal
    port: 5432
    username: dosi
    password: ${PG_PASSWORD}
    database: analytics
    schema: public          # optional
    sslmode: prefer         # see TLS below

Or as a single DSN:

datasources:
  warehouse_pg:
    type: postgres
    uri: postgres://dosi:${PG_PASSWORD}@pg.internal:5432/analytics

Parameters

Key Type Required Default Notes
type string yes — postgres
host string yes* — *Unless uri: is given.
port int no 5432 Driver default.
username string no —
password string no — Prefer ${VAR}; a literal warns on stderr.
database string no —
schema string no — Pins search_path.
uri string no — A full postgres:// DSN, in libpq keyword syntax. It replaces the discrete host/port/username/password/database keys — with one exception: an explicit sslmode: still overrides whatever the DSN carries.
sslmode string no prefer See TLS.
sslrootcert path no — CA bundle, required by the verifying modes.
default bool no false See connection profiles.

Parsed and ignored on this connector: arrow_flight_port, compat_mode, account, role, warehouse, catalog.

Pool size is not a profile key — it is process-wide: --pool-size or DOSI_POOL_SIZE on the server, pool_size= in the Python bindings, 8 otherwise.

TLS

sslmode: takes the libpq vocabulary, with libpq's verification ladder:

sslmode Encrypted Certificate verified
disable no —
allow, prefer (default) if the server offers it no
require yes only when sslrootcert: is also set (libpq back-compat)
verify-ca yes chain, against sslrootcert:
verify-full yes chain and hostname

Two consequences worth knowing. When a mode verifies, the built-in root store is switched off deliberately — the connection trusts exactly the CA in sslrootcert:, nothing else. And verify-ca/verify-full without sslrootcert: is rejected at construction, rather than quietly downgrading to an unverified connection.

Limitations

  • No 3-argument DATEDIFF. PostgreSQL has no DATEDIFF(unit, start, end); a metric written with it is refused at compile time for this dialect instead of returning something subtly different. Express the difference with date arithmetic (end - start) or EXTRACT.
  • Integer division truncates, as everywhere in the Postgres family. A ratio metric over two integer measures is computed as a true ratio by the compiler, but a hand-written integer division inside a field expression truncates.
  • Text ordering follows the server's collation, so ORDER BY on text can differ from a C-collation engine such as Hologres or DuckDB.

Verify the connection

$ dosi query --model model.yaml \
    --metrics revenue --group-by orders.status --execute --connection warehouse_pg

If the profile is wrong, the failure names the stage: a connection error never reached the server, an auth error reached it and was rejected, and a sql_rejected error is the server's own message about the compiled SQL.

Troubleshooting

Message Cause and fix
connection "x": bad url: <e> The uri: is not a valid postgres:// DSN.
connection "x" (postgres) is missing required field "host" Give host: or a full uri:.
server rejected SQL (<code>): <message> Postgres refused the compiled statement; the SQLSTATE and text are the server's.
connection "x": unknown sslmode "verify" One of disable, allow, prefer, require, verify-ca, verify-full.
connection "x": sslmode "verify-full" verifies the server certificate, which needs a CA bundle Add sslrootcert: /path/to/ca.pem.
connection "x": sslrootcert "<path>" is not a PEM certificate The file exists but is not PEM — a DER .crt lands here.
this build has no postgres executor (feature "exec-postgres" not enabled) Published binaries include it; a hand-built one needs --features exec-postgres.

Reference