PostgreSQL Backend

mempill-postgres is the PostgreSQL persistence adapter for mempill. It implements the same PersistencePort trait as mempill-sqlite and passes the same cross-adapter conformance harness, guaranteeing behavioral parity between the two backends.

When to use PostgreSQL

Scenario	Recommendation
Single agent, single process	SQLite — simpler, zero infra
Multiple agents, shared database	PostgreSQL — per-agent advisory locking
Multiple processes writing the same agent	PostgreSQL — r2d2 pool + OCC
Embedded / no server	SQLite only (PostgreSQL requires a running server)

Cargo dependency

[dependencies]
mempill-postgres = "0.2"
mempill-core     = "0.2"
mempill-types    = "0.2"

tokio  = { version = "1", features = ["full"] }
anyhow = "1"

All crates are published on crates.io.

Opening an engine

Without an oracle

use mempill_postgres::{open_postgres, PostgresEngine};
use mempill_core::{EngineConfig, NoOpOracle, NoOpVector};

let conn_str = "host=localhost port=5432 user=mempill dbname=mempill password=secret";

let engine: PostgresEngine<NoOpOracle, NoOpVector> = open_postgres(
    conn_str,
    None,                      // oracle — None uses NoOpOracle
    None,                      // vector — None uses NoOpVector
    EngineConfig::default(),
)?;

With an oracle

use mempill_postgres::open_postgres_with_oracle;
use std::sync::Arc;

let engine = open_postgres_with_oracle(
    conn_str,
    Arc::new(my_oracle),       // impl OraclePort + Send + Sync + 'static
    None,
    EngineConfig::default(),
)?;

Both constructors are exported from mempill-postgres/src/store.rs and re-exported at the crate root.

Connection string format

Standard libpq keyword-value format:

host=localhost port=5432 user=mempill dbname=mempill password=secret

Or URI format (also accepted by the underlying postgres crate):

postgresql://mempill:secret@localhost:5432/mempill

TLS: NoTls only in the current release. Production TLS is planned for a future release. Until then, use a private network or SSH tunnel for connections in production.

Concurrency model (topology-b)

The Postgres adapter is designed for one backend service per deployment with multiple concurrent agents sharing one PostgreSQL database. Three mechanisms work together:

1. r2d2 connection pool

Up to 20 connections (default) from a single process. Concurrent reads from different agents do not block each other.

2. Per-agent advisory lock

Every write acquires pg_advisory_xact_lock(hashtext(agent_id)::bigint) at the start of the transaction. This serializes same-agent writes across pool connections — the single-writer invariant (I9) holds without a global application lock.

3. OCC belt-and-suspenders

UNIQUE(agent_id, stream_seq) on ledger_entries catches any race condition that slips through the advisory lock. A UniqueViolation error from the Postgres layer is mapped to a retryable ConflictError at the SDK boundary.

Because requires_global_write_serialization() returns false for the Postgres adapter, EngineHandle skips its internal global write mutex — Postgres handles cross-agent concurrency natively at the database level.

Schema and migrations

The schema is embedded at compile time via refinery::embed_migrations!("migrations"). Migrations run automatically on first connection — no manual schema setup is required. The migration runner is idempotent; re-running against an up-to-date database is safe.

Tested against PostgreSQL 16 and PostgreSQL 18.4 via testcontainers.

Running the integration tests

The PostgreSQL integration tests spin up real postgres:16 / postgres:18.4 containers via testcontainers, so they require Docker. They are gated behind the postgres-integration cargo feature (the same pattern sqlx and sea-orm use), so a plain cargo test --workspace stays fast and Docker-free. Run them explicitly:

cargo test -p mempill-postgres --features postgres-integration       # just the PG tests
cargo test --workspace --features mempill-postgres/postgres-integration   # full verification

testcontainers removes each container when its handle drops (on a clean run), and the watchdog feature (enabled here) cleans up on Ctrl-C / SIGTERM too. As a fallback (e.g. after a hard kill -9), sweep any strays with:

docker rm -f $(docker ps -aq --filter 'label=org.testcontainers.managed-by=testcontainers')

Docker Compose example for local development

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: mempill
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: mempill
    ports:
      - "5432:5432"
    volumes:
      - mempill-data:/var/lib/postgresql/data

volumes:
  mempill-data:

Start with docker compose up -d, then connect with:

host=localhost port=5432 user=mempill dbname=mempill password=secret

Behavioral parity with SQLite

Both adapters pass the shared run_persistence_conformance harness. The following behaviors are identical:

Claim ingestion, conflict detection, Contested surfacing
CommittedCheap / Contested / QueuedForAdjudication dispositions
query_memory belief fold (including valid-time succession)
query_audit ledger contents
reconcile and all oracle paths
Ledger append-only invariant (I1)
Atomic commit per claim (I9)

The only behavioral difference is the concurrency model: SQLite uses a global write mutex in EngineHandle; Postgres uses per-agent advisory locks at the database level.

Full API usage

The API (ingest_claim, query_memory, reconcile, query_audit, list_pending_adjudications, submit_adjudication, sweep_expired_adjudications) is identical to the SQLite adapter. See Rust (Advanced) for full field documentation and patterns. The Rust guide examples work unchanged with PostgresEngine.

Known limitations

TLS: NoTls only. Do not expose the PostgreSQL port to untrusted networks without a TLS terminator or SSH tunnel. Production TLS is planned for a future release.
Connection string: libpq keyword-value and URI format only. No SSL parameters in the connection string yet.
No Python binding for Postgres directly: the Python wheel (mempill-python) uses the SQLite adapter. Postgres is available to Rust consumers only in the current release.

Rust (Advanced) — full engine API with field-level documentation
Writing an Oracle — using open_postgres_with_oracle
Choosing a Backend — decision matrix