Comparison
SQLite or Postgres? Look at your write pattern first
The verdict
Default to Postgres 18. It is the safest database decision in web development and the one you are least likely to regret at scale. Choose SQLite - through Turso, Cloudflare D1, or a plain file on one machine - when reads dominate, the working set fits on disk, and writes are either low volume or naturally partitioned one database per tenant. That is not a small niche, and 2026 is the first year the SQLite side of this argument stands up without excuses. But the moment you need many concurrent writers, a real query planner, or an extension like pgvector, the answer flips back and stays there.
Both sides moved. Postgres 18 shipped in September 2025 with an asynchronous I/O subsystem that cut sequential scan times by roughly two to three times on storage-bound workloads, plus a native uuidv7() function and OAuth authentication. Postgres 19 reached beta 2 in July 2026 with a final release expected in the autumn. On the SQLite side, libSQL added server mode, embedded replicas, concurrent writes via MVCC, and native vector search.
Postgres vs SQLite where the difference is measurable
Latency figures below come from published third-party comparisons and should be treated as directional claims, not guarantees. The shape of the result reproduces consistently even when the absolute numbers do not.
| Dimension | Postgres 18 | SQLite and libSQL |
|---|---|---|
| Read latency | Managed instances serving cross-region traffic typically land at 30 to 80 ms per query. | Cloudflare D1 edge replicas report sub-10 ms reads; Turso embedded replicas measure in microseconds because the read never leaves the process. |
| Complex queries | Mature cost-based planner. Aggregations in one published test ran 18 ms against D1's 45 ms, and the gap widens with joins. | Excellent on primary key lookups, weaker on multi-join analytics. No parallel query, no advanced join strategies. |
| Concurrency | MVCC with true multi-writer concurrency. This is the reason Postgres is the default answer. | Historically one writer at a time. libSQL adds concurrent writes through MVCC, but the ceiling is still far lower. |
| Extensions | pgvector, PostGIS, pg_cron, TimescaleDB, full text search, row level security. The extension ecosystem is the moat. | libSQL ships native vector search and FTS5 covers text. Beyond that you are writing application code. |
| Operations | Connection pooling, vacuum tuning, failover, and version upgrades are all real jobs, even on a managed provider. | Effectively none. D1 is a Worker binding, not a connection string. There is no pool to exhaust. |
| Cost at low traffic | A managed instance is a fixed monthly floor even when idle, unless you use a serverless Postgres that scales to zero. | Near zero. A file costs nothing and D1 free tier covers a real side project comfortably. |
| Exit cost | Low between Postgres providers. Standard SQL, standard dump format, dozens of hosts. | Cheap early, expensive late. Type affinity, limited ALTER TABLE, and dates stored as text all surface during a migration. |
When each database is the right call
Choose Postgres when
- Multiple users write to the same tables at the same time, which describes most multi-user applications.
- You run reporting, aggregation, or anything with more than two joins on a hot path.
- You are storing embeddings and want pgvector next to the rows it describes rather than in a separate service.
- You need row level security, geospatial types, scheduled jobs, or logical replication into a warehouse.
- You want the widest hiring pool and the deepest pile of answered questions when something breaks at 2am.
Choose SQLite when
- You are running one database per tenant, which turns the single-writer limit into a non-issue and makes backups trivial.
- The app runs at the edge and a 40 ms round trip to a central primary would dominate your response time.
- It is a CLI, a desktop app, a mobile app, or a developer tool where shipping a server is absurd.
- Reads outnumber writes by a wide margin: docs sites, catalogs, analytics dashboards over precomputed tables.
- You want the entire test suite to run against a real database created and destroyed per test in milliseconds.
The part nobody mentions until it hurts
The SQLite renaissance is real, and it is mostly a hardware story plus a distribution story. Modern NVMe makes a single-node database absurdly fast, and libSQL, Turso, D1 and LiteFS solved the two things SQLite genuinely lacked: replication and remote access. Turso's embedded replicas are the most interesting of the group because the read path is a local file with background sync, which is why measured averages land in the hundreds of microseconds rather than milliseconds. That is not a marginal improvement over a network round trip; it is a different category.
The catch arrives on writes. Every one of these systems funnels writes to a single primary, so your write throughput is bounded by one machine and one region no matter how many replicas you have. If your product has a write-heavy core - a chat app, an order system, a collaborative editor without CRDTs - you will hit that wall, and the fix is a migration, not a config change. Postgres does not have this problem until much later, and by then you have revenue to pay for the solution.
The other underrated cost is schema evolution. SQLite's limited ALTER TABLE support means many migrations become create-copy-rename dances that your ORM hides right up until the day it does not. Postgres handles most column changes in place, and transactional DDL means a failed migration rolls back cleanly instead of leaving the schema half-applied. On a product that ships weekly, that difference shows up as real hours.
A practical hedge that works: build on Postgres, but keep the SQL boring. Avoid vendor-specific syntax, keep your types conservative, and you retain the option to run per-tenant SQLite later for the workloads that suit it. Going the other way - starting on SQLite and discovering you needed Postgres a year in - costs far more, because by then the application has grown around the limitations. Documentation is at postgresql.org and sqlite.org.
Where this fits in a full stack
The database layer covers managed Postgres providers, the SQLite platforms named above, and the document and key-value options this comparison deliberately skipped. For a service where the database is the product, the REST and backend API stack guide puts it in context.
Per-tenant SQLite is at its best in admin panels and back offices, which the internal tools stack guide covers. When you need the syntax rather than the decision, the SQL cheatsheet has it.