> 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.
Haha, I just started doing this on my own. Found it helps the agents preserve state better. I typically ask them to design a DAG first based on a set of specifications and then execute it (each step stores something in a SQLite DB). Iteration is pretty simple then because I just ask for a tweak to one or two steps of the DAG, and then to re-run.
Funny how people are independently converging on similar patterns of "what works" here. Still feels like we're in the wild west with all these ad-hoc patterns of agent orchestration that people are coming up with.
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. Additionally, the web UI makes it very easy to inspect workflows, review agent execution, etc. Temporal also encodes much higher reliability into your system, almost for free. Distributed and reliable systems are hard, don't reinvent the wheel IMO.
If you find yourself wanting things like an easy way to then introspect your SQLite database, figure out what is happening in the workflow, compose individual tasks, make workflows trivially callable, etc, give Temporal a look.
Alongside this, I have mostly moved away from files for agents. Markdown and JSON are great, but also feel like traps when building out smaller local apps. LLMs are great at SQLite and you can render anything you want out of it (Markdown, JSON, etc). It saves a lot of tokens when an agent can just query a specific row instead of having to fire up jq or grep through markdown. You get a nice portable self contained data management system that encourages agents to be more disciplined about how they structure their data than a bunch of files. It also continues to scale into MySQL/Postgres if your little local projects start to outgrow or become more formal, you already have schema and discipline around data.
It sounds like you’re running this mostly on a single machine?
Temporal gets much more complex with scale. Cassandra isn’t fun to manage. Ringpop and TChannel are hard to debug when things go wrong. The SQL backend support doesn’t support horizontally scaled replicas (just single instance) due to consistency requirements. Depending on how your code is written, modifying code baked into workflows becomes complex, as anything that modifies the history event ordering breaks determinism in already-deployed workers.
We use it heavily and everyone who started on it doing simple scripting/automation all love it, everyone who built real production systems on top of it all hate it. Possibly operator error, but my experience hasn’t matched the rosy picture painted in these comments.
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).
> It saves a lot of tokens when an agent can just query a specific row instead of having to fire up jq or grep through markdown
Just wanted to make sure no one missed this point in your comment because eventually users will be paying the full cost for tokens instead of VC's paying, with GitHub Copilot's pricing realignment leading the way.
What's wrong with looking up the algorithm called "work stealing" and just implementing it? Making web UIs for a working algorithm is something AI is really, really good at (and I'll actually trust it with that)
Meta comment:
This is a domain under my countries TLD (Slovakia) and it is one of the handful of words that are a word with the TLD in my language (and coincidentally) also in English. Every now and then, I will check on the domains with a retrograde dictionary for domains that have this property and root of this particular domain had a roundcube email server on it (can be checked on archive.org). After further checking, the local company actually named themselves Obeli s.r.o. (s.r.o. is Ltd), presumably so that they could use a domain that is a real word when said together with the TLD. (EDIT:) Forgot to write the thing I wanted to mention in the first place: it appears the domain must have lapsed and/or the author bought it from the company that was using it.
Another fascinating fact: our countries TLD has been stolen Ocean's 11 style (I am not kidding). After Czechoslovakia split into Czech Republic and Slovak Republic, the newly created Slovak .sk TLD has been under the care of people from the local university. The university also had some offices that they were leasing out. Someone had leased this office space (EDIT: this is important as this means they had the same physical address), created a company that had the same name as the NGO that was taking care of the domain, so e.g. the NGO was named "My Company o.z." and the perpetrator created a "My Company s.r.o." (our countries version of the american Ltd). This person then wrote to ICANN to change the address to the "My Company s.r.o." presumably under the pretense that this was just an administrative error and from this point, they have functionally taken custody of the TLD. I was not able to find how they did it technically, but I presume they persuaded ICANN to then point to their servers instead of the real ones.
After this happened, it seems that no one noticed for some time. When they noticed, they tried taking it back, but they weren't able to. For some inexplicable reason, the government during that time (Šuster era, early 2000s) gave the new company a contract that was functionally uncancellable from the government side. Later governments made this even more uncancellable and in 2017, then Minister of IT (and as of this day president!) Pellegrini made the contract literally uncancellable.
As a result of this, we have one of the most expensive domains around (18e/year, rising each year for no good reason). (EDIT:) The company running our countries TLD is now a foreign entity that the whole thing has been sold to (multiple owners over time) and we as a country have no control over if I understand it correctly.
I might have gotten some details wrong as I am writing this from my memory of researching it a couple of years back, but you get the idea, crazy stuff. Here is an article in Czech [0] that tells the story a bit better, but you have to translate it.
// EDIT:
I have found that the article actually links the movement to return the TLD back [1]. It also has a story tab [2], so they have something much more precise than the paraphrasing I wrote.
SQLite is surprisingly performant for single node applications even when comparing to Postgres. Postgres consumes a lot more memory and requires IO to hop through IPC whereas you can keep everything in process in SQLite with a shared connection pool.
I've been testing different storage engines for my agent harness and I can get up to 7.5k concurrent sessions on a single vCPU with SQLite whereas Postgres crashes or runs out connections.
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 "SQLite for everything" crowd is a little bit inexperienced.
there is a difference between concurrency in a distributed environment and concurrency on a single machine across processes. SQLite is incredibly useful for the latter.
For me, I have a use case that needs to support a few thousand users, probably a few hundred concurrently.
The combination of SQLite (libsql, a concurrent implementation of sqlite) and Rust means I can do so from a $2/m VPS and a single server instance.
Backups are done via a cron job that uploads to S3.
Does it pass the "Netflix scale" test? No
But it doesn't need to. I'm not profiting from the service and SQLite offers a path to scale if/when ready because... well it's just SQL and I can literally just swap `libsql::Connection` with `psql::Connection` in my repositories.
Plus upgrading from a $2/m VPS to a $10/month VPS quadripples the number of concurrent users I can support.
IMO, you can vertically scale extraordinary far with SQlite and an efficient server implementation.
I'd wager that 90% of forum websites, wordpress sites and online shops would be fine with SQLite.
There is something appealing about "it's just a file" (it really isn't; it has locks and a WAL), but I agree with you.
I think people are afraid to read the documentation for postgres. You can start it up in milliseconds. Fast enough and light enough to run one copy for every test case in your test suite, or whatever you're using it for. (mkdir /tmp/whatever; initdb -D /tmp/whatever --no-instructions -A reject -c listen_addresses= --auth-local=trust --no-sync -c fsync=off -c unix_socket_directories=/tmp/whatever -U postgres --no-locale; postgres -D /tmp/whatever) Now you have a test database that behaves exactly like production because it's exactly like production. (OK, turning fsync off makes it a lot faster than production, so be careful.)
Personally I like Postgres for this reason too. Its extremely easy to run with Docker, I can dump data from all kinds of apps in there and I know it's not going to take any rearchitecting as soon as I need multiple concurrent writers.
I think docker is still super underappreciated so setting up any kind of server is seen as a chore. In my eyes it makes running tons of services like this very easy, so ill take the extra functionality, extensibility etc of postgres.
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, absolutely nothing. So why bother?
Don't get me wrong, I've worked with plenty of server-based databases, including proper dedicated database servers. It's great tech and often the best tool for the job. But not always and I'd argue not in the majority of uses.
is just wrong, and I don't think that the SQLite fans are that crowd. Taking a database server for everything is probably possible, but often unnecessary. With experience, one can properly judge when SQLite is sufficient and when it is not.
So arguing that the SQLite crowd is inexperienced feels weird, because inexperienced people have a much harder time judging when to use what and can just use the database server all the time (even when it is overkill).
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.
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.
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.
I've been following litestream for a while, and it seems like the project has been hijacked by a vibe coder. I wouldn't trust it for critical tasks anymore.
Yep. But we all know that one machine can and will fail (or be patched and restarted), so the log needs to be distributed.
Different workflows should probably go in different buckets or "topics" for clarity. Since it's distributed, the system must guarantee that the log items are stored in the same ordering ("offsets") among the nodes.
Pardon my ignorance trying to follow up on what is most likely sarcasm but is this not Kafka's claim to fame?
I am joining a new project and need to know to what extent Kafka is still a part of the future for new big data projects. It doesn't seem like there are alternatives at the high end but instead the question is when other technologies (that are easier to manage, require less compute, etc.) max out.
There's a wide gap from files to multipartition databases.
Running databases in a container is not for me sorry whenever real production stuff is on the table.
Personally, lots of ETL can just be taken care of locally without involving enterprise databases. In such cases, DuckDB is 5x-10x better than SQLite and orders of magnitude simpler/faster than spinning up a dedicated Postgres database.
For general scripting, there's no match between a 20-lines awk script and a much cleaner, robust, maintainable equivalent SQL script based on DuckDB.
I just hope MotherDuck don't need to pump/dump for IPO - it would be sad losing that tool for the usual corporate greed.
I use DuckDB and like it. Since many mentioned GB level json in this post, so they have large amount of data. Been column based, DuckDB uses more RAM as row count grows. It can be an advantage or disadvantage depends whether memory is constrained. Traditional row based DB such as SQLite can deal with large database with less memory.
Instead of "just use Litestream," I'd like to see a review of different object stores one could use and which ones work well with Litestream. Is there a nice object store I could run in another Linux VM? As a hobbyist, which services providing an S3-like API make the most sense?
103 comments
[ 1.9 ms ] story [ 82.8 ms ] threadhttps://xkcd.com/378/
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.
Funny how people are independently converging on similar patterns of "what works" here. Still feels like we're in the wild west with all these ad-hoc patterns of agent orchestration that people are coming up with.
Obligatory list of workflow engines and libraries because it's such a common need that a lot have rolled their own. [1]
[0] https://docs.dbos.dev/python/tutorials/database-connection
[1] https://github.com/meirwah/awesome-workflow-engines
If you find yourself wanting things like an easy way to then introspect your SQLite database, figure out what is happening in the workflow, compose individual tasks, make workflows trivially callable, etc, give Temporal a look.
Alongside this, I have mostly moved away from files for agents. Markdown and JSON are great, but also feel like traps when building out smaller local apps. LLMs are great at SQLite and you can render anything you want out of it (Markdown, JSON, etc). It saves a lot of tokens when an agent can just query a specific row instead of having to fire up jq or grep through markdown. You get a nice portable self contained data management system that encourages agents to be more disciplined about how they structure their data than a bunch of files. It also continues to scale into MySQL/Postgres if your little local projects start to outgrow or become more formal, you already have schema and discipline around data.
We use it heavily and everyone who started on it doing simple scripting/automation all love it, everyone who built real production systems on top of it all hate it. Possibly operator error, but my experience hasn’t matched the rosy picture painted in these comments.
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).
Just wanted to make sure no one missed this point in your comment because eventually users will be paying the full cost for tokens instead of VC's paying, with GitHub Copilot's pricing realignment leading the way.
Another fascinating fact: our countries TLD has been stolen Ocean's 11 style (I am not kidding). After Czechoslovakia split into Czech Republic and Slovak Republic, the newly created Slovak .sk TLD has been under the care of people from the local university. The university also had some offices that they were leasing out. Someone had leased this office space (EDIT: this is important as this means they had the same physical address), created a company that had the same name as the NGO that was taking care of the domain, so e.g. the NGO was named "My Company o.z." and the perpetrator created a "My Company s.r.o." (our countries version of the american Ltd). This person then wrote to ICANN to change the address to the "My Company s.r.o." presumably under the pretense that this was just an administrative error and from this point, they have functionally taken custody of the TLD. I was not able to find how they did it technically, but I presume they persuaded ICANN to then point to their servers instead of the real ones. After this happened, it seems that no one noticed for some time. When they noticed, they tried taking it back, but they weren't able to. For some inexplicable reason, the government during that time (Šuster era, early 2000s) gave the new company a contract that was functionally uncancellable from the government side. Later governments made this even more uncancellable and in 2017, then Minister of IT (and as of this day president!) Pellegrini made the contract literally uncancellable. As a result of this, we have one of the most expensive domains around (18e/year, rising each year for no good reason). (EDIT:) The company running our countries TLD is now a foreign entity that the whole thing has been sold to (multiple owners over time) and we as a country have no control over if I understand it correctly.
I might have gotten some details wrong as I am writing this from my memory of researching it a couple of years back, but you get the idea, crazy stuff. Here is an article in Czech [0] that tells the story a bit better, but you have to translate it.
[0] https://www.root.cz/clanky/pribeh-domeny-sk-aneb-kradez-za-b...
// EDIT: I have found that the article actually links the movement to return the TLD back [1]. It also has a story tab [2], so they have something much more precise than the paraphrasing I wrote.
[1] https://www.nasadomena.sk/
[2] https://www.nasadomena.sk/historia/
I've been testing different storage engines for my agent harness and I can get up to 7.5k concurrent sessions on a single vCPU with SQLite whereas Postgres crashes or runs out connections.
[0] https://github.com/impalasys/talon/pull/23#issuecomment-4577...
This is a foundational principle of computer science. It seems to me that the "SQLite for everything" crowd is a little bit inexperienced.
you seem like the inexperienced one to me..
The combination of SQLite (libsql, a concurrent implementation of sqlite) and Rust means I can do so from a $2/m VPS and a single server instance.
Backups are done via a cron job that uploads to S3.
Does it pass the "Netflix scale" test? No
But it doesn't need to. I'm not profiting from the service and SQLite offers a path to scale if/when ready because... well it's just SQL and I can literally just swap `libsql::Connection` with `psql::Connection` in my repositories.
Plus upgrading from a $2/m VPS to a $10/month VPS quadripples the number of concurrent users I can support.
IMO, you can vertically scale extraordinary far with SQlite and an efficient server implementation.
I'd wager that 90% of forum websites, wordpress sites and online shops would be fine with SQLite.
I think people are afraid to read the documentation for postgres. You can start it up in milliseconds. Fast enough and light enough to run one copy for every test case in your test suite, or whatever you're using it for. (mkdir /tmp/whatever; initdb -D /tmp/whatever --no-instructions -A reject -c listen_addresses= --auth-local=trust --no-sync -c fsync=off -c unix_socket_directories=/tmp/whatever -U postgres --no-locale; postgres -D /tmp/whatever) Now you have a test database that behaves exactly like production because it's exactly like production. (OK, turning fsync off makes it a lot faster than production, so be careful.)
I think docker is still super underappreciated so setting up any kind of server is seen as a chore. In my eyes it makes running tons of services like this very easy, so ill take the extra functionality, extensibility etc of postgres.
Don't get me wrong, I've worked with plenty of server-based databases, including proper dedicated database servers. It's great tech and often the best tool for the job. But not always and I'd argue not in the majority of uses.
is just wrong, and I don't think that the SQLite fans are that crowd. Taking a database server for everything is probably possible, but often unnecessary. With experience, one can properly judge when SQLite is sufficient and when it is not.
So arguing that the SQLite crowd is inexperienced feels weird, because inexperienced people have a much harder time judging when to use what and can just use the database server all the time (even when it is overkill).
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 does not compete with client/server databases. SQLite competes with fopen().
"Just use postgres" "Just use sqlite" "Juse use a monolith" "Just use sftp" "Just use an ec2 instance"
Usually these people have flunked out of the school of (distributed system) hard knocks. They couldn't hack it and are retreating to familiar.
The funny part is when one of those people fluke themselves into senior management when their saas takes off.
Inevitably they have to suck it up and hire experts in the same technologies that "no one needs".
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.
Building durable workflows on Postgres
https://news.ycombinator.com/item?id=48313530
One reason why a "logs are all you need" solution may fail: untrusted-log-as-injection[1].
Check those SBOM, and don't forget to include their CICD pipelines[2].
[1] https://news.ycombinator.com/item?id=48315440
[2] https://github.com/jqwik-team/jqwik/issues/708#issuecomment-...
Different workflows should probably go in different buckets or "topics" for clarity. Since it's distributed, the system must guarantee that the log items are stored in the same ordering ("offsets") among the nodes.
Not a bad way to do things.
I am joining a new project and need to know to what extent Kafka is still a part of the future for new big data projects. It doesn't seem like there are alternatives at the high end but instead the question is when other technologies (that are easier to manage, require less compute, etc.) max out.
Personally, lots of ETL can just be taken care of locally without involving enterprise databases. In such cases, DuckDB is 5x-10x better than SQLite and orders of magnitude simpler/faster than spinning up a dedicated Postgres database.
For general scripting, there's no match between a 20-lines awk script and a much cleaner, robust, maintainable equivalent SQL script based on DuckDB.
I just hope MotherDuck don't need to pump/dump for IPO - it would be sad losing that tool for the usual corporate greed.
[1]. https://turbopuffer.com/