Home / Guides
Comparison

SQLite vs PostgreSQL: When It Is Time to Move

By · November 22, 2026 · 13 min read

The short version

SQLite vs PostgreSQL comes down to who writes and from where. SQLite is a library that reads and writes one file inside your application's process, and sqlite.org itself says it competes with fopen(), not with client/server databases. It allows one writer at a time, and WAL mode lets readers and that writer overlap but does not add a second writer. PostgreSQL is a server: many clients write at once, from many machines, with types enforced. Stay on SQLite while one process on one machine does most of the writing; move when writers queue behind each other, when a second machine needs to write, or when the data is too important to let a column hold whatever arrives.

Everything below was run on one Apple M1 Pro laptop: SQLite 3.45.3 through Python's sqlite3 module (the CLI tests used the sqlite3 shell, 3.43.2), and PostgreSQL 16.14 in a local container reached over TCP on 127.0.0.1. The timings are for this machine only.

What sqlite.org says SQLite is for

The SQLite authors are unusually direct about where their engine stops fitting. The checklist on "Appropriate Uses For SQLite" has three questions, and a yes to any of them means client/server:

Question from sqlite.org Their answer
Is the data separated from the application by a network? choose client/server
Many concurrent writers? choose client/server
Big data, more than you are comfortable keeping in one file? choose client/server
None of the above choose SQLite

The same page says that for "device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution", and that a write-intensive website, or one busy enough to need several servers, should consider a client/server engine. The hard size limit is 281 TB, so size is rarely what forces the move. Writers usually are.

Can SQLite handle multiple writers?

It handles them by making them take turns. I started 8 processes, each running 200 transactions against one SQLite file. Each transaction inserts one row, then sleeps 2 ms before COMMIT, standing in for an application that does some work while its transaction is open. I ran it in the default rollback-journal mode and in WAL mode, first with no busy timeout and then with 5 seconds, which is Python's default for sqlite3.connect(). Then the same 8 processes against PostgreSQL with default settings:

sqlite 3.45.3 | 8 processes x 200 transactions, 2 ms of work inside each
  journal_mode = delete timeout=0                          committed    10  failed  1590   0.22 s  ['database is locked']
  journal_mode = delete timeout=5.0                        committed  1600  failed     0   6.27 s  []
  journal_mode = wal timeout=0                          committed     8  failed  1592   0.10 s  ['database is locked']
  journal_mode = wal timeout=5.0                        committed  1600  failed     0   5.12 s  []
postgres 16 (default settings)     committed  1600  failed     0   0.99 s  []
  postgres rows: 1600

"Failed" means the statement raised sqlite3.OperationalError: database is locked, my script rolled back and moved on without retrying. With no timeout, only 10 of 1,600 transactions got through. That error is SQLite's SQLITE_BUSY, which sqlite.org explains as process B trying to start a write while process A holds one, "because SQLite only supports one writer at a time".

A busy timeout makes the error go away by making each writer wait and retry. Every transaction then committed, but they ran one after another: 1,600 transactions with 2 ms of work each is at least 3.2 seconds of held lock, and the runs took 5 to 6 seconds. PostgreSQL ran the same workload in under a second because the 8 transactions really ran side by side; its row-level locks "block only writers and lockers to the same row", and these inserts never shared a row.

WAL mode did not change the writer picture at all. The WAL documentation says so plainly: "since there is only one WAL file, there can only be one writer at a time." If you set PRAGMA journal_mode=WAL hoping to fix "database is locked" between writers, this is why it did not work.

The sqlite.org page on result codes also explains one trap in the test above: SQLITE_BUSY can arrive mid-transaction, at any write or at commit. Starting with BEGIN IMMEDIATE moves the wait to the start, so a transaction that begins can finish.

What WAL mode does change

WAL changes how readers and the writer get along. Two connections to one file: a reader opens a transaction and reads, a writer inserts a row and commits.

-- journal_mode=delete
reader: BEGIN; SELECT count(*) -> 1
writer: BEGIN; INSERT ok
writer: COMMIT -> database is locked
reader: SELECT count(*) -> 1
writer: COMMIT after reader finished ok
reader, new transaction: SELECT count(*) -> 2
files: ['rw.db']
-- journal_mode=wal
reader: BEGIN; SELECT count(*) -> 1
writer: BEGIN; INSERT ok
writer: COMMIT ok
reader: SELECT count(*) -> 1
reader, new transaction: SELECT count(*) -> 2
files: ['rw.db', 'rw.db-shm', 'rw.db-wal']

In rollback-journal mode an open read transaction stopped the writer from committing. In WAL mode the commit went through and the reader kept its consistent view until its own transaction ended. That matches the WAL page: "readers do not block writers and a writer does not block readers". The setting is persistent, stored in the file, and it brings the -wal and -shm files with it. The cost is on the same page: all processes must be on the same host, and WAL does not work over a network filesystem.

PostgreSQL gets the same reader and writer behaviour from MVCC: "reading never blocks writing and writing never blocks reading", and it does it for many writers, not one.

Type strictness: flexible tables, STRICT tables and PostgreSQL

The same three inserts into an ordinary SQLite table:

sqlite> CREATE TABLE loose (id INTEGER PRIMARY KEY, qty INTEGER, price REAL, sku VARCHAR(5));
sqlite> INSERT INTO loose (qty, price, sku) VALUES ('12', '9.99', 'AB-123456');
sqlite> INSERT INTO loose (qty, price, sku) VALUES ('twelve', 'n/a', 7);
sqlite> INSERT INTO loose (qty, price, sku) VALUES (1.5, 3, '00042');
sqlite> SELECT id, qty, typeof(qty) AS t_qty, ... FROM loose;
id  qty     t_qty    price  t_price  sku        t_sku
--  ------  -------  -----  -------  ---------  -----
1   12      integer  9.99   real     AB-123456  text
2   twelve  text     n/a    text     7          text
3   1.5     real     3.0    real     00042      text

The word twelve sits in an INTEGER column, n/a in a REAL one, and the VARCHAR(5) holds nine characters. The pgloader guide shows what that kind of data does to a migration. SQLite has had an answer since 3.37.0: STRICT tables. Same inserts (with a number in place of 'n/a'), plus (1, 2, 7):

sqlite> CREATE TABLE tight (id INTEGER PRIMARY KEY, qty INTEGER, price REAL, sku TEXT) STRICT;
...
Runtime error near line 8: cannot store TEXT value in INTEGER column tight.qty (19)
Runtime error near line 9: cannot store REAL value in INTEGER column tight.qty (19)
id  qty  t_qty    price  t_price  sku        t_sku
--  ---  -------  -----  -------  ---------  -----
1   12   integer  9.99   real     AB-123456  text
2   1    integer  2.0    real     7          text

sqlite> CREATE TABLE t2 (sku VARCHAR(5)) STRICT;
Parse error near line 12: unknown datatype for t2.sku: "VARCHAR(5)"

STRICT refuses values that cannot be converted losslessly (the error is SQLITE_CONSTRAINT_DATATYPE), but it still converts the ones that can: '12' became the integer 12 and 7 became the text '7'. It allows only INT, INTEGER, REAL, TEXT, BLOB and ANY, so there is no length limit and no date or decimal type. PostgreSQL on the same values:

=> INSERT INTO items (qty, price, sku) VALUES ('12', '9.99', 'AB-123456')
ERROR:  value too long for type character varying(5)
=> INSERT INTO items (qty, price, sku) VALUES ('twelve', 1, 'x')
ERROR:  invalid input syntax for type integer: "twelve"
=> INSERT INTO items (qty, price, sku) VALUES ('1.5', 3, '00042')
ERROR:  invalid input syntax for type integer: "1.5"
=> INSERT INTO items (qty, price, sku) VALUES (1.5, 3, '00042')
INSERT 0 1
=> SELECT id, qty, price, sku, pg_typeof(sku) FROM items ORDER BY id
id | qty | price | sku | pg_typeof
1 | 12 | 9.99 | AB-12 | character varying
2 | 2 | 3.0 | 00042 | character varying
3 | 1 | 2.0 | 7 | character varying

PostgreSQL enforced the length that SQLite ignores in both modes. It also surprised me once: the numeric literal 1.5 went into the integer column as 2, rounded without a word, where STRICT SQLite refused it. Neither engine catches everything, so if strict typing is your only complaint, STRICT tables may be enough and you can stay.

Is SQLite faster than PostgreSQL?

For one process doing small queries, yes, in my run, by a lot. For a scan over a whole table, PostgreSQL was faster. One table of 200,000 sales rows, the same seeded data on both sides, one client process. SQLite ran twice, once with defaults (rollback journal, synchronous=FULL) and once with WAL and synchronous=NORMAL. PostgreSQL was loaded with multi-row INSERTs of 1,000 rows (execute_values). The second of two runs; the first was close on every line, the widest gap being the rollback-journal updates at 1.60 s:

sqlite delete/full     load 200,000 rows, one transaction        0.17 s
sqlite delete/full     20,000 lookups by primary key         0.13 s
sqlite delete/full     aggregate over all rows, 3 runs     0.051, 0.051, 0.049 s
sqlite delete/full     5,000 updates, one commit each       1.38 s
sqlite wal/normal      load 200,000 rows, one transaction        0.21 s
sqlite wal/normal      20,000 lookups by primary key         0.05 s
sqlite wal/normal      aggregate over all rows, 3 runs     0.049, 0.051, 0.053 s
sqlite wal/normal      5,000 updates, one commit each       0.12 s
   ('central', 25181, 6288759.64)
postgres 16 over tcp   load 200,000 rows, one transaction        2.17 s
postgres 16 over tcp   20,000 lookups by primary key         3.90 s
postgres 16 over tcp   aggregate over all rows, 3 runs     0.025, 0.023, 0.022 s
postgres 16 over tcp   5,000 updates, one commit each       2.51 s
   ('central', 25181, Decimal('6288759.64'))

The read-heavy line, 20,000 lookups, is the one that matters for most SQLite apps: 0.05 to 0.13 s in SQLite against 3.9 s in PostgreSQL. sqlite.org explains why on its N+1 queries page: queries "do not involve message round-trips, only a function call". Every PostgreSQL query here crossed a TCP connection into a container. The aggregate went the other way, about twice as fast in PostgreSQL; its plan was a plain Seq Scan and HashAggregate, no parallel workers.

The write-heavy line needs a caveat. WAL with synchronous=NORMAL was the fastest at single-row commits, and the pragma page says what it trades for that: a transaction committed that way "might roll back following a power loss or system crash". And all of these numbers are one process. Add the second writer from the earlier test and SQLite stops scaling while PostgreSQL does not.

File size growth and VACUUM

Both engines keep space after a delete. SQLite, on the benchmark file, in rollback-journal mode:

after load                             rows 200,000  file  7,467,008 bytes  pages  1823  free pages     0
DELETE half the rows                   rows 100,000  file  7,467,008 bytes  pages  1823  free pages     6
INSERT 25,000 new rows                 rows 125,000  file  8,372,224 bytes  pages  2044  free pages     0
VACUUM                                 rows 125,000  file  4,657,152 bytes  pages  1137  free pages     0
auto_vacuum = 0

Deleting every second row freed only 6 whole pages, because every page was left half full. freelist_count undercounts that kind of waste. New rows with higher ids went to the end, and the file grew while holding fewer rows than at the start. VACUUM rebuilt it to 4.7 MB; it copies the database to a temporary file and can need up to twice the file size in free disk. auto_vacuum is off by default and only truncates free pages; it does not repack half-empty ones.

PostgreSQL, same data, autovacuum switched off on this table so the steps stay visible:

after load                             rows 200,000  table 12,173,312 bytes  with indexes 16,719,872
UPDATE every row once                  rows 200,000  table 24,158,208 bytes  with indexes 33,193,984
DELETE half the rows                   rows 100,000  table 24,158,208 bytes  with indexes 33,193,984
VACUUM                                 rows 100,000  table 24,158,208 bytes  with indexes 33,202,176
INSERT 25,000 new rows                 rows 125,000  table 24,158,208 bytes  with indexes 34,119,680
VACUUM FULL                            rows 125,000  table  7,536,640 bytes  with indexes 10,379,264

One UPDATE of every row doubled the table, because PostgreSQL keeps the old row version until VACUUM removes it. Plain VACUUM did not shrink the file; it marked the space for reuse, and the 25,000 new rows went into it. VACUUM FULL gave the space back, but it rewrites the table under an ACCESS EXCLUSIVE lock. In normal running the autovacuum daemon does the plain VACUUM for you. File growth is not a reason to move; both engines need housekeeping, PostgreSQL just schedules its own.

When is it time to move from SQLite to PostgreSQL?

Each of these ties back to something above.

If none of these are true, one process on one machine, mostly reads, sqlite.org's advice is to stay, and my numbers agree. If some are, the SQLite to PostgreSQL converter reads the file and writes a PostgreSQL dump with identities and sequences set. The pgloader guide covers the three migration routes and the type cleanup, and for a Django project, Django: switching from SQLite to PostgreSQL covers the framework side.

Sources