Configuration and management of the PostgreSQL and Redis data stores.

Strait relies on PostgreSQL for persistent storage and job queuing, and Redis for real-time pub/sub and CDC event streaming.

PostgreSQL

PostgreSQL 18 is the primary data store. It handles job definitions, run states, workflow DAGs, and the job queue itself using SELECT FOR UPDATE SKIP LOCKED.

Connection String

The DATABASE_URL environment variable follows the standard PostgreSQL URI format:

DATABASE_URL=postgres://username:password@localhost:5432/database_name?sslmode=disable

Connection Pool Tuning

Strait uses pgx/v5 for high-performance connection pooling. You can tune the pool using the following variables:

Migration System

Database migrations are managed using golang-migrate/v4.

• Count: Migrations currently run through the 000066_* series and continue incrementally.
• Embedding: Migrations are embedded into the Go binary using go:embed.
• Execution: Migrations run automatically on application startup.

Key Indexes

Key indexes are implemented to support high-throughput operations:

• Job Queue: Partial indexes on job_runs(status, scheduled_at) where status = 'queued' to support fast SKIP LOCKED dequeuing.
• Idempotency: Unique indexes on job_runs(idempotency_key) to prevent duplicate executions.
• Workflow DAG: Indexes on workflow_step_runs(workflow_run_id, status) for efficient dependency resolution.
• Retention: Brin indexes on created_at columns for fast range-based cleanup.

PgBouncer Mode

When running behind PgBouncer in transaction-pooling mode, enable DB_PGBOUNCER_MODE=true to avoid using features incompatible with connection poolers (prepared statements, advisory locks in session mode).

Async I/O (PostgreSQL 18)

PostgreSQL 18 introduces native async I/O via io_uring on Linux, which can significantly reduce context switches for write-heavy workloads like heartbeat updates, run state transitions, and queue operations.

To enable async I/O in your PostgreSQL 18 deployment:

# postgresql.conf
io_method = io_uring
io_workers = 4           # match your CPU core count
io_max_concurrency = 128 # concurrent I/O operations

Recommended tuning for Strait workloads:

Connection Retries

Strait implements an exponential backoff strategy for database connection failures:

• Strategy: Exponential backoff.
• Delays: 1s, 2s, 4s, 8s, 16s.
• Max Retries: 5 attempts.

Redis Graceful Degradation

By default, Strait operates in fail-open mode for Redis: if Redis becomes unavailable, real-time pub/sub features degrade gracefully while core job execution continues unaffected.

Redis

Redis 8 is used for real-time features and as a bridge for CDC events.

Configuration

Redis Retry Strategy

Redis connections use the same exponential backoff strategy as PostgreSQL:

• Backoff: 1s, 2s, 4s, 8s, 16s.
• Attempts: 5 retries before failing.