SQLite vs Postgres: When to Pick Which Database

Two Databases, Two Different Shapes

SQLite and PostgreSQL both speak SQL, both store relational data, and both can power real applications. Past that, they're built for different worlds.

• SQLite is a library. It lives inside your application's process and reads from a single .db file on disk. No server, no port, no users to configure.
• PostgreSQL is a server. It runs as its own process, listens on a network port, and your application connects to it as a client.

Almost every other difference between them — concurrency, deployment, type strictness, performance — comes from that one architectural split. Keep it in mind as we go.

Architecture: In-Process vs Client/Server

Opening a SQLite database is opening a file:

-- SQLite: the database is just this file.
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('hello from a file');
SELECT * FROM notes;

There's no daemon to start, no pg_hba.conf to edit, no port to expose. Your app loads the SQLite library, opens notes.db, and starts running queries. Deployment is "copy the file."

Postgres looks more like this:

# Start the server (once, as an admin):
sudo systemctl start postgresql

# Then connect from your app:
psql -h localhost -U alice -d mydb

Your application talks to a separate process — usually over TCP, sometimes over a Unix socket. That extra layer costs you setup time and a connection round-trip per query, but it buys you network access, multi-user authentication, and true concurrent writers.

Concurrency Is the Big One

This is usually the deciding factor. SQLite serializes writes: at any moment, one writer holds a lock on the database file, and other writers wait. Reads can happen in parallel (especially in WAL mode), but writes go one at a time.

Postgres uses MVCC (multi-version concurrency control) and row-level locking. Many transactions can write to different rows simultaneously without blocking each other.

In practice:

• A blog with 50 readers per second and one author writing occasionally? SQLite is fine.
• An e-commerce checkout where hundreds of users update inventory at once? Postgres.
• A mobile app's local cache? SQLite, no contest.
• A multi-tenant SaaS backend with dozens of background workers? Postgres.

WAL mode (PRAGMA journal_mode = WAL;) makes SQLite's concurrency story much better — readers don't block writers — but it doesn't change the one-writer-at-a-time rule.

Type Systems: Loose vs Strict

Postgres is strict. A column declared INTEGER rejects strings, period:

-- Postgres
CREATE TABLE t (n INTEGER);
INSERT INTO t (n) VALUES ('not a number');
-- ERROR: invalid input syntax for type integer

SQLite, by default, uses type affinity — a suggestion rather than a rule. The same insert succeeds:

CREATE TABLE t (n INTEGER);
INSERT INTO t (n) VALUES ('not a number');
SELECT n, typeof(n) FROM t;

The string sits there in an INTEGER column. SQLite stored it as text. This flexibility was a deliberate design choice — useful for quick prototypes, dangerous for long-lived schemas.

Modern SQLite (3.37+) supports STRICT tables that behave more like Postgres:

CREATE TABLE t (n INTEGER) STRICT;
-- INSERT INTO t (n) VALUES ('not a number');  -- now an error
INSERT INTO t (n) VALUES (42);
SELECT n, typeof(n) FROM t;

If you're starting a new SQLite project, use STRICT. It removes a whole class of "why is there a string in my number column" surprises.

Feature Surface

Postgres has more of nearly everything: data types (arrays, ranges, geometric, network, custom enums), procedural languages (PL/pgSQL, PL/Python), full-text search with ranking, materialized views, table partitioning, replication, role-based security, and a deep extension ecosystem (PostGIS, TimescaleDB, pgvector).

SQLite covers the essentials and adds a handful of nice features for its scale: JSON functions, full-text search via FTS5, R-Tree indexes, window functions, CTEs, generated columns. What it skips is anything that assumes a server: users, roles, replication, network access.

A rough mental model:

• Need GIS, vector search, or replication? Postgres.
• Need to ship a database inside an iOS app? SQLite.
• Need both? Many teams develop and test against SQLite, then deploy on Postgres — though that mix can bite you on syntax differences (see below).

Syntax Differences You'll Actually Hit

Most everyday SQL is identical. The differences cluster around schema, types, and a few built-in functions:

-- Auto-incrementing primary key
-- SQLite:
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
-- Postgres:
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
-- or, modern Postgres:
CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);

-- Current timestamp
-- SQLite:  CURRENT_TIMESTAMP   (returns text)
-- Postgres: NOW()              (returns timestamp)

-- Boolean type
-- SQLite:  no real BOOLEAN; use INTEGER 0/1
-- Postgres: BOOLEAN with TRUE/FALSE

If you develop on SQLite and deploy on Postgres, it pays to keep an ORM or a migration tool between you and the raw SQL — otherwise these differences leak into your app.

Performance, Honestly

"Faster" depends on the question. For a single process doing reads and small writes, SQLite is hard to beat — there's no network hop, no protocol parsing, no client connection. For benchmarks with one client, SQLite often outruns Postgres on simple queries.

Add concurrent writers, large datasets that need parallel query execution, or complex query plans that benefit from Postgres's mature planner, and Postgres takes the lead. Postgres also scales vertically (bigger machines, more cores) in ways SQLite simply isn't designed for.

The honest summary: SQLite is fast for what it's for. Postgres is fast for what it's for. Pick based on workload shape, not benchmark headlines.

A Quick Decision Guide

Reach for SQLite when:

• The data lives next to one application — desktop, mobile, embedded, CLI tool.
• Writes come from a single process or a small number of processes.
• You want zero-configuration deployment.
• You're prototyping and want to focus on the schema, not on infrastructure.

Reach for Postgres when:

• Multiple application servers or workers write to the database.
• You need network access from many clients.
• You need advanced features: roles, replication, GIS, custom types, stored procedures.
• The data is the durable, central store of a production service.

A common path: start a small project on SQLite, switch to Postgres if and when traffic shape demands it. The migration isn't free, but it's a known operation — and most projects never need it.

Next: When SQLite Is the Right Choice

The comparison above gives you the trade-offs. The next page goes deeper on the positive case for SQLite — the workloads where it's not just good enough but actually the better tool, and the warning signs that mean you've outgrown it.