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

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:

// 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—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. 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), 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}