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
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
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
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.
This looks like it could be a big deal for Rails developers. Increased speed to build standard UI elements, completely control over the look and feel, plus increased rendering speed due to Phlex. I can only imagine how much time this has taken and will take to build. Thanks for such an awesome contribution to the space!
Yes, the .sqlite file needs to be persisted across deployments. It is fairly common for the /storage directory to be persisted across deployments. The Kamal deployment tool will use this directory. The Hatchbox.io service likewise uses a persisted /storage directory.
This doesn’t mean that you have to have manual deployments tho. You simply need a file system and some persisted storage, which you should already have as most deployment setups don’t want to nuke and pave every static asset on every deployment.
When you open your database connection in WAL journal mode, you can have multiple concurrent readers. This is the new default for all Rails applications. WAL mode doesn’t allow concurrent writers, but you are often talking about milliseconds to wait for the busy connection to resolve, and SQLite retries for you invisibly.
Happy to talk anything about Rails 8 and SQLite.