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.
- "database is locked" shows up in your logs, or you raised the busy timeout until it stopped. The timeout hides the queue without removing it: in my test, 8 writers took 5 to 6 seconds in SQLite and under 1 second in PostgreSQL.
- You turned on WAL to fix writer errors and they continued. WAL helps readers; it keeps one writer at a time.
- A second machine needs to write, such as a second app server, a worker box or a cron host. WAL needs one host, and sqlite.org's checklist answers "client/server" as soon as a network sits between the data and the application.
- Your transactions do slow work while holding the write lock, like the 2 ms in my test: an API call, a file upload, a long computation. In SQLite every other writer waits for all of it.
- You found a word in an INTEGER column or nine characters in a VARCHAR(5). If STRICT tables cover what you need, convert those tables and stay. If you need lengths, dates or decimals enforced, which STRICT has no types for, that is PostgreSQL.
- Reports over the whole table are slowing down. The aggregate ran about twice as fast in PostgreSQL here; point lookups did not, so this signal counts only alongside one of the others.
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
- SQLite: Appropriate Uses For SQLite
- SQLite: Write-Ahead Logging
- SQLite: Result and Error Codes
- SQLite: STRICT Tables
- SQLite: Many Small Queries Are Efficient In SQLite
- SQLite: PRAGMA Statements
- SQLite: VACUUM
- PostgreSQL 16: MVCC Introduction
- PostgreSQL 16: Routine Vacuuming
- PostgreSQL 16: Explicit Locking, Row-Level Locks
- Python: sqlite3 module