Use PostgreSQL by default for all new projects
Context
Multiple Aspire projects need persistent storage. Without a standing default, each project re-debates the database choice (MySQL, SQLite, MongoDB, etc.) and we end up with a heterogeneous stack that costs more to operate, backup, and integrate.
Detail
Options considered
| Option | Pros | Cons | Cost |
|---|---|---|---|
| A — PostgreSQL | ACID, mature, pgvector extension, JSON support, self-hostable on Coolify, every Aspire engineer knows it | Slightly more setup than SQLite for tiny apps | $0 (self-hosted) |
| B — MySQL/MariaDB | Familiar from WordPress side | No native JSONB, weaker constraint enforcement, less pgvector-equivalent | $0 |
| C — Heterogeneous (per-project choice) | Maximum flexibility | Cognitive load, backup tooling fragmentation, hiring complexity | High operational |
Decision
We chose: PostgreSQL (option A).
Rationale
- ACID + mature ecosystem covers ~95% of Aspire needs (transactional apps, reporting, search).
pgvectorextension lets us defer the "do we need a separate vector DB?" decision until we actually have semantic-search load (Knowledge OS Stage 2+).- One DB to back up, monitor, patch.
- Sync layer to Odoo (the ERP source of truth) is well-understood from PostgreSQL.
Constraints we accepted
- Tiny single-user apps that could use SQLite still use Postgres (slight overhead).
- We do NOT use MongoDB anywhere unless a third-party SDK forces it.
Revisit trigger
Volume on any single table exceeds 100M rows AND read latency degrades, OR a vendor SDK we adopt mandates a different DB.
Actions
- [x] All new Aspire monorepo apps default to Postgres in their
docker-compose.yml. - [x] Knowledge OS uses Postgres 16 + pgvector.
- [x] Zinga PMS, Zinga Booking, Alby Place all on Postgres.
Related
🔗 Relationships
graph LR
use_postgresql_by_default["use-postgresql-by-default"]:::self
use_postgresql_by_default --> knowledge_os_stage_1["knowledge-os-stage-1"]
use_postgresql_by_default --> aspire_llm_gateway_only_egress["aspire-llm-gateway-only-egress"]
classDef self fill:#715EE3,color:#fff,stroke:#291F50;