[sudhirj]

Pooling is more a problem with larger frameworks like Rails(Ruby) where a connection is automatically checked out of the client pool for you on every request and held till the end of the request. If you're doing other work on the request, like network calls or non-DB related slow stuff, you'll quickly run out of connections.

A separate pooler makes sense here because it'll let Rails imagine it has how many ever thousands of connections it needs open, but map them to real connections only when usage actually happens.

This is easier than taking over connection management manually in the code, which the only other option.

[needusername]

How does this work? I always assumed that you can have only one transaction open per session/connection (yes there are subtransaction but I don't see how these could be used for multiplexing independent transaction over the same session.).

[sudhirj]

Can't multiplex transactions, but if you're doing long running work inside transactions you have bigger problems.

The Rails will checkout a connection for you, but it won't necessarily start a transaction if you don't ask it to. Think there's some magic involved there, but generally transactions don't start unless necessary or they're initiated by code.

[needusername]

Uh oh. I naturally assumed Rails runs every request in a transaction because that's obviously the default you want.

[sudhirj]

Not really. Though this should be trivial to do - just start and commit in the base controller life cycle hooks - I would never want or recommend it as he default. Too many things can go wrong with out of hand work, sequencing, assuming that hooks will happen when you do call save methods etc.

[needusername]

Why would you not have that as a default? What things can go wrong? Not having it means you have to think about transactions in every method or you accept database inconsistency.