Postgres

Postgres is the framework's reference dialect — every reactive feature was prototyped against it and the others were brought to parity. If you have no procurement constraint, this is the dialect that gives you the lowest latency + highest feature density without workarounds.

#Why it's the default

• LISTEN/NOTIFY delivers change events with very low latency end-to-end. mariadb gets cross-instance CDC too (tailing the ROW-format binlog); mysql/mssql are inline-only (the writing instance emits its own deltas — single-instance reactivity); sqlite uses an in-process bus.
• Advisory locks (pg_advisory_lock(key)) give @effect/cluster's SqlRunnerStorage a coordinatable primitive for shard ownership. Workflow runners migrate cleanly on instance loss; no manual recovery needed.
• Streaming replication is the basis of the framework's read-replica adapter — LSN positions track replica freshness for the per-subject RYW (read-your-writes) policy.
• JSONB stores json() columns natively; the driver parses on read, the framework never sees serialized strings.
• BIGSERIAL / GENERATED ALWAYS AS IDENTITY for numeric auto-increment without surprises.
• TIMESTAMPTZ keeps timezone offset on the wire; the framework's Date columns round-trip without ambiguity.

#Configuration

DB_DIALECT=postgres
PG_HOST=…  PG_PORT=…  PG_USER=…  PG_PASSWORD=…  PG_DATABASE=…

# Recommended for any deployment that uses CDC, replicas, or BOTH:
# `wal_level=logical` is required for logical-replication slots.
# `max_replication_slots ≥ 4` so the framework can open one per
# replica + leave headroom for ad-hoc pg_recvlogical sessions.

wal_level=logical is required only for logical-replication consumers (the CDC replication adapter opens a slot). The LISTEN/NOTIFY reactivity path doesn't need WAL at all — it works on the default wal_level. If you enable replication and the slot can't open, the driver error surfaces the misconfiguration.

#Driver: @effect/sql-pg

The framework wraps pg (node-postgres) via Effect's typed connection layer. Key settings the framework defaults sensibly + you can override:

Env var	Default	What it does
PG_HOST	localhost	hostname
PG_PORT	5432	port
PG_USER	app	role
PG_PASSWORD	app	password (use a secret manager in prod)
PG_DATABASE	app	database name
PG_MAX_CONNECTIONS	10	pool ceiling. Tune up for high concurrency; CPU-bound workloads rarely benefit past ~2× cores.
PG_SSL	unset	'require' (TLS without certificate verification — what production deployments behind RDS / Cloud SQL / Supabase want) / 'disable' (force plaintext). Any other value — including libpq's 'prefer', which node-postgres cannot express (the driver has no TLS-then-plaintext fallback) — fails at boot instead of silently downgrading to plaintext. A ?sslmode=require|disable query on DB_URL works too; an explicit PG_SSL wins when both are set.

#CDC — LISTEN/NOTIFY

Every *.reactive() table gets an AFTER trigger that emits a framework_changes notification on each insert / update / delete. The dispatcher LISTENs on that channel once per process and fans out to subscribers in-memory.

Latency: very low on a local-network postgres (it varies with network and load). The framework instruments this — voltro traces shows the notification → dispatcher → subscriber waterfall.

Trade-offs of the LISTEN/NOTIFY path:

• ✅ Sub-frame latency (60fps UIs feel real-time).
• ✅ Zero polling load even on idle tables.
• ❌ Requires a long-lived connection per process. Connection-pooled deploys must use a sidecar listener or pgbouncer in session mode.
• ❌ NOTIFY payloads are capped at 8KB. The trigger sends the full row images (row_to_json(OLD)/row_to_json(NEW)) so subscribers get the pre/post values directly; when a wide row would exceed the cap the trigger drops the body and sends an ID-only notification (the subscriber re-queries), so a very wide reactive row degrades gracefully rather than failing.

Set CDC=0 to disable + force the inline-emit path (single-process only, no cross-process fan-out). Useful for tests + single-binary deploys.

#Workflow cluster

@effect/cluster's SqlRunnerStorage uses pg_advisory_lock(key) to claim shard ownership. The framework wires this transparently — set DB_DIALECT=postgres + provide a SqlClient layer and workflowEngineLayer({ runnerStorage: 'sql' }) does the rest.

Shard re-assignment on runner death: postgres releases advisory locks on session close, so a crashed runner's shards become acquirable by survivors automatically. Typical takeover time: 5–15s depending on heartbeat interval.

#Read replicas

The framework's ReplicatedDataStore uses pg_last_wal_replay_lsn() to measure replica freshness against the primary's pg_current_wal_lsn(). Per-subject RYW (read-your-writes) waits until the replica catches up to the primary's LSN at write time, or falls back to the primary if RYW_POLICY=fallback.

Enable with:

DB_REPLICA_URLS=postgresql://app:app@replica-1:5432/app,postgresql://app:app@replica-2:5432/app
RYW_POLICY=fallback   # default. 'wait' is the alternative.
RYW_TTL_MS=30000      # how long a write keeps its subject on the primary.

Without DB_REPLICA_URLS the wrapper isn't instantiated — zero overhead, every query goes to primary.

#JSON columns

json() columns emit JSONB in DDL (binary stored format, indexable via GIN, comparison/membership operators native). The driver auto-parses on read; the framework never sees strings.

If you need raw JSON (text) storage for some reason — preserving formatting, embedding non-canonical UTF-8 — drop down to unsafe() and emit JSON explicitly. Rare.

#Identifier quoting

"users" — double quotes. The framework emits these for every identifier when needed; user-written SQL that hand-quotes column names needs to use double quotes for portability.

#Migration emitter

voltro migrate against postgres uses the standard DDL idiom:

• CREATE TABLE … IF NOT EXISTS
• ALTER TABLE … ADD COLUMN … IF NOT EXISTS
• CREATE INDEX … IF NOT EXISTS
• FK constraints with explicit ON DELETE / ON UPDATE clauses
• BIGSERIAL PRIMARY KEY for numeric id columns

The _voltro_migrations ledger is a regular table with a unique constraint on (id, hash) for idempotency — re-running the same migration is a no-op.

#Known caveats

• Long-running transactions hold ROW EXCLUSIVE locks. The framework's transactional() wrapper auto-retries on serialization failures (40001) and deadlocks (40P01); if you build custom long-running flows, expect contention.
• pg_listen_notify has a per-connection capacity. The framework uses ONE dedicated LISTEN connection per process — never multiplexes through the pool.
• pg_advisory_lock keys are 8-byte ints. The framework hashes shard-id strings to int64; collisions are astronomically improbable but theoretically possible.

#Where it lives

• voltro/packages/sql-postgres/src/store.ts — PostgresDataStore implementation + LISTEN/NOTIFY consumer
• voltro/packages/sql-postgres/src/retry.ts — isRetryablePgFailure (40001 / 40P01)
• voltro/packages/sql-postgres/src/replicationAdapter.ts — LSN compare for RYW
• voltro/packages/database/src/migrate.ts — postgres DDL emission (the orElse branch)