> 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.
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.
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?
> 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.
> 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.
It's also not just "a file on disk". I know this because I re-read the docs about locking last night: https://www.sqlite.org/lockingv3.html
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.
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
1 reply →
You might be interested in https://devnull-as-a-service.com/
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.
ODBC drivers for SQLite are just a thin abstraction over the C library.
1 reply →
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?
[1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/pricing/spaces-object-storage
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.
(no affiliation to fly/litefs, just a fan)
[0]: https://fly.io/blog/litefs-cloud/
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.
Nowadays you're considered crazy if you don't pay rent-seekers for every simple thing.
this comments seems to say, that you could corrupt your copy, if you copy it while it is in use https://news.ycombinator.com/item?id=39838753
2 replies →
For my project I've been using Litestream to Cloudflare's R2, which is compatible with Amazon S3, and has a very generous free tier.
It seems that the R2 free tier is "Available on Workers Paid plan" which is being replaced by the "Standard plan" [1].
I interpret this as saying that you can't get R2 literally for free. Looks like Cloudflare KV could work, with values that go up to a bit over 25MB.
$5 per month is very reasonable if you're using it for real, though.
[1] https://blog.cloudflare.com/workers-pricing-scale-to-zero/
I use Litestream for my production web app and stream to Backblaze B2. It's S3 compatible and literally free!
Wasabi should give you a lot more storage for almost the same price
I checked your blog and I am impressed you are still very curious to learn things which didn't exist before you retired (generative AI).
Thanks! Just tinkering, though. I skim AI papers sometimes, but don’t do the homework.
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.
TIL for me too. Looks like it's in beta still so maybe not fully marketable yet.
Laravel 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...
Feels 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?
Since the page didn't seem to link to it, here's a really great article about using SQLite in production for moderately-sized web apps: https://blog.wesleyac.com/posts/consider-sqlite
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.
another article praising sqlite in production and working around a known sqlite limitation: concurrent writes. As they link in the article:
> But, my favorite feature of the gem is its improved concurrency support.
> [..] https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-i...
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 allows one writer concurrent with any number of readers. It does not allow two concurrent writers.
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.
6 replies →
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.
ive just put it here: https://github.com/abbbi/sqlitestress
maybe its useful for some people to simulate their workloads.
2 replies →
Sure the actual write might only take 1s but the transaction might lock for 10ms.
Does SQLite support overlapping write transactions with locks on different rows?
Also do you know if WAL2 mode changes anything? https://www.sqlite.org/cgi/src/timeline?r=wal2
1 reply →
Before you even need to consider postgres, you can batch your writes to sqlite!
I am no sqlite fanboy, although I might be, but I found the industry seems to run to postgres for just about anything. I prefer simplicity first.
How it that simplicity?
To batch updates makes the code far more complex.
To install any full strength DB is trivial.
I don't get the 'simplicity'?
2 replies →
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.
[1] https://docs.servicestack.net/ormlite/litestream
I've been running sqlite and litefs in production for my side project over the last year or so now.
It really is quite nice how much database you get for the cost and maintenance overhead required when litefs is thrown in the mix.
Crazy to me that the whole thread here doesn't mention how schema changes are more difficult and sometimes also lead to inconsistent data https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...
Doesn't help that most ORMs (or other migration tools) don't generate you the correct migration you need
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.
Is there a similar effort for Django? Django Sqlite driver is slow and not tested for production.
Setting SQLite PRAGMAs via Django settings was recently merged. I would expect to see this show up in the next version or two of Django.
https://code.djangoproject.com/ticket/24018
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.
https://centraltrunks.blogspot.com/2022/07/django-sqlite-dat...
https://code.djangoproject.com/ticket/29280
(I authored the rant in the first link)
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.
I've been using it for a couple different production apps, and it has been great. No problems for me.
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).
This paragraph appears twice in the article at different places.
I don't think the speaker said it twice word by word in the talk.
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.
I was not saying this particular paragraph itself is repeating.
It literally appears twice in the article at different places.
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.
It's not immediately clear. How does this work over the network for multiple app servers (or does it)?
Is the DB hosted on something like nfs, or are writes synced to all app servers or something else?
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.
IIUC it does not work, the assumption here is that you can serve all your traffic with a single instance
I'm creating a flutter app, I need a database but don't need online storage, local only. SQLite really is the only option for this.
Firebird is sometimes an option.
At first I misread this as firebase but searched for Firebird. I want a local to the device database.
Postgres. Only postgres.
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
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?
1 reply →
> 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.
sqlite db forum stats: <https://sqlite.org/forum/reports>