Environment:PrefectHQ Prefect Prefect Server Database
| Knowledge Sources | |
|---|---|
| Domains | Infrastructure, Database |
| Last Updated | 2026-02-09 22:00 GMT |
Overview
SQLite or PostgreSQL database backend for the Prefect server, with WAL mode, tuned PRAGMA settings, and SQLAlchemy 2.0 async ORM.
Description
The Prefect server persists all flow runs, task runs, deployments, logs, and concurrency limits in a relational database. By default it uses SQLite with WAL (Write-Ahead Logging) mode for development and single-node deployments. PostgreSQL (via `asyncpg`) is supported for production multi-node deployments. The database layer uses SQLAlchemy 2.0 with async session management and Alembic for schema migrations. SQLite is configured with specific PRAGMA settings tuned for Prefect's read-heavy, concurrent workload.
Usage
This environment is required for running any Prefect server instance, whether self-hosted or for local development. All server-side operations (flow run creation, state transitions, log storage, artifact creation) depend on the database backend. Implementations that call `get_client()`, `client.read_flow_runs()`, or `client.delete_flow_run()` interact with this database through the API.
System Requirements
| Category | Requirement | Notes |
|---|---|---|
| OS | Linux, macOS, Windows | SQLite works everywhere; PostgreSQL requires a separate server |
| Database | SQLite 3.35+ or PostgreSQL 13+ | SQLite is bundled with Python; PostgreSQL requires `asyncpg` |
| Disk | 1GB+ SSD recommended | High IOPS needed for WAL mode and concurrent writes |
| RAM | 512MB+ for SQLite cache | `cache_size = 20000` pages (~80MB) configured by default |
Dependencies
System Packages
- `sqlite3` (bundled with Python)
- `postgresql` >= 13 (optional, for production deployments)
Python Packages
- `sqlalchemy[asyncio]` >= 2.0, < 3.0.0
- `aiosqlite` >= 0.17.0, < 1.0.0
- `asyncpg` >= 0.23, < 1.0.0 (for PostgreSQL)
- `alembic` >= 1.7.5, < 2.0.0
Credentials
The following environment variables control database connection:
- `PREFECT_API_DATABASE_CONNECTION_URL`: Full database connection string (e.g., `postgresql+asyncpg://user:pass@host/db` or `sqlite+aiosqlite:///path/to/prefect.db`)
- `PREFECT_HOME`: Directory where the default SQLite database file is stored (defaults to `~/.prefect`)
Quick Install
# SQLite (default, no extra setup needed)
pip install prefect
# PostgreSQL backend
pip install prefect asyncpg
# Then set the connection URL:
export PREFECT_API_DATABASE_CONNECTION_URL="postgresql+asyncpg://user:password@localhost:5432/prefect"
Code Evidence
SQLite PRAGMA configuration from `src/prefect/server/database/configurations.py:528-547`:
# when using the WAL, we do need to sync changes on every write. sqlite
# recommends using 'normal' mode which is much faster
cursor.execute("PRAGMA synchronous = NORMAL;")
# a higher cache size (default of 2000) for more aggressive performance
cursor.execute("PRAGMA cache_size = 20000;")
# wait for this amount of time while a table is locked
# before returning and raising an error
# setting the value very high allows for more 'concurrency'
# without running into errors, but may result in slow api calls
if PREFECT_TESTING_UNIT_TEST_MODE.value() is True:
cursor.execute("PRAGMA busy_timeout = 5000;") # 5s
else:
cursor.execute("PRAGMA busy_timeout = 60000;") # 60s
# `PRAGMA temp_store = memory;` moves temporary tables from disk into RAM
# this supposedly speeds up reads, but it seems to actually
# decrease overall performance, see https://github.com/PrefectHQ/prefect/pull/14812
# cursor.execute("PRAGMA temp_store = memory;")
SQLite query parameter limit from `src/prefect/server/models/logs.py:18-25`:
# We have a limit of 32,767 parameters at a time for a single query...
MAXIMUM_QUERY_PARAMETERS = 32_767
# ...and logs have a certain number of fields...
NUMBER_OF_LOG_FIELDS = len(schemas.core.Log.model_fields)
# ...so we can only INSERT batches of a certain size at a time
LOG_BATCH_SIZE = MAXIMUM_QUERY_PARAMETERS // NUMBER_OF_LOG_FIELDS
Common Errors
| Error Message | Cause | Solution |
|---|---|---|
| `database is locked` | SQLite busy timeout exceeded under concurrent access | Increase `PRAGMA busy_timeout` or migrate to PostgreSQL for production workloads |
| `too many SQL variables` | Bulk insert exceeding SQLite 32,767 parameter limit | Framework handles this automatically via `LOG_BATCH_SIZE`; if hitting manually, batch your inserts |
| `cache lookup failed for type` | Alembic migration race condition in test environments | Known flake; migrations run in worker threads for isolation |
Compatibility Notes
- SQLite: Default backend; suitable for development and single-server deployments. WAL mode enables concurrent reads but writes are serialized.
- PostgreSQL: Required for production multi-node deployments. Uses `asyncpg` driver for async access. `GREATEST()` function handles NULL differently than SQLite's `MAX()`.
- SQLAlchemy `on_conflict_do_update`: Does not trigger `Column.onupdate` callbacks on SQLite. Timestamps must be set manually in upsert operations.
- Migration threading: Alembic migrations run via `run_async_from_worker_thread` to avoid cache lookup flakes in concurrent test scenarios.