Quick Tip: Fix ActiveRecord Connection Pool Errors For Good

You’ve almost certainly seen this error, perhaps more times than you can count.

In this quick tip article, we’ll dissect what causes the error and what you can do to fix it.

What causes an ActiveRecord::ConnectionTimeoutError?

The key word in the error message is “pool”:

could not obtain a connection from the pool within 5.000 seconds; all pooled connections were in use

Each Rails process maintains a pool of database connections. Why not just one connection? Because you’re probably running multiple threads in each Rails process, and you don’t want those threads contending for a single database connection.

The ConnectionTimeoutError is saying the pool doesn’t have enough connections. The error has nothing to do with your database. It’s a Rails configuration issue.

If you’re seeing this error, you likely need to increase your pool size — you need more connections available for your threads!

How do you ensure an optimal ActiveRecord connection pool size?

By default, the pool size is specified in database.yml, which looks like this:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

This means your connection pool is equal to the RAILS_MAX_THREADS environment variable. This is a good thing, because the default puma.rb uses the same environment variable to specify the number of threads:

threads_count = ENV.fetch("RAILS_MAX_THREADS", 5)
threads threads_count, threads_count

So, while these defaults should ensure that this error never arises from your web processes (though flukes do happen), what about worker processes? Luckily our ultimate solution will cover both, so read on to find out!

Understanding Sidekiq concurrency

The ActiveRecord::ConnectionTimeoutError most often occurs in a worker process, and the culprit is Sidekiq concurrency. You can set Sidekiq concurrency in a few ways:

• Specify concurrency in a sidekiq.yml configuration file.
• Specify concurrency via the -c command line flag when starting Sidekiq.
• Specify concurrency via the RAILS_MAX_THREADS environment variable.

If you do none of the above, concurrency defaults to 5 threads.

So what does “concurrency” really mean here? Sidekiq concurrency is the number of threads in your Sidekiq (Rails) process. And remember from above that we need our connection pool to have enough connections for all of our Rails threads.

If you’re seeing ActiveRecord::ConnectionTimeoutError, it’s very likely you’ve specified a Sidekiq concurrency that’s larger than your connection pool. This is likely via the sidekiq.yml config file or the -c command line flag. But wait!

⚠️⚠️ The right answer is not to simply change your Sidekiq concurrency!

The best answer is not to simply get your pool count and Sidekiq concurrency to match, either:

Let’s get the best answer to solving this problem.

Fixing your database connection pool for Sidekiq

So we’ve found our issue — our database.yml file, by default, sets our connection pool equal to RAILS_MAX_THREADS, but our Sidekiq process spins up more than RAILS_MAX_THREADS number of threads. We’ve got too many Sidekiq threads contending for not enough connections in the database pool!

The good news here is that the answer is extremely simple and fixes all connection pool issues for both our web processes and background worker processes: manually set the pool value to a large number, one way beyond any number of threads you’d ever run in a Sidekiq process. Like 200. 200’s a great number:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: 200

And, seriously, your ActiveRecord::ConnectionTimeoutErrors will end! As a side-benefit, you also no longer need to worry about your web-process thread-count and your worker-process thread-count having any bearing on your database connection pool! They can both operate independently. Neat 😎

The reason this works is because Rails processes don’t actually pre-reserve any connections in their connection pool. Instead, connections are checked out as needed and checked back in once the work is done (e.g. at the beginning and end of a job). Ultimately this makes our pool: value more like a maximum than a reserved count. That means the number of connections in actual use at any given moment will likely be around the number of threads our Sidekiq or Rails processes are running.

This isn’t to say you’ll be error-free forever with this change, it just shifts the conditions for an error and which error we’ll see. Instead of our Rails / Sidekiq processes throwing an error for “the pool is all used right now and I can’t get a connection”, we instead could see an error directly from the database that says, “another thread is trying to connect but I don’t support that many connections!” (“I’ve run out of connections”). This is a wholly different issue.

Databases only support so many connections at any given time. For example, Heroku Postgres offers a different number of maximum concurrent connections depending on which service tier you pay for:

Standard-0	Standard-2

That said, there are two points of reality that we should touch on here.

First, sometimes you just need to upgrade your database! If your app has gotten to a size where you simply don’t have any more database connections to work with, you might simply need to upgrade. You could just need more horsepower!

Second, the strategy of hard-coding your pool: value to a large number doesn’t change that you have a database-side problem going on anyway. If you’d left your pool: value as it was or changed it in some other way you’d just be obscuring your need for a real database upgrade by just getting ActiveRecord::ConnectionTimeoutErrors instead!

Ahead of the Curve

The last thing worth mentioning here is that this strategy (hard-coding a high value) is generally in line with what Rails itself will likely do: abolish an upper limit altogether. Per Rails PR 51073’s comments from core team maintainers:

Also this should be moot soon … we want the pool not to have a limit by default anymore

Meaning that it’s likely the connection pool will soon be unbounded altogether!

Until then, 200 essentially represents “no upper limit”, so we’ll keep it there 😎