Storage backends

Choosing between SQLite and PostgreSQL for the Flux event log — decision matrix, setup, and migration between them.

The Flux event log lives in a SQLAlchemy database. Every checkpoint, every dispatch query, and every replay reads from it, so the choice of backend is the single biggest infrastructure decision in operating Flux.

The two backends

Flux 0.56.0 ships with two SQLAlchemy repositories, defined in flux/models.py:

That is the entire list. There is no MySQL repository, no MariaDB driver, no MongoDB adapter — RepositoryFactory.create_repository() only accepts "sqlite" or "postgresql", and any other value raises ValueError: Unsupported database type. The schema on both backends is Alembic-managed (flux/migrations/): on first connect, Flux automatically migrates the database to the latest revision — see Upgrades and migrations.

The backend is selected by the URL scheme in database_url. Flux infers database_type from the scheme; you can also set it explicitly.

Decision matrix

SQLitePostgreSQL
Best forDevelopment, single-node demos, CIProduction, multi-worker, multi-server
Server replicas1Many — replicas coordinate via advisory locks
Concurrent workers1 — a second worker logs a warningLimited by database_pool_size × replicas
ReplicationNone — copy the file offlineStandard PG streaming replication
BackupStop server, copy .flux/flux.db (or sqlite3 .backup)pg_dump while live
FK enforcementLoose — SQLite ignores workflow_id FKStrict — auto-registration matters
VersionBuilt in to PythonPostgreSQL 14+
DriverBuilt in to Pythonpsycopg v3 (the postgresql extra)
Pool config used?No (single file)Yes (database_pool_* settings)
When to pick itOne server plus at most one colocated workerThe instant you add a second worker, a second server replica, or any cross-host topology

SQLite is supported for single-node mode only — one server plus one colocated worker, or inline workflow.run(). Registering a second worker against a SQLite database logs a warning (“multi-worker fleets require PostgreSQL”) because claim safety (SELECT … FOR UPDATE SKIP LOCKED) and FK cascades are silently unenforced there. Multi-node deployments require PostgreSQL 14+. Multiple server replicas are supported on PostgreSQL — the scheduler and retention sweeps run as fleet-wide singletons via advisory locks; see High availability.

SQLite setup

SQLite is the zero-config default. The shipped configuration in flux/config.py:

database_url = "sqlite:///.flux/flux.db"
database_type = "sqlite"

The file lives under the configured home directory (.flux by default). On first connect, SQLiteRepository._create_engine applies four pragmas:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;  -- 256 MiB

WAL mode is the important one — it lets readers and writers operate without blocking each other, which is what makes a single-server-plus-one-colocated-worker setup tolerable on SQLite. It does not make SQLite multi-node: the moment a second worker registers, the server logs a warning and you should be planning the PostgreSQL move.

Backup. SQLite is a single file. With the server stopped, copy it. With the server running, use the SQLite .backup command, which is consistent with WAL:

sqlite3 .flux/flux.db ".backup '/path/to/backup.db'"

See Backups and restore for the full procedure.

PostgreSQL setup

1. Install the extra.

pip install 'flux-core[postgresql]'

This pulls in psycopg v3 (psycopg[binary,pool] ^3.2) — one driver for both the synchronous SQLAlchemy engine and the async LISTEN/NOTIFY listener used by event-driven dispatch. PostgreSQL 14+ is expected; the upstream Docker compose ships postgres:16-alpine, which is the version Flux is exercised against in CI.

2. Point Flux at the database. The DSN scheme must be postgresql://, not postgres://:

export FLUX_DATABASE_URL='postgresql://flux:flux@db.internal:5432/flux'
# flux.toml
[flux]
database_url = "postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:5432/${DB_NAME}"
database_type = "postgresql"
database_pool_size = 10
database_max_overflow = 20

flux.toml supports ${VAR} interpolation against the process environment, so credentials stay out of the file. database_type is optional — Flux auto-infers postgresql from the URL scheme. Keep the driverless postgresql:// form: Flux pins the psycopg (v3) dialect internally (flux/models.py::normalize_postgresql_url), and legacy postgresql+psycopg2:// URLs from older configs are normalized to psycopg automatically.

3. Start the server. Schema creation is automatic. On first connect, Flux runs the Alembic migration chain (flux/migrations/) and brings the database to the latest revision — on a fresh database that creates the full schema. To run the step explicitly (for example, once before rolling multiple replicas), use flux db upgrade; see Upgrades and migrations.

poetry run flux start server

A connection failure raises PostgreSQLConnectionError. A SELECT 1 health check is built in — call it from your liveness probe (see Running the server).

Connection pool

The pool settings in flux/config.py apply only to the PostgreSQL backend (SQLite uses a single file connection):

SettingDefaultMaps to SQLAlchemy
database_pool_size20pool_size
database_max_overflow20max_overflow
database_pool_timeout30pool_timeout (seconds to wait for a free connection)
database_pool_recycle3600pool_recycle (seconds before a connection is reopened)
database_executor_threads16Server-side thread pool for blocking DB calls — size it at or below pool_size
database_health_check_interval300Used by external health probes, not by the pool itself

Pre-ping is on (pool_pre_ping = True), so dead connections are detected before use. The effective ceiling per process is pool_size + max_overflow — 40 connections by default. Multiply by however many server replicas you run when sizing PostgreSQL max_connections.

Migration between backends

There is no built-in migration tool. The supported procedure is operator-driven:

  1. Drain. Pause schedules (flux schedule pause) and let in-flight executions finish.
  2. Stop. Shut down every server replica and every worker.
  3. Export. Dump the source database — sqlite3 .flux/flux.db .dump > flux.sql for SQLite.
  4. Import. Create the empty Postgres database, point Flux at it, and run flux db upgrade (or start the server once) — that builds the schema via the Alembic migration chain. Then load the rows. The SQLite dump syntax is not directly importable; expect to massage types or export the tables you care about as CSV.
  5. Restart. Switch every server and worker to the new DSN, restart, resume schedules.

For non-trivial event histories, most teams treat the cutover as “start fresh on Postgres” rather than migrating row-for-row — workflow source travels in the catalog, so re-registering after the cutover is cheap. The broader migration story (including schema upgrades between Flux releases) lives in Upgrades and migrations.

Cloud Postgres

Anything that speaks the PostgreSQL wire protocol works, because Flux uses standard psycopg (v3): AWS RDS / Aurora, GCP Cloud SQL / AlloyDB, Azure Database for PostgreSQL, Neon, Supabase, Crunchy Data, Timescale Cloud. There is no Flux-side configuration specific to any of these — point FLUX_DATABASE_URL at the provider’s connection string. If your provider requires TLS, append ?sslmode=require to the DSN.

Serverless Postgres (Neon, Aurora Serverless v2) pairs well with the defaults: pool_recycle = 3600 and pool_pre_ping = True both protect against the provider closing idle connections behind your back.

What can go wrong

SQLite “database is locked” under load. Symptom: workers and the server log OperationalError: database is locked once concurrent work climbs. WAL mode helps but does not eliminate this — under sustained concurrent writes, SQLite will block. Fix: switch to PostgreSQL. This is the canonical signal that you have outgrown SQLite — the server has probably already logged the multi-worker warning by the time you see it.

PostgreSQL connection pool exhaustion. Symptom: requests stall for 30 seconds and then raise QueuePool limit ... overflow ... reached. The pool default is 20 + 20 overflow = 40 per process. Fix: raise FLUX_DATABASE_POOL_SIZE and FLUX_DATABASE_MAX_OVERFLOW (and PostgreSQL max_connections alongside), or shrink the number of concurrent workers per server. Check database_pool_timeout if you want to fail faster instead of waiting 30 seconds.

Wrong DSN scheme. SQLAlchemy accepts postgresql:// but not the older postgres:// shorthand that some providers print. Symptom: Invalid PostgreSQL connection URL format from PostgreSQLConnectionError, raised by _validate_postgresql_url which explicitly requires the postgresql:// prefix. Fix: rewrite the scheme. Heroku and a few other providers still emit postgres:// in their config UI; just replace it.

See also