Operations

Storage


Orcher uses PostgreSQL as its durable, ACID-compliant primary data store. All workflow state, task execution records, journal entries, timers, and queue data live in PostgreSQL. Redis is optional — it provides fast queue notification and query caching but is not required for correctness.

  • PostgreSQL is the source of truth — Every workflow step, task result, and timer is persisted durably
  • Redis is a performance optimization — Notifies workers of new work at once and caches queries
  • In-memory caching — A Moka-based LRU cache (HighPerfCache) reduces database roundtrips for hot workflow state
  • PgBouncer recommended — Connection multiplexing for production deployments with many concurrent workflows

PostgreSQL

What's Stored

The orchestrator manages approximately 20 core tables via embedded migrations (V001–V023). The key tables are:

TablePurpose
workflow_executionsWorkflow instances with claim tracking, shard routing, and priority
task_executionsTask lifecycle (scheduled → started → completed/failed), eager claim TTL
execution_logStep-level execution records (task, closure, child workflow, side effect)
workflow_execution_journalEvent-sourced journal for replay protection
outbox_queueAtomic task dispatch with lease-based claiming
timer_queueDurable timers (sleep, timeouts, scheduled triggers)
dead_letter_queueFailed tasks after max retries
eventsPlatform event store (task, actor, worker streams)
worker_heartbeatsWorker health metrics and status
actor_stateVirtual actor state persistence
namespacesLogical partitioning for multi-tenancy
organizationsOrganization-level multi-tenancy
execution_idempotencyWorkflow deduplication cache
child_workflow_executionsParent-child workflow relationships
schedulesCron-style scheduled workflow triggers

Key design patterns used across the schema:

  • Claim-based polling — claimed_by and claim_timeout_at fields enable safe distributed polling without database locks
  • Outbox pattern — Workflow state changes and task dispatch happen in a single transaction
  • Shard routing — shard_id column for horizontal partitioning across orchestrator nodes
  • Idempotency keys — Unique constraints prevent duplicate operations
  • JSONB attributes — Flexible metadata with GIN indexes for fast queries

Connection Configuration

The orchestrator connects to PostgreSQL via the DATABASE_URL environment variable:

1
# Direct connection
2
DATABASE_URL=postgresql://orcher:orcher@localhost:5432/orcher
3
4
# Via PgBouncer (recommended for production)
5
DATABASE_URL=postgresql://orcher:orcher@localhost:6432/orcher

Default connection pool settings:

SettingDefaultDescription
min_connections10Minimum idle connections
max_connections50Maximum connections in pool
connect_timeout30sTimeout for establishing a connection
max_lifetime1800s (30 min)Max time a connection is reused
idle_timeout600s (10 min)Time before idle connections are closed
acquire_timeout30sTimeout for acquiring a connection from the pool
statement_timeout300s (5 min)Max query execution time

PgBouncer

For production deployments, PgBouncer is strongly recommended. PostgreSQL connections cost ~10 MB each and the default max_connections is ~100 — easily exhausted with many concurrent workflows. PgBouncer multiplexes thousands of clients over a smaller pool of actual database connections.

1
# docker-compose.yml
2
services:
3
pgbouncer:
4
image: edoburu/pgbouncer:latest
5
depends_on:
6
postgres:
7
condition: service_healthy
8
environment:
9
DATABASE_URL: postgresql://orcher:orcher@postgres:5432/orcher
10
POOL_MODE: transaction
11
MAX_CLIENT_CONN: 1000
12
DEFAULT_POOL_SIZE: 50
13
MIN_POOL_SIZE: 10
14
RESERVE_POOL_SIZE: 10
15
RESERVE_POOL_TIMEOUT: 3
16
ports:
17
- "6432:6432"

Use transaction mode — it supports the highest concurrency. Point the orchestrator at PgBouncer's port (6432), not PostgreSQL's port (5432).

PostgreSQL Tuning

For production workloads, tune these PostgreSQL settings:

1
# postgresql.conf
2
max_connections = 200
3
shared_buffers = 256MB
4
effective_cache_size = 768MB
5
work_mem = 4MB
6
checkpoint_completion_target = 0.9
7
max_wal_size = 4GB

Migrations

Migrations are embedded in the orchestrator binary and run automatically at startup in development mode. Each migration is versioned (V001–V023) with SHA256 checksums to prevent tampering.

Startup behavior is controlled by SetupConfig:

ModeAuto-Create DBAuto-MigrateFail on Pending
DevelopmentYesYesNo
ProductionNoNoYes

In production, run migrations separately before deploying the new binary:

1
use orcher_storage::setup::{SetupConfig, setup_database};
2
3
// Production: fail if migrations are pending
4
let config = SetupConfig::production();
5
setup_database(&config).await?;

Next Steps