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 full graph, showing the overall performance improvement after switching to SQLite and upgrading Ruby.

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

This graph shows the performance of only Postgres and SQLite. SQLite is noticeably faster.

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.

This graph shows the performance of Ruby 3.0 and 3.1. Ruby 3.1 is slightly but noticably faster.

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 ;)

Posted on 2022-07-20 09:10 PM -0600
Contact
hello(at)zachahn(dot)com
© Copyright 2008–2022 Zach Ahn