SQLite vs PostgreSQL: Which One Your Project Actually Needs

SQLite is a file your application opens. PostgreSQL is a server your application connects to. Almost every real difference between them falls out of that one sentence.

The comparison usually gets framed as “lightweight versus serious”, which is both flattering to PostgreSQL and unfair to SQLite. SQLite is not a toy. It is one of the most widely deployed pieces of software on the planet, it is fully ACID compliant, and it will happily serve a read-heavy site faster than a Postgres instance sitting behind a network hop.

The question is not which database is better. It is whether your workload wants a file or a server. Get that right and the rest of the decision makes itself.

Table of contents

• The architectural difference that drives everything else
• Concurrency: the line most projects cross without noticing
• Where SQLite is the right answer
• Where PostgreSQL earns its overhead
• Migrating from SQLite to PostgreSQL without a bad weekend
• The honest default for a new project
• How this fits the rest of the stack
• FAQ

The architectural difference that drives everything else

SQLite runs inside your process. There is no daemon, no port, no connection string in the usual sense — you open a file and start issuing SQL. The entire database is that file, and copying it is a backup.

PostgreSQL runs as a separate process, usually on a separate machine. Your application opens a TCP connection, authenticates, and issues queries across that socket. That indirection costs you a network round trip on every query, and buys you everything else on this page.

Every other difference is downstream of this. Concurrency limits, deployment complexity, backup strategy, who can connect — all of it traces back to whether the database is a file in your container or a service on your network.

# SQLite: the connection is a file path
import sqlite3
conn = sqlite3.connect("app.db")

# PostgreSQL: the connection is a network endpoint
import psycopg
conn = psycopg.connect("postgresql://user:[email protected]:5432/app")

Concurrency: the line most projects cross without noticing

SQLite handles concurrent readers well. Many processes can read the same database file simultaneously without blocking each other, especially in WAL mode. This surprises people who assume it serialises everything.

Writes are the constraint. SQLite takes a write lock over the whole database, so one writer at a time, full stop. In WAL mode readers do not block behind that writer, which makes read-heavy workloads genuinely fast. But two concurrent writes queue, and if the queue is longer than your busy timeout you get SQLITE_BUSY.

PostgreSQL uses MVCC with row-level locking. Two transactions writing different rows in the same table do not contend at all. This is the capability you are actually buying when you move off SQLite, and it is worth real money the moment you have concurrent users submitting data.

The practical test: count your writes per second and ask whether they arrive in bursts. A blog with a hundred thousand readers and one author is a perfect SQLite workload. A booking system with forty simultaneous users claiming inventory is not.

Where SQLite is the right answer

Read-dominated workloads with a single writer. Documentation sites, content-driven applications, analytics dashboards reading a nightly-rebuilt file, and anything where the data changes on a deploy rather than on a request.

Local-first and embedded contexts. Desktop applications, mobile apps, CLI tools that need to remember state, and test suites that want a real SQL engine without a container. A test suite backed by an in-memory SQLite database starts in milliseconds.

Single-instance deployments where operational simplicity is worth more than horizontal scale. No connection pool to tune, no credentials to rotate, no separate service to monitor. The backup is cp app.db app.db.bak.

• The write path is one process, or writes are rare and tolerant of queuing
• The whole dataset fits comfortably on one machine’s disk
• You do not need multiple application instances writing to the same data
• Simplicity of operation is a feature, not a compromise

Where PostgreSQL earns its overhead

The moment more than one application instance needs to write, the argument is over. SQLite over a network filesystem is a well-documented way to corrupt data, and every workaround is worse than just running Postgres.

Type richness matters more than people expect. PostgreSQL gives you native jsonb with indexing, arrays, ranges, proper timestamptz handling, enums, and user-defined types. SQLite has dynamic typing and five storage classes, which is elegant until you want the database to reject a bad value rather than coerce it.

Then there is everything operational: real user and role management, row-level security, replication, point-in-time recovery, and concurrent index builds. These are not features you want to reimplement in your application layer.

-- The kind of thing Postgres does natively and SQLite does not
CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  payload     jsonb NOT NULL,
  tags        text[] NOT NULL DEFAULT '{}',
  occurred_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX events_payload_idx ON events USING gin (payload);
SELECT * FROM events WHERE payload @> '{"level": "error"}';

Migrating from SQLite to PostgreSQL without a bad weekend

Most migrations are not hard, they are just detailed. The schema translates almost directly; the surprises are in the type system and the places where SQLite let you be sloppy.

1. Audit your types first. SQLite’s INTEGER PRIMARY KEY becomes bigserial or an identity column. TEXT timestamps become timestamptz, and you must decide what timezone the old strings were in.
2. Find every place you relied on dynamic typing. Postgres will reject the string you were storing in a numeric column, and it is better to find those in a migration script than in production.
3. Add explicit transaction boundaries. Code written against a single-writer database often has races that were invisible because SQLite serialised them for you.
4. Introduce a connection pool. This is new infrastructure that did not exist in the SQLite version, and an unbounded pool will exhaust max_connections faster than you expect.
5. Run both in parallel against production traffic if you can, comparing results before you cut over.

The step teams skip is the third one. Code that never needed to think about concurrent writes usually contains a read-then-write sequence that was safe only because the database refused to interleave it. Postgres will happily interleave it.

The honest default for a new project

If you are shipping a service that other people connect to over a network, and you expect it to run as more than one instance eventually, start with PostgreSQL. Not because SQLite cannot handle the load, but because the migration is a project you will have to schedule, and starting on Postgres costs you a container you were probably running anyway.

If you are shipping something local, embedded, or read-dominated with a single writer, start with SQLite and stop apologising for it. The operational simplicity is real, and a database you can back up with cp has genuine advantages at three in the morning.

What you should not do is pick SQLite to avoid provisioning a database and then bolt on a network filesystem when you need a second instance. That is the one path that ends badly.

How this fits the rest of the stack

The database decision is rarely isolated. Choosing PostgreSQL means a managed instance, connection limits, backups, and a private network path from your service — each one a line item. On RunxBuild those pieces provision together, and the RunxBuild hosting calculator shows what the service plus the database plus the storage actually costs before you commit to the shape. Model both options if you are genuinely undecided; the difference is usually smaller than the cost of migrating later.

Useful related references:

• PostgreSQL 18: How to Upgrade Without Downtime
• PostgreSQL Show Tables: \dt, pg_catalog, and information_schema
• PostgreSQL ‘Not In’ Queries: Why They Stall and How to Fix Them
• Databases on RunxBuild

FAQ

Is SQLite production ready?

Yes, unambiguously. SQLite is ACID compliant, extensively tested, and deployed on billions of devices. The question is never whether it is production quality — it is whether your concurrency model matches what a single-writer database can offer.

How many concurrent users can SQLite handle?

For reads, a great many — thousands of concurrent readers is achievable in WAL mode. For writes, the ceiling is one at a time, so the real limit is your write rate and how long callers will wait. A few writes per second is comfortable; sustained heavy writing is not.

Can I run SQLite on a network filesystem so multiple servers share it?

You should not. SQLite’s locking depends on filesystem primitives that NFS and similar filesystems implement inconsistently, and the documented failure mode is database corruption. If you need multiple machines writing, you need a database server.

Does PostgreSQL support the SHOW DATABASES command from MySQL?

No. PostgreSQL uses the psql meta-command for listing databases, or you query pg_database directly with SQL. The MySQL syntax is not recognised.

Is PostgreSQL slower than SQLite?

For a single simple query on a local file, SQLite usually wins because there is no network round trip and no connection overhead. Under concurrent load, PostgreSQL pulls ahead decisively because it can execute writes in parallel where SQLite must serialise them.