SQLite vs Postgres on a small project

For a small project with one application process, SQLite in WAL mode is enough: 300,000 rows take 26.8 MB and indexed reads stay under a tenth of a millisecond. PostgreSQL earns its place when you need concurrent writers, clients over the network, or extensions SQLite lacks.

What you get

• One file: the database is a file on the application disk, with no service, port or credential to configure.
• Backup in one line: the .backup command reads a consistent state even while the site is running.
• ACID transactions: atomicity and isolation are there, with BEGIN IMMEDIATE to take the write lock up front.
• A licence that asks nothing: public domain, no attribution and no obligation to open your code.

What you lose

• Concurrent writers: one writes at a time; the others wait for the lock, and without busy_timeout they get an error instead of waiting.
• Multiple hosts: the file lives on a local disk. Locking is not reliable on a network filesystem, so two servers cannot share the same database.
• Extensions and replicas: no PostGIS, no native read replica, no parallel query.
• Roles and grants: permissions belong to the filesystem, not to a database user.

The test numbers

When migrating pays

The signal is not database size, it is how many processes write. Two application instances behind a load balancer already break the single-writer model, and a jobs worker writing while the site writes creates waits no index can fix. Add a need for read replicas, point-in-time recovery or a geographic extension, and the migration pays for itself.

Mode, backup, waiting

Three settings matter in production: WAL so reads are not blocked during a write, a busy_timeout so waits become waits instead of errors, and a .backup instead of a file copy. The same settings make a later move to PostgreSQL easier, because the application logic does not depend on the engine.

sqlite3 app.db "PRAGMA journal_mode=WAL; PRAGMA busy_timeout=5000;"
sqlite3 app.db ".backup 'backup.db'"
sqlite3 app.db "PRAGMA integrity_check;"

An online backup leaves the -wal and -shm files of the live database in place: they are part of the state, not leftovers. Copy the database by hand while the application writes and you risk a file that opens without errors but holds inconsistent pages, and an integrity check is the only way to notice.

The EneUI catalogue

A view over rows is needed whatever the engine: the catalogue holds table components, dashboard blocks and the Origin UI table record, with an MIT licence declared upstream. Checked on 1 November 2026 against 111,985 components.

The limitation

The numbers come from one machine, with Node and a local disk whose performance we do not know: they are not an engine comparison. We did not measure behaviour with several simultaneous writers, nor latency on a network filesystem, which is exactly where SQLite gets worse. If the deployment is serverless, multi-region or several writing containers, the answer is PostgreSQL and no measurement changes it.

Where to continue

The shape of queries and of the contract is in designing an API; the cache rules that decide half the perceived latency are in HTTP cache headers. If you are choosing between API styles, the comparison with the costs of each is in MCP and REST.