hruk

bruh

Roughly 80/20 read to write. On the instance's gp3 EBS volume (which is pretty slow), we've pushed ~700 write transactions per second without much problem.

This is just untrue - the naive implementation (make the API call, write a single row to the db) will work fine, as transactions are quite fast on modern hardware.

What do you consider "serious" work? We've served a SaaS product from SQLite (roughly 300-500 queries per second at peak) for several years without much pain. Plus, it's not like PG and MySQL are pain-free, either - they all have their quirks.

This is basically Breiman's "two cultures" at play. Do you care about optimizing y-hat, or do you care about doing inference on some parameters in your model? Depends on the business case, typically.

You can do fairly well here with ridge regression as a poor man's hierarchical model. We've used this library's Bayesian ridge regression to support a geo-pricing strategy (and it contains the Dirichlet-Multinomial approach as well): https://github.com/bayesianbandits/bayesianbandits

All databases are painful in their own way. I've used all three at various times in my career, and I think SQLite behaves quite predictably, which has made it a lot easier for me personally to administrate.

If I had to start something new, I'd use SQLite until at least high 5 digit queries per second. Maybe more.

Agree on many things here, but SQLite does support WAL mode which supports 1 writer/N writer readers with snapshot isolation on reads. Writes are serialized but still quite fast.

SQLite (actually SQL-ite, like a mineral) maybe be light, but so are many workloads these days. Even 1000 queries per second is quite doable with SQLite and modest hardware, and I've worked at billion dollar businesses handling fewer queries than that.

We've used this Python package to do this: https://github.com/bayesianbandits/bayesianbandits

Yep, we use Litestream. It's been very reliable.

FWIW, I've been running a system with roughly 100K users, about 25 qps on average, with a single SQLite file for several years. No issues with data.

We've used this library for Bayesian contextual bandits in production (we have a critical business use case supported by a ~200K feature sparse Linear UCB bandit). It's a small community, but it's also a small enough codebase that we've read through all of it and feel fine about maintaining it ourselves in case it goes inactive.

https://github.com/bayesianbandits/bayesianbandits

We do zero-downtime deployments with a single Docker volume containing the db. Spin up a container running the new code, wait til it's healthy, then kill the old container.

We have some analytics queries powered by Airflow running on a separate box. We set up Litestream to replicate to that box via SFTP, and then dump changes hourly into Snowflake.

You are still correct for network filesystems, but there are no issues opening multiple connections on the same host (even across OCI container boundaries).

You can have as many connections from as many processes as you want.

Yes, we've used the Litestream + SQLite + Go webserver route in production for several years and while we've had to deal with all of these issues, on the whole I don't have a strong argument for going back and doing it differently.

I'm curious what issues the author had with high availability - I totally understand the issue in theory, but in practice our SQLite-backed service has had 6 minutes of downtime in the last 3 years when we were upgrading the host. That works out to being quite a few 9s.

I do agree with the article on a few points - distributed SQLite doesn't really seem all that worthwhile to me (yet, anyway).

No, you definitely have to pick your battles. Something that you want to continuously optimize over time makes a lot more sense than something where it's reasonable to test and the commit to a path forever.

Indeed, we are well aware.

I don't know, all of these are pretty surmountable. We've done dynamic pricing with contextual multi-armed bandits, in which each context gets a single decision per time block and gross profit is summed up at the end of each block and used to reward the agent.

That being said, I agree that MABs are poor for experimentation (they produce biased estimates that depend on somewhat hard-to-quantify properties of your policy). But they're not for experimentation! They're for optimizing a target metric.

We've been happy using Thompson sampling in production with this library https://github.com/bayesianbandits/bayesianbandits

Nah, just SQLite. I had to learn a number of things about good SQLite defaults and habits, but that was years back before it really started to take off in this space. Now, there are tons of blogs you can refer to giving reasonable advice.

The highest-traffic site I run off of SQLite gets roughly 1 million hits per day. I run it on a VPS with 2vCPU and 4 GB RAM and it hovers around ~8% CPU utilization and 12% memory usage during peak hours. Last I checked, the database is handling about ~80 queries per second during peak usage, and clearly there's a ton of headroom.

If the site got 10x as much traffic, maybe I'd double the instance size. If it 100xed, maybe I'd need to upgrade from storing the DB on an EBS volume to storing it on a NVME drive. If it 1000xed... maybe I'd have problems? That seems like a pretty long way away, and in the meantime I get super simple deployment, replication, disaster recovery, and an easy route to pull down snapshots for debugging, etc.

I use SQLite for this sort of thing (small e-commerce storefront, wife's niche popular blog, etc.). Actually, I use multiple - one for the DB and Diskcache for the cache.

Caching is cheap, backup is trivial and cheap, and pulling down a perfect snapshot of production when I need to fiddle with something is also trivial and cheap.

Indeed. I've found that as long as you store your database on a modern NVMe drive, you can easily push 5K+ write transactions per second with SQLite. I've worked at very large companies who received an order of magnitude fewer writes than that, and only during peak business hours.

You can have multiple containers on the same host use the same db via Docker volumes - we have a number of production services set up this way.

I've used HTMX quite frequently recently for internal frontends (admin pages, reporting, etc.). It's quite good.

A pattern I've been using a lot is not changing the backend at all - each endpoint still returns the full page. Each hypermedia control makes liberal use of hx-select and hx-select-oob to update the elements that may have changed.

This isn't super efficient, but it provides some "locality of behavior" in that you can look at a hypermedia control and know what other elements might be changed by it. It also means the backend doesn't really need to "know" about HTMX.

S3, yes. Litestream doesn't store the backup as a single DB file - rather, as snapshots and WAL segments that are streaming in, so it's unfortunately not so simple.

I have not, but I keep meaning to collate everything I've learned into a set of useful defaults just to remind myself what settings I should be enabling and why.

Regarding Litestream, I learned pretty much all I know from their documentation: https://litestream.io/

Interesting question. Our services currently contain "own" all of cron jobs, so the jobs run as background tasks on the host containing the db. I think we used Huey for the Django app, and just some CLI applications fired via systemd timers for the Go services.

Every service is running on a single (somewhat meaty) host, so we get true snapshot isolation on reads and serialized isolation on writes without doing anything extra. We run weekly tests to determine how long AWS takes to spin everything back up upon catastrophic failure, and it's between 6 and 10 minutes, depending on how much data the service has. It does bother me that this can only get longer, but we've never actually had a host crash in production.

Like the other commentator, we did our BI for a while by restoring the Litestream backup to another server. Then that started to get expensive (because the file was very large), so we just added a scraping endpoint to each table. I think there's probably a more elegant way of doing this.

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.

p99.9 referring to latency. However, we also do a weekly test of how quickly we recover from a catastrophic crash, which is roughly about 6 minutes (which is the amount of time it takes for the autoscaling group to spin up a new host, Litestream to restore the database from s3, and the server to start up again).

Honestly, 99.9% uptime is pretty generous - we can fit in quite a few catastrophes per year and still have 99.9% uptime. In the 2 years this service has been running, we've had 100% uptime via zero-downtime deployments, anyway.

In terms of monitoring, traces and error logs are shipped to our observability solution, yes.