Ask HN: SQLite in Production?

Yes, we use it to back several services in production at an 8 figure ARR business, with Litestream as our streaming backup option. I'm quite happy with it because it's trivial to debug and test against a local copy of the production data if necessary, but there are a few things to think about.

* You need to have your code repopulate index statistics with ANALYZE or PRAGMA OPTIMIZE now and again, or you may get a confused query planner.

* It's best if your code defines meaningful types to scan SQLite values into, and you ideally avoid ever writing to the DB via the CLI. Alternatively, be aggressive in writing CHECK constraints and triggers to ensure data validity.

* You need to be working with a team of developers that are pretty comfortable with databases to get a lot of value out of SQLite. I've been surprised at how performant features can be if a single mind is designing the schema, constraints, triggers, and queries, examining the query plan to choose indexes, and writing the application code that accesses the database. SQLite minimizes the barrier to doing this with real snapshots of production data - but that still doesn't mean it's going to be easy.

* You need to think about how long your code holds a write transaction open. Individual statements in SQLite run very quickly (I've seen ~250 microseconds per insert on a EBS-backed EC2 instance), but if you have a Django app with @transaction.atomic everywhere, you're going to run into lock contention quite quickly because your Python functions take much longer to run. If possible, "organize" all the data for your writes outside the transaction and then hold the lock for as short as possible.

* Any transaction that may eventually write must start with BEGIN IMMEDIATE, otherwise SQLite may throw an error to keep it's promise of serialized isolation on writes.

All said, I'd do it again for any other services-based backend. It also incidentally enforces the good hygiene of "services can only access their own data." Happy to answer any questions you have.

I've been using SQLite/Litestream for https://extensionpay.com ↗ for about 3 years now! Serves about 120m requests per month (most of those are cached and don't hit the db), but it's been great!

I was convinced that SQLite could be a viable db option from this great post about it called Consider SQLite: https://blog.wesleyac.com/posts/consider-sqlite ↗

Using SQLite with Litestream helped me to launch the site quickly without having to pay for or configure/manage a db server, especially when I didn't know if the site would make any money and didn't have any personal experience with running production databases. Litestream streams to blackblaze b2 for literally $0 per month which is great. I already had a backblaze account for personal backups and it was easy to just add b2 storage. I've never had to restore from backup so far.

There's a pleasing operational simplicity in this setup — one $14 DigitalOcean droplet serves my entire app (single-threaded still!) and it's been easy to scale vertically by just upgrading the server to the next tier when I started pushing the limits of a droplet (or doing some obvious SQLite config optimizations). DigitalOcean's "premium" intel and amd droplets use NVMe drives which seem to be especially good with SQLite.

One downside of using SQLite is that there's just not as much community knowledge about using and tuning it for web applications. For example, I'm using it with SvelteKit and there's not much written online about deploying multi-threaded SvelteKit apps with SQLite. Also, not many example configs to learn from. By far the biggest performance improvement I found was turning on memory mapping for SQLite.

I have spent the last 18 months building a web app using SQLite, python (starlette) and htmx. It is a back office app that now runs daily operations for a 60 person tax firm. The load is low, and it runs on a very cheap ec2 instance with litestream backups to s3.

The low latency is wonderful because I can write serial queries to construct a complex response. All the queries are written by hand and I do not use an ORM.

I have had a couple of outages, and all but one were basic operator errors. The notable exception was when tax season started, load went up, and I found that I was leaking (or creating too many) db connections. This was scary and I never quite understood the low level failure mechanism. The solution I came up with is to issue warnings whenever a connection (subclass of std sqlite3 Connection) is destroyed (__delete__()) without having been explicitly closed. Then I found all of the usage sites and put them into `with` contexts. Explicitly managing the connection lifetimes took the pressure off and I haven’t had to think about it since. I’d still like to reproduce the problem better though.

I do wonder what I would do if we scaled up to a point where things started to fall apart again, but there’s a good chance it won’t happen. It would require a lot more traffic, which implies massive staff growth.

The other architectural decision worth noting is that I keep complete local copies of various saas api data. A lightweight CRM, customer support, billing, call center, etc. Each of these has a background api fetcher/poller, and writes to its own SQLite file. Then the web app attaches each file to the main db. This gives me schema.table namespacing in the sql, and allows for separate backup policies for the different files. The trade off is no atomicity across the files, but for my purposes it is not significant.

We run sqlite at https://tender.run ↗, both in our backend (as a primary data store, backed by litefs) and on the client (as a index and cache).

In general works really great, esp. in terms of speed. There are some limitations worth knowing though - for instance, figuring out making write throughput reasonable with the combination of wal mode, busy timeout, and begin immediate.

Another that I've had to work around recently is the lack of materialized views - you can make and query from a view, but it's just a select statement that gets shoved into queries that use it. To get the perf of a materialized view, you'd have to do something manual and custom. For the most part, this isn't an issue simply due to how fast sqlite is, but in the browser it's a bit more significant because of how much slower the wasm-compiled sqlite is.

Running SQLite in production for our mid-six figure SaaS for a year now. We use it with Go and run it in WAL mode that allows for concurrent reads and serialized writes. Litestream for backups. No issues, and extremely good performance.

Our app is read-heavy so it works great with WAL mode only allowing one writer at a time. If you have a write-heavy workload, I would go with Postgres.

We use Go's mattn/sqlite3 library and open a connection pool for read-only queries, and a single connection for write queries. The read-only connection pool uses DEFERRED transactions, and the write-only connection uses IMMEDIATE transactions.

All tables are STRICT which helps some with type safety, and foreign key checks are always turned on.