Author here: these are my original speaker notes and slides stitched together for the talk that I gave at Rails World 2024 in Toronto at the end of September. There is a link to the YouTube video at the top of the post. Unfortunately, the video doesn't display my slides as much as I was hoping, so I wanted to publish this post so that people can get the full message, as the slides are an important facet of the message.
Rails works with a connection pool and multiple threads processing incoming requests. Not a problem. In fact, Rails has one of the very best tunings to use SQLite in production in the context of a web application. And yes, you can also start a console session in production while your app is running. Just maybe don't make a long-running write query from within that console ;)
As I explain in the post, if you have multiple connections and consistent write load, the timeout will penalize older quereres and noticeably harm your long tail latency
This is my perspective as well. You certainly can horizontally scale with SQLite, but I strongly recommend that you vertically until you hit an actual limit there. If you know you will absolutely need multiple app nodes on day 1 or day 10, I think you will probably be better served by choosing a client/server database like MySQL or PG instead.
So, you aren’t limited to single machine, but you should stay single machine as long as possible and extract as much value from that operational simplicity before you trade that simplicity for some kind of horizontal scale
> If you know you will absolutely need multiple app nodes on day 1 or day 10
Yes, but there are a few options even then. First, you can of course tune http caching etc, find traditional bottlenecks.
Second, you can also break the business logic into a separate API endpoint that runs only SQLite + business logic + API responses. Then you can add more frontend nodes in case rendering and other things are most expensive.
The main downside is all logic practically has to be written in the same language as a monolith.
Very true. There isn’t an actual limit. You can horizontally scale with SQLite if you want to or need to. I just think it is worth pushing vertical scaling as far as possible as long as possible. And I don’t actually believe that SQLite is the right tool for every problem or web app. Some apps absolutely should use managed PG/MySQL or serverless PG/MySQL. I think they are the statistical exception and 80% of web apps would be well served with SQLite. But for the other 20%, probably simpler to just start with PlanetScale
I’m very excited that yes indeed we have the four major pillars in Rails 8, which is releasing soon, but can be used now via the main branch. The default, out-of-the-box experience with Rails 8 will go all in on SQLite, a database will be the only dependency, and you will have a production-ready app with jobs, cache, web sockets, and a primary database ready from `rails new`. I’ll be talking more about this all at Rails World in a couple weeks and that talk will be on YouTube sometime after that. But exciting times are ahead for Rails, for sure.
You can read more discussion here: https://github.com/sparklemotion/sqlite3-ruby/pull/528 and here: https://github.com/digital-fabric/extralite/pull/46 to see how it was validated that simply releases the GVL for every `step` in the SQLite VM majorly hurts single-threaded performance. Finding a middle ground for both single threaded and multi-threaded performance is tricky. In Rails, we know it is multi-threaded because of the connection pool. But the lower level gem is used in many other libraries and tools where it is used in a single threaded environment
This presentation is focused on the use-case of vertically scaling a single server and driving everything through that app server, which is running SQLite embedded within your application process.
This is the sweet-spot for SQLite applications, but there have been explorations and advances to running SQLite across a network of app servers. LiteFS (https://fly.io/docs/litefs/), the sibling to Litestream for backups (https://litestream.io), is aimed at precisely this use-case. Similarly, Turso (https://turso.tech) is a new-ish managed database company for running SQLite in a more traditional client-server distribution.
Yep, that was an errant duplication. I did proofread, but how is it that one thing always seems to sneak through anyway? Thanks for the catch. Fixing now.
Happy to talk anything about Rails 8 and SQLite.