SQLite Performance Optimizations

This project runs on a Raspberry Pi 3 (1GB RAM) and uses SQLite for both interactive API reads and ETL writes. To reduce lock contention and SD-card I/O pressure, we enforce SQLite PRAGMAs for every new SQLAlchemy connection.

Why This Exists

Without consistent per-connection SQLite settings, mixed read/write workloads can hit database is locked errors and high fsync overhead.

The backend enforces these settings at connection time in backend-api/src/dicechess_trainer/database.py using SQLAlchemy engine events.

Enforced PRAGMAs

• PRAGMA journal_mode=WAL;
  • Enables concurrent readers during writes.
• PRAGMA synchronous=NORMAL;
  • Reduces fsync calls while keeping a safe durability profile with WAL.
• PRAGMA cache_size=-10000;
  • Caps cache to about 10 MB per connection (negative value is KiB units).
• PRAGMA foreign_keys=ON;
  • Ensures relational integrity checks are active.

Where It Is Configured

• Runtime hook: backend-api/src/dicechess_trainer/database.py
• Listener: @event.listens_for(Engine, "connect")

The listener applies PRAGMAs on each new DBAPI connection. This is important because SQLAlchemy pools can create new connections over time.

Verification

Automated check:

• Test file: backend-api/tests/test_database_pragmas.py
• Assertions:
  • PRAGMA journal_mode; returns wal
  • PRAGMA foreign_keys; returns 1

Manual check against a local DB file (optional):

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA cache_size;
PRAGMA foreign_keys;

Contributor Notes

• Prefer changing these values only with a clear benchmark or operational reason.
• Keep docs and backend-api/tests/test_database_pragmas.py aligned with runtime behavior.
• WAL behavior is meaningful for file-backed SQLite databases; in-memory DBs do not behave the same way.
• For production backup/export steps on the Raspberry Pi, see Backup Production Database.