Adopt PostgreSQL for production persistence (ADR-0033)
Nobody has claimed this yet.
Assessment
- Difficulty
- 5/5
- Estimated time
- Over a week
- Newbie friendliness
- 38/100
Research direction
Start with ADR-0033 and the Config.db_url and engine configuration mentioned in the issue, then review the existing Alembic setup. Use the implementation checklist to scope the driver, connection behavior, migrations, and SQLite compatibility. Verify completion with the stated podman compose workflow and uv run demo/demonstration.py, while keeping the demo and test suite on SQLite.
Written by the indexing model from the issue text.
Description
Summary
Adopt PostgreSQL as the production database, keeping SQLite as the default for local development, the demo, and the test suite. Documented in ADR-0033 (branch docs/adr-0033-postgres-prod, status: Proposed).
Config.db_url currently hardcodes SQLite; productionising FDS to replace FAIR MAST needs a client/server datastore. The database holds metadata only — bulk scientific data stays in object storage (IceChunk/Zarr/NetCDF, ADR-0029).
Why
- Concurrent writes — multiple uvicorn workers + parallel ingest hit SQLite's single write lock (
SQLITE_BUSY); Postgres MVCC handles concurrent readers/writers. - Stateless app needs a shared, networked store — SQLite is an in-process file bound to one host's disk.
- Safe schema evolution — Postgres has transactional DDL; SQLite needs fragile batch (copy-and-swap) migrations.
- Feature fit — server-side cursors for NDJSON export (ADR-0020); JSONB/GIN, full-text search,
timestamptzfor a searchable catalog at scale ("millions of shots", ADR-0029). - Parity with FAIR MAST (already Postgres) + ops familiarity + future Postgres→Postgres migration.
SQLite stays for dev/demo/tests (zero-config, lightweight); none of those pressures apply there.
Implementation
- Add
psycopg(3) driver behind apostgresextra -
Config.db_urlbuildspostgresql+psycopg://…whenFDS_DB_HOSTis set, else SQLite fallback - Engine:
check_same_threadonly for SQLite;pool_pre_ping=Truefor Postgres - Commit real Alembic migrations (replace demo runtime autogeneration);
render_as_batch=Truefor SQLite - Demo + tests remain on SQLite; CI may also run against Postgres
- Verify demo still works end-to-end (
podman compose … up+uv run demo/demonstration.py)
See ADR-0033 for the full rationale.
Migrated from the internal tracker, where it was #13, opened 2026-06-17.
- Dominant language
- Python
- Stars
- 0
- Forks
- 1
- Avg merge
- 26m
- Merged PRs (30d)
- 24
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from ukaea/fds
-
performance
Difficulty 4/5 3-5 days Newbie friendliness 52/100
-
api architecture feature metadata priority: high
Difficulty 5/5 Over a week Newbie friendliness 35/100
-
architecture metadata priority: medium
Difficulty 5/5 Over a week Newbie friendliness 30/100
-
enhancement fair-compliance metadata priority: medium
Difficulty 5/5 Over a week Newbie friendliness 35/100
-
Consume external provenance: inverse PROV mapping to register upstream inputs + create a Collection Openapi fair-compliance feature metadata priority: medium
Difficulty 5/5 Over a week Newbie friendliness 35/100
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
bancolombia/sentinel#23 ·
-
test md OpenCI
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
-
integration:quickjs org:external priority:backlog topic:code-interpreter topic:middleware type:feature
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
langchain-ai/deepagents#6450 ·
-
bug client
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100