Why Dropping SQLite May Be Wrong – Do You Actually Need PostgreSQL for Write Load?

Why historic reasons for abandoning SQLite are fading

SQLite was originally designed for 2000‑era hardware, leading to criticisms about single‑writer concurrency, lack of network access, no user management, limited ALTER TABLE support, and no replication. Since 2024 SSD and NVMe storage have changed the I/O cost model. Enabling WAL mode on modern NVMe drives yields 10 k–50 k writes per second on a single node, comparable to a tuned PostgreSQL instance, and reduces p99 write latency by 30‑60%.

Ecosystem changes

LibSQL and Turso – network‑enabled SQLite

LibSQL is an open‑source fork of SQLite maintained by the Turso team. It adds HTTP/WebSocket server mode, remote replica sync, multi‑writer support via MVCC, native vector search for AI/RAG workloads, and richer ALTER TABLE functionality. Turso builds a fully managed cloud service on LibSQL that offers unlimited databases with a free tier of 9 GB storage and 5 × 10⁸ rows read per month. This enables a “one‑file‑per‑tenant” model: each tenant or AI agent owns an isolated SQLite file, reads locally with micro‑second latency, and writes are synchronized to a remote primary without connection‑pool overhead.

Litestream – server‑less continuous backup

Litestream runs as a sidecar, tails SQLite’s WAL, and streams changes to S3‑compatible object storage, providing point‑in‑time recovery, low RPO, and multi‑environment replication without a separate database server.

# litestream.yml — continuous WAL streaming to S3
dbs:
  - path: /data/app.db
    replicas:
      - url: s3://your-bucket/app.db

Cloudflare D1 – edge‑node SQLite

Launched in April 2024, D1 embeds SQLite in Cloudflare Workers and automatically replicates reads across global edge nodes. Benchmarks on a 500 k‑row table show D1 query performance 3.2× faster than mainstream serverless PostgreSQL services.

JVM integration

The xerial sqlite-jdbc driver (3.50.x, released Nov 2025) provides a standard JDBC interface. Hibernate 6 includes a SQLite dialect, enabling Spring Boot + JPA integration.

# application.properties — Spring Boot + SQLite + Hibernate 6
spring.datasource.url=jdbc:sqlite:./data/app.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.mode=always
# Enable WAL mode for better concurrent read performance
spring.datasource.hikari.connection-init-sql=PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;

Maven dependency for the driver and Hibernate dialect:

<dependency>
  <groupId>org.xerial</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>3.50.3.0</version>
</dependency>
<dependency>
  <groupId>org.hibernate.orm</groupId>
  <artifactId>hibernate-community-dialects</artifactId>
</dependency>

Because SQLite runs in‑process, there is no socket or separate authentication layer, but developers must configure the connection pool to avoid SQLITE_BUSY errors (e.g., set PRAGMA busy_timeout=5000).

Multi‑tenant architecture shift

SQLite’s single‑file model allows a “one‑database‑per‑tenant” architecture. Each tenant’s file can be backed up, migrated, or deleted independently, simplifying GDPR‑compliant deletion and reducing operational complexity compared with PostgreSQL’s shared‑schema or row‑level isolation.

Practical selection guide

Read‑intensive single‑region web app – SQLite is very suitable; PostgreSQL can be used but reads are slower.

Edge / global low‑latency reads – SQLite (D1, Turso) is ideal; PostgreSQL suffers cross‑region latency.

Multi‑tenant SaaS (one DB per tenant) – SQLite fits natively; PostgreSQL requires complex schema isolation.

High‑write concurrency (>5 k/s, multi‑process) – SQLite’s single‑writer becomes a bottleneck; PostgreSQL’s row‑level locking scales.

Spring Boot / JVM embedded tools – SQLite runs in‑process with no server; PostgreSQL requires a separate server.

Complex analytics / window functions – SQLite supports them but the optimizer is limited; PostgreSQL offers a top‑tier optimizer.

Full‑text search – SQLite provides built‑in FTS5 (basic); PostgreSQL can use pg_trgm or other extensions.

AI / vector search (RAG) – SQLite can use the sqlite-vec extension or LibSQL native support; PostgreSQL uses pgvector (more mature).

Backup / simple disaster recovery – SQLite + Litestream gives single‑file backup; PostgreSQL requires pg_dump / WAL archiving.

Zero‑ops development (no infra) – SQLite needs no server or Docker; PostgreSQL requires a running instance.

Performance tuning

Apply the following PRAGMA settings on each new connection (e.g., via HikariCP connectionInitSql) to adapt SQLite to modern SSDs:

PRAGMA journal_mode=WAL;          -- concurrent reads, single writer
PRAGMA synchronous=NORMAL;        -- safe on SSD, fsync only at WAL checkpoints
PRAGMA busy_timeout=5000;        -- wait up to 5 s before SQLITE_BUSY
PRAGMA cache_size=-64000;        -- 64 MiB page cache
PRAGMA foreign_keys=ON;          -- enforce foreign‑key constraints

These settings are widely used in Ruby on Rails, Django, and Go communities and provide a documented baseline.

Additional ecosystem tools

Sqlite‑VEC – lightweight vector similarity extension (github.com/asg017/sqlite-vec)

LiteFS (Fly.io) – FUSE‑based replication for low‑write workloads (github.com/superfly/litefs)

Turso (Rust rewrite, project Limbo) – MVCC, async‑first architecture, beta stage (github.com/tursodatabase/turso)

rqlite / dqlite – Raft‑based multi‑master distributed SQLite for when single‑node limits are hit (github.com/rqlite/rqlite)

When to abandon SQLite

Consider PostgreSQL if any of the following apply:

Multiple processes with high write concurrency causing frequent SQLITE_BUSY errors.

Need for fine‑grained row‑ or table‑level locking.

Complex analytical queries that benefit from a cost‑based optimizer.

Dependence on advanced PostgreSQL extensions such as PostGIS or TimescaleDB.

Key takeaways

Modern SSDs and WAL mode give SQLite read latency 100‑1000× lower than network databases and write throughput comparable to a co‑located PostgreSQL.

LibSQL, Turso, Litestream, Cloudflare D1, LiteFS, and related tools systematically close SQLite’s historic gaps without modifying the core engine.

The “one‑file‑per‑tenant” model simplifies multi‑tenant SaaS and AI‑agent architectures.

In the JVM ecosystem, the xerial driver and Hibernate 6 dialect enable production‑grade SQLite with proper connection‑pool tuning.

Litestream turns SQLite’s single‑file nature into a production‑grade backup solution with real‑time S3 streaming.

For most read‑heavy web applications, the answer to “Do I really need PostgreSQL?” is often no.