7 ms·
How (and why) to run SQLite in production
- Glench 3y agoSince 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 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.
- 5Qn8mNbc2FNCiVV 3y agoCrazy 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-table/ 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
- massysett 3y agoI 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.
- hnarn 3y agoRunning 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.
- toastercat 3y agoVery 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.
- thrdbndndn 3y agoMight 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.
- hyperadvanced 3y agoI 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.
- thrdbndndn 3y agoI was not saying this particular paragraph itself is repeating. It literally appears twice in the article at different places.
- lxgr 3y agoThis 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).
- thrdbndndn 3y agoThis paragraph appears twice in the article at different places. I don't think the speaker said it twice word by word in the talk.
- fractaledmind 3y agoYep, 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.
- abhiyerra 3y agoWe 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.
- klabb3 3y agoTIL 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.
- walthamstow 3y agoTIL for me too. Looks like it's in beta still so maybe not fully marketable yet.
- deleted 3y ago[deleted]
- aaviator42 3y agoI'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 https://github.com/aaviator42/StorX
- begueradj 3y agoLaravel switched its default database to SQLite since the current version (11).
- criddell 3y agoWindows 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/System32/winsqlite3_dll/PowerShell/index https://renenyffenegger.ch/notes/Windows/dirs/Windows/System...
- abbbi 3y agoanother 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-improving-concurrency/ 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.
- duped 3y agoWhat'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.
- abbbi 3y agoWAL 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.
- stpn 3y agoI'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.
- skybrian 3y agoI 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-sqlite-setting-up-litestream/ https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/pricing/spaces-object-storage https://www.digitalocean.com/pricing/spaces-object-storage
- Glench 3y agoI use Litestream for my production web app and stream to Backblaze B2. It's S3 compatible and literally free!
- stpn 3y agoFwiw, 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/ https://fly.io/blog/litefs-cloud/
- begueradj 3y agoI checked your blog and I am impressed you are still very curious to learn things which didn't exist before you retired (generative AI).
- skybrian 3y agoThanks! Just tinkering, though. I skim AI papers sometimes, but don’t do the homework.
- yread 3y agoMaybe 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.
- aitchnyu 3y agoIs there a similar effort for Django? Django Sqlite driver is slow and not tested for production.
- infamia 3y agoSetting 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 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.
- frodowtf 3y agoIt 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.
- hnfong 3y agoA 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-database-is-locked-errors.html https://centraltrunks.blogspot.com/2022/07/django-sqlite-dat... https://code.djangoproject.com/ticket/29280 https://code.djangoproject.com/ticket/29280 (I authored the rant in the first link)
- nprateem 3y agoIt'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?
- vander_elst 3y agoIIUC it does not work, the assumption here is that you can serve all your traffic with a single instance
- fractaledmind 3y agoThis 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/ https://fly.io/docs/litefs/), the sibling to Litestream for backups (https://litestream.io https://litestream.io), is aimed at precisely this use-case. Similarly, Turso (https://turso.tech https://turso.tech) is a new-ish managed database company for running SQLite in a more traditional client-server distribution.
- account-5 3y agoI'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.
- ojosilva 3y ago> 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.
- 01HNNWZ0MV43FF 3y agoIt'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 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.
- klabb3 3y agoYes. 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.
- PokestarFan 3y ago
- mythz 3y agoWe'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 https://docs.servicestack.net/ormlite/litestream
- jddj 3y agoSeems 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 3y ago[deleted]
- zimpenfish 3y agoI'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.
- fractaledmind 3y agoI've been using it for a couple different production apps, and it has been great. No problems for me.
- helsinki 3y agoFeels like half of the comments in this thread are covert advertisements from cloud providers.
- jddj 3y agoKind 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?
- p0w3n3d 3y agoPostgres. 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
- sgbeal 3y ago> 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 https://sqlite.org/forum/reports>
- hruk 3y agoBut 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.
- p0w3n3d 3y agoHow 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?
- hruk 3y agop99.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.