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

Re: SQLite is all you need for durable workflows

#281

Post by bob1029 » Sat, May 30, 2026, 9:50 AM UTC

> This is especially attractive for AI agents and AI-generated workflows. Those systems are often bursty, experimental, and easier to reason about when each agent or tenant has a small self-contained unit of state.

I am finding that the most important thing is one big, consistent data warehouse that is updated with the state of the business as close to real time as we can get.

SQLite is not really great at this particular problem. Something like Postgres or SQL Server would be much more suitable for an OLAP data warehouse that can serve clients (AI agents) while simultaneously merging massive record sets from upstream business systems. These products also offer intricate permissions control. You can prove to an auditor that your AI solution will never see tables or rows it's not supposed to. SQLite doesn't even have a concept of a user, role or login.

> The compute can stay cheap and disposable.

Again, hosted sql is better aligned. The alternative is DIY hosted sql (SQLite + some other magic) which immediately violates this rule.

Re: SQLite is all you need for durable workflows

#282

Post by franga2000 » Sat, May 30, 2026, 9:52 AM UTC

HDThoreaun wrote: post #268

franga2000 wrote: post #239

Earlier quoted context omitted.

And I don't understand the obsession with server-based databases for single apps. Especially in containerised setups, every "app" gets its own database anyways, and if the app is further broken down into services, they usually communicate between each other and not with a shared database. So in those cases, what do you gain by pulling the database out of the "process" and onto the other end of a socket? In most cases…

Every container gets its own database?

Yes? Well, every "app", as I quite explicity wrote. Look up the docker compose file or helm chart for basically any app. I'm running dozens of apps, each with their own postgres, redis and nginx containers alongside the main application server. That's what the stack is designed for.

Re: SQLite is all you need for durable workflows

#283

Post by mnahkies » Sat, May 30, 2026, 9:55 AM UTC

kenforthewin wrote: post #184

svara wrote: post #113

Earlier quoted context omitted.

Word on HN is that you're either paying more money than you expected for temporal's managed solution or taking on substantial ops burden ultimately running their very heavy system yourself. I wouldn't know, I've not done either, but I'd like to learn more from your or other's experience.

Could you expand on the "substantial ops burden"? Let's say you're using a managed Postgres instance as the underlying data store, how substantial is the ops burden in that case? I understand that temporal is actually a set of 4 or so microservices on top of a data store, but if you're already running a distributed system backed by k8s or something like that, it doesn't seem like it adds significant incremental ops o…

In my experience with a relatively modest number of concurrent workflows (think hundreds) you'll be pushing several thousand transactions per second through that postgres instance.

As best I can tell it doesn't do any batching of it's writes/reads, and it's update heavy in places rather than append (I suspect their cloud version might do some of these things)

It's pretty close to "let's make every function call serialise it's parameters/return value, go through a postgres table and several network hops"

That said it can be very useful, but it's a heavy tool that's best suited for high value/risk workflows where you're earning enough from the execution that you can afford the overhead (for example an Uber trip with several dollars of service fees is probably a good fit, unsurprisingly since it's roots are from Uber)

Re: SQLite is all you need for durable workflows

#284

Post by password4321 » Sat, May 30, 2026, 9:58 AM UTC

recursive wrote: post #59

andriy_koval wrote: post #40

Earlier quoted context omitted.

That project has 0 commits for 2 years.

Sounds pretty stable

v0.7 with the following disclaimer:

> Versions before 1.0 do not promise syntax/API stability or storage compatibility.

Re: SQLite is all you need for durable workflows

#285

Post by bambax » Sat, May 30, 2026, 10:02 AM 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…

My current client has a forest of 90+ SnapLogic pipelines that were badly written and maintained even worse; one of those was completely wrong, in that it generated wrong accounting data which could eventually have financial, fiducial and legal repercussions.

I rewrote the pipeline in Python (a correct version of it) with state management in SQLite and logs in plain old flat files, and everything has been running smoothly ever since. In fact this is the only data flow that has worked without errors or interruptions in the last six months.

Instead of replicating the db file with Litestream I do a remote backup with Restic before and after each run; it's not an exact replacement of Litestream as we could possibly lose a whole run if the machine died / disappeared at the end of a run, but it lets one restore any day very easily. In an ideal world I think we should have both (live replica + backups).

Re: SQLite is all you need for durable workflows

#286

Post by bambax » Sat, May 30, 2026, 10:06 AM 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…

> SQLite is an embedded database

Yes, but that's not its main selling point. An SQLite database is also a single file, which makes it incredibly easy to replicate, backup, transfer, restore, etc.

Re: SQLite is all you need for durable workflows

#287

Post by password4321 » Sat, May 30, 2026, 10:07 AM 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

Thank you for sharing a benchmark pretty much exactly like the parent comment is planning to do.

Also thanks for the incidental exposure to a DB I'd never heard of before... with a browser-based demo CozoDB may be a good way to start experimenting with Datalog.

Re: SQLite is all you need for durable workflows

#288

Post by mxey » Sat, May 30, 2026, 10:13 AM UTC

bambax wrote: post #286

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…

> SQLite is an embedded database Yes, but that's not its main selling point. An SQLite database is also a single file, which makes it incredibly easy to replicate, backup, transfer, restore, etc.

SQLite in WAL mode which you want for server apps is multiple files.

Files which you cannot just copy while your application is running if you want a correct backup.

Re: SQLite is all you need for durable workflows

#289

Post by password4321 » Sat, May 30, 2026, 10:15 AM UTC

psanford wrote: post #202

I wrote a library[0] to let you concurrently update a sqlite db in s3 safely. It uses the little known sqlite sessions extension plus s3 compare-and-swap on a small metadata file to make this work reasonably efficiently and safely. I have been enjoying it for a bunch of small projects where I want a lambda function to have a db for state but I don't want to pay for a full database instance. [0]: https://github.com/ps…

This is exactly what I had dreamed of except without being aware of the session extension... I put it off because I thought I would have to create a layer opening sequential partial sqlite dbs and somehow filtering to the most recent version of each record in-between compactions.

It sounds like sqlite sessions handles the hardest part and you've sanded down some of the rough edges while implementing the glue bits for s3 (generously licensed MIT); thanks for the heads-up!

Re: SQLite is all you need for durable workflows

#290

Post by mxey » Sat, May 30, 2026, 10:21 AM UTC

franga2000 wrote: post #280

stingraycharles wrote: post #261

Earlier quoted context omitted.

“Especially in containerised setups, every "app" gets its own database anyways, and if the app is further broken down into services, they usually communicate between each other and not with a shared database. “ You seem to be talking about a vastly different use case. Containerized apps having their own database? What? Aren’t these types of containers stateless? I always very much try to keep state out of app contain…

If an app needs a database, it gets a database server container, instead of getting a user and database on a shared database server as things used to be done. Every little django app has its own postgres container. Every wordpress site gets its own mysql container. That is the modern way. Those database containers get a PVC/volume/mount for their data dirs. The only thing ever connecting to them is their "owner" appl…

Yes if you run a database server like an embedded application database, then it won’t be very different from an embedded application database.