Is there any reason to use a database connection pool with ActiveRecord?

Question

What are the benefits to using an external connection pool?

I've heard that most other applications will open up a connection for each unit of work. In Rails, for example, I'd take that to mean that each request could open a new connection. I'm assuming a connection pool would make that possible.

The only benefit I can think of is that it allows you to have 1,000 frontend processes without having 1,000 postgres processes running.

Are there any other benefits?

Answer 1

Rails has connection pooling built in:

1. Simply use ActiveRecord::Base.connection as with Active Record 2.1 and earlier (pre-connection-pooling). Eventually, when you’re done with the connection(s) and wish it to be returned to the pool, you call ActiveRecord::Base.clear_active_connections!. This will be the default behavior for Active Record when used in conjunction with Action Pack’s request handling cycle.
2. Manually check out a connection from the pool with ActiveRecord::Base.connection_pool.checkout. You are responsible for returning this connection to the pool when finished by calling ActiveRecord::Base.connection_pool.checkin(connection).
3. Use ActiveRecord::Base.connection_pool.with_connection(&block), which obtains a connection, yields it as the sole argument to the block, and returns it to the pool after the block completes.

This has been available since version 2.2. You'll see a pool parameter in your database.yml for controlling it:

pool: number indicating size of connection pool (default 5)

I don't think there would be much point in layering another pooling system underneath it and it could even confuse AR's pooling if you tried it.