Rails connections, pools and handlers

In Active Record there are db connections, connection pools and handlers. To put it shortly:

Connection Handler has many Connection Pools has many Connections

Connection

This is what you most often directly interact with. You can get hold of it via ActiveRecord::Base.connection and, for example, use it to execute raw SQL:

ActiveRecord::Base.connection.execute("select 1 as x").to_a
# => [{"x"=>1}]

If you’re on PostgreSQL and inspect connection‘s class, here’s what you get:

ActiveRecord::Base.connection.class
# => ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
ActiveRecord::Base.connection.class.superclass
# => ActiveRecord::ConnectionAdapters::AbstractAdapter

Connection pool

Connection pool is a bag with connections. Why do we need one? If you’re using threads (e.g. on Puma server), every thread needs a separate db connection. Otherwise their conversations with the db could mix up.

On the other hand, you want to control the number of db connections, because it typically increases the resources needed on the db server. That’s why there’s a pool of connections. If a thread wants to use AR, it gets a connection from the pool. If pool size is exceeded, ActiveRecord::ConnectionTimeoutError is raised. See Object pool pattern.

You can get hold of it via ActiveRecord::Base.connection_pool. It’s class:

ActiveRecord::Base.connection_pool.class
# => ActiveRecord::ConnectionAdapters::ConnectionPool

You can list all your active connections:

ActiveRecord::Base.connection_pool.connections
# => (an array with all active connections)

Note: this array can be empty - this can happen for example when you just started a console session and haven’t yet interacted with AR.

Max pool size is controlled by the pool option in config/database.yml. By default it can be overridden by ENV["RAILS_MAX_THREADS"]. You can check max pool size via:

ActiveRecord::Base.connection_pool.size                 # => 5

This number is not to be confused with the number of currently active connections:

ActiveRecord::Base.connection_pool.connections.size     # => 1

When there’s another connection in the pool? Open up a thread and talk to AR:

User.count
Thread.new { User.count }
ActiveRecord::Base.connection_pool.connections.size     # => 2

Note: you may want to read about with_connection method.

Connection handler

Now a plot twist: you can have many connection pools in your Rails app. Connection handler manages them.

ActiveRecord::Base.connection_handler.class
# => ActiveRecord::ConnectionAdapters::ConnectionHandler

When can there be multiple connection pools? For example when there’s an AR model which makes its own establish_connection - usually to another database. Note: each connection pool has its own max pool size.

You can use connection handler to get hold of all your pools:

ActiveRecord::Base.connection_handler.connection_pools
# => (an array with all connection pools)

So if you wanna traverse the whole hierarchy, you end up with:

ActiveRecord::Base
  .connection_handler
  .connection_pools.first
  .connections.first
  .execute("select 'Do not execute SQL on random connections' as helpful_hint")

Want to contribute to this blogpost?

You can do it right here!

More resources

Also, if you are looking for a team to hire - it might be an exceptional moment in our history to do so. We are typically fully booked and rarely jump on new projects, but we’re available for hire starting from Q3 2020. Drop us a line.

You might also like