Ask HN: SQLite in Production?

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

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

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.

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.

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.

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.

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 !

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.

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.

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.

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

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.