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 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.

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.

"SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild." https://sqlite.org/mostdeployed.html

Although this isn't what most people usually mean by "production". See "Appropriate Uses For SQLite": https://www.sqlite.org/whentouse.html

https://kerkour.com/sqlite-for-servers is a good read if you've not seen it before

I write custom apps for enterprise clients. Most think that they need Oracle or DB2. In the majority of cases apps run with SQLite just as effectively. In the early days I used Postgres with Python. Moved to SQLite for ease of admin and nobody even noticed.

The database is just one component of systems, without measuring things you can try to guess (usually wrongly) where the bottlenecks are. See @qweqwe14 response.

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.

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.

I use it a lot for production ETL pipes, and sqlite3 is wonderful. The only issue we see is the writes slow down when the table rows hit 8–9 figures during a fresh load.

In client/server, our biggest issue is data replication and sync. We have this application that use sqlite3 as a db for mostly read only web application. It really act as a cache server. We can’t figure out a clean way to update the data from remote systems. We end up writing a web socket process on the box to pull data from remote systems and write the data one batch at a time. This was/is a big pain point for us. Especially when updates are coming in fast.

I'm building a hosting platform / system for SQLite databases at https://www.airsequel.com. Just like you we discovered that for many (most?) use-cases SQLite is the better database. Ordinary projects / companies don't have "webscale" requirements and they are better off with a system that is fast, simple, low resource, and easy to deploy and manage.

There's a company (http://turso.tech) that can host sqlite as a service. One of the features allows a replica local sqlite file to be used for reads and the writes still go to the primary on their edge network.

Been using it (without the local feature) for a couple of months on a project and I will do it again for future products without a second thought.

You might be interested in how Expensify use(s/d) SQLite for their product. https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

I assume you are thinking about server side applications. Otherwise, it is extremely common in "desktop" applications for saving data. And it is a common approach for saving user data in Android applications (based on my knowledge from a few years ago, could be outdated)

there are tens of thousands of apps that use it, for billions of instances; most all of the most popular apps use it in some fashion. it’s a beast.

I use sqlite as a caching storage for anacrolix/torrent. It's also the main database for my DHT indexer.

The only real limit is transactions per second, and single writer. If you batch things that becomes a non issue.

If it’s an embedded app like an iOS or android app it’s the right choices.

I run a bunch of blogs from a single Django app with a CMS, SQLite, and extremely optimized SEO hacks.

It’s the perfect use case, light on writes, super heavy on reads. Almost as fast as any caching solution would be, except it’s actually running live SQL queries on the data. I am not sure why more people don’t opt for this to start.

I think some HN user talked about his DNS company using sqlite for years and years without a glitch.

How are you syncing data?

I use it for some doc making in small family business :)

However sqlite gained "SELECT * FROM /etc/passwd" and other system stats gathering features so looking for replacement...

Some fork dedicated to eradicated more complicated things like eg. WINDOWS would be nice. And ex-Redis and others simplifications too !