SQLite vs PostgreSQL: Embedded or Server?
SQLite is an embedded, serverless database stored in a single file; PostgreSQL is a full client-server database built for concurrency and scale.
SQLite runs in-process with no server, making it ideal for local apps, tests, edge, and small workloads where a single file is enough. PostgreSQL runs as a server with rich concurrency, roles, replication, and advanced SQL, suited to multi-user production systems. They serve different scales rather than competing head-to-head: SQLite for embedded simplicity, Postgres for concurrent production.
| SQLite | PostgreSQL | |
|---|---|---|
| Architecture | Embedded (in-process) | Client-server |
| Setup | Zero config (a file) | Server + roles |
| Concurrency | Limited writers | High, MVCC |
| Features | Core SQL | Rich (extensions, types) |
| Best for | Local apps, tests, edge | Concurrent production |
Use case and scale
SQLite shines for desktop and mobile apps, CLIs, prototypes, and as a fast test database with no service to manage. Postgres is the choice once you have concurrent writers, multiple services, or need replication and advanced features. Some teams develop against SQLite and deploy on Postgres, but watch for SQL dialect differences.
In CI
SQLite needs no service container, so tests are fast and hermetic. Postgres runs as a service container for production-parity tests. On managed runners, SQLite keeps test jobs lean while Postgres parity tests benefit from faster container startup.
The verdict
Embedded, single-user, or hermetic test workloads: SQLite, with zero operational overhead. Concurrent, multi-service production systems: PostgreSQL. Mixing them (SQLite in dev, Postgres in prod) is common but risks dialect drift, so prefer testing against your production engine.