Comment by levkk
1 day ago
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.
> 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!
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.
11 replies →
What additional skill set do you need to "learn" for SQLite? Copying files around?
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.
> 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.
You can probably do it with regular SQLite, too. Being limited to a single writer isn't as devastating as it sounds when they get processed very quickly. Probably don't need Rust either but it'll be more efficient than the usual choices.
(Also, it looks like libsql is the same as SQLite? Only Turso has concurrent writes)
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.
“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 containers.
What kind of data storage are we talking about?
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" application container. So at that point, why not drop the postgres container and PVC mount a sqlite directory in the app container? The result is the same.
5 replies →
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.
1 reply →
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).
Why go through the trouble of shoehorning SQLite into a cloud database by getting solutions for HA/failover and DR, when you can just use Postgres off the shelf?
So you can post about it on HN, obviously
I was thinking of using SQLite on top of k3s/Longhorn to replicate it. Anyone do something similar? Folks mention light steam and aws but Jeff Bezos’s biceps are too much for me to handle.
A longhorn volume can only be attached to one node at at time. It can share it with other nodes over nfs. I don't think this is going to scale well.
Just use Postgres with ro replicas.
I'll echo the other response.
I've had pretty terrible experiences with SQLite and Longhorn/NFS.
It's just not the right database for pretty much ANY network based filesystem, where the locking primatives aren't as robust, and you might get two processes trying to hit it at the same time.
Frankly - they say this themselves: https://sqlite.org/howtocorrupt.html
As someone who runs a fairly big personal cluster backed by a mix of giant NFS storage for media, and relatively large longhorn SSD drives for configs/temp data...
I avoid sqlite backing like the plague. It will get corrupted. Period. It's not the db for this use-case, and I'll take postgres/maria/mysql/mongo/ANYTHING else over it.
If you do it - back it up ALL THE TIME, because it's going to get corrupted.
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.)
> I think people are afraid to read the documentation for postgres.
Postgres may introduce a single-file embedded filesystem because what the hell, but the irony is all these guys won't even notice it. The same people that say Postgres backups are too hard.
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
https://sqlite.org/mostdeployed.html
That’s a comprehensive list of single user devices.
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.
'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.
1 reply →
levkk is talking about concurrency. The list you gave doesn't explain high concurrency requirements for usage.
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.
Both can be true.
1 reply →
Why doesn’t each of your users have a SQLite database writing up to a main?
You can have as many as you want - and one is often plenty.
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.
SQLite is good for read-concurrency, not great for write-concurrency.
2 replies →
I mean... if you count flat files as "databases", there are a heck of a lot more
sqlite is great for the contacts app on your phone, but that's it.
Hipp even said that it is not a replacement for a real multi-user, concurrent RDMS. Its primary competitor is "fsync".
SQLite is able to handle tens of thousands of write transactions per second on modern hardware. That is probably similar to or more than your real, multi-user, concurrent RDBMS.
> SQLite for everything
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).
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."
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.
> Running my family's social media site off a single sqlite DB on a VPS is great. ~15 users, almost zero maintenance.
Details, please!
It's touted by the people who use the word "just" a lot.
"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".
That may exist but the opposite type of irrationality is much more common.
Scalability = success. We need to be "scalable" because that means we're successful right? Scalability = real engineering. I'm a real engineer so I need to design everything to be "scalable" because I'm so smart
>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".
Sounds like they were the wise ones to build something simple that achieved a high level of success.
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).
[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
Seeing as I can get about 200K TPS from a networked DB in my environment, I have to question your setup here.
In the real world we are looking at things like RPO (recovery point objective) and RTO (recovery time objective). You need to consider HA and DR. It’s in these areas where SQLite does not scale.
That’s why I struggle to see the fit for SQLite in any sort of multi-user server environment. If you need the data to be durable, then the bigger DB’s have the tools. If you don’t need the data to be durable, just keep it in memory. I’m sure there are niches I am missing.
In this demo each T in TPS is two updates over a billion rows and most importantly skewing high on row lock contention. On a 5 year old macbook, using a dynamic language. Isolation level serializable and synchronous full (so max durability).
You can definitely go faster over less data doing single inserts on a better stack, with weaker guarantees.
RPO litestream even in it's default settings gives you point in time streaming backups to the second, which is considerably better than what RDS five minutes. So the funny thing is the durability guarantees are worse with the "bigger DBs".
RTO again you can have a standby that's warm with a copy of the data through litestream. Regional sharding also becomes trivial.
It's a solid set up for a lot of products/apps. Postgres is still fine if you want things like roles and permissions etc. Or if you don't have experience getting the most out of sqlite.
Wow, what an apples and aliens comparison. You add a bunch of transaction delays to your postgresql case because you can access a database over a network, but you use transaction batching for sqlite? Maybe just compare a local postgresql with/without batching to a local sqlite with/without batching to be much less misleading.
Because local postgres is a bad time unlesss it's the only thing running on the server. Even then sqlite will smoke postgres (even with unix sockets).
The point is to survive the Pareto row locking problem you need to move away from a network database (if you want to still have interactive transactions). The network part is the main point of a network database, once you drop that there's not much pointing sticking with the added complexity unless there's another feature you really need.
You know you can host a database like Postgres on the same machine, right?
Yes, it's still slower on the same machine, even with unix domain sockets.
It doesn't play nice with other things running with it in practice. JVM and postgres on the same box is a textbook bad time.
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.
[0] https://www.postgresql.org/docs/current/sql-createtable.html...
There are so many unfortunate footguns with unlogged tables, that I'd argue that the goroutine route is preferable.
3 replies →
Running postgresql is an order of magnitude more complicated than sqlite.
130k tps even with unlogged is not always super easy especially if getting hit concurrently. Postgresql connection overhead alone can be pretty brutal if you are setting up and tearing down connections or have 1,000 writers etc.
Postgresql generally requires good network connectivity. Folks doing sqlite distributed tend to have everything independent, you literally don't need to worry about connection / security / firewall / permissioning / internode escape or data leaking etc, can even have problems in local side networking and services can still serve.
2 replies →
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.
> 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.
Vacuum into or .backup work perfectly with a running, WAL enabled db.
3 replies →
I think you'd be surprised to learn how many real production apps are actually running on top of SQLite (by way of Cloudflare D1).
Many DB servers are built upon embedded DB primitives (like RocksDB), that doesn’t mean the primitives are sufficient on their own.
I'm not sure what this has to do with my comment? D1 is pretty much sufficient on it's own...
1 reply →
> This is a foundational principle of computer science
How exactly is this a foundational principle of computer science?
If your data is naturally sharded (users) with writes happening within a single shard, parallelism becomes easy. The request is routed to the shard hosting the user's data and reads/writes locally.
This makes scalability _much_ easier to reason about. It's cut-paste, cut-paste. Every N users needs another shard.
It does buy you a _different_ set of problems, like cross-shard querying (analytics) and how to do load leveling as users age out.
But it avoids the whole shared index scaling problems from inserts/updates with large user counts.
It becomes a hierarchical instead of a relational database.
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.
you seem like the inexperienced one to me..
SQLite does not support concurrent writes at all (on a single machine), a single writer process locks the entire database.
> you seem like the inexperienced one to me
There is irony here
Computer science no more get its hands dirty with concrete software than physics primarily being about building bridges.
It is not «a foundational principle of computer science».
I think the SQLite website itself says it best:
> SQLite does not compete with client/server databases. SQLite competes with fopen().
SQLite also gets really slow at around 50 million rows.
Most apps do not actually need the concurrency capacity that Postgres or MySQL are designed for.
I worked on an app that had sqlite databases per user... it was fine.
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.
8 replies →
Well if you run a tiny single-threaded app then SQLite is a nice simplification over spinning up a separate machine for Postgres.
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.
But now you have another process to babysit. How do you keep it healthy? And you have to ensure the client-server communication won't break.
For me the main benefit of sqlite is that it's a library rather than an app.
6 replies →
Its 2x the infra. You have to manage an additional process, auth, backups, logging, etc.
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"?
1 reply →
That's still orders of magnitude more complexity for no real benefit. A migration from sqlite to postgres, if really required, is not that hard.
1 reply →
Now you've added a substantial dependency, and annoying setup requirements. Good luck doing this for a native app on mobile or desktop.
2 replies →
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.
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 reason the parent post is complaining that it doesn't make sense, is because people have indeed pushed the idea of using SQLite as an alternative for web apps like that.
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.
Scale to zero is very useful.
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.
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. :)
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.
And of course there are now several responses proving your point.
How many production apps do you think have enough users to justify these huge DB servers?
Huge?
Everything is huge compared to sqlite.
Are you one of my enterprise customers? What if your workload does not require write concurrency?
Someone with experience would know that concurrency isn't a universal requirement.
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.
sqlite is more like a file format than a database. it competes with .xlsx.
> "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)
What kind of issues commonly arise?
anything that requires more than 1 user or not being down all the time
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.
It's very likely that you have multiple SQLite databases in your pocket right now. It's one of the most widely deployed pieces of software on the planet. If your conclusion is that it's guaranteed to cause more problems than other solutions, then that's on you.
Correct! I'm not "worried" about it, I've been putting SQLites in your and my pocket for the last 17 years.
I don't want to be glib and leave it there, even though I'm slightly annoyed you missed several sigils in my post that I was well past that.
The point is, for the not in your pocket case, for the not a singular document store case, I'm curious what the use case is.
1 reply →