I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and I think it's great that it's getting hyped because it truly is a capable database, but after trying myself I think I'd never reach for it in production because it lacks a lot of power that a database like Postgres has, and some of that power is actually relevant to a real production setting:
- Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
- Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times.
- You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to somehow copy/get the migrations to your server to then run the migration is a bit clunky.
All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant.
Personally, I think the better way to tackle sqlite_busy is to have a single writer managed at the application level. That effectively eliminates sqlite_busy in the context of a single process.
> To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma
Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).
I have some experience with different databases and system designs. In one of my last projects I used SQLite. However, I used only one dedicated process for writing and other processes for read only. This might help.
As someone really tempted to use SQLite in production, the one thing I keep bumping against is how to have a nice GUI to interact with the running database. With our current prod databases, I can connect dbeaver and the like to them and nicely browse the data, query, or even do the occasional fix. Seems like this would be much more of a head scratcher if the database is just a file on the same VPS the app runs on.
"If the database size is smaller than the mmap_size, the entire database is mapped into memory, turning disk reads into simple pointer arithmetic." hah so that's how it works?
I love SQLite, and I really want to run it in production, but my clients expect minimal data loss and downtime when one of my servers goes down.
The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.
Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated):
Where I part ways a little with the recommendation is in the busy_timeout + BEGIN IMMEDIATE suggestion. On an embedded system, that's not really sufficient; I maintain a caching service that is used by thousands of clients. It ingests data over MQTT and has a web interface for queries. In my design, there is a MQTT thread and pruning thread. The MQTT ingestion thread and the pruning thread can both end up "contending" for the write lock, and increasing the busy_timeout only makes them wait longer before noticing that contention and moving on to the next task. What we ended up needing to do was add an application-level lock that only allows a single writer transaction to be in flight at a time; BEGIN IMMEDIATE is nice for preventing other readers from blocking, but it doesn't help much with other writers.
Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
One of the lessons I picked up from my very brief foray into the world of realtime software is the idea of amortization. If you have one activity that is possibly time-unbounded, then the solution is to trade away a bit of throughput for certainty by taxing the frequent task with making incremental progress on the cleanup. The first real-time garbage collectors worked this way. Every allocation had to do a bit of sweeping work, which generally keeps all but the largest allocations from every being in danger of hitting a pause (in real time systems large allocations are typically done at bootstrapping time to avoid this issue entirely)
> Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
Probably shouldn't be. One of the more high profile use cases for SQLite is per-customer sharding. Particularly with services where at any given time a small number of customers are generating the majority of the traffic. The data for customers that you haven't seen logged in for weeks tends to have a logarithmic effect on the query time for active customers when you use a single shared database for all of the data. Unloading that data reduces the cost of interactive workloads handily, even though the cost of backups may still be either the same or still have a logarithmic cost (eg, rsync detects deltas).
> In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact.
No, this is not safe.
You can lose the latest committed transaction with this pragma.
The era of AI agents need serverless SQL. Its very important to have cost effective tech to run the internet.
hence we ended up writing our own WAL for a Postgres compatible engine, and what surprised me was how much of the work is in the fsync ordering rather than the log format.
Relaxing that is a real choice to lose the last few transactions on a crash. Fine for some workloads but it should be a decision someone made on purpose.
21 comments
[ 0.24 ms ] story [ 29.8 ms ] thread- Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
- Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times.
- You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to somehow copy/get the migrations to your server to then run the migration is a bit clunky.
All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant.
> To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma
Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).
The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.
- https://sqlite.org/whentouse.html
- https://sqlite.org/different.html
- https://sqlite.org/quirks.html
I don't think the all-too-common "written by AI" remarks are helpful.
The article was informative and useful. That's what matters IMHO.
- https://powersync.com/blog/sqlite-optimizations-for-ultra-hi...
Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
> Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
Probably shouldn't be. One of the more high profile use cases for SQLite is per-customer sharding. Particularly with services where at any given time a small number of customers are generating the majority of the traffic. The data for customers that you haven't seen logged in for weeks tends to have a logarithmic effect on the query time for active customers when you use a single shared database for all of the data. Unloading that data reduces the cost of interactive workloads handily, even though the cost of backups may still be either the same or still have a logarithmic cost (eg, rsync detects deltas).
> In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact.
No, this is not safe. You can lose the latest committed transaction with this pragma.
hence we ended up writing our own WAL for a Postgres compatible engine, and what surprised me was how much of the work is in the fsync ordering rather than the log format.
Relaxing that is a real choice to lose the last few transactions on a crash. Fine for some workloads but it should be a decision someone made on purpose.