🔥 PostgreSQL vs SQLite – which one should you actually use for your web app or SaaS?

#postgressql #sqlite #sass

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.

DEV Community