Database Overview
Compare PostgreSQL and SQLite drivers, understand architecture, and choose the right database for Compozy deployments.
Multi-Database Support at a Glance
- Unified repository interfaces keep domain logic identical for both drivers.
database.driverselects PostgreSQL (postgres) or SQLite (sqlite) at startup.- Vector databases remain a separate concern; SQLite requires an external vector DB.
- Migration tooling (
compozy migrate) automatically targets the active driver.
Choosing Your Database
| Criterion | PostgreSQL ✅ | SQLite ✅ |
|---|---|---|
| Use Case | Production, Multi-tenant | Development, Edge, Single-tenant |
| Concurrency | High (100+ workflows) | Low (5-10 workflows) |
| Scalability | Excellent (horizontal/vertical) | Limited (single file) |
| Vector Search | pgvector (built-in) | External DB required |
| Deployment | Separate database server | Embedded (single binary) |
| Setup Complexity | Moderate | Low (just a file path) |
| Performance | Optimized for high load | Optimized for reads |
| Backup | PostgreSQL tools (pg_dump) | File copy |
| Recommended For | ✅ Production deployments | ✅ Development, testing, edge |
When to Use PostgreSQL
- Sustained throughput with 25+ concurrent workflows or multi-tenant projects.
- Built-in vector search via
pgvectorfor knowledge bases and RAG. - Horizontal and vertical scaling on managed services (RDS, Neon, Supabase).
- Advanced SQL features: advisory locks, JSONB operators, partitioning.
- Strict audit, compliance, or HA requirements.
When to Use SQLite
- Instant local development with no external dependencies.
- Single-binary edge, IoT, or CI pipelines where a file-backed store is ideal.
- Low to moderate concurrency (≤10 parallel workflows) with predictable load.
- Disposable environments that can snapshot or replace the database file quickly.
- Scenarios where you already run an external vector database service.
Architecture Overview
Loading diagram...
Configuration
`database.driver` determines which repository provider is instantiated during startup.
Provider Factory
The factory constructs driver-specific repositories while keeping domain interfaces unchanged.
Runtime Behavior
Concurrent workflow execution relies on database capabilities—PostgreSQL supports high concurrency; SQLite enforces database-level locks.
Driver Decision Flowchart
Loading diagram...
Migration Considerations
- Run
compozy migrate upafter switching drivers; Compozy selects the correct migration set automatically. - SQLite migrations focus on single-file durability—keep
.dbfiles in persistent storage. - PostgreSQL migrations can run online but prefer maintenance windows for schema-heavy releases.
- Switching from SQLite to PostgreSQL requires exporting data (see driver guides) and re-running migrations on the target database.
Operational Readiness Checklist
- Backups
PostgreSQL: schedule
pg_dumpor managed snapshots. SQLite: copy the.dbfile during quiescent periods. - Monitoring
Track connection pool saturation (PostgreSQL) or lock contention (SQLite) via diagnostics.
- Vector Search
Confirm knowledge base configuration matches the driver’s capabilities (
pgvectorvs external providers). - Scaling Plan
Document when to graduate from SQLite to PostgreSQL as workload scales beyond 10 concurrent workflows.