Postgres

@helipod/docstore-postgres implements PostgresDocStore, the Postgres analogue of the SQLite adapter's DatabaseAdapter: the same DocStore contract, a different storage substrate underneath. The engine itself never imports a Postgres driver directly. PostgresDocStore sits on a narrow, driver-agnostic PgClient seam whose core surface is query, transaction, acquireWriterLock, and close. This is the same "engine never imports a driver" discipline docstore-sqlite follows for SQLite. A leak of driver specifics out of the adapter package would be a design bug.

Two implementations of that seam ship, and the engine picks one automatically by runtime. Under Bun (the primary production runtime, including the compiled single binary), it uses BunSqlClient, built on Bun's native Bun.SQL and measured roughly 10 to 17% faster per query than the pg driver. Under Node, or any other non-Bun host, it uses NodePgClient, built on the pg (node-postgres) driver. Both implement the full seam, both pass the same conformance suite, and nothing downstream of PgClient knows or cares which one it got.

A few normalization details the clients handle so PostgresDocStore never has to think about the driver:

• Postgres int8 (an MVCC timestamp column) decodes as a JS bigint, not a string. NodePgClient installs a per-client type-parser override for that one OID rather than mutating pg's types globally; BunSqlClient passes bigint: true to Bun.SQL.
• Postgres bytea (index keys, internal document ids) round-trips as Uint8Array in both directions.
• The writer lock and transaction() live on one pinned connection for the client's lifetime. BEGIN/COMMIT must land on the same session, and the single-writer invariant is itself a property of that one session. Reads are not confined to it: streaming index scans (see the next section) run on a small bounded pool of extra connections, a pg read pool on Node and a reuse pool of reserved Bun.SQL connections under Bun. (Tier 2 fleet sharding opens additional per-shard commit connections. That's a multi-node concern covered in Scaling, not part of the single-node path this page describes.)

The Postgres adapter is physically schemaless. Your app's tables, fields, and indexes are never represented as Postgres DDL. They're data inside a small, fixed set of internal tables that never change shape as schema.ts evolves:

documents  (table_id, internal_id, ts, prev_ts, value, shard_id)   -- one row per document revision
indexes    (index_id, key, ts, table_id, internal_id, deleted, shard_id)  -- MVCC index entries
persistence_globals (key, value)                                   -- engine metadata KV
client_mutations (identity, client_id, seq, verdict, commit_ts, value_json, error_code, created_at)
client_floors    (identity, client_id, pruned_through_seq, updated_at)  -- offline outbox receipts

A table in your schema.ts is a table_id value. A field is a key inside the JSON blob in value. An index is a set of rows in indexes. Adding a table, adding a field, or changing an index needs no ALTER TABLE, no migration file, and no Postgres-side schema change at all. That's why the Postgres adapter is bounded in scope rather than an open-ended migrations project. There's nothing app-shaped to migrate.

Reads over this log-shaped storage are set-based, not per-row round trips. scan/count use SELECT DISTINCT ON (internal_id) ... ORDER BY internal_id, ts DESC to resolve "the latest revision of every document" in one query. An index scan (what .collect()/.paginate() compile down to) uses DISTINCT ON (key) to find each key's latest visible index entry, LEFT JOIN LATERAL to resolve each entry's document in the same query, and filters tombstoned or deleted rows out before applying LIMIT (a raw SQL LIMIT there would count dead rows and silently return short pages). The OCC conflict check's "read the previous revision of N documents" step is similarly one round trip: a VALUES list of the N lookups, LATERAL-joined to each one's latest visible revision, instead of N separate queries.

Paginated and limited reads (.paginate(), .take(n), .first()) stream rows from a server-side Postgres cursor instead of materializing the whole index range up front. The moment the query engine stops consuming, because the page is full or the limit is hit, the cursor stops fetching. Measured at 100k rows on the paginated shape, that's about a 92% cut in p50 wall-clock and roughly 1000x fewer rows fetched: the win is work avoided on the server, not just bytes saved on the wire.

This is on by default and needs no configuration. Set HELIPOD_PG_STREAM=0 (or false) to disable it; the adapter then falls back to the buffered read path, which returns identical results (the full docstore conformance suite runs over both paths to prove that). Under the hood, NodePgClient streams via pg-cursor with adaptive fetch batching, and BunSqlClient uses DECLARE/FETCH cursors, since Bun.SQL has no native cursor API. One honest caveat on the Bun side: a pooled streaming connection that dies while idle is only detected on its next use. That surfaces as one spurious error, after which the pool discards the connection and recovers on its own.

Every commit through the single writer normally does its own BEGIN/insert/COMMIT, which on Postgres means its own fsync. Group commit batches multiple concurrent commits that land in the same short window into one shared transaction: one fsync amortized across all of them, without changing the atomicity or ordering guarantees of any individual commit.

Group commit defaults on for single-node Postgres and off for SQLite. HELIPOD_GROUP_COMMIT overrides that default in either direction (1/true/yes, case-insensitive, forces it on; any other value, including 0/false, forces it off):

# force it off even against Postgres
HELIPOD_GROUP_COMMIT=0 helipod serve --database-url postgres://... --dir helipod

# force it on even against SQLite (rarely useful, see the measured cost below)
HELIPOD_GROUP_COMMIT=1 helipod serve --dir helipod

The store-conditional default exists because the win is store-dependent, not universal. Measured on real containerized postgres:16 (fsync=on, synchronous_commit=on, genuine on-disk fsync per commit):

At one client it's byte-identical latency: the "batch of 1 when idle" design adds no wait when there's nothing to batch with. At real concurrency it's a strict throughput and latency win. The same batching measures as roughly an 8% throughput loss on SQLite: an in-memory, CPU-bound store has no fsync to amortize, so group commit there is pure pipeline overhead with nothing to pay for it. That's the whole reason the default is per-store rather than one global switch.

SQLite is the right default for local development and for a single-node deployment that doesn't need an externally-managed database. It's zero configuration, there's no separate service to run, and it's measurably the faster single-node store: an in-memory, CPU-bound commit path with no network round trip or fsync-per-commit cost.

Postgres is the right choice when you want durability, backups, monitoring, and replicas managed by infrastructure you already operate, or when you're headed toward the multi-node fleet in Scaling (which requires Postgres as its shared substrate). It's still a single-writer store at this tier. Postgres doesn't make helipod's transactor multi-writer by itself. You scale writes by sharding or by adding fleet nodes (Tier 2), never by adding threads against one Postgres connection.

Either way, write throughput is single-writer-bound and essentially flat across concurrency. That's the OCC single-writer transactor's architectural signature, not a Postgres-specific artifact. Measured on the same write-throughput benchmark (embedded Postgres, 3s/cell, insert workload):

Going from 1 to 8 to 64 concurrent clients barely moves either store's ops/s. Only tail latency grows, as extra clients queue behind the one serial writer instead of adding capacity. Postgres is roughly an order of magnitude slower than SQLite here, and it's fsync-bound: the commit's real cost is the disk sync, not the engine's own work. That's exactly why group commit is the effective lever on Postgres and not on SQLite. If your write volume genuinely exceeds what one Postgres writer delivers, the answer is Scaling's sharding and fleet options, not tuning this adapter further.

PostgresDocStore is checked against the same shared conformance suite SQLite runs: the identical set of DocStore assertions executed against both SqliteDocStore and PostgresDocStore (backed by an in-process PGlite Postgres for fast, hermetic runs), so the two backends are provably behaviorally interchangeable, not just similar by inspection.

On top of that, a real-container ship gate spins up an actual postgres:16 Docker container and drives the real helipod serve production entrypoint (spawned as the real CLI binary, not an in-process harness) through: a mutation commit, its reactive fan-out to a WebSocket subscription opened before the write, a read-back via a second query, a second serve process rejected fast by the single-writer lock while the first is still up, and, after stopping the first, a third serve process on the same database seeing the earlier row still there.

That's the same bar every self-host deploy path in this repo is held to: proven through the shipped entrypoint, not just unit-tested in isolation.