Scaling Connections in PostgreSQL Using Connection Pooling

Opening a database connection is an expensive operation, and connection pooling is used to keep database connections open so they can be reused. This avoids having to repeatedly open network sessions, authenticate and check authorisation. Pooling keeps the connections active so that, when a connection is later requested, one of the active ones is used rather than having to create one from scratch.

Connection Pooling

Connection pooling has become one of the most common methods of handling database connections prior to a query request. We normally think that a connection to the database is fast but that ain’t the case especially when a large number of clients are connecting. Without connection pooling, a request would take up to 35-50 ms to connect but 1-2ms if connection pooling is employed. Connection pooling is therefore preallocating database connections, and then recycling them when new clients are connecting

Reasons for Connection Pooling

1. To avoid crashing your server. PostgreSQL servers are limited to a number of clients they handle at a time depending on the memory parameter. If this number is surpassed, then you will end up crashing the server. With connection pooling, clients use a set number of connections.
2. Facilitate query processing. Normally, database requests are executed in a serial manner with a criterion of first-in first-out. With a large set of clients, this would take ages to achieve this process. Therefore the approach should be to make a single connection with pipelined requests which can be executed simultaneously rather than each at a time.
3. Improve on security. Often a connection involves a handshake that may take 25-35 ms average over which an SSL is established, passwords are checked and sharing of the configuration information. All this work for each connected user will result in extensive usage of memory. However, with connection pooling, the number of connections is reduced hence saving on memory.

Types of Connection Pooling

There are basically two types of connection pooling, however, there is a third type work-around that acts like a connection pooling strategy known as persistent connections.

Persistent Connection Pooling

This approach intends to keep an initial connection active from the time it is initiated. It doesn’t fully hold the connection pooling features but well enough to provide some continuous connection. It is quite helpful for a small set of client connections whose overhead may range between 25-50 ms. A limitation with this approach is that it is limited to a number of connections to the db with normally a single connection per entry to the server.

Framework Connection Pooling

Framework connection pooling occurs at an application level whereby, whenever your server script is started, a pool of connections is established to handle query requests that will be arriving later.

Standalone Connection Pooling

For every connection to the database, an overhead memory between 5 to 10 MB is used up to cater for a query request. This is not quite good for a large number of connections. Using the framework connection pooling can be limited by this number of connections as it may encounter a large memory size usage. We thus opt to use the Standalone connection pooling which is configured in accordance to Postgres sessions, statements and transactions. The core advantage associated with this approach is: minimal overhead cost of about 2kb for every connection.

When you create a connection pooling class, it should meet the following factors for an increased database performance:

1. Preallocate the connections
2. Oversee connections that are available
3. Assign new connections
4. Wait for a connection to be available
5. Close connection

Preallocating the Connections

Ensuring more connections in advance will facilitate requests handling at the point when the application has been started. For example if your server is developed with Java, you can use vectors to store available idle connections using the code below.

availableConnections = new Vector(connections); 
busyConnections = new Vector();
for(int i=0; i

public synchronized Connection getConnection() throws SQLException {
if (!availableConnections.isEmpty()) { Connection existingConnection =
(Connection)availableConnections.lastElement(); int lastIndex = availableConnections.size() - 1; availableConnections.removeElementAt(lastIndex); if (existingConnection.isClosed()) {
notifyAll(); // Freed up a spot for anybody waiting.
return(getConnection()); // Repeat process. } else {
busyConnections.addElement(existingConnection);
return(existingConnection); }
} }

if ((totalConnections() < maxConnections) && !connectionPending) { // Pending = connecting in bg
makeBackgroundConnection(); }
try {
wait(); // Give up lock and suspend self.
} catch(InterruptedException ie) {} return(getConnection()); // Try again.

try {
     wait();
} catch(InterruptedException ie) {} 
return(getConnection());

throw new SQLException("Connection limit reached");

public synchronized void closeAllConnections() {
// The closeConnections method loops down Vector, calling // close and ignoring any exceptions thrown. closeConnections(availableConnections); availableConnections = new Vector(); closeConnections(busyConnections);
busyConnections = new Vector();
}