Rails Database Connection Pooling Explained

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Without connection pooling (this is what would happen internally)
def handle_request
  # This takes 20-50ms each time!
  connection = PG.connect(
    host: 'localhost',
    dbname: 'myapp_production',
    user: 'rails',
    password: 'secret'
  )

  result = connection.exec("SELECT * FROM users WHERE id = 1")
  connection.close

  result
end

1
2
3
4
5
6
# config/database.yml
production:
  adapter: postgresql
  database: myapp_production
  pool: 5      # Maximum number of connections in the pool
  timeout: 5000 # Wait up to 5 seconds for a connection

1
2
3
4
5
6
7
class UsersController < ApplicationController
  def show
    # ActiveRecord automatically checks out a connection from the pool
    @user = User.find(params[:id])
    # Connection is automatically returned to the pool after the request
  end
end

1
2
3
4
5
6
7
# Simplified version of what ActiveRecord does
def with_connection(&block)
  connection = connection_pool.checkout  # Get connection from pool
  yield connection                       # Use it for queries
ensure
  connection_pool.checkin(connection)    # Return to pool
end

1
2
3
4
5
6
7
def show
  # Thread checks out connection at request start
  @user = User.find(1)        # Uses connection A
  @posts = @user.posts        # Still using connection A
  @comments = Comment.recent  # Still using connection A
  # Connection returned at request end
end

1
2
3
4
5
def show
  @user = User.find(1)        # Uses connection A, returns it immediately
  @posts = @user.posts        # Might use connection B, returns it immediately
  @comments = Comment.recent  # Might use connection C, returns it immediately
end

1
2
3
4
5
6
# config/database.yml
production:
  adapter: postgresql
  database: myapp_production
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

1
2
3
4
5
6
7
8
9
10
11
12
# This configuration:
production:
  pool: 100

# Does NOT mean:
# ❌ "Create 100 database connections on startup"
# ❌ "Always maintain 100 open connections"

# It actually means:
# ✅ "Allow up to 100 connections IF needed"
# ✅ "Create connections lazily as demand requires"
# ✅ "Automatically close idle connections"

1
2
# This error means all connections are in use
ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds

1
2
3
4
5
6
7
8
9
10
11
12
# Check current pool status
ActiveRecord::Base.connection_pool.stat
# => { size: 5, connections: 5, busy: 5, dead: 0, idle: 0, waiting: 2, checkout_timeout: 5 }

# See all connections and what they're doing
ActiveRecord::Base.connection_pool.connections.map do |conn|
  {
    in_use: conn.in_use?,
    owner: conn.owner,
    last_query: conn.instance_variable_get(:@last_query)
  }
end

1
2
3
4
5
6
7
# config/puma.rb
threads_count = ENV.fetch("RAILS_MAX_THREADS") { 5 }
threads threads_count, threads_count

# config/database.yml
production:
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>  # Should match!

1
2
3
4
5
6
7
8
9
10
11
12
13
# BAD: Connection leak!
def process_large_dataset
  conn = ActiveRecord::Base.connection_pool.checkout
  conn.execute("SELECT * FROM huge_table")
  # Forgot to check in the connection!
end

# GOOD: Proper connection handling
def process_large_dataset
  ActiveRecord::Base.connection_pool.with_connection do |conn|
    conn.execute("SELECT * FROM huge_table")
  end  # Connection automatically returned
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# config/database.yml
production:
  primary:
    adapter: postgresql
    database: myapp_production
    pool: 25

  analytics:
    adapter: postgresql
    database: myapp_analytics
    pool: 10

# app/models/analytics_base.rb
class AnalyticsBase < ApplicationRecord
  self.abstract_class = true
  connects_to database: { writing: :analytics, reading: :analytics }
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# app/middleware/connection_pool_monitor.rb
class ConnectionPoolMonitor
  def initialize(app)
    @app = app
  end

  def call(env)
    pool = ActiveRecord::Base.connection_pool
    
    @app.call(env)
  ensure
    stats = pool.stat
    
    # Focus on actual connections, not pool limits
    if stats[:connections] > 50  # Arbitrary threshold
      Rails.logger.info "High connection count: #{stats[:connections]} actual connections"
    end
    
    # Check for connection leaks
    if stats[:dead] > 0
      Rails.logger.warn "Dead connections detected: #{stats[:dead]}"
    end
  end
end

1
2
3
4
5
6
# config/database.yml
production:
  adapter: postgresql
  database: myapp_production
  pool: 100  # Set it high and forget about it
  timeout: 5000

1
2
3
4
5
6
7
8
9
10
# Check PostgreSQL max connections
ActiveRecord::Base.connection.execute("SHOW max_connections").first
# => {"max_connections"=>"100"}

# Monitor actual connections in use
ActiveRecord::Base.connection.execute("
  SELECT count(*) FROM pg_stat_activity 
  WHERE datname = 'myapp_production'
").first
# => {"count"=>"23"}  # Only 23 connections actually created!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# config/database.yml
production:
  primary:
    adapter: postgresql
    database: myapp_production
    pool: 15

  primary_replica:
    adapter: postgresql
    database: myapp_production
    host: replica.example.com
    pool: 10
    replica: true

# Queries automatically use replica for reads
User.where(active: true).to_a  # Uses replica
User.create!(name: "New")       # Uses primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# config/initializers/connection_monitoring.rb
module ConnectionMonitoring
  def self.check_database_connections
    # Monitor actual connections at the database
    result = ActiveRecord::Base.connection.execute("
      SELECT count(*) as total,
             count(*) FILTER (WHERE state = 'active') as active
      FROM pg_stat_activity
      WHERE datname = current_database()
    ").first
    
    StatsD.gauge('db.connections.total', result['total'])
    StatsD.gauge('db.connections.active', result['active'])
    
    # Alert on database limits, not pool limits
    max_conn = ActiveRecord::Base.connection.execute(
      "SHOW max_connections"
    ).first['max_connections'].to_i
    
    if result['total'] > max_conn * 0.8
      Rails.logger.warn "Approaching database connection limit: #{result['total']}/#{max_conn}"
    end
  end
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# spec/connection_pool_spec.rb
RSpec.describe "Connection Pool" do
  it "handles concurrent requests without exhaustion" do
    threads = []
    errors = []

    20.times do
      threads << Thread.new do
        begin
          User.connection_pool.with_connection do
            User.count
            sleep 0.1  # Simulate work
          end
        rescue ActiveRecord::ConnectionTimeoutError => e
          errors << e
        end
      end
    end

    threads.each(&:join)

    expect(errors).to be_empty
    expect(User.connection_pool.stat[:waiting]).to eq(0)
  end
end

1
2
3
4
5
# Old approach - trying to calculate the "perfect" size:
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

# Modern approach - set it high and let Rails manage:
pool: 100

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Monitor actual database connections
ActiveRecord::Base.connection.execute("
  SELECT state, count(*) 
  FROM pg_stat_activity 
  WHERE datname = current_database()
  GROUP BY state
")
# => [{"state"=>"active", "count"=>3}, {"state"=>"idle", "count"=>20}]

# Check if approaching database limits
ActiveRecord::Base.connection.execute("
  SELECT setting::int - count(*) as connections_available
  FROM pg_settings, pg_stat_activity
  WHERE name = 'max_connections'
  GROUP BY setting::int
").first
# => {"connections_available"=>77}