Skip to content
mempill

Choosing a Backend

mempill ships two persistence backends. Both pass the shared behavioral conformance harness, so your claim logic works identically on either — only the deployment characteristics differ.

Decision table

Section titled “Decision table”
Criterion SQLite (topology-a) PostgreSQL (topology-b)
Setup Zero config — file per agent Requires running PG 16+ instance
Multi-agent One DB file per agent_id Shared DB; true per-agent concurrency
Write locking Single-connection serialized writes pg_advisory_xact_lock per agent_id
Connection pool Single connection r2d2 pool (default max 20, configurable)
Production TLS N/A Planned future release (NoTls only today)
Vector search NoOp (structural seam only) NoOp (structural seam only)
Best for Single-agent, embedded, tests, MCP Multi-agent production service

SQLite (topology-a) — embedded default

Section titled “SQLite (topology-a) — embedded default”

SQLite is the default backend and requires no external process. The adapter opens one database file per agent_id and applies mandatory PRAGMAs at connection open:

PRAGMA journal_mode = WAL;
PRAGMA synchronous  = FULL;
PRAGMA foreign_keys = ON;

synchronous=FULL is non-negotiable. WAL + NORMAL can lose committed writes on an unclean shutdown. All writes are serialized through a single connection per process — the engine’s per-agent lock map and a store-level write lock enforce this invariant.

Opening a SQLite engine

Section titled “Opening a SQLite engine”

SQLite constructors are per-agent as of mempill 0.4.0 — the database file is derived automatically as base_dir/agent_{agent_id}.db, so it is structurally impossible for two different agent_ids to collide on the same file.

use mempill_sqlite::{open_default_for_agent, open_default_in_memory};
use std::path::Path;


// File-backed (durable) — file derived as base_dir/agent_{agent_id}.db
let engine = open_default_for_agent(Path::new("/path/to/data"), "my-agent")?;


// In-memory (ephemeral — useful for tests and MCP sessions)
let engine = open_default_in_memory()?;

When to choose SQLite

Section titled “When to choose SQLite”
  • Single-agent embedded deployment
  • CLI tools, MCP adapters, local development
  • Integration tests (in-memory mode, zero config)
  • Situations where an external database process is undesirable

PostgreSQL (topology-b) — shared database

Section titled “PostgreSQL (topology-b) — shared database”

The PostgreSQL adapter targets multi-agent deployments where multiple agents share a single database. It uses an r2d2 connection pool (default max 20, configurable via PoolConfig as of 0.4.0) and serializes same-agent writes via pg_advisory_xact_lock(hashtext(agent_id)::bigint). An optimistic concurrency control (OCC) constraint (UNIQUE(agent_id, stream_seq) on ledger_entries) provides belt-and-suspenders protection against concurrent write anomalies.

Schema is managed by a refinery migration embedded at compile time (V1 — runs once on first connect). Tested against PostgreSQL 16 and 18.4.

Opening a PostgreSQL engine

Section titled “Opening a PostgreSQL engine”

Rust:

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


let engine = open_postgres(
    "host=localhost port=5432 user=mempill dbname=mempill password=secret",
    None::<std::sync::Arc<NoOpOracle>>,
    None::<std::sync::Arc<NoOpVector>>,
    EngineConfig::default(),
)?;

When to choose PostgreSQL

Section titled “When to choose PostgreSQL”
  • Multi-agent deployments (several agents writing concurrently to a shared database)
  • Production service with external persistence
  • Scenarios requiring PostgreSQL-level backup and replication

Behavioral parity

Section titled “Behavioral parity”

Both adapters are verified behaviorally identical by the shared run_persistence_conformance harness in mempill-core (compiled under the test-support feature flag). The conformance suite runs against both backends in CI. Any claim that produces CommittedCheap on SQLite produces CommittedCheap on PostgreSQL for the same input.

Summary

Section titled “Summary”
  • Start with SQLite (open_default_for_agent or open_default_in_memory). It requires no external process and is the default for tests, MCP adapters, and single-agent tools.
  • Migrate to PostgreSQL when you need multiple agents sharing a single database or durable, poolable persistence across multiple agents.
  • Never mix the two in a single deployment.

Next steps

Section titled “Next steps”