Ask HN: Are you using SQLite and Litestream in production?

We're using ECS to manage our containers. What worked best for us is two task definitions, one daemon with only Litestream, another replica task with our web app. The web app task has a placement rule ensuring that Litestream has started before it can be placed, ensuring the database is "in place" before the app starts. Both containers have the same directory on the host mounted as volumes, so they can both access the same database. This series of tests convinced me that this is a safe setup: https://tangentsoft.com/sqlite/doc/trunk/walbanger/README.md

This has been working well for us - the db is fast, we can deploy whenever we want, and I don't feel anxious about the host going down.

One remaining source of anxiety is updating the machine image of the host - that will cause some downtime, of course. I think the safest strategy will be to increase the size of the ASG the host is in to warm up a second, updated EC2, then drain the first one.

I suggest taking a look at the replica settings (https://litestream.io/reference/config/#replica-settings) and making sure the default values work for you.

Using both the default retention (24h) and snapshot (24h) intervals will not give you 24h of data history. This happens because any snapshots older than 24h will be deleted. Granted, you'll have another snapshot ready, but it will only contain a short history of your DBs changes.

Also, test restore times. If you need to restore in a hurry, having to download 24h of wal logs can take a while.

I started using it during a time when it was unmaintained (between v0.3.9 and v0.3.10). There were a few bugs I hit, e.g. memory usage spikes. Shout out to hifi, who created patches and was active at the Slack community. I relied on his fork for a while, nice to see it integrated on main.

But we did run into some scariness around trying to use Litestream that put me off it for the time being. Litestream is really cool (and I'm a big fan of its author!) but it is also very much a cool hack in the way it works.

The scariness I ran into was related to this issue https://github.com/benbjohnson/litestream/issues/510

And after a bit more fiddling I came away nervous about the possibility of corruption, which is not something I want to worry about from my backup method.

So for now we're just doing regular snapshotting with remote backups and that seems sufficient with RAID1 storage. I would like something lower latency at some point but don't have the time at the moment to work on it.

I use SQLite/Litestream for https://extensionpay.com! 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.

Happy to answer any questions you might have!

Short answer: Litestream was really easy to set up to replicate to Cloudflare's R2 (api is S3 compatible), and basically Just Worked.

That said, a handful of people's worth of data isn't exactly stressing its functionality; I have no idea how well it scales. I did some manual restores to make sure that it works, but I'm not (yet) doing regular verification tests on the data to ensure that there's been no data corruption (other than its own auto-checking, which of course could itself be broken).

There are a couple things I like about sqlite. First, somewhat simplified administration, as you don't need to set up a Postgres instance. I find it easier to test, because you just create test databases in temporary files and throw them away.

Secondly, the application I'm using has some data that's common and almost entirely read-only, and some data that's user-specific and read-write; sqlite makes it possible to divide these into different files, such that there's a single "shared.sqlite" database and each user has a "user.sqlite" database, and to join the two in a single "view" using the "attach" command. The data now is essentially pre-sharded: if I ever did need to scale past a single machine (or at some point if I want instances closer to where people), I'd just have to replicate the shared data and move the user-specific data to the appropriate instance, without any major refactoring or fancy synchronization needed.

The main downside of this is wrt litestream is that litestream's configuration file isn't really designed for this; you have to have a separate entry for each of these databases, and often you have to duplicate settings across them because most of the settings don't have a global setting you can make. At the moment, every time I add a user I have to regenerate an even-larger configuration file.

But that turns out to be fairly straightforward; and at some point I should be able to write my own daemon using the litestream golang package to do what I need to do, rather than using the litestream daemon.

It's mostly great. I love how easy it is to backup the database, mirror prod into my local environment for development, etc. Restoring things from backup is also quite nice: I can attach to the restored db and the prod db in the same sqlite session and update/insert the necessary records (w/ joins/etc.)

I'm starting to have to work around the single-writer limitation. I've got a web server and a set of background job workers. As my scale has increased, I've had to start working around write conflicts (I'm debating switching to postgres for this reason, but I know sqlite is working on concurrent writes, so I'm hoping they beat me to it).

I would adventure to use it in a small production environment if you can have user/customer based sharding of sqlites and then have a clickhouse/postgres for multiuser analytics store.

https://news.ycombinator.com/item?id=38793795