Postgres vs SQLite

SQLite is one of the most deployed pieces of software on the planet ,it's everywhere. It's also a genuinely great database for a huge range of apps. So why would anyone reach for Postgres instead?

The honest answer: because SQLite and Postgres aren't really competing for the same job. One is an embedded file format with a SQL interface. The other is a client-server database designed to be shared. Once you understand that distinction, it's obvious when each one wins.

Concurrency

SQLite locks the entire database file when a write happens. Older versions blocked all other writers (and in some modes, readers) until that write finished. Newer WAL mode improves concurrent reads during writes, but SQLite still fundamentally serializes writes there's no getting around that it's one writer at a time.

Postgres uses MVCC (multi-version concurrency control), so readers never block writers and writers don't block readers. Multiple clients can write to different rows simultaneously without stepping on each other.

-- Postgres: two clients can safely update different rows of the same table
-- at the same time, with no manual locking required
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If your app has more than one process or more than a handful of concurrent users writing data, this alone usually settles the decision.

It's a server, not a file

SQLite reads and writes a single file on disk from inside your application process. That's its superpower for embedded use — and its ceiling for anything else. There's no network protocol, no remote access, no separate process to manage.

Postgres runs as its own server that any number of applications, services, or machines can connect to over the network:

# Any service on your network can connect to the same database
psql "postgresql://app_user:[e-mail]:5432/production"

This is the difference between "one app owns this data" and "this data is a shared resource for your whole system" , which matters the moment you have more than one service (an API, a background worker, an analytics job) that all need the same data.

A real type system

SQLite is dynamically typed by default ,a column declared INTEGER will still accept text unless you explicitly opt into strict typing (added in SQLite 3.37+). Postgres enforces types strictly by default, and gives you far more of them:

-- Postgres: rich, enforced types out of the box
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  roles TEXT[] DEFAULT '{}',
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

Arrays, JSONB, UUIDs, ranges, network address types, enums. These aren't bolted on, they're core to how Postgres models data.

Querying power

SQLite's SQL support is deliberately minimal, which keeps it small and fast to embed. Postgres supports the full range of modern SQL plus extensions:

-- Window functions: rank each order within a customer's history
SELECT
  customer_id,
  order_id,
  total,
  RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS spend_rank
FROM orders;

-- Full-text search built into the engine, no extra service required
SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('database & performance');

Things like materialized views, recursive CTEs at scale, PostGIS for geospatial data, and pgvector for embeddings simply aren't in SQLite's scope.

Access control

SQLite has no concept of users or permissions, if a process can read the file, it can read (and write) everything in it. Access control is whatever your filesystem gives you.

Postgres has real roles, grants, and even row level security:

-- Restrict a role to only see rows belonging to their own tenant
CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

If different users or services need different levels of access to the same data, Postgres can enforce that at the database layer instead of trusting every client to behave.

Where SQLite still wins

None of this makes SQLite a worse database — it makes it a different one:

• Zero ops. No server to install, patch, or monitor.
• Portability. A whole database is one file you can copy, email, or check into version control.
• Speed for local, single-writer workloads. Mobile apps, desktop apps, browser storage, embedded devices, CLI tools, and tests are often faster and simpler with SQLite.
• Simplicity. For a prototype or a small app that will only ever run as one process, standing up Postgres is pure overhead.

The actual decision

Ask one question: does more than one process need to write to this data?

• If no — one app, one process, local file — SQLite is usually the better call.
• If yes — multiple services, multiple users, anything accessed over a network . Postgres is built for exactly that.

Plenty of successful products start on SQLite and migrate to Postgres later, and that's a fine path. The mistake is picking SQLite for a multi-writer, networked system because it seemed simpler at the start, and picking Postgres for a single-user local app because it seemed more "production-grade." Match the tool to the shape of the problem, not its reputation.

---