Postgres

The @concile/docstore-postgres package provides PostgresDocStore. Think of it as the Postgres equivalent to our SQLite adapter. It follows the exact same contract but uses a different database under the hood. To keep things clean, the core engine never directly imports a Postgres driver. Instead, PostgresDocStore relies on a simple, driver-independent interface called PgClient for things like queries, transactions, and locks. We use the same design pattern for SQLite, ensuring that driver-specific details never leak into the rest of the application.

We actually ship two different implementations of this interface, and the engine automatically picks the best one for your setup. If you are using Bun, which includes our compiled single binary, it uses BunSqlClient. This is built on native Bun.SQL and is about 10 to 17% faster per query than the standard Node driver. If you are on Node or any other runtime, it falls back to NodePgClient, powered by the popular pg driver. Both options are fully tested and behave exactly the same, so the rest of the system never needs to worry about which one is running.

These clients also handle some helpful data normalization behind the scenes:

• Postgres timestamp columns (int8) are automatically converted to JavaScript bigint values instead of strings.
• Binary data (bytea), like index keys and document IDs, seamlessly converts back and forth to Uint8Array.
• The writer lock and active transactions are securely pinned to a single connection for the lifetime of the client. This means BEGIN and COMMIT always happen on the same session, keeping our single-writer rule intact. Reads, on the other hand, are free to use a small pool of extra connections for fast, streaming index scans.

You will be happy to hear that our Postgres adapter doesn't use a traditional schema. The tables, fields, and indexes you define in your app are never actually converted into Postgres table structures. Instead, we store everything as data within a few fixed internal tables. These tables stay exactly the same even as your 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

When you define a table in your app, it just becomes a table_id. Fields are just keys in the JSON blob, and indexes are just rows in the indexes table. This means you can add tables, create new fields, or change indexes without ever needing an ALTER TABLE command or a complex migration file. It keeps the Postgres adapter simple and focused, saving you from migration headaches.

Reading from this log-based storage is incredibly efficient. We use set-based queries rather than checking rows one by one. For example, a scan uses a neat SQL trick to instantly find the latest revision of every document in a single query. Index scans resolve the right document and filter out deleted rows before limiting the results, ensuring you always get accurate pages. We even check for conflicts by fetching the latest revisions of multiple documents in one quick database trip.

When you paginate or limit your reads using methods like .paginate(), .take(n), or .first(), the engine streams the rows using a Postgres cursor instead of loading everything into memory at once. As soon as the page is full or the limit is reached, it stops fetching. In our tests with 100,000 rows, this approach cut the wait time by 92% and fetched roughly a thousand times fewer rows. It is a huge win for server performance, not just network bandwidth.

This streaming feature is enabled by default, so you don't need to configure a thing. If you ever want to turn it off, just set CONCILE_PG_STREAM=0 (or false), and the system will fall back to standard buffered reads. Don't worry, both methods return the exact same data!

Normally, every commit through the single writer handles its own BEGIN, insert, and COMMIT. On Postgres, this means a separate disk sync (fsync) for each one. Group commit changes this by batching multiple concurrent commits from a short time window into one shared transaction. This way, we only pay the cost of one disk sync across all of them, while still keeping the exact same atomicity and ordering guarantees for every individual commit.

We have set group commit to be on by default for single-node Postgres and off for SQLite. If you ever need to change it, you can use the CONCILE_GROUP_COMMIT environment variable. Setting it to 1, true, or yes turns it on, while anything else turns it off.

# Turn it off even for Postgres
CONCILE_GROUP_COMMIT=0 concile serve --database-url postgres://... --dir concile

# Turn it on even for SQLite (though this usually isn't helpful, as explained below)
CONCILE_GROUP_COMMIT=1 concile serve --dir concile

We chose these defaults because the benefits really depend on the storage engine you are using. Here are the numbers from a real containerized postgres:16 setup with true on-disk syncing enabled:

With just one client, the latency is practically identical. Our system is smart enough to skip batching when there is nothing to batch with. But as concurrency grows, group commit delivers a huge win in both throughput and latency.

On the flip side, we saw about an 8% drop in throughput when running this on SQLite. Since SQLite runs in memory and is CPU-bound with no per-commit disk sync, group commit just adds overhead without any real benefit. That is exactly why we tailor the default setting to your chosen database.

If you are developing locally or running a simple single-node deployment, SQLite is typically your best bet. It requires zero configuration, you do not need to run a separate service, and it is noticeably faster since everything runs in memory without network or disk-sync overhead.

Postgres is a fantastic choice when you need enterprise features like backups, monitoring, and replication managed by your existing infrastructure. It is also required if you plan to scale up to a multi-node fleet later on (see Scaling). Just keep in mind that at this single-node tier, Postgres still uses a single-writer setup. Switching to Postgres will not magically make the transactor multi-writer. If you need to scale your writes, you will want to look at sharding or adding fleet nodes rather than just throwing more threads at a single Postgres connection.

In both databases, write throughput remains fairly constant even as concurrency increases. This is a core part of our optimistic concurrency control design, not a quirk of Postgres. Here is a look at the write throughput on an embedded Postgres setup with an insert workload:

Notice how jumping from 1 to 64 concurrent clients barely changes the operations per second for either database. What does increase is the tail latency, as extra clients have to wait their turn behind the single writer. Postgres is understandably slower here because it has to wait for disk syncs, whereas the engine itself does very little work. This is exactly why group commit makes such a big difference on Postgres but not on SQLite. If your app is pushing more write volume than one Postgres writer can handle, check out our Scaling guide for sharding and fleet options.

We test PostgresDocStore using the exact same conformance suite as our SQLite implementation. Both SqliteDocStore and PostgresDocStore (powered by an in-process PGlite Postgres for quick, isolated runs) pass the identical set of DocStore checks. This means the two backends are truly interchangeable in their behavior, not just similar in theory.

To be extra sure, our automated tests spin up a real postgres:16 Docker container and run the actual concile serve production binary through its paces. It tests a full cycle: committing a mutation, broadcasting it via WebSockets, reading it back, and making sure a second serve process gets safely blocked by the single-writer lock. Finally, it stops the first process and verifies that a third one can boot up and still see the data.

We hold every deployment path to this standard. We test the real shipped binary, not just an isolated unit test.