Rails connections, pools and handlers
… and check why 5600+ Rails engineers read also this
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
The connection object is the one 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 <<~SQL
SELECT 'Do not execute SQL on random connections'
AS helpful_hint
SQL
Want to contribute to this blogpost?
You can do it right here!