SQLite is all you need for durable workflows

Original Article ↗ Hacker News Discussion ↗

Advocates of SQLite argue that a single-file, embedded database plus tools like Litestream can provide durable, inspectable workflow state for many applications, especially AI agents and small- to medium-scale services, without the operational overhead of Postgres or specialized workflow engines. Critics counter that SQLite’s weak type system, single-writer limits, and lack of built‑in high availability or access control make it a poor fit for multi-user, highly concurrent, or regulated environments, where networked databases and systems like Temporal shine. Overall, the exchange centers on when the simplicity and performance of “just SQLite” are sufficient versus when it’s wiser to invest early in heavier infrastructure that better handles scale, replication, and complex reliability needs.

Durability & Replication

  • Core claim: SQLite + Litestream + object storage can give “durable workflows,” but many argue Litestream’s async replication means recent writes can be lost on sudden node failure.
  • Some report serious Litestream issues (runaway disk/replication usage, bugs in recent releases) and prefer simpler approaches (rsync, restic, nightly backups) if they truly care about disaster recovery.
  • Others note that Postgres also needs explicit configuration for strict durability and replication; distributed durability always requires some external target (another DB node, object store, etc.).
  • Concern: SQLite on networked storage (NFS, Longhorn) is called out as a corruption risk per SQLite’s own docs.

Performance, Concurrency & Scaling

  • Many highlight SQLite’s excellent single-node performance: low latency (in-process, no network), thousands of TPS, and ability to serve surprisingly high loads if write contention is modest.
  • Critics argue SQLite’s single-writer model makes it “unsuitable” for multi-user, high-concurrency web apps; defenders counter that most apps never reach that scale, and sharding by user/tenant or per-device DBs sidestep the issue.
  • There is debate over whether it’s wiser to start with Postgres for HA/DR and multi-writer concurrency versus YAGNI: start with SQLite and only migrate if needed.
  • Multiple examples of entire services and even 7‑figure MAU products running on SQLite or “SQLite-like” durable objects.

SQLite vs Other Storage Options

  • Comparisons:
    • Postgres/MySQL: better for multi-process, multi-node concurrency, rich features (roles, permissions), and OLAP-style warehouses serving many clients concurrently.
    • DuckDB: praised for analytics over files (JSON/CSV), good SQL over columnar storage; but uses more RAM with large row counts.
    • S3 and “logs/WAL”: mentioned as underlying durability mechanism; some see SQLite as “competing with fopen()” or as an advanced file format.

Workflows, Agents & Orchestration

  • Several people independently use SQLite as the backing store for AI agents and DAG-like workflows: each step logs state to a DB, making retries, tweaks, and inspection easy; querying rows saves tokens vs parsing large JSON/markdown.
  • Temporal is frequently cited as a richer alternative for durable workflows (retries, long-running tasks, observability), but experiences diverge:
    • Fans: it encodes good distributed-systems practices and dramatically improves reliability.
    • Skeptics: heavy ops burden, performance overhead, determinism/upgrade complexity; workable locally/on small scales, painful at large scale unless using the managed service.
  • Other workflow engines (DBOS, Conductor, Unmeshed, Oban, etc.) are mentioned as alternatives to “roll your own with SQLite.”

Schema, Types & Tooling

  • SQLite’s weak type system and awkward date/time handling frustrate some, especially coming from Postgres.
  • Mitigations: strict tables, CHECK constraints, and PRAGMAs help enforce types and durability, but ergonomics are seen as inferior.
  • Lack of rich ALTER TABLE operations is another annoyance, though recent SQLite versions are improving this.
  • Despite shortcomings, many value SQLite’s simplicity: a database as (mostly) a single file that’s easy to copy, back up, and reason about.