Feels like half of the comments in this thread are covert advertisements from cloud providers.
How (and why) to run SQLite in production
41–50 of 85 posts
Re: How (and why) to run SQLite in production
#42Earlier quoted context omitted.
WAL does not help with "database locked" situations. At some point you will see them even with WAL enabled, and your application frontend code has to deal with the timeout and retry or whatever.
You just need to set busy_timeout > 0, and you'll basically never have database locked situations. It's just unfortunate, that it's not activated per default: https://sqlite.org/forum/info/7e456bf5544ab128
yeah, but that will slow down your app and may help in a short run, but you basically just shift the problem and make it even worse if your workload gets even higher.
Re: How (and why) to run SQLite in production
#43Seems as good a place as any to put this question: has anyone had any issues with Litestream? I've got a more basic backup solution running currently and don't want to put another moving piece in the way of a production service unless it's extremely solid, but I do like the idea.
[deleted]
Re: How (and why) to run SQLite in production
#44Feels like half of the comments in this thread are covert advertisements from cloud providers.
Kind of a funny observation in the context of moving away from managed DBs.
Is it that the suggestions are for companies other than the big 3 that it caught your eye?
Re: How (and why) to run SQLite in production
#45> First is that SQLite is simple. The database is a literal file on disk. The engine is a single executable. No, it's not a single executable. There's no database executable as far as your app goes. There's no engine. The "engine" is a library, meant to be embedded into other code and may be concurrent and have many "executables" if you implement it that way or have different apps access the database. Think of SQLite…
Yes. If you write your server in eg Go or Rust, you can have a “single executable deployment” so to say. But the main limitation is no horizontal scalability. So if you want web scale, you have to use something faster, like /dev/null.
Webscale always reminds me of this video [1] - MongoDB is webscale.
[1] - https://www.youtube.com/watch?v=b2F-DItXtZs
edit: and now I believe you are directly referencing this but may be useful for others who don't get the reference
Re: How (and why) to run SQLite in production
#46Seems as good a place as any to put this question: has anyone had any issues with Litestream? I've got a more basic backup solution running currently and don't want to put another moving piece in the way of a production service unless it's extremely solid, but I do like the idea.
I've been running Litestream for a few years now on several machines without any problems[1]. But I also use Borg with the `.backup` command to take backups. No harm in deepening your depth of defence.
[1] Had a problem with corruption back in 2021 but that was fixed quickly.
Re: How (and why) to run SQLite in production
#47Earlier quoted context omitted.
What's wrong with PRAGMA journal_mode=WAL? It enables concurrent writes, at the expense of disk. Which to my understanding is the same as any other database backend with write ahead logging to enable concurrent writers. Anecdotally, I've seen the WAL file grow way too large even after all writes have finished and should shrink, but that's manageable.
WAL does not help with "database locked" situations. At some point you will see them even with WAL enabled, and your application frontend code has to deal with the timeout and retry or whatever.
I'm relatively new to the DB field, and have only really used SQLite; but it seems obvious to me that if you're doing a transaction, particularly a complicated one, then you should expect your transaction to fail occasionally due to concurrent changes, and be executing your transaction in a loop. This should be true for any database.
If you loop-retry all transactions which fail due to transitory effects, then you won't have a problem with "database locked" situations.
Abysmally documented, but this is what I use for golang + sqlite:
https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-202312...
EDIT: Typo
Re: How (and why) to run SQLite in production
#48> First is that SQLite is simple. The database is a literal file on disk. The engine is a single executable. No, it's not a single executable. There's no database executable as far as your app goes. There's no engine. The "engine" is a library, meant to be embedded into other code and may be concurrent and have many "executables" if you implement it that way or have different apps access the database. Think of SQLite…
There are ODBC drivers for SQLite. It can make sense when you want to offer SQLite as an option alongside other, more traditional DBMS. If your queries and data are relatively simple, it will probably work just fine.
Re: How (and why) to run SQLite in production
#49Earlier quoted context omitted.
You just need to set busy_timeout > 0, and you'll basically never have database locked situations. It's just unfortunate, that it's not activated per default: https://sqlite.org/forum/info/7e456bf5544ab128
yeah, but that will slow down your app and may help in a short run, but you basically just shift the problem and make it even worse if your workload gets even higher.
You should only be waiting if someone else is writing. And yes, disallowing any concurrent writes, as SQLite does, is certainly going to be a bottleneck at some point, but unless you have a particularly write-heavy workload, it will probably get you pretty far.
Re: How (and why) to run SQLite in production
#50Laravel switched its default database to SQLite since the current version (11).
Windows has included SQLite in the base install for almost 10 years now. They call it winsqlite.
Here’s an article about using it in PowerShell scripts:
https://renenyffenegger.ch/notes/Windows/dirs/Windows/System...