When SQLite Is NOT the Right Choice

Where it stops being a good idea

Honest defense of SQLite includes naming the cases where it is clearly the wrong tool. Reach for Postgres or another network database when:

Many writers across many machines — SQLite serializes writes inside a single file. Multiple application servers behind a load balancer all writing to the same logical database is a Postgres workload.
Network access required — if your data must be queried from clients on different hosts, you need a server. SQLite has no built-in network protocol.
Granular per-user authentication / row-level security — Postgres has rich RBAC and RLS. SQLite has filesystem permissions and that's it.
Very large datasets with hot writes — SQLite handles terabytes for read-heavy / append-heavy workloads, but a 10 TB OLTP system with thousands of concurrent writers belongs on a real database server.
Replication / HA out of the box — Postgres has streaming replication and managed services everywhere. SQLite needs Litestream / LiteFS / Turso to add replication, and those have different semantics.
Built-in role-based access — multi-tenant SaaS where each tenant must be isolated by SQL-level credentials.

Warning: The most common SQLite footgun is putting it behind a load-balanced web app on multiple servers without a replication layer. Each server gets its own copy of the file and they silently diverge. If you find yourself reaching for NFS, Glusterfs, or 'just rsync it', stop — pick a different database or add Litestream/Turso.

The honest version of 'SQLite scales' is: SQLite scales vertically and read-wise on a single node, dramatically better than people assume. It does not scale horizontally for concurrent writers without a real replication layer.

Code

What 'one writer at a time' looks like·python

# Two processes both trying to write — second one blocks briefly
# until the first commits, then proceeds. With WAL + busy_timeout this
# is fine for normal traffic; under hot-loop write contention from many
# processes it becomes the bottleneck.
import sqlite3, time, threading

def writer(name):
    conn = sqlite3.connect('contention.db', timeout=5.0)
    conn.execute('PRAGMA journal_mode=WAL')
    conn.execute('CREATE TABLE IF NOT EXISTS log(id INTEGER PRIMARY KEY, who TEXT, t REAL)')
    for _ in range(1000):
        conn.execute('INSERT INTO log(who, t) VALUES (?, ?)', (name, time.time()))
        conn.commit()
    conn.close()

threading.Thread(target=writer, args=('A',)).start()
threading.Thread(target=writer, args=('B',)).start()

Exercise

Take a service you've worked on that uses Postgres or MySQL. Identify which specific properties of that workload disqualify SQLite (network access, multi-writer, RBAC, etc.). Then ask: if you redesigned the architecture to put compute near the data (per-user database, edge runtime, sharded by tenant), could SQLite have been viable? Where would the design have to change?

When SQLite Is NOT the Right Choice

Where it stops being a good idea

Code

External links

Exercise

Progress

Comments 0