SQLite is all you need for durable workflows • Hacker News Board

Re: SQLite is all you need for durable workflows

#31

Post by fragmede » Fri, May 29, 2026, 7:04 PM UTC

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

So teach them. If you want to bring up computer science fundamentals, the question is where does SQLite sit with regards to the CAP theorem. Consistency, Availability, and Partition tolerance. SQLite isn't a distributed system, so there are no partitions to tolerate, so it's a CA system. Other databases make different tradeoffs. For systems that don't need concurrent writes, SQLite is pretty great! There are no users to manage, no permissions, no daemon to run, no server and port to mix up. Just open a file on disk using a library.

Re: SQLite is all you need for durable workflows

#32

Post by sevenzero » Fri, May 29, 2026, 7:05 PM UTC

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

Isn't concurrency also limited by your machines disk speed for writes, what difference does it make if you write sequentially vs concurrently? Why does concurrency even matter for databases?

Re: SQLite is all you need for durable workflows

#33

Post by refulgentis » Fri, May 29, 2026, 7:06 PM UTC

fragmede wrote: post #31

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

So teach them. If you want to bring up computer science fundamentals, the question is where does SQLite sit with regards to the CAP theorem. Consistency, Availability, and Partition tolerance. SQLite isn't a distributed system, so there are no partitions to tolerate, so it's a CA system. Other databases make different tradeoffs. For systems that don't need concurrent writes , SQLite is pretty great! There are no user…

Strawman, no? "run an Obelisk server with a SQLite database", now we're distributed.

SQLite is a nice local store. It's this server stuff that I don’t grok, well, yet. :)

Re: SQLite is all you need for durable workflows

#34

Post by 0x59 » Fri, May 29, 2026, 7:07 PM UTC

Big complex data model with ambiguous query patterns? Postgres

Small, well defined, data model with known query patterns? Bespoke model

There probably is a place for sqlite and my project space so far hasn't yet well-aligned with it.

Re: SQLite is all you need for durable workflows

#35

Post by yokoprime » Fri, May 29, 2026, 7:08 PM UTC

If you're just doing workflows from a single node, i guess it can be ok as long as theres a single writer. But scaling across multiple servers it clearly is not all you need.

Re: SQLite is all you need for durable workflows

#36

Post by golem14 » Fri, May 29, 2026, 7:09 PM UTC

Litestream releases 5.9 and newer have a bug that causes instances to sync an insane amount of data. a DB with <10K of data in it and practically no writes/reads causes something like 10GB of daily replication traffic. For my toy project that got needlessly expensive.

Re: SQLite is all you need for durable workflows

#37

Post by refulgentis » Fri, May 29, 2026, 7:10 PM UTC

sevenzero wrote: post #32

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

Isn't concurrency also limited by your machines disk speed for writes, what difference does it make if you write sequentially vs concurrently? Why does concurrency even matter for databases?

> Isn't concurrency also limited by your machines disk speed for writes

Yes, in theory: given a large enough database, and a disk that can only do one operation at a time, and a large enough operation that touches enough of the database. In practice, in a SQLite single tenant scenario? No, not at all.

> what difference does it make if you write sequentially vs concurrently. Why does concurrency even matter for databases?

As soon as your codebase involves reacting to events independently of a user taking action it becomes a practical concern. Generally, this is a broad question and has 1,000,000 answers.

EDIT: Originally I had "I think you understand generally, no?" appended but realized that's not helpful at all, if you did, you wouldn't be asking.

Something that may help is imagining what'd happen if a DB wasn't thread safe / didn't allow multiple writers. Ex. in SQLite's case, it allows multiple write operations to take place but there's a one-at-a-time queue. If we didn't have databases that were able to execute multiple writes simultaneously, you'd need a separate database for each concurrent writer you expect, and you'd effectively have a global lock. Orderly scaling would be ~impossible unless you did something crazy like have a single server per user

Re: SQLite is all you need for durable workflows

#38

Post by peterson_lock » Fri, May 29, 2026, 7:14 PM UTC

bitexploder wrote: post #7

I started setting up my workflows using Temporal. It deploys as relatively light weight local app. For an isolated local installation it uses SQLite. It makes the process of dealing with API retries and organizing workflows and tasks really simple. I recommend giving it a try. It is, philosophically, exactly what this article is suggesting, but it adds an incredibly rich and flexible interface for agents to work with…

This reads like an advertisement for Temporal :)

Re: SQLite is all you need for durable workflows

#39

Post by dilyevsky » Fri, May 29, 2026, 7:14 PM UTC

orf wrote: post #3

> The caveat is that Litestream replication is asynchronous. A restore can miss the newest local writes if the SQLite volume disappears before they are copied. That is fine for many AI and experimentation workflows In short: SQLite is not all you need, unless you’re just experimenting don’t actually care about durability, in which case you also need litestream + object storage. Right.

i mean it's durable as long as nothing crashes or litestream has a data corruption bug which only happens every other release...

Re: SQLite is all you need for durable workflows

#40

Post by andriy_koval » Fri, May 29, 2026, 7:15 PM UTC

onlyrealcuzzo wrote: post #26

shukantpal wrote: post #25

Earlier quoted context omitted.

Yes, agreed on SQLite/Postgres. But I'm going to benchmark RocksDB next and see what the performance characteristics are. I suspect the LSM tree storage engine of RocksDB might perform better since agents are so write heavy when running highly concurrent workloads. After all, you are streaming LLM tokens into disk and fanning them out to subscribed clients.

You might want to start here: https://docs.cozodb.org/en/latest/releases/v0.3.html

That project has 0 commits for 2 years.