Distributed SQLite: Paradigm shift or hype?

Original Article ↗ Hacker News Discussion ↗

Role of Distributed SQLite vs PostgreSQL

  • Many see this as a continuum, not SQLite vs Postgres.
  • SQLite is praised for simplicity, in-process speed, and being “good enough” for most apps; Postgres is seen as the richer, more general backend with a large feature ecosystem.
  • Some argue most apps never need to scale beyond SQLite, especially if an ORM makes later migration to Postgres feasible. Others counter that serious data workloads lean heavily on Postgres features and extensions.

LiteFS / Distributed SQLite in Practice

  • LiteFS is positioned as a “next step” after single-node SQLite replication tools, mainly for read-heavy apps needing horizontal scale.
  • Main benefit: removing network hops between app and DB, especially valuable at the edge and in multi-region setups.
  • Reported limitation: ~100 writes/sec per node (in one implementation) makes it better for read-heavy workloads than write-heavy systems like some CRMs.

Latency, Loading States, and Query Patterns

  • Moving the DB onto the app server (or edge) reduces DB latency but does not remove frontend–backend network latency; loading states in UIs are still needed.
  • Suggested pattern: issue a single backend request that runs many SQL queries at once (N+1 becomes cheap with SQLite), then return one JSON blob.
  • GraphQL is seen as a strong fit with SQLite when many small queries per request are common.

Concurrency, Writes, and Consistency

  • SQLite’s single-writer model is widely discussed. WAL/WAL2 and BEGIN CONCURRENT can reduce contention but push more conflict resolution into the application.
  • Some use CDC-based replication tools (e.g., over NATS) and accept eventual consistency; schema-change replication is a pain point.
  • For background jobs, single-writer constraints complicate multi-process workers unless everything is funneled through a single writer or an HTTP/daemon layer.

Features, SQL Differences, and Tooling

  • SQLite’s more limited built-ins (types, aggregates, date handling) push more logic into application code; this suits ORMs but frustrates those used to rich Postgres features.
  • Date/time handling via strings and strftime is seen as low-level and verbose.
  • Schema evolution quirks (e.g., column reordering) are acknowledged but have tooling/workarounds.

Partitioning and Local‑First

  • Per-tenant or per-user SQLite databases are being used successfully; benefits include reduced contention and easy export of user data, but cross-tenant analytics get harder.
  • Several commenters see the real “paradigm shift” not at the edge but on user devices: local-first apps using SQLite replicas plus CRDT-based sync to central servers.