SQLite and PostgreSQL both speak SQL, and a query written for one often runs unchanged on the other. That similarity hides a much bigger difference: SQLite is a library that reads and writes a single file, and Postgres is a client-server database that runs as its own process, usually on its own machine. That one architectural choice explains almost every other difference between them.
SQLite: a database that's just a file
There's no server to start, no port to open, and no user to authenticate — your
application links against SQLite directly and reads a .db file on disk. That
makes it close to zero-setup, trivially easy to back up (copy the file), and a natural fit
for anything that ships as a single artifact: mobile apps, desktop apps, embedded devices,
and command-line tools.
The trade-off is concurrency. SQLite allows any number of simultaneous readers, but only one writer at a time. In WAL mode (write-ahead logging), readers don't block on a writer, which covers a lot of real workloads — but you still can't have two processes writing to the same database file at once without one waiting on the other.
-- Enable WAL mode once, per database file
PRAGMA journal_mode = WAL;PostgreSQL: a server built for concurrency and scale
Postgres runs as a long-lived server process that many clients connect to over TCP, each getting its own backend process and a consistent view of the data via MVCC (multi-version concurrency control). That's what lets dozens of application servers write to the same database at once without stepping on each other.
The cost is operational: you provision a server (or use a managed one), manage connections, handle backups and replication, and think about network latency between your app and the database. For a side project that's overhead you may not need yet; for a multi-tenant SaaS product serving real traffic, it's exactly what you signed up for.
Feature differences that actually matter
| Trait | SQLite | PostgreSQL |
|---|---|---|
| Deployment | Embedded, single file | Client-server |
| Concurrent writers | One at a time | Many, via MVCC |
| Data types | Dynamically typed columns | Strict, rich type system (JSONB, arrays, ranges) |
| Network access | No — local file only | Yes — connect from anywhere with credentials |
| Extensions | Minimal, compiled in | Large ecosystem (PostGIS, pg_trgm, pgvector...) |
| Best fit | Local-first apps, embedded, tests | Multi-user apps, production web services |
A practical way to decide
Ask one question: does more than one process need to write to this database at the same time, over a network? If no — a desktop app, a mobile app, a CLI tool, a test suite, a low-traffic internal script — SQLite is usually the simpler, faster choice. If yes — a web app with multiple servers, a team of engineers hitting the same database, a product that needs to scale past one machine — reach for Postgres.
It's also common to use both in the same project: SQLite for local development and tests (fast, no server to run), Postgres in production. That only works cleanly if you avoid SQLite-specific looseness — stick to standard types and standard SQL, and test against Postgres before you ship.
If you work with both dialects, a client that speaks both natively — rather than picking a side — removes a bit of context-switching. QuerySQL is a free desktop client for exactly this: SQLite and Postgres in one schema tree, one editor, one table view.