Switching to SQLite📝

I’ve been using Postgres for my website, but I started wondering if switching to SQLite would improve its stability and performance.

• I’d been getting occasional errors where the Rails server wouldn’t be able to connect to the Postgres database
• I’d heard that SQLite can be faster than Postgres. At the very least, there’s no overhead of communicating over a network
• I knew that SQLite was very robust, fast, and stable. I doubt I’ll ever outgrow SQLite!

Although I didn’t run any real world benchmarks, I was pretty confident about what improvements SQLite would bring, so I decided to go for it.

Process

I’ve done a lot of zero-downtime deploys and data migrations, but that’s what I do at work. The main complexity is that users may write to the database throughout the process, but since I’m the only user with write access, I was able to take some liberties with the process.

I used Rails’ multi-database support to connect to the old and new databases, and I copied the data from one to the other.

The only challenges I really came across were the following:

• SQLite didn’t have nearly as many date/time related functions as Postgres, so I had to move some of that behavior into application code
• GoodJob, the ActiveJob backend I’d been using, used Postgres-specific features. I thought it would be fun to write my own
• Unrelated, but I took this time to switch from ActiveStorage to a home grown solution—just 49 lines of code and no third-party libraries! I knew ActiveStorage was one of my slower endpoints, so I’d been looking forward to do something about it

Results

Like I mentioned, I didn’t have any real-world benchmarks. But thankfully, I came across some charts in New Relic (I’m on the free tier, and lol, not an ad).

Below, you can see that the graph goes down a little at around the middle of the graph. (Lower is faster and better in all of the graphs below.)

The change is a bit more visible when looking at just Postgres and SQLite.

I do have to admit though that these results are confounded because I upgraded from Ruby 3.0 to Ruby 3.1 at the same time.

I was happy to see that switching to SQLite did improve performance, like I predicted. It’s too early to say if stability improved too. I do think it’s worth considering if SQLite fits your use-case. The answer might be an easy “no”; like I said, it does have its limitations.

I was surprised and happy to see that upgrading Ruby came with performance improvements. Definitely upgrade your Ruby versions! They don’t usually come with breaking changes.

Oh, and also, take a look at Disqualified. It’s a simple code base, and it could use lots of improvements. And it might even be useful for you if you too are using SQLite in production ;)