Persistence

Stabilize supports two backends. Both support full ACID transactions and optimistic locking.

SQLite

Best for development, testing, and single-node workloads.

Zero configuration required — the schema is created automatically.
Uses the DELETE journal by default for simple, predictable locking.

store = SqliteWorkflowStore("sqlite:///:memory:")
# or
store = SqliteWorkflowStore("sqlite:///./workflows.db")

Journal mode (WAL, opt-in)

The default journal mode is DELETE. For higher read/write concurrency on a single node you can opt into WAL (Write-Ahead Logging) without any code change:

export STABILIZE_SQLITE_JOURNAL_MODE=WAL

Or programmatically:

from stabilize.persistence.sqlite_config import SqliteConfig

config = SqliteConfig(journal_mode="WAL")  # default is "DELETE"

The value is validated against an allow-list (DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF); an unknown value safely falls back to DELETE. When WAL is active, wal_autocheckpoint is applied per the optimization tier.

Schema migrations

The SQLite schema is versioned. create_tables() installs the baseline schema (version 1) and then runs a small forward-only migration runner that records applied versions in a schema_migrations table. This means:

New databases are stamped at the baseline version automatically.
Existing, pre-versioning databases upgrade in place — they are stamped at the baseline without re-running the baseline DDL, then any newer migrations are applied in order.
Re-running the migrator is idempotent.

Future schema changes are added as ordered, additive entries to stabilize.persistence.sqlite.migrations.MIGRATIONS.

PostgreSQL

Required for “Airport Grade” production deployments.

Uses psycopg (v3) connection pooling.
Supports massive concurrency via SKIP LOCKED (in queue implementation) and efficient indexing.

# Migrations
export MG_DATABASE_URL="postgres://user:pass@localhost:5432/stabilize"
stabilize mg-up

By default tables are created in the connection’s default schema (usually public). To keep them in a dedicated schema, add ?schema= to the URL (or set MG_SCHEMA, or a schema: key under database: in mg.yaml); mg-up creates the schema if missing and applies all migrations into it. Point the runtime store at the same schema with a search_path option in the DSN:

export MG_DATABASE_URL="postgres://user:pass@localhost:5432/stabilize?schema=stabilize"
stabilize mg-up

store = PostgresWorkflowStore(
    "postgres://user:pass@localhost:5432/stabilize?options=-csearch_path%3Dstabilize"
)

Without ?schema=, both the migrator and the runtime store use the connection’s default schema and the DSN needs no options:

store = PostgresWorkflowStore("postgres://user:pass@localhost:5432/stabilize")

Warning

Scope search_path to the connections Stabilize owns — the workflow store, the queue, and the event store. Do not apply it to a database URL that non-Stabilize application code also uses.

In a hybrid deployment (application tables in public, Stabilize tables in stabilize) a globally applied search_path silently re-resolves every unqualified application query whose table name Stabilize also uses. The reported case is snapshots: an application register table of that name and the event store’s own snapshots table both exist, so a bare SELECT ... FROM snapshots in the application starts reading the event store instead. Nothing errors — the query simply returns the wrong rows.

For the same reason, administrative or housekeeping queries that read Stabilize tables over an unscoped pool must schema-qualify their table names (stabilize.pipeline_executions, not pipeline_executions).

A Stabilize-only database has no such collision, and can set the option on the single shared DSN.

Pool options and idle cost

PostgresWorkflowStore and PostgresQueue both accept options=PoolOptions(...). Pools are keyed by connection string and options, so pass the same PoolOptions instance to both to share one pool:

from stabilize.persistence.pool_options import PoolOptions

options = PoolOptions(min_size=1, max_size=4)
store = PostgresWorkflowStore(dsn, options=options)
queue = PostgresQueue(dsn, options=options)

Without options each gets the default pool (min_size=5, max_size=15).

An idle QueueProcessor polls once per poll_frequency_ms and every poll is one transaction. Measured on PostgreSQL 16 for one idle processor:

`poll_frequency_ms`	transactions/second	transactions/day
50 (default)	19.6	about 1.7 million
1000	1.2	about 0.1 million

Raise poll_frequency_ms where pickup latency allows; the same cost applies per processor instance.