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.
> an example of a case where you'd use SQLite instead of jq or grep through Markdown?
Usually we end up writing a script to incrementally refresh a data-set I'm analyzing (or have someone send me a copy after they pull it).
I've been using sqlite for anything which needs an UPDATE - modifying a row deep inside the data-set with jsonl is a pain.
My github is full of java programs which update sqlite3 files with threadpools and a single big lock around the UPDATE (& then I write or have an agent write code to analyze it).
DuckDB is slowly replacing it in the context of python, simply because of the ease of pushing a UDF into the SQL.
Also because I really like expressing things as LEAD/LAG with a UDF on top.
My favorite lens on SQLite is that it is actually two things:
1. A robust durability implementation
2. A library of high performance data structure and algorithms
The fact this it's SQL is nice, but those two attributes are what make it great.
For example, I'm implement an in-process event log that I want to be durable. I started simple, but soon saw some edge cases and instead of playing whackamole I just swapped to using sqlite as an ordered kv store that gives me ACID.
Another example: ingesting multiple inter related datasets. Instead of a dozen hash maps in memory, I load them up into sqlite (no persistence) and then slice and dice as I need to.
The moment my JSON has any sort of depth and I need to write a parser for it and potentially account for unspecified behavior. JSON's nice when it's nice, but it's terrible when it's terrible. It's 100x easier to write SQL than writing jq and... dear god if I have to use grep -A or -B, I'm doing something wrong. Constraints are actually a good thing!
The underlying database isn't the most important thing. Just use SQL. Its namespacing (eg, through CTEs) is good and you're more likely to have colleagues who know SQL compared to jq.
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.
I'm someone else who has inherited a bunch of ad-hoc orchestration systems and also used Temporal quite heavily. The latter does certainly come with some overhead (not so bad in the age of LLMs), but it also guides you along a well-trodden path of good practices. The latter being very important - it means that when you want to take on more advanced capabilities, you probably haven't painted yourself into a corner too badly and can take that on fairly easily. Think: retries, multi-tenancy, multi-lang, observability, etc.
I can vouch for them too, being a super early adopter. One of the best early bets I've ever made. Awesome OSS product, glad the team decided to leave Uber to commercialize it.
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.
You seem to have a rather limited understanding of what kinds of concurrency exist and how those needs are best met. Whether something is a server or not is not very relevant to this discussion.
SQLite is an excellent production db for many real world workloads, as has been widely documented. It is very different to Postgres, so requires learning a whole new skill set.
One way to think about it is that SQLite can work well for the parts of your system where there is naturally strong partitioning.
You can do big data in SQLite. Concurrent writes, sure, I'd recommend something else.
If you think the majority of systems require massively concurrent writes, I think you need to look a bit harder. SQLite is, after all, the most widely deployed database system, ever.
Thing is SQLite scales better than both those network databases [1] if you're prepared to stick with one big machine (+ a standby).
This is even more obvious when you start doing transactions processing an row locks across the network limit you to 1-3k TPS that you cannot scale out of (Pareto distribution is merciless).
Sqlite is good for lots of stuff, but you're probably focusing your days on high-scale webapps that want sharding with networked DBs. That's one domain, and an interesting one, but there are lots of others.
I'm a big fan of re-evaluating prior "best practices" in light of technology changes, especially in ways that improve simplicity. Running my family's social media site off a single sqlite DB on a VPS is great. ~15 users, almost zero maintenance. I run my FreshRSS instance off of sqlite, as well as my "now" page. At work, I used sqlite for all kinds of things over the past decades: as an ad hoc job queue, as a quick way to ingest and query lots of logs locally, and present/filter in realtime with simonw's excellent https://github.com/simonw/datasette.
I don't think it's every "sqlite for everything" as much as it is "sqlite in lots of places you probably didn't think to apply it."
I suspect being aware of all those little neat cases and wanting to leverage sqlite for them may be an indicator of experience, rather than the opposite.
There are many cases where SQLite + concurrent front end (like a go net/http server) can handle all the load that a service might ever conceivably have to handle, especially if allowed to scale up hardware over time. You can trivially scale up SQLite to, what, hundreds of thousands of tps?
The only thing you really give up is HA/failover and DR. But there are solutions to deal with those. And single-server systems are generally surprisingly robust (since, in the absence of very complex control planes, uptime goes down with more systems).
That’s why there are billions of SQLite databases right?
SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. SQLite is found in:
Every Android device
Every iPhone and iOS device
Every Mac
Every Windows 10/11 installation
Every Firefox, Chrome, and Safari web browser
Every instance of Skype
Every instance of iTunes
Every Dropbox client
Every TurboTax and QuickBooks
PHP and Python
Most television sets and set-top cable boxes
Most automotive multimedia systems
Countless millions of other applications
'production' doesn't equal 'multi-user concurrent access'. There are production uses where sqlite is a valid choice even if it may not be the best choice for multi-user production use cases.
Single-user, a single natural person, doesn't striclty mean single-accessor though. I don't think anyone here is suggesting that sqlite is a viable replacement a for any networked client/server postgresql system, but it is certainly capable of handling more than the most basic 1:1 tasks. Beyond that, the point is that you only need a file, so when you have natural data boundaries, a lot of problems decompose to that single user/single concern paradigm.
GP calls out concurrency as a weakness of SQLite. Most of the examples here don't experience the same load even a moderately sized web service experience day to day.
And no, being a part of the python standard library doesn't means it is being used by the average python user. These days I'd say at least half of them are just there for machine learning.
My read is that levkk is conflating concurrency with "real production apps" and this whole thread is starting to surface that "real production apps" and "high concurrency" are not measuring the same thing at all.
Sqlite is used in real production apps more than any other database.
Sqlite is also weak at any sort of write concurrency.
I had very good results giving 1 SQL DB per go routine, so the accesses were serialized up front, on a very high volume (130K requests/second) service. Exact transactionality was not a product goal, and the SQLite was just to backup the in memory state. If we lost a little due to abend or something, that was ok (although for normal maintenance it caught SIGTERM and stopped the listen and then waited for in flight calls and then flushed the remaining changes to SQLite; then on startup it would read the SQLite into memory to populate before taking the listen; persistent storage across container runs, and never both reads and writes to the same file at the same time. (It also just closed the DB and opened a new one when it hit some limit of rows, so as not to fill the disk; the max size of the SQLite corresponded to the max size of the LRU map being served from in memory; then it just flipped A / B between "a full memory worth of data stored" and "the currently updating state." A lot easier than having to write out proto bufs to disk or whatever I would have done for transient (during restarts/maintenance) persistence.
Woof. That sounds very complicated. If you need that kind of write concurrency, use an unlogged table in postgres [0]. Then you don't have to invent a whole sharded thing yourself.
It's almost as if Postgres isn't perfect, and one size shoe doesn't fit all.
Some people want some of the benefits you get from SQLite.
SQLite is obviously not perfect, but it's an incredible piece of software, and people regularly find good ways to make use of an excellent pieces of software.
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, what difference does it make if you write sequentially vs concurrently? Why does concurrency even matter for databases?
For a simplified example, having three processes reading blocks X, Y, Z in parallel is much faster than having a single process read block X, wait for the read to finish, read block Y, wait for the read to finish, read block Z and wait for the read to finish.
> 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
I guess I need to dive deeper into this as I do not understand the implications you gave me, but I appreciate the attempt. Generally I understand why concurrency is good in many cases, I just dont get why its important for database stuff too.
Edit: thanks for clarifying in the edit, makes a lot more sense.
Imagine if every tweet had to go through a one-at-a-time queue before being persisted. There's about 6000 tweets per second, so you would have to be able to save them at <0.17ms per tweet or else you would become backlogged. If you are getting backlogged, you have to buffer those incoming tweets somewhere until they can be writted, and eventually that buffer gets full and you start losing tweets.
Maybe that too is a native question, but there's a large scale between single user and 6000 tweets per second - most of our apps will never reach anything approaching even one save a second. So where to draw the line? I do far have gone the sqlite route for my hobby apps as it's so easy to handle and doesn't require setting up two docker containers for a single app. Am I drawing myself in a corner in case my apps ever do become relevant?
Or you can run postgres on the same machine as the application, which lets you much more easily migrate if the time comes when you need to scale to multiple application servers.
There's a world between "local file" and "network DB server", running a DB server locally has lots of benefits from being able to easily query from outside if needed to forcing you to consider concurrency without the latency overhead of a network hop.
This decision tree doesn't make much sense to me. Why you someone forego performance today in favor of adding a completely unnecessary network layer to every DB query in order to "satisfy" future imaginary "scaling concerns"?
I use postgres for very simple apps. I have a Dockerfile I use in my boilerplate repo. It takes a single make cmd for me to build, start and run migrations. Its as simple as using sqlite.
I mean - I agree for the typical multi-user, SaaS webapp. But I don't think that's what these folks are proposing. If they are - yeesh, count me out.
If on the other hand they're talking about single-user, software in the small - hell yeah. In fact, I'd also promote DuckDB in this regard (mostly for analytics) - with the power of a single machine these days, you can do a surprising amount and never have to worry about distribution. Unless you know you'll have to, in which case you're probably just digging yourself a hole?
The typical multi-user SaaS webapp doesn't have anywhere near enough users to overwhelm a single SQLite instance. Of the few that do succeed to the point where that's no longer true, a significant fraction can use techniques like sharding to stretch SQLite further.
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.
In the beginning apps and SQL were co-mingled. Oracle eventually came along and noticed that people wanted SQL on the network so that many different apps, running on different computers, could all access the same data. But then people realized that clients really want rich, 'tree'-like data, not simple rows and columns, so people started sticking networked databases in front of networked databases to serve as a transformation system. And now people are realizing that the second networked database layer is redundant and never used beyond what is required for the client-facing network database, so they are moving the storage back into the first network database layer, just like Oracle did all those years ago. What is old is new again.
What changed is SSDs. SSDs means that local access is faster than hitting the network. An expensive SAN stopped making sense because of this in specific cases. So for read heavy, or even read only database loads, you copy the SQLite file to the node that's processing the file, and just update that file whenever the data does get changed.
Sure, SQLite doesn't solve every problem -- but in many cases it solves the need at hand with the reward of one less piece of infra required to support it.
I see obsessions with tooling/solutions constantly from experienced devs who fall in love with the original solution and think it's the only way to do things -- so the experience part cuts both ways.
I absolutely 100% do not understand it either. At all. Every time I try to over the last year or two I come away with the conclusion its something that sounds cool (to me too!) but is guaranteed to cause more problems than more obvious solutions.
That being said I'd kill for someone who used it and benefited to explain it to me in a practical sense. (specifically where syncing is involved, and syncing a subset of the SQLite is necessary. If it's "just" a document store thats treated like a blob for syncing/backup, that's familiar. If it's all in one storage but only local, that's familiar.)
Re: TFA, I guess it would have helped if I knew what Obelisk was, which is on me, and a more in-depth explanation of how this ties into AI/agents, which is on the industry/writer.
I started using SQLite for a home project after years of reading about it, I was shocked at the poor type system coming from Postgres. It is really inferior, not sure why it gets so much praise.
Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so many people like it.
This is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection:
PRAGMA journal_mode = WAL
PRAGMA foreign_keys = ON
# Something non-null
PRAGMA busy_timeout = 1000
# This is fine for most applications, but see the manual
PRAGMA synchronous = NORMAL
# If you use it as a file format
PRAGMA trusted_schema = OFF
You might need additional options, depending on the binding. E.g. Python applications should not use the defaults of the sqlite3 module, which are simply wrong (with no alternative except out-of-stdlib bindings pre-3.12): https://docs.python.org/3/library/sqlite3.html#transaction-c...
It gets praise because of stuff other than the type system.
I agree it is disappointing, especially before strict tables.
You should check out DuckDB which is basically SQLite but with proper types. Although it is also OLAP (struct of arrays) rather than OLTP (array of structs) which may have worse performance for typical SQLite loads. In practice I doubt it matters if you have an application where you're considering either.
I went from using the various big player postgres clusters to SQLite, we have an MAU in 7 figures, all backed by SQLite durable objects. We have to think differently about the access patterns but the benefits have been worth it.
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.
Hello, DuckDB devrel here. First, thanks for the kind words :)
Second, it's funny you should mention the 20-line awk script. I was making a very similar argument yesterday at the Ubuntu Summit: at some point, using shell scripts with GNU coreutilus becomes impractical, while DuckDB SQL scripts scale better in terms of complexity and maintainability (and often also performance). My slides are here: https://blobs.duckdb.org/slides/duckdb-ubuntu-summit-2026.pd... (pages 32 to 36)
Third, MotherDuck develops a closed-source DBaaS on DuckDB. They build on DuckDB, and you connect to MotherDuck with DuckDB but they are a separate VC-funded company headquartered in Seattle. DuckDB is developed by DuckLabs, a bootstrapped (revenue-funded) company in Amsterdam. And the IP of the project is in a third organization: a Dutch non-profit called the DuckDB Foundation. For details, see https://duckdb.org/faq#how-are-duckdb-the-duckdb-foundation-...
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.
When used properly, SQLite is effectively an in-process method invoke. If the only remaining things in the way are your runtime, kernel, file system and a local NVMe storage device, you may find it massively outperforms hosted alternatives.
Leaving the current thread is where you lose the game in terms of latency. SQLite can work on timescales measured in microseconds if you don't force interthread communication.
Pedantically it's an in process virtual machine for operating on structured data. Which is precisely where it shows it's weakness, in my experience, when you end up with complicated table structures and complex join mechanics you then need to start thinking ahead of the query planner and VM code a bit in order to maintain reasonable performance.
There are more than a few unusual things worth knowing:
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.
> Postgres ... is the right choice when you need higher availability, broader shared scalability, or other deployment properties that are better served by a network database. It is also the better fit when asynchronous replication to object storage is not the durability model you want...
Many workflow systems do not need that on day one and should not start with more infrastructure than their state actually demands.
------
I see this kind of YAGNI thinking a lot, but in my view, it must be balanced against the effort you'd put into resolving any edge cases and adapting current architecture to your use case.
Imagine you deploy Sqlite, and thought it fine by itself, you keep running into some unforeseen challenges with the use to which you are putting. YOu'd need to sink valuable time and effort into addressing those. Then, when you have outgrown it, you'd beed to spend additional valuable times dping the same with Postgres.
This is why, when it comes to Architecture, I increasingly find my myself over-enigneering a bit. Assuming there is a good chance you might need to upgrade your architecture in the not too distant future, that approach is actually kind of very efficient. I find that I am able to uncover a lot of potential gotchas, which feeds back into the what the simplified current architecture should be, and helps me understand the roadmap I'm facing very well. I also avoid wasting too much time going too deep in directions that make sense now, but need a lot of plumbing to get right, when I can see that I'd likely have to throw it all out in a few years. Going from A -> B -C -> D, where each step is the optimal good-enough-for-now architecture but which requires a lot of work to stabilize and iron out the kinks of, is much less efficient than exploring D well enough to know whether you should build A, B, or C now.
Basically, some over-engineering, if done right, is not wasted. It cuts right to the heart of what you are dealing with, efficiently, and allows you to make (maybe) simpler but informed choices now as to how best to allocate your development resources now.
Are logs all you need for durable workflows? I'm confused here. How'd persist and query nested or related data over logs? By logs I assume you mean something like elasticsearch or meilisearch?
I assume they meant a log like a WAL. A WAL should be (quite literally?) all you need for durable workflows.
A distributed WAL (to survive a machine death) would also probably be something I'd want, and … something I'm not sure you're getting directly from SQLite.
Pretty much every durable system has an intent log of some sort. The log provides durability, the database system just integrates that log into a more queryable format.
if you have an application that needs to maintain state in a non-critical section or if you discover that using SQL is actually a good idea for some tasks (even in critical sections), SQLite is not only a good choice but it will save you a lot of time coming up with a brittle custom solution.
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.
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.
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.
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.
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?
Probably going to get some winces for this but I do everything with flat files. Maybe my data aren't massive enough, but I mean I can do the relational thing by just having these metadata in some column, and returning rows that contain my desired information in these columns. Even if the file were too big to fit into memory one could just subset chunks of it and chew through. All this can be done with no dependencies, just base libraries of a lot of languages.
> 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.
The suitability of Litestream for production disaster recovery is also an open question in my mind. I used 0.3.x for several years and when I tried to upgrade to the 0.5.x series there were runaway disk usage problems that would have caused downtime had they made it to prod. As far as I can tell these have not been entirely addressed, although recent bug reports suggest that they might be getting closer.
I want to love it, and I don't take open source projects like this for granted. But during my last production upgrade I chose to decommission Litestream in favor of a dumber, less granular solution using sqlite3_rsync and nightly backups because there is no point in using a backup system that is not rock solid.
Either you have durable storage or you do not. SQLite and Postgres can both ensure local durability of commits. If you want distributed durability, you need to ship that data elsewhere. That is another Postgres node, object store, whatever that’s still an external dependency.
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.
Usually we end up writing a script to incrementally refresh a data-set I'm analyzing (or have someone send me a copy after they pull it).
I've been using sqlite for anything which needs an UPDATE - modifying a row deep inside the data-set with jsonl is a pain.
My github is full of java programs which update sqlite3 files with threadpools and a single big lock around the UPDATE (& then I write or have an agent write code to analyze it).
DuckDB is slowly replacing it in the context of python, simply because of the ease of pushing a UDF into the SQL.
Also because I really like expressing things as LEAD/LAG with a UDF on top.
1. A robust durability implementation 2. A library of high performance data structure and algorithms
The fact this it's SQL is nice, but those two attributes are what make it great.
For example, I'm implement an in-process event log that I want to be durable. I started simple, but soon saw some edge cases and instead of playing whackamole I just swapped to using sqlite as an ordered kv store that gives me ACID.
Another example: ingesting multiple inter related datasets. Instead of a dozen hash maps in memory, I load them up into sqlite (no persistence) and then slice and dice as I need to.
It's a super useful tool.
The underlying database isn't the most important thing. Just use SQL. Its namespacing (eg, through CTEs) is good and you're more likely to have colleagues who know SQL compared to jq.
On a modern processor, that's about GBs of data typically, right?
I wouldn't know, I've not done either, but I'd like to learn more from your or other's experience.
This is a foundational principle of computer science. It seems to me that the "SQLite for everything" crowd is a little bit inexperienced.
SQLite is an excellent production db for many real world workloads, as has been widely documented. It is very different to Postgres, so requires learning a whole new skill set.
One way to think about it is that SQLite can work well for the parts of your system where there is naturally strong partitioning.
Or the parts of your system that don't have big data and no need for massively concurrent writes. And that's the vast majority of systems!
If you think the majority of systems require massively concurrent writes, I think you need to look a bit harder. SQLite is, after all, the most widely deployed database system, ever.
This is even more obvious when you start doing transactions processing an row locks across the network limit you to 1-3k TPS that you cannot scale out of (Pareto distribution is merciless).
[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
I'm a big fan of re-evaluating prior "best practices" in light of technology changes, especially in ways that improve simplicity. Running my family's social media site off a single sqlite DB on a VPS is great. ~15 users, almost zero maintenance. I run my FreshRSS instance off of sqlite, as well as my "now" page. At work, I used sqlite for all kinds of things over the past decades: as an ad hoc job queue, as a quick way to ingest and query lots of logs locally, and present/filter in realtime with simonw's excellent https://github.com/simonw/datasette.
I don't think it's every "sqlite for everything" as much as it is "sqlite in lots of places you probably didn't think to apply it."
kentonv/Cloudflare's work on sqlite at the edge might have made this thinking a bit more popular, but it was always around. https://blog.cloudflare.com/sqlite-in-durable-objects/
I suspect being aware of all those little neat cases and wanting to leverage sqlite for them may be an indicator of experience, rather than the opposite.
Details, please!
The only thing you really give up is HA/failover and DR. But there are solutions to deal with those. And single-server systems are generally surprisingly robust (since, in the absence of very complex control planes, uptime goes down with more systems).
SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. SQLite is found in:
Every Android device Every iPhone and iOS device Every Mac Every Windows 10/11 installation Every Firefox, Chrome, and Safari web browser Every instance of Skype Every instance of iTunes Every Dropbox client Every TurboTax and QuickBooks PHP and Python Most television sets and set-top cable boxes Most automotive multimedia systems Countless millions of other applications
https://sqlite.org/mostdeployed.html
And no, being a part of the python standard library doesn't means it is being used by the average python user. These days I'd say at least half of them are just there for machine learning.
Sqlite is used in real production apps more than any other database.
Sqlite is also weak at any sort of write concurrency.
Both can be true.
Hipp even said that it is not a replacement for a real multi-user, concurrent RDMS. Its primary competitor is "fsync".
It is not «a foundational principle of computer science».
[0] https://www.postgresql.org/docs/current/sql-createtable.html...
How exactly is this a foundational principle of computer science?
Some people want some of the benefits you get from SQLite.
SQLite is obviously not perfect, but it's an incredible piece of software, and people regularly find good ways to make use of an excellent pieces of software.
For a simplified example, having three processes reading blocks X, Y, Z in parallel is much faster than having a single process read block X, wait for the read to finish, read block Y, wait for the read to finish, read block Z and wait for the read to finish.
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
Edit: thanks for clarifying in the edit, makes a lot more sense.
There's a world between "local file" and "network DB server", running a DB server locally has lots of benefits from being able to easily query from outside if needed to forcing you to consider concurrency without the latency overhead of a network hop.
For me the main benefit of sqlite is that it's a library rather than an app.
Im not saying that sqlite isn't useful, im mostly saying that using postgres doesnt have to be complicated.
If on the other hand they're talking about single-user, software in the small - hell yeah. In fact, I'd also promote DuckDB in this regard (mostly for analytics) - with the power of a single machine these days, you can do a surprising amount and never have to worry about distribution. Unless you know you'll have to, in which case you're probably just digging yourself a hole?
SQLite is a nice local store. It's this server stuff that I don’t grok, well, yet. :)
I see obsessions with tooling/solutions constantly from experienced devs who fall in love with the original solution and think it's the only way to do things -- so the experience part cuts both ways.
> "SQLite for everything" crowd is a little bit inexperienced.
every time i see it in a real application, it becomes a huge focus of issues (for example: jellyfin, hermes, openwebui, comfyui)
That being said I'd kill for someone who used it and benefited to explain it to me in a practical sense. (specifically where syncing is involved, and syncing a subset of the SQLite is necessary. If it's "just" a document store thats treated like a blob for syncing/backup, that's familiar. If it's all in one storage but only local, that's familiar.)
Re: TFA, I guess it would have helped if I knew what Obelisk was, which is on me, and a more in-depth explanation of how this ties into AI/agents, which is on the industry/writer.
https://sqlite.org/datatype3.html
https://www.postgresql.org/docs/current/datatype.html
Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so many people like it.
Also use strict tables. https://www.sqlite.org/stricttables.html
I agree it is disappointing, especially before strict tables.
You should check out DuckDB which is basically SQLite but with proper types. Although it is also OLAP (struct of arrays) rather than OLTP (array of structs) which may have worse performance for typical SQLite loads. In practice I doubt it matters if you have an application where you're considering either.
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.
Second, it's funny you should mention the 20-line awk script. I was making a very similar argument yesterday at the Ubuntu Summit: at some point, using shell scripts with GNU coreutilus becomes impractical, while DuckDB SQL scripts scale better in terms of complexity and maintainability (and often also performance). My slides are here: https://blobs.duckdb.org/slides/duckdb-ubuntu-summit-2026.pd... (pages 32 to 36)
Third, MotherDuck develops a closed-source DBaaS on DuckDB. They build on DuckDB, and you connect to MotherDuck with DuckDB but they are a separate VC-funded company headquartered in Seattle. DuckDB is developed by DuckLabs, a bootstrapped (revenue-funded) company in Amsterdam. And the IP of the project is in a third organization: a Dutch non-profit called the DuckDB Foundation. For details, see https://duckdb.org/faq#how-are-duckdb-the-duckdb-foundation-...
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...
Leaving the current thread is where you lose the game in terms of latency. SQLite can work on timescales measured in microseconds if you don't force interthread communication.
Pedantically it's an in process virtual machine for operating on structured data. Which is precisely where it shows it's weakness, in my experience, when you end up with complicated table structures and complex join mechanics you then need to start thinking ahead of the query planner and VM code a bit in order to maintain reasonable performance.
There are more than a few unusual things worth knowing:
https://sqlite.org/optoverview.html
In the context of SQLite being understood to be a quite excellent piece of software - shouldn't we expect it to be?
In the context of a single-node, Postgres is overkill. It should not be expected to be competitive with SQLite.
This is almost like benchmarking an in-memory HashMap to Redis and being surprised that it performs well in ideal conditions.
------
I see this kind of YAGNI thinking a lot, but in my view, it must be balanced against the effort you'd put into resolving any edge cases and adapting current architecture to your use case.
Imagine you deploy Sqlite, and thought it fine by itself, you keep running into some unforeseen challenges with the use to which you are putting. YOu'd need to sink valuable time and effort into addressing those. Then, when you have outgrown it, you'd beed to spend additional valuable times dping the same with Postgres.
This is why, when it comes to Architecture, I increasingly find my myself over-enigneering a bit. Assuming there is a good chance you might need to upgrade your architecture in the not too distant future, that approach is actually kind of very efficient. I find that I am able to uncover a lot of potential gotchas, which feeds back into the what the simplified current architecture should be, and helps me understand the roadmap I'm facing very well. I also avoid wasting too much time going too deep in directions that make sense now, but need a lot of plumbing to get right, when I can see that I'd likely have to throw it all out in a few years. Going from A -> B -C -> D, where each step is the optimal good-enough-for-now architecture but which requires a lot of work to stabilize and iron out the kinks of, is much less efficient than exploring D well enough to know whether you should build A, B, or C now.
Basically, some over-engineering, if done right, is not wasted. It cuts right to the heart of what you are dealing with, efficiently, and allows you to make (maybe) simpler but informed choices now as to how best to allocate your development resources now.
A distributed WAL (to survive a machine death) would also probably be something I'd want, and … something I'm not sure you're getting directly from SQLite.
"Sockets are all you need for durable workflows" and then finally "Kernel primitives are all you need for durable workflows."
But seriously, part of being a professional is using the right tool for the job.
maintain an in-memory SQLite db and work it with SQL commands, and if you also want to preserve state across application restarts you can routinely save to disk or load from it: <https://www.sqlite.org/backup.html#example_1_loading_and_sav...>
this also happens to be the most convenient file-format (aka. application-format) I ever worked with.
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/
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.
Think Wirecutter, not install guide.
[1]. https://turbopuffer.com/
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
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
Armin Ronacher's "Absurd Workflows: Durable Execution With Just Postgres" https://lucumr.pocoo.org/2025/11/3/absurd-workflows/
https://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.
I want to love it, and I don't take open source projects like this for granted. But during my last production upgrade I chose to decommission Litestream in favor of a dumber, less granular solution using sqlite3_rsync and nightly backups because there is no point in using a backup system that is not rock solid.
By default. Generally your primary database is in a completely different failure category than a kubernetes node running an ephemeral workflow pod.
That's distributed workflows :)