Storage Layer
SQLite pragmas, schema, indexes, connection pooling, and Postgres differences.
SQLite pragmas, schema, indexes, connection pooling, and Postgres differences.
| Pragma | Value | Why |
|---|---|---|
journal_mode | WAL | Concurrent reads while writing |
busy_timeout | 5000ms | Wait on lock contention instead of failing |
synchronous | NORMAL | Fast writes, safe with WAL |
journal_size_limit | 64MB | Prevent unbounded WAL file growth |
Six tables back the SQLite store — jobs and their error history, the dead letter queue, rate limits, periodic tasks, and worker heartbeats.
Relationships: jobs → job_errors (1 ─ *, error history) and jobs →
dead_letter (1 ─ 0..1, DLQ on retry exhaustion).
idx_jobs_dequeue: (queue, status, priority DESC, scheduled_at) — fast dequeueidx_jobs_status: (status) — fast stats queriesidx_jobs_unique_key: partial unique index on unique_key where status is pending/runningidx_job_errors_job_id: (job_id) — fast error history lookupDiesel's r2d2 connection pool with up to 8 connections (SQLite) or 10 connections
(Postgres). In-memory databases use a single connection (SQLite :memory: is
per-connection).
flexiq also supports PostgreSQL as an alternative storage backend. See the Postgres backend guide for full details.
r2d2 pool with a default of 10 connections (vs. 8 for SQLite)flexiq), with search_path set on each connectionjob_dependencies, task_metrics, replay_history, task_logs, and
circuit_breakers