SQLite vs PostgreSQL: How to Choose the Right Database for Your Next Project

Here's a question I've seen answered badly more times than I can count: "Should I use SQLite or PostgreSQL?"

The typical response is some version of "SQLite is for prototypes, PostgreSQL is for production." That's not just oversimplified — it's actively misleading. It leads people to stand up a Postgres server for a single-user CLI tool (overkill), or to ship SQLite as the primary database for a multi-writer SaaS (a problem waiting to happen).

The honest answer is that they're optimized for fundamentally different things, and the right choice depends on specifics of your workload that most comparison articles never ask about. Let's actually go through them.

The Core Difference (That Most Comparisons Skip)

SQLite is a library. You link it into your application process, and it reads and writes a file on disk. There's no server, no network, no authentication handshake. Your app and the database live in the same process.

PostgreSQL is a server. It runs as a separate daemon, manages its own memory and connection pool, and accepts queries over a network protocol. Your application connects to it as a client.

This single architectural difference explains almost every other difference people argue about:

• Concurrency — Postgres handles hundreds of simultaneous connections with MVCC. SQLite serializes writes through a single global lock (even in WAL mode).
• Deployment — SQLite is npm install better-sqlite3 and you're done. Postgres is a server you have to run, monitor, back up, and keep alive.
• Scale — Postgres is built for terabytes across multiple disks. SQLite is built for gigabytes on a single machine.
• Latency — SQLite queries are microseconds, because there's no network round-trip. Postgres queries are milliseconds, because there's always a network hop (even on localhost).

None of these make one "better." They make one appropriate for a specific workload.

When SQLite Is the Right Call

1. Single-Process Applications

If your app runs as a single process — a CLI tool, a desktop app, a mobile app, a single-server webhook handler — SQLite is almost always the right default. There's no benefit to a separate database server when there's only one client.

Examples I'd reach for SQLite without thinking:

• A desktop app that needs local storage (BaseVolt itself uses SQLite for its config)
• A CLI tool that tracks state between runs
• A mobile app (SQLite is already embedded in iOS and Android)
• A prototype or MVP where you don't know your schema yet
• A read-heavy analytics dashboard backed by a pre-computed .db file

2. Embedded and Edge Workloads

SQLite's "no server" property becomes a superpower at the edge. Cloudflare D1 is literally SQLite running on Cloudflare's edge network. Turso and libSQL are SQLite forks optimized for distributed reads. If you're building something that runs close to users — a worker on a CDN edge, an IoT device, an embedded system — SQLite is the only sensible option. Postgres doesn't run on a Raspberry Pi Zero in any way you'd want to maintain.

3. Read-Heavy Workloads with Low Write Contention

Here's a fact that surprises people: SQLite in WAL mode can handle thousands of concurrent readers with no contention. Readers don't block writers, and writers don't block readers. The limitation is specifically concurrent writes — only one writer at a time.

If your workload is 99% reads (a CMS, a product catalog, a config store, an analytics dashboard over pre-computed data), SQLite will scale further than you think. The "SQLite doesn't scale" meme is mostly about write-heavy multi-user apps, not read-heavy ones.

4. When You Don't Want to Operate a Database

This is underrated. Running Postgres in production means:

• Setting up connection pooling (PgBouncer or built-in pooler)
• Configuring shared_buffers, work_mem, max_connections, wal_level
• Setting up automated backups and testing restores
• Monitoring replication lag, vacuum bloat, lock contention
• Upgrading major versions with pg_upgrade or logical replication

If you're a solo developer or a small team without a dedicated DBA, every one of those is a thing that can break at 3am. SQLite has none of them. The backup story is literally cp database.db database.db.bak.

When PostgreSQL Is the Right Call

1. Multi-Process or Multi-Server Workloads

The moment you have two application servers that need to read and write the same data, SQLite is out. You can put a SQLite file on a network share, but you shouldn't — file locking over NFS/SMB is a footgun that will corrupt your database eventually.

Postgres is designed for exactly this. Multiple app servers connect to a single Postgres instance, and Postgres handles the concurrency correctly. If you're running more than one replica of your application, you need a server-side database.

2. High-Write-Concurrency Workloads

If you have dozens of concurrent writers — a SaaS with active users, a logging pipeline, a real-time analytics ingest — SQLite's single-writer lock becomes the bottleneck. Even in WAL mode, writers queue. Postgres handles this with MVCC: multiple writers can modify different rows simultaneously, and conflicts are resolved at commit time.

Rule of thumb: if "concurrent writes from different processes" is a core characteristic of your workload, you need Postgres (or MySQL, or another server-side RDBMS).

3. Advanced SQL Features You Actually Need

Postgres has features SQLite doesn't, and some of them matter:

• Window functions — SQLite has them now, but Postgres's implementation is more complete
• Materialized views — Postgres has them natively; SQLite doesn't
• Full-text search — both have FTS, but Postgres's tsvector/tsquery is more powerful
• JSON/JSONB — both support JSON, but Postgres's JSONB with GIN indexes is in a different league
• PostGIS — if you need geospatial queries, PostGIS is the gold standard
• Logical replication, partitioning, LISTEN/NOTIFY — server-side features with no SQLite equivalent

The key phrase above is "you actually need." Most apps don't use most of these. But if you're building a geospatial application, or you need real-time push via LISTEN/NOTIFY, or you're querying nested JSON at scale, Postgres is the answer.

4. Strict Data Integrity at the Database Layer

SQLite is famously permissive by default. It uses dynamic typing — you can insert a string into an integer column and SQLite will happily store it. Foreign key enforcement is off by default (you have to PRAGMA foreign_keys = ON every connection). Postgres is strict by default: type mismatches throw errors, foreign keys are enforced, constraints are non-negotiable.

If you're working with a team and you want the database to enforce integrity rather than relying on application code, Postgres's strictness is a feature, not a bug.

The Comparison Table (With Honest Tradeoffs)

Dimension	SQLite	PostgreSQL
Architecture	Embedded library	Client-server
Setup time	Seconds (one file)	Minutes to hours (server config)
Concurrent readers	Thousands (WAL mode)	Thousands
Concurrent writers	1 (serialized)	Hundreds (MVCC)
Max practical size	~1TB per file	Terabytes+
Network latency	None (in-process)	0.1-2ms even on localhost
Backup	Copy the file	pg_dump / WAL archiving / snapshots
Operational burden	Near zero	Significant
Cost	Free	Free (self-hosted) or $$ (managed)
Strict typing	Optional (dynamic by default)	Enforced
JSON support	JSON1 extension	JSONB with GIN indexing
Full-text search	FTS5	tsvector with ranking
Geospatial	Limited	PostGIS (best in class)
Replication	Not built-in (litestream etc.)	Built-in streaming + logical
Best for	Single-process, edge, read-heavy	Multi-server, write-heavy, complex

A Practical Decision Framework

Instead of a flowchart you'll skip past, here are the four questions that actually determine the answer.

1. How many processes will write to this database at the same time?

• One → SQLite is fine
• More than one → Postgres

2. Will the database live on more than one machine?

• No, single machine → SQLite is fine
• Yes, multiple servers / replicas → Postgres

3. Do you need advanced Postgres-only features (PostGIS, materialized views, JSONB at scale, logical replication)?

• No → SQLite is fine
• Yes → Postgres

4. Is your team's ability to operate a database server a constraint?

• Yes, we have no DBA and no time → SQLite (or managed Postgres)
• No, we can run Postgres → Postgres

If you answered "SQLite is fine" to three or more, use SQLite. If you answered "Postgres" to two or more, use Postgres. The edge cases are rarer than the internet makes them seem.

The Reality: A Lot of Teams Use Both

Here's what nobody tells you: the choice isn't always either/or.

A common production pattern I've seen in well-run teams:

• PostgreSQL as the primary operational database (multi-server, high-write, the source of truth)
• SQLite as a local cache, edge replica, or analytics snapshot on individual machines

Cloudflare D1 (SQLite at the edge) paired with a Postgres primary is a legitimate architecture for globally-distributed apps. Turso + Postgres is the same idea. You write to Postgres, replicate to SQLite instances close to users, and read locally at zero latency.

The friction in this setup isn't the databases — it's the tooling. Most admin panels are built for one or the other. You end up with DBeaver for Postgres and DB Browser for SQLite, with completely different workflows for each.

This is part of why we built BaseVolt to handle both. Point it at a .db file for SQLite, or give it a Postgres connection string, and you get the same admin interface — grid views, kanban boards, dashboards, relationship configuration — regardless of which database you're connected to. For teams running both, it means one tool instead of two. (There's a live demo at demo.basevolt.app if you want to see it before installing.)

Common Mistakes I've Seen

Using SQLite for a multi-writer SaaS because "it's simpler." It is simpler — until your second user writes at the same time as your first and you hit SQLITE_BUSY. I've seen this in production. Use Postgres.

Standing up Postgres for a single-user CLI tool. You now have a database server to back up, monitor, and upgrade, for an app used by one person. Use SQLite.

Dismissing SQLite because "it doesn't scale." It scales to hundreds of GB and thousands of concurrent readers. The thing it doesn't do is concurrent writes from multiple processes. If that's not your workload, SQLite scales further than you think.

Treating Postgres defaults as production-ready. They're not. max_connections = 100 with no pooling will break under any real load. If you're running Postgres, learn about PgBouncer, shared_buffers, and wal_compression — or use a managed service (Neon, Supabase, RDS) that handles this for you.

Bottom Line

SQLite and PostgreSQL aren't competitors. They're tools for different jobs.

• SQLite when the database and the app live together: desktop apps, mobile apps, edge functions, CLI tools, read-heavy single-server workloads, prototypes.
• PostgreSQL when the database serves multiple clients: web apps with multiple servers, high-write-concurrency workloads, apps that need advanced SQL features, anything where strict integrity at the DB layer matters.

The wrong move is to pick based on what's "more powerful." Pick based on what your workload actually does. Most apps don't need a database server. Some apps can't work without one. Knowing which is which is the actual skill.

If you're working with either (or both), BaseVolt gives you a local-first admin panel for SQLite and PostgreSQL without sending your data anywhere. No account, no cloud, works offline.

---

Building something with SQLite or Postgres? I'm curious what you're working on — find me on X.