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.

PG_SSL is read by every command that opens a connection — voltro dev, voltro serve, voltro start, voltro migrate and every voltro db … subcommand — so TLS is not something one command negotiates and the next one skips.

#CDC — LISTEN/NOTIFY

Every 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.

Every table gets this by default. Reactivity is what the framework is for, so you write nothing to opt in — the trigger above is installed for every table unless you opt it out.

.nonReactive() turns reactivity off for a table — not "off across instances". It emits no change events at all: no local subscriber fires and no cross-instance transport carries it. That holds on every dialect, including sqlite and turso, because the guard sits at each store's emit. On postgres it additionally drops the trigger and REPLICA IDENTITY FULL; on mysql/mariadb and mssql it drops the table from the reader's filter.

The write itself is unaffected — this is about notification, never persistence.

Worth using for a genuinely hot table nobody subscribes to: an append-only event log, a metrics sink. REPLICA IDENTITY FULL widens every UPDATE/DELETE in the WAL and the trigger fires on every write, so that is a real saving.

Do not use it on a table a query reads — that subscription will never fire. voltro dev and voltro serve warn at boot when that combination exists.

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 8000 bytes. The trigger sends the full row images (row_to_json(OLD)/row_to_json(NEW)) so subscribers get the pre/post values directly; a wide row that would exceed the cap falls back to a key-only notification. See the delivery guarantee below — the fallback is not free, and it is not the same for a subscription as it is for a tap.

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

#Oversized rows — what is guaranteed

A row whose JSON image exceeds ~8000 bytes (a document, a large json() column, an embedded array) cannot travel in a NOTIFY payload. The trigger keeps the change and the primary key, drops the images, and the CDC consumer re-reads the row from the database before the event reaches anything. What each consumer gets:

Change	What is delivered	event.oversized
insert / update	the row re-read from the database	'rehydrated'
delete	the primary key only — a tombstone	'tombstone'
key missing, re-read failed, or the row is already gone	both images null	'unrecovered'

Read the marker before you treat an image as a snapshot. Three limits are real and cannot be engineered away:

• A re-read returns the row as it is NOW. If a second write lands between the change and the re-read, this event carries the newer state — and the second change delivers it again. The stream is convergent, not point-in-time. Postgres keeps no copy of an image the transport dropped.
• old is null on an oversized update, and pk-only on an oversized delete. There is nowhere to read a pre-image from. A tombstone is enough to REMOVE the row from a search index, an analytics mirror or a CDC stream; it is not a record of what the row contained, and @voltro/plugin-row-history writes data: null for one rather than a fabricated empty snapshot.
• 'unrecovered' means the content is gone. No retry can bring it back — it was never delivered. Subscriptions are unaffected (they re-query); taps miss that row until the next write to it or a re-seed.

Every fallback is counted as voltro_cdc_oversized_total{outcome=…} (scrapeable via @voltro/plugin-prometheus at /metrics, or GET /_voltro/inspect/metrics), the first one per table is logged at warn, and every unrecovered one is logged at error. Alert on outcome="unrecovered" — a non-zero rate means the deployment is losing changes for its taps.

Tunables (options on the postgres store, or environment):

Env	Default	Meaning
VOLTRO_CDC_REHYDRATE_TIMEOUT_MS	5000	total budget for recovering one oversized change. The LISTEN consumer is serial, so this also bounds how long one oversized row can hold up the change stream.
VOLTRO_CDC_REHYDRATE_RETRIES	2	re-reads after the first attempt, inside that budget.

The re-read is issued against the schema the write landed in, so it is correct under namespace (schema-per-tenant) isolation.

This lives in the database, so it has to be applied. The trigger function is DDL: a database created before this shipped still carries the old body, which drops the key and makes every oversized change 'unrecovered'. voltro db apply replaces it (voltro dev reports it at boot as trigger drift, and the error log line names the same remedy).

#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)