The choice between SQLite and PostgreSQL is a frequent point of confusion. The quick choice is: For almost any public-facing web app or SaaS, you want PostgreSQL. SQLite is for embedded, local-first, or very low-traffic internal tools.
Here is a detailed breakdown across all relevant aspects for common web apps and then specifically for SaaS.
Part 1: Common Web Apps (e.g., blog, small e-commerce, corporate site)
For a typical web app with concurrent users (even just 10-50), the differences are stark.
| Aspect | PostgreSQL | SQLite |
|---|---|---|
| Concurrency | Excellent. Handles hundreds of simultaneous writes and thousands of reads via MVCC (Multi-Version Concurrency Control). | Poor. Entire database is locked for writes. Reads are blocked during a write. Works for < 10 concurrent writes/sec. |
| Write Scaling | Multiple writers can work simultaneously, thanks to row-level locking. | Only one writer at a time. If two users submit a form at the same second, one fails with database is locked. |
| Data Integrity | Robust crash recovery, transactional DDL, point-in-time recovery. | Good for single-file, but if a write is interrupted (power loss, crash), the entire DB can corrupt. WAL mode helps but not perfect. |
| User Management | Full role-based security, connection limits, per-schema permissions. | No user management. The OS file permissions are the only security. |
| Data Types | Rich set: arrays, JSONB (binary JSON with indexing), hstore, range types, enum. | Basic: INTEGER, TEXT, BLOB, REAL, NUMERIC. JSON support exists but no efficient indexing. |
| Indexing | Many types: B-tree, Hash, GiST, SP-GiST, GIN, BRIN. Partial and expression indexes. | B-tree only. Partial indexes possible. No full-text search (requires FTS5 extension, but less powerful). |
| Scalability | Vertical (more CPU/RAM) works great. Horizontal (read replicas, partitioning) possible. | Limited to one machine, one file. No built-in replication. |
| Deployment | Requires a separate server/container, configuration, memory tuning (shared_buffers, etc.). | Trivial. Just a file. Zero config. Perfect for development or CLI tools. |
| Connection Overhead | Heavy process per connection (though connection pooling like PgBouncer helps). | Lightweight. In-process library – no network overhead. |
| Backup / Restore |
pg_dump, WAL archiving, continuous backup. |
Just copy the .db file (when not in use). |
Verdict for common web apps:
- Use PostgreSQL if: The app has more than ~5 concurrent users, needs any security separation, runs on a shared host, or you care about data safety.
- Use SQLite if: It’s a demo, a single-user dashboard, a local-first Electron app, or a development/testing environment (mimics some but not all Postgres features).
Part 2: SaaS App (Multi-tenant, high reliability, scale)
In SaaS, requirements multiply: autoscaling, high availability, data isolation, backups, compliance, analytics, and zero downtime migrations.
| Aspect | PostgreSQL | SQLite |
|---|---|---|
| Multi-tenancy | Native support for DB-per-tenant (via schemas or separate DBs) with row-level security policies. | No concept. You’d need one SQLite file per tenant — but managing thousands of files is a nightmare for backups, migrations, and connections. |
| High Availability | Streaming replication (synchronous/asynchronous), failover, Patroni, cloud managed services (RDS, Cloud SQL, Crunchy Bridge). | None. If the server hosting the .db file dies, all data is offline until the disk is restored. |
| Zero-downtime migrations |
pgroll, gh-ost patterns, transactional DDL, concurrent index creation. |
Adding a column is fast, but removing or altering columns requires rewriting the entire file (downtime proportional to data size). |
| Analytics / Reporting | Excellent. Materialized views, window functions, CTEs, parallel query, foreign data wrappers. | Very limited. No parallel query. Large aggregations on tables > 100MB become slow. |
| Connection Pooling | Essential for serverless/containerized SaaS. Use PgBouncer, Supavisor, or builtin pooler. | Not applicable (no network layer). |
| Compliance (GDPR, SOC2) | Fine-grained audit logs, row-level security, encrypted columns (pgcrypto), IAM integration. | None. SQLite has no audit capability. |
| Scaling beyond 1 node | Read replicas (for scaling analytics), partitioning (for time-series), foreign data wrappers. | Impossible. |
| Backup strategy | Point-in-time recovery (PITR), continuous WAL shipping, backup to S3. | You must copy the file while quiesced. For large SaaS, this means application downtime. |
| Cost / Complexity | Higher ops cost (managed service reduces this). Requires tuning work_mem, shared_buffers, max_connections. |
Zero ops cost. But you pay in developer time handling locking errors, corruption, and workarounds. |
| Real-world usage in SaaS | Industry standard. Stripe, Notion, GitHub (for many services), Shopify. | Almost none for core transaction processing. Exceptions: Local caches, SQLite as a read-only replica edge cache (LiteFS, rqlite). |
Verdict for SaaS:
Unequivocally PostgreSQL. SQLite is not a production-ready database for a multi-user, always-on SaaS product.
The only “SaaS” use case for SQLite is edge computing (e.g., using LiteFS or Turso to put read-only SQLite at edge locations for ultra-low latency reads). But the primary write master is still PostgreSQL.
Part 3: Overlooked Differences That Sink Projects
| Subtle Issue | PostgreSQL | SQLite |
|---|---|---|
| ALTER TABLE |
ALTER is lightweight (metadata change) for adding columns, except with defaults. |
Adding a column with a default rewrites the whole file – O(n) disk IO. |
| Foreign Keys | Enforced strictly by default. | Optional (PRAGMA foreign_keys=ON). Off by default in many versions. |
| Data Durability | fsync controlled by synchronous_commit. Can trade safety for speed. |
PRAGMA synchronous=FULL is safe but slow. Normal mode risks corruption on power loss. |
| Concurrent read/write | Readers never block writers and vice versa (MVCC). | Writes block reads. Reads block writes (depending on journal mode). |
| JSON / Document store | JSONB with GIN indexes – can replace MongoDB for many use cases. | JSON is text. You cannot index inside JSON efficiently. |
Final Recommendation – Decision Flow
Will your app have > 5 concurrent users?
├── Yes → Use PostgreSQL.
└── No → Can you tolerate occasional "database locked" errors?
├── No → Use PostgreSQL.
└── Yes → Is it a local tool, mobile app, or CLI?
├── Yes → SQLite is great.
└── No → Still PostgreSQL (future proofing).
Simple rule for production web apps:
Default to PostgreSQL. Only choose SQLite if you have a compelling reason not to (e.g., embedded device, local-first desktop app, or testing).
SQLite is an engineering marvel – for its domain. But a multi-tenant SaaS app is not its domain.