Using ActiveRecord with threads might use more database connections than you think

Database connections are not thread-safe. That's why ActiveRecord uses a separate database connection for each thread.

For instance, the following code uses 3 database connections:

Copy3.times do
  Thread.new do
    User.first # first database access makes a new connection
  end
end

These three connections will remain connected to the database server after the threads terminate. This only affects threads that use ActiveRecord.

You can rely on Rails' various clean-up mechanisms to release connections, as outlined below. This may cause your application to open more connections than your database server allows. If you don't want to risk that, you can also manually release connections once your thread is done.

How can I exceed connection limits?

There are two limits to consider:

• Your database server has a limit how many open connections it will allow in total.
• You can configure the maximum number of connections for each Rails process. This is called the size of your connection pool.
• The default pool size is 5. You can configure the pool size with a key like pool: 3 in your database.yml.

Every Rails process has its own connection pool, allowing the configured maximum number of connections. Note that an application worker process, a Rails console or a Sidekiq process each counts as its own process.

You can calculate the maximum number that your application could theoretically use at the same time. For example, let's say we have:

• 2 application servers
• 6 Rails worker processes in each application server
• 1 background job server with Sidekiq ( will spawn 10 threads by default Show archive.org snapshot )

When your code does not use ActiveRecord in threads you may use the following number of connections:

Copy(2 app servers * 6 app workers) + (1 job server * 10 sidekiq workers) = 12 + 10 = 22 connections

When your code does use ActiveRecord from up to 4 threads in parallel, you may reach the following number of connections:

Copy((2 app servers * 6 app workers) + (1 job server * 10 sidekiq workers)) * 4 threads = (12 + 10) * 4 = 88 connections

This calculation is a maximum. In practice the number will often be lower, as there are various automatic clean-up mechanisms.

When will inactive threads release their connections?

Database connections will be released when one of the following events occur:

After every request

When Action Pack handles a HTTP request it will automatically release connections of inactive threads.

When another thread needs a new connection and the pool is empty

When a thread tries to check out a new connection and the connection pool is exhausted, Rails will release connections from inactive threads before giving up.

If there are no connections from inactive threads, ActiveRecord::ConnectionTimeoutError is raised.

Every 60 seconds

The connection pool has a "reaper thread" that periodically looks for connections from inactive threads and returns them to the pool.

By default the reaper runs once every 60 seconds. You can configure a different interval by setting the reaping_frequency key in your database.yml

When you manually release the connection.

If you don't want to rely on any of the automatic clean-up mechanisms above, you can explicitely release the connection from your thread.

To do so, call clear_active_connections! before your thread terminates:

CopyThread.new do
  begin
    User.first # first database access makes a new connection
  ensure
    ActiveRecord::Base.clear_active_connections!
  end
end

When the Rails process terminates

E.g. when you stop the server or console.

Will the pool ever close an unused connection?

A released connection will be returned to the pool, but not disconnect from the database server. The connection will remain connected in the pool, waiting for a new thread to request a connection. Hence the connection will count against your database server's maximum number of connections.

Only when a connection in the pool remains unused for more than 5 minutes, ActiveRecord will actually close the connection. You can configure this by setting the idle_timeout in your database.yml.