Heuristic:PrefectHQ Prefect SQLite Performance Tuning
| Knowledge Sources | |
|---|---|
| Domains | Database, Optimization |
| Last Updated | 2026-02-09 22:00 GMT |
Overview
SQLite PRAGMA tuning for Prefect server: WAL mode with `synchronous=NORMAL`, `cache_size=20000`, `busy_timeout=60000`, and explicitly not using `temp_store=memory` despite the intuition that it should help.
Description
The Prefect server's SQLite backend is tuned with specific PRAGMA settings that differ from SQLite defaults. The most counter-intuitive finding is that `PRAGMA temp_store = memory` (which moves temporary tables into RAM) actually decreases overall performance despite seeming like it should speed up reads. This was discovered empirically and documented in PR #14812. The busy timeout is set to 60 seconds in production (5 seconds in tests) to allow concurrent access without errors, at the cost of potentially slower API calls during contention.
Usage
Apply this heuristic when tuning SQLite performance for Prefect server deployments, or when considering similar PRAGMA optimizations for other SQLAlchemy-based applications. Also relevant when diagnosing "database is locked" errors or slow API responses under concurrent load.
The Insight (Rule of Thumb)
- Action: Set WAL mode with `synchronous=NORMAL`, `cache_size=20000`, `busy_timeout=60000`. Do not enable `temp_store=memory`.
- Value: 10x cache size increase (2000 → 20000 pages), 60s lock wait timeout.
- Trade-off: Higher cache consumes ~80MB RAM. Longer busy_timeout prevents lock errors but may slow individual API calls during contention. `temp_store=memory` is intentionally disabled because it decreases performance.
- Counter-intuitive: RAM-based temp storage is slower, not faster, for Prefect's workload pattern (discovered via PR #14812).
Reasoning
Prefect's SQLite workload is read-heavy with concurrent writes from multiple flow runs. WAL mode allows concurrent reads while writes are serialized. The `synchronous=NORMAL` setting is SQLite-recommended for WAL mode and significantly faster than the default `FULL`. The 20000-page cache reduces disk I/O for frequently accessed data.
The `temp_store=memory` finding is the key tribal knowledge: the Prefect team tested this optimization and found it actually decreased overall performance. The hypothesis is that moving temp tables to memory increases memory pressure, causing more frequent cache evictions that offset any temp table speedup.
Code evidence 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;")
# `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;")
Log batch size calculation 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
# ...so we can only INSERT batches of a certain size at a time
LOG_BATCH_SIZE = MAXIMUM_QUERY_PARAMETERS // NUMBER_OF_LOG_FIELDS