> ## Documentation Index
> Fetch the complete documentation index at: https://docs.strait.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> 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:

```bash theme={null}
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:

| Variable                | Default | Description                         |
| :---------------------- | :------ | :---------------------------------- |
| `DB_MAX_CONNS`          | `25`    | Maximum number of open connections. |
| `DB_MIN_CONNS`          | `5`     | Minimum number of idle connections. |
| `DB_MAX_CONN_LIFETIME`  | `30m`   | Maximum connection age.             |
| `DB_MAX_CONN_IDLE_TIME` | `5m`    | Maximum idle time before closing.   |

### 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).

| Variable            | Default | Description                                              |
| :------------------ | :------ | :------------------------------------------------------- |
| `DB_PGBOUNCER_MODE` | `false` | Adjusts connection behavior for PgBouncer compatibility. |

### 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:

```ini theme={null}
# 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:

| Parameter                    | Recommended | Description                                       |
| :--------------------------- | :---------- | :------------------------------------------------ |
| `io_method`                  | `io_uring`  | Use kernel async I/O instead of `sync` (default). |
| `io_workers`                 | CPU cores   | Number of I/O worker threads.                     |
| `io_max_concurrency`         | `128`       | Max concurrent async I/O operations.              |
| `effective_io_concurrency`   | `200`       | Bitmap heap scan concurrency hint.                |
| `maintenance_io_concurrency` | `10`        | Maintenance operation concurrency.                |

<Note>
  Async I/O requires Linux 5.1+ with `io_uring` support. On other platforms, PostgreSQL falls back to synchronous I/O. No application-level changes are needed.
</Note>

### 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.

| Variable | Default | Description |
| :------- | :------ | :---------- |

## Redis

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

### Configuration

| Variable                | Description                                                        |
| :---------------------- | :----------------------------------------------------------------- |
| `REDIS_URL`             | Standard Redis connection string (e.g., `redis://localhost:6379`). |
| `REDIS_SENTINEL_MASTER` | Master name for Sentinel configurations.                           |
| `REDIS_SENTINEL_ADDRS`  | Comma-separated list of Sentinel addresses.                        |

### Redis Retry Strategy

Redis connections use the same exponential backoff strategy as PostgreSQL:

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

<Tip>
  For high availability, it is recommended to use Redis Sentinel or a managed Redis service that supports the Sentinel protocol.
</Tip>