HNHacker News
TopNewBestAskShowJobs

hruk

80 karma · joined January 20, 2024

submissionscomments
hruk··on How (and why) to run SQLite in production
But 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.
hruk··on Django SQLite Benchmark
We came to pretty much the same conclusions for our Django app. We ended up using the "connection created" signal to set SYNCHRONOUS=NORMAL, but I hope to see that supported natively in Django soon enough.

Honestly, this setup will keep us going for a long time. There are some design considerations that you need to keep in mind while structuring your Python code. For example, on a gp3 EBS volume, our mean single-write latency IN SQLite is about 100 microseconds, so in theory, if we can keep transactions short, the scale we can achieve on just SQLite is massive. We'll also get serialized isolation on writes and snapshot isolation on reads for "free." However, Django/Python are not super quick, so if we decorate all our Python functions that make writes with `@transaction.atomic` we end up with 10+ MILLIsecond locks on the DB, massively reducing throughput.

This is a price to pay for using SQLite, but honestly, even if we end up having to move to a proper RDBMS later, our database will probably be happier we wrote our code with transaction length in mind.

hruk··on Ask HN: Are you using SQLite and Litestream in production?
No, we're using EBS with Docker volumes, since all the containers are running on the same host.

SQLite actually performs very poorly on EFS in my experience (likely due to the fact that acquiring file locks is over the network).

hruk··on Ask HN: Are you using SQLite and Litestream in production?
I do not - however, I keep meaning to write up a generic version of our CDK code for sharing/reuse. I'll make a post here when I do.
hruk··on Ask HN: Are you using SQLite and Litestream in production?
Yes, we use this setup for two "branch" services. It is pretty easy to get going, but setting things up to achieve zero-downtime deploys requires a little thought.

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.

← PreviousPage 2 of 2