SQLite vs PostgreSQL for Growing Web Apps: Decision Criteria and Switching Signa

SQLite and PostgreSQL are both excellent for a single-server web app; the question is which failure mode you'd rather hit. Here's the practical breakdown. START WITH SQLITE IF: - Data fits comfortably in memory or is under ~10-50 GB - Writes are moderate and mostly one-at-a-time (one app process, low concurrency) - Ops budget is near zero: no DBA, no dedicated server, you want a single file you can copy for backups - You're prototyping or the app is single-instance (one web server process, one worker) START WITH POSTGRESQL IF: - You expect multiple app instances, background workers, or many concurrent writers - Write-heavy workload: high insert/update rates, hot rows contended by many clients - You need JSONB, full-text search, geospatial (PostGIS), or complex analytical queries - Data will plausibly exceed tens of GB, or the working set won't fit in RAM - You need roles, row-level security, replication, or point-in-time recovery later CONCRETE DECISION CRITERIA 1. Data size. SQLite degrades when the working set (hot tables + indexes) exceeds page cache. Fine under ~10-50 GB for typical apps; painful at 100 GB+. PostgreSQL handles TB-scale with proper config. 2. Concurrency. SQLite in WAL mode = one writer at a time, many readers. Any second writer blocks until the first commits; under contention you get SQLITE_BUSY / "database is locked". PostgreSQL uses MVCC: many concurrent writers, row-level locks, no global write lock. 3. Write patterns. SQLite is great for periodic/batched writes (cron jobs, ingestion bursts). It's bad for many small concurrent writes (e.g. every HTTP request updating a counter or a session). That's PostgreSQL's home turf. 4. Ops budget. SQLite: zero ops, backups are file copies. PostgreSQL: server process, memory tuning, autovacuum, connection pooling (pgbouncer), upgrade and backup strategy. If you have no ops time, SQLite wins until it genuinely can't. 5. App architecture. One process / serverless / edge / embedded → SQLite. Multiple replicas, workers, or a separate DB tier → PostgreSQL. 6. Team and features. If you know you'll need PG-only features (JSONB, FTS, PostGIS, extensions), start on PG. Migrating later is the expensive part. SIGNALS THAT IT'S ACTUALLY TIME TO SWITCH - "database is locked" / SQLITE_BUSY errors appear in logs regularly, or write timeouts under normal load - Write latency climbs as data grows even though the query itself is fast — sign of page-cache misses / lock waits - You add a second app instance or background worker and contention appears immediately - Backups start requiring downtime or awkward hacks for a consistent snapshot - Long-running schema migrations now block writes for minutes - File bloat: VACUUM takes long or you can't keep up with it - You need read replicas, HA, or point-in-time recovery - Working set exceeds RAM and you're constantly tuning cache THE ONE THING TO WEIGHT HEAVILY The switch itself is the real cost: schema/type differences, query rewrites, connection handling, and the migration cutover. If there's a plausible path to "many writers" or "multi-instance" within a couple years, start on PostgreSQL — the cost of starting on SQLite and migrating later usually exceeds the ops cost of running PG from day one. If the app is a tool, internal service, or prototype that will stay single-process, SQLite is the right call and switching may never happen. Rule of thumb: SQLite until you see regular lock errors or multi-writer needs; PostgreSQL if you can already see either coming.