What Nobody Tells You About SQLite in Production

The conventional wisdom is that SQLite is for development, embedded systems, and local apps. For anything production, you reach for Postgres or MySQL.

I used SQLite in production for a year. Here’s what I actually learned.

The Setup

A small internal tool — a project tracking dashboard for a team of eight. Single server, low concurrency, maybe 50 requests per minute at peak. The kind of workload that gets called “small” but still needs to be reliable.

I chose SQLite because it eliminated infrastructure. No separate database process to run, no connection pooling to configure, no backup agent to maintain.

WAL Mode Changes Everything

The default SQLite journal mode blocks readers during writes. This is where the “SQLite doesn’t scale” reputation comes from. Turn it off immediately.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

WAL (Write-Ahead Logging) allows concurrent readers and a single writer simultaneously. After enabling it, I never saw a locking error in production.

busy_timeout is important. It tells SQLite to retry on lock contention for up to N milliseconds before returning an error. Without it, a brief write will cause reader failures.

Performance Is Surprisingly Good

Benchmarks for my workload:

Operation	Time
Simple SELECT by primary key	~0.1ms
JOIN across 3 tables, 50k rows	~8ms
INSERT with transaction	~0.3ms
Bulk insert (1000 rows, single txn)	~12ms

These numbers are on a $6/month VPS. Postgres on the same machine was roughly comparable for reads, faster for heavy analytics.

For single-server workloads, SQLite is fast enough that it’s never the bottleneck. Your application code will be.

Backups Are Trivially Easy

sqlite3 production.db ".backup /backups/production-$(date +%Y%m%d-%H%M%S).db"

sqlite3’s .backup command creates a consistent snapshot while the database is live. I run this in a cron job every hour and upload to object storage.

Compare this to Postgres: pg_dump, WAL archiving, a recovery plan, a tested restore procedure. All necessary for large datasets, complete overkill for 200MB.

What Actually Caused Problems

Migrations. SQLite’s ALTER TABLE support is limited — you can’t drop columns or change column types without recreating the table. This is fine until you need to drop a column.

The workaround is the standard SQLite migration dance:

CREATE TABLE projects_new (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  -- new schema without the dropped column
);

INSERT INTO projects_new SELECT id, name FROM projects;
DROP TABLE projects;
ALTER TABLE projects_new RENAME TO projects;

Tedious but predictable. I wrapped this in a migration runner that I wrote in a weekend.

Type affinity confusion. SQLite’s type system is flexible to a fault. Storing "2026-02-20" versus 2026-02-20 versus 1740009600 as a date means your queries need to be consistent. Use ISO 8601 strings and validate at the application layer.

No concurrent writes. This is the real limit. A single writer means that long-running write transactions will queue up. If you’re processing bulk jobs while serving web traffic, you’ll hit this.

The fix is to keep transactions short. Don’t hold a write lock while doing external API calls or file I/O.

When to Use It

SQLite is a good fit when:

• You’re running a single server (or don’t need horizontal write scaling)
• Your dataset is under a few GB
• Operational simplicity matters
• You want trivially easy backups

It’s the wrong choice when:

• You need read replicas or write scaling
• Multiple application servers share the same database
• Your team expects Postgres

For most internal tools, side projects, and small production apps, SQLite is under-used. The constraints are real but rarely apply.

Postgres is almost always the right default. But “almost always” isn’t “always”, and SQLite earns its place more often than its reputation suggests.

The production database for that internal tool is still running SQLite. Nobody has noticed, or cared.