> 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 as an intricate file format for which your app will use an API (ie sqlite.c) to access.
Between this and WAL, which-ever one you pick, you have the following caveats:
- You aren't supposed to use a SQLite DB through a hard or soft link
- By default, in rollback journal mode, it will create a temporary `-journal` file during every write
- If you're doing atomic transactions on multiple DBs, it also creates a super-journal file
- You definitely can't `cp` a database file if it's in use, the copy may be corrupt
- It relies on POSIX locks to cooperate with other SQLite threads / processes, the docs advise that locks don't work right in many NFS implementations
- In WAL mode it needs a `-wal` and a `-shm` file, and I believe the use of shared memory makes it extra impossible to run it over NFS
SQLite is not simple, it's like a million lines of code. It is as simple as a DB can be while still implementing SQL and ACID without any network protocol.
I am thinking of writing a toy DB for fun and frankly I am not sure if I want to start with SQLite's locking and pager or just make an on-demand server architecture like `sccache` uses. Then the only lock I'd have to worry about is an exclusive lock on the whole DB file.
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.
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.
I've been using SQLite in production for years and am very happy with it. I believe it's sufficient for the vast majority of web use cases.
I have a bit of battle-tested code that gives me a nice key-value interface to SQLite files, perhaps others will find it useful: https://github.com/aaviator42/StorX
I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1].
It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started?
Fwiw, I've been using hosted LiteFS Cloud[0] (written by the same person who wrote litestream) on fly.io. The pricing starts at $5/mo for the first 10GB (kind of steep) but it's been working out so far.
Maybe I'm crazy but I just have a cron job that does .backup during a time with little activity. My db is only 40GB or so and stored on an NVMe so it finishes quickly.
We recently moved some of our cron jobs that needed a database backend to GitHub Actions + Cloudflare D1 which is SQLite in the Cloud and couldn’t be happier.
TIL D1 is SQLite. Why don’t they market it more prominently that way? If their sql is interoperable with SQLite it’s a huge selling point, both for reducing vendor lock-in and compatibility with existing frameworks and such.
That article, along with the ability to have streaming backups with Litestream and not wanting to pay for a separate DB server, inspired me to use SQLite in my SaaS four years ago. I've been enjoying the operational simplicity a lot although there is not a lot of community documentation currently about tuning SQLite for web app loads. My app does about 120m hits a month (mostly cached and not hitting the DB) on a $14/month single-processor DigitalOcean droplet.
Really. At the point you are experiencing database locked in your productive app that uses sqlite as backend, i would strongly suggest to use another database backend that was designed with concurrent writes in mind.
Nothing against sqlite in production, its nice, as long as your workload meets its feature set.
What both articles are saying is that concurrent writes under a certain threshold or heavy read-only activity is perfectly acceptable usage pattern for sqlite in production.
...the problem here is that historically rails (and others) use deferred transactions and that causes sqlite to fail even under trivial load conditions without simply waiting for the db to free for the next write, because people who've written the drivers don't understand how to use sqlite.
If you use sqlite correctly under heavy load it's slow not unreliable.
> as long as your workload meets its feature set.
Sure... but to be fair that probably covers a lot of microservices and probably a lot of apps too.
Obviously as you scale, it won't, so sure, it's a limited use case... but, heck, I've seen dozens of microservices each with their 'own database' (ie. read same RDS, with different database instances) that all fail at once when that instance goes down. Woops.
Better? Worse?
Hm. Sqlite is insanely reliable. I like isolated reliable services.
It's not for everything, but nothing is... I think it's suitable for more use cases than you're giving it credit for.
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.
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.
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:
In my experience most SQLite writes take less than 1ms.
Do your writes really need to be concurrent if they run that fast?
Hard to get upset about waiting for the current write to complete before you get your turn when we are talking delays measured in thousandths of a second.
If you have more than 1000 writes per second then maybe this is something to worry about. The solution there is probably to run a slightly more powerful server!
back in the days where we hit this issue (mostly on windows systems) i used to create a little stress tool, you would be surprised how fast you reach the database-locked state.
You're misunderstanding. No-one mentioned transactions, that's not what we're talking about.
We're talking about concurrent writes. We're talking about batching inserts. Because SQLite can't handle a high throughput, so you batch a bunch of inserts together.
If the only way to get performance is to batch inserts, then you've got to write a whole load of manual queue code to queue up X number of inserts to insert them all at once.
Worse still, if your server crashes, bug, etc. you've just lost all those inserts. But you've already responded with 201s! So if you want any sort of guarantee, you've got to write even more code to cache them on disk or redis or something.
You're basically re-implementing features of postgre, badly, to make up for SQLite's deficiencies,
It really doesn't matter HOW you do it, it's the fact you have to do it at all. It's not simpler, it's more complicated. Installing/using a fully fledged DB is trivial these days.
We're also defaulting to using SQLite first for our new Apps with Simplicity, Speed, Cost and flexibility being the motivating factors.
Not needing a managed database also allows us to move away from AWS onto much better value Hetzner servers, much love to Litestream [1] which makes replication to R2/S3 effortless. It's a silly 30-40x cheaper hosting SQLite Web Apps on Hetzner compared to the "recommended" managed DB configurations on AWS/Azure.
I routinely migrate my SQLite databases. I keep the schema in a text file and I write a separate SQL file to insert all data from the old database into a new one, making changes as needed.
With the proper PRAGMAs set and modern hardware, you can do 400+ writes/sec with 4 uvicorn workers and ~100 clients connected. The achilles heal is writing lots of large files. That will cause concurrent requests to wait around for the disk to finish writing to the WAL.
It also seems to have the same concurrency issues as described in the article. At least from my experience the "database is locked" error appears quite often.
A rather late reply, but in case anyone is reading this.... Django is basically designed to have sqlite deadlocks, and there's a trivial fix (that the dev team refused to include) that allows fixes the problem and allows you to run up to moderate loads.
Seems 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.
Running a single-node Postgres server isn't very complicated, so my first thought would be what benefits SQLite bring in terms of setup and maintainability that makes it so much more attractive than Postgres. SQLite is great for a lot of use cases, but using it as your primary database in production just feels like contrarianism to me, or some kind of psychological idea to use "pure" solutions.
Another one of my rule of thumbs is that unless there is massive evidence to the contrary, you should simply use what "most people use" for each specific use case. In my world, when it comes to web applications, that is Postgres.
Might want to proofread it. There are a few paragraphs that are repeating and out of place:
> Why? So, let’s explore that together. Who here is running or has run an application in production with SQLite? Who has experimented with SQLite for an app, but not shipped it to production? There are a couple hands up, but not many. So, let’s turn this question around.
This pretty clearly seems to be a transcript of a conference talk, and redundancy and repetition are quite useful for getting a point across in spoken language.
Maybe it could be more accessible when rewritten as a blog post, but if it’s a useful talk I’d much rather have an official transcript than only a video link and maybe Youtube’s auto-transcript.
So much good content is buried in video form for me (I really don’t like watching technical videos and prefer reading that content).
I think this is paraphrased or exact copy from a conference talk. Not that I love the rhetorical question mode of communication, but it’s more understandable.
Yep, that was an errant duplication. I did proofread, but how is it that one thing always seems to sneak through anyway? Thanks for the catch. Fixing now.
Very frustrating read. The author glosses over the "why not", and never brings up the topic of concurrent writes or how they deal with that in Production.
This presentation is focused on the use-case of vertically scaling a single server and driving everything through that app server, which is running SQLite embedded within your application process.
This is the sweet-spot for SQLite applications, but there have been explorations and advances to running SQLite across a network of app servers. LiteFS (https://fly.io/docs/litefs/), the sibling to Litestream for backups (https://litestream.io), is aimed at precisely this use-case. Similarly, Turso (https://turso.tech) is a new-ish managed database company for running SQLite in a more traditional client-server distribution.
But n can be so big that it you never run into it. My company has a read-heavy SQLite-backed service that serves roughly 150,000 daily users with p99.9 at about 5 milliseconds. We did some rough projection and determined we could reach the total addressable market of our product in the US without even approaching having a problem.
How can your company guarantee p99.9 if there is only one instance? Is there any log shipping/duplication etc? Is consistency maintained on one server fault?
p99.9 referring to latency. However, we also do a weekly test of how quickly we recover from a catastrophic crash, which is roughly about 6 minutes (which is the amount of time it takes for the autoscaling group to spin up a new host, Litestream to restore the database from s3, and the server to start up again).
Honestly, 99.9% uptime is pretty generous - we can fit in quite a few catastrophes per year and still have 99.9% uptime. In the 2 years this service has been running, we've had 100% uptime via zero-downtime deployments, anyway.
In terms of monitoring, traces and error logs are shipped to our observability solution, yes.
> Why? Because once your app gets more than _n_ users you will run into scaling problems and then you end up with huge tech debt
Anecdote: the sqlite forum runs on a single sqlite db and has well over 1500 users. Similarly, the sqlite source site is heavily visited by thousands of folks and actively used in write mode by its developers. It sibling project, the Fossil SCM, also runs entirely from a single sqlite db.
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 as an intricate file format for which your app will use an API (ie sqlite.c) to access.