Tuesday, July 9, 2013

Connection Pooling vs. Persistent Connections

The distinction between connection pooling and persistent connections recently came up again with a client, and I realized that there's a significant amount of confusion in the industry due to deceptive feature naming by driver and ORM authors.  To set the record straight:

Persistent Connections are a feature of many drivers, driver frameworks and ORM tools, such as psycopg2, PDO, Hibernate and SQLObject, in which the driver keeps connection handles open for reuse by the same process or thread the next time it needs database access.  This reduces latency from database authentication and connection startup time.

Connection Pooling is a feature of network proxies and application servers, such as pgBouncer and various J2EE servers, in which a "pool" of persistent connections to the database are maintained and shared among multiple application servers, often allowing more than one application process to share the same database connection.  This not only decreases connection startup time, but allows for better management of a reduced number of database server connections, lowering the load on the database server.

So, why the confusion?  Well, in an instance of inflationary marketing, many driver/ORM authors have been calling their persistent connection feature "connection pooling".   No doubt this is out of a desire to compete with J2EE or other alternative platforms (certainly, in the case of the many Tomcat "poolers", it is), but the result is that users think they have connection pooling and are surprised to get this error message:

   FATAL connection limit exceeded non-superusers
 
Here's a tip: if the tool you're using doesn't come with configuration parameters for total number of database connections and database connection lifetime, or if it doesn't run a separate service, it's probably not a real connection pool.

No comments:

Post a Comment