# PostgreSQL Persistence in OpenMAIC: Trade-offs, Benefits, and Implementation

> Explore PostgreSQL persistence in OpenMAIC. Understand tradeoffs like latency and scalability, benefits of ACID durability, and implementation details. Optimize your data management.

- Repository: [MAIC/OpenMAIC](https://github.com/THU-MAIC/OpenMAIC)
- Tags: deep-dive
- Published: 2026-09-09

---

**PostgreSQL persistence in OpenMAIC provides ACID durability and horizontal scalability through connection pooling, but introduces query latency, requires manual schema migration management, and restricts data types to JSONB-serializable values.**

OpenMAIC uses a pluggable storage architecture for runtime data including sessions, records, and learner materials. The PostgreSQL driver, implemented in `packages/@openmaic/storage/src/runtime/pg.ts`, offers production-grade reliability through atomic transactions and indexed JSONB storage. Understanding the trade-offs between data integrity guarantees and operational overhead is essential for teams deploying OpenMAIC at scale.

## How PostgreSQL Persistence Works in OpenMAIC

The PostgreSQL storage layer wraps the `node-postgres` driver with a transactional interface. The `withTransaction` helper ensures every operation executes within a fresh database transaction, providing atomicity for session appends and record updates. Schema initialization happens lazily through `ensureSchema`, which creates tables and indexes—including `runtime_sessions_stage_learner_idx` and `runtime_records_session_scene_idx`—only when they do not exist.

The following example demonstrates connecting to PostgreSQL and appending session data:

```typescript
// 1️⃣ Configure the DB connection (environment variable)
process.env.DATABASE_URL = 'postgres://openmaic:openmaic-dev@localhost:5432/openmaic';

// 2️⃣ Create a node‑postgres pool
import { Pool } from 'pg';
import { PgRuntimeStore, ensureSchema } from '@openmaic/storage';

// Wrap the pool in the required `withTransaction` contract
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const withTransaction = async <T>(body: (q: Queryable) => Promise<T>) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await body(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
};

// 3️⃣ Initialise the PostgreSQL runtime store
const runtimeStore = new PgRuntimeStore({ withTransaction });
await ensureSchema(runtimeStore); // creates tables if they don’t exist

// 4️⃣ Append a payload to a session
await runtimeStore.append({
  sessionId: 'sess‑123',
  payload: { role: 'assistant', content: 'Hello world!' },
});

```

This implementation relies on `DATABASE_URL` for connection parameters and uses `encodeJson` validation to ensure payload compatibility before persistence.

## Durability vs. Latency: The ACID Trade-off

**PostgreSQL persistence guarantees** durability through ACID-compliant transactions. Every write operation in `packages/@openmaic/storage/src/runtime/pg.ts` executes inside a transaction boundary that commits only after all modifications succeed, ensuring read-committed isolation and preventing partial writes.

However, these guarantees introduce **per-operation latency**. Each `append` or `update` call requires a network round-trip to the database server and synchronous disk writes for transaction commits. Compared to the in-memory PGlite alternative used for testing, PostgreSQL adds measurable overhead that becomes significant under high-throughput scenarios or when connection pool limits are reached.

## Scalability and Concurrency Management

The PostgreSQL implementation leverages **connection pooling** via `node-postgres` to handle concurrent users efficiently. Optimized indexes such as `runtime_sessions_stage_learner_idx` and `runtime_records_session_scene_idx`—defined in lines 80-96 of [`pg.ts`](https://github.com/THU-MAIC/OpenMAIC/blob/main/pg.ts)—enable fast lookups for learner-specific sessions and scene-based records.

Despite these optimizations, scaling requires careful capacity planning. Database performance depends on proper sizing of the connection pool, routine vacuum maintenance, and potentially manual sharding for very large deployments. Unlike horizontally scalable object stores, PostgreSQL instances must scale vertically or through read replicas, introducing replica lag considerations for real-time session data.

## Schema Evolution and Migration Limitations

The `ensureSchema` function provides **idempotent table creation**, safely creating schema objects only when missing. This protects against deployment races and simplifies initial setup.

However, OpenMAIC does **not automatically migrate existing table schemas**. Adding columns or altering types requires manual SQL migrations outside the application's runtime logic. While the system handles DSL version migrations through `needsRuntimeMigration` and `migrateRuntime` (lines 78-84, 91-93) for record-level data transformations, structural changes to the database schema remain an operational responsibility. This creates friction during upgrades when the data model evolves.

## JSONB Flexibility and Data Type Constraints

OpenMAIC stores session payloads in a **JSONB column**, allowing flexible schemaless storage of DSL-defined structures without requiring table alterations for every new field. The `payloadValidators` run in-process before persistence (lines 14-39), ensuring only well-formed JSON reaches the database.

This flexibility comes with **serialization limitations**. The `encodeJson` function rejects native JavaScript types including `Date`, `Map`, `Set`, `undefined`, and non-finite numbers. Serializing these values requires manual transformation to JSON-compatible primitives before calling storage methods, complicating client code that relies on rich JavaScript types.

## Operational Complexity and Security Considerations

Production deployments configure PostgreSQL through the **`DATABASE_URL`** environment variable, typically in a Docker Compose stack defined in [`docker-compose.yml`](https://github.com/THU-MAIC/OpenMAIC/blob/main/docker-compose.yml). The connection string follows the format `postgres://user:password@host:port/database`.

**Critical security pitfall**: The `PERSISTENCE_POSTGRES_PASSWORD` variable initializes the database only on first volume creation. Changing this environment variable after initial deployment does not update existing database users, causing authentication failures if the variable is rotated without recreating the PostgreSQL volume. Operators must manage secrets carefully and treat this variable as immutable after first boot.

Additionally, the dependency on a running PostgreSQL instance complicates local development compared to the zero-dependency PGlite mock.

## Testing Strategy and Local Development

OpenMAIC provides **PGlite**, an in-process PostgreSQL-compatible mock, for fast unit testing without Docker dependencies. This enables deterministic test runs for logic validation.

However, **integration testing** requires a live PostgreSQL instance to validate actual transaction behavior, connection pooling, and error handling paths like the `23505` unique violation detection. Developers must maintain a local PostgreSQL server or Docker container to run the full test suite ([`runtime-store.pg.test.ts`](https://github.com/THU-MAIC/OpenMAIC/blob/main/runtime-store.pg.test.ts)), increasing setup time and potential false negatives if database configuration drifts from production.

## Error Handling and Database Coupling

The PostgreSQL layer maps specific **error codes** to domain exceptions. For example, unique constraint violations (PostgreSQL code `23505`) transform into `RuntimeAppendConflictError`, allowing the application to handle collision scenarios gracefully.

This approach creates **tight coupling** to PostgreSQL-specific error semantics. Swapping to an alternative RDBMS like MySQL or SQLite would require re-mapping error codes and possibly adjusting the `withTransaction` implementation, increasing the cost of future storage backend migrations.

## Summary

- **PostgreSQL persistence in OpenMAIC** delivers ACID transactions and durable storage for runtime data through `withTransaction` and indexed JSONB tables.
- **Trade-offs include** higher latency than in-memory stores, manual schema migration requirements, and JSONB type restrictions prohibiting native JavaScript objects.
- **Operational complexity** involves managing `DATABASE_URL` secrets, understanding the immutability of `PERSISTENCE_POSTGRES_PASSWORD` after initialization, and maintaining database infrastructure.
- **Testing requires** balancing fast PGlite unit tests against slower but accurate PostgreSQL integration tests.
- **Error handling** leverages PostgreSQL-specific codes like `23505`, facilitating precise exception mapping but creating vendor lock-in.

## Frequently Asked Questions

### When should I use PostgreSQL instead of PGlite in OpenMAIC?

Use PostgreSQL for production deployments requiring data durability across restarts and horizontal scalability through connection pooling. Choose PGlite for local development, unit testing, or low-latency single-node deployments where data loss on process termination is acceptable.

### How do I handle schema changes when upgrading OpenMAIC?

You must execute manual SQL migrations to alter table structures, as `ensureSchema` only creates missing tables and indexes. For DSL version changes in stored records, use the built-in `needsRuntimeMigration` and `migrateRuntime` utilities, but structural schema changes require external migration scripts.

### What data types cannot be stored in OpenMAIC's PostgreSQL session payloads?

The JSONB storage rejects native JavaScript types including `Date`, `Map`, `Set`, `undefined`, and non-finite numbers during the `encodeJson` validation phase. Convert these to JSON-serializable primitives (ISO strings, plain objects, null) before calling `runtimeStore.append()`.

### Why does my OpenMAIC deployment fail authentication after changing the database password?

The `PERSISTENCE_POSTGRES_PASSWORD` environment variable only sets the PostgreSQL user password during initial volume creation. Changing this variable after the first container start does not update the existing database user, causing authentication mismatches. You must recreate the PostgreSQL volume or manually update the database user to resolve this.