SQLite vs PostgreSQL: When to Upgrade

Pocket notebook vs filing office

SQLite is a single library you link into your application. The whole database is one file on disk; opening it is opening a file. PostgreSQL is a separate server process you talk to over a socket. Both are excellent at their respective jobs. The question is never "which is better" but "which fits this workload."

SQLite is the right tool when

One process at a time writes (mobile apps, CLI tools, single-user desktop apps).
Data lives on the same machine as the application.
You want zero operational footprint — no server to start, secure, back up, or upgrade.
Test suites that need a real database without standing up a server.

You graduate to PostgreSQL when

Multiple application servers need to write concurrently.
You need replication, point-in-time recovery, or a hot standby.
JSONB, full-text search, pgvector, PostGIS, or row-level security comes into the picture.
Dataset is growing past a few GB or write rate exceeds what one machine sustains.

The migration is mostly painless

SQL is largely portable. The differences worth knowing: SQLite types are dynamic; Postgres types are strict. SQLite's AUTOINCREMENT becomes GENERATED ALWAYS AS IDENTITY. Date functions differ. Almost everything else translates one-to-one.

Code

SQLite zero-config·bash

sqlite3 myapp.db "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);"
sqlite3 myapp.db "INSERT INTO notes (body) VALUES ('Hello world');"
# The entire database is the file myapp.db. Copy it; mail it; it works.

PostgreSQL features SQLite has no equivalent for·sql

-- Concurrent multi-user writes via MVCC (no global writer lock)
-- Real-time pub/sub baked into the engine
LISTEN order_events;
NOTIFY order_events, '{"order_id":42,"status":"paid"}';

-- Row-level security
ALTER TABLE patient_records ENABLE ROW LEVEL SECURITY;
CREATE POLICY doctor_sees_own ON patient_records
  USING (doctor_id = current_setting('app.current_doctor')::int);

-- Logical replication of selected tables
CREATE PUBLICATION orders_pub FOR TABLE orders, line_items;

SQLite vs PostgreSQL: When to Upgrade

Pocket notebook vs filing office

SQLite is the right tool when

You graduate to PostgreSQL when

The migration is mostly painless

Code

External links

Exercise

Progress

Comments 0