CONNECTION POOLING IN POSTGRESQL

What is Connection Pooling?

Pooling in context of databases is similar to any other real life example of pooling. Pooling in general refers to reusability of resources.
Similarly connection pooling is a method to increase the reusability of database connections to avoid the overhead of excess database connections and improve the request and response time of a database.

Need Of Connection Pooling:

Connection Pooling is required to enhance the performance of database. If a database opens and closes connection for every single request, the database be occupied for most of the times, thus increasing the response time which can create timeout situations, delayed response and other issues. To deal with it, caching of database can be performed.
Instead of opening and closing connections for every request, connection pooling will create cache of database which will manage the database traffic efficiently and optimize the performance of database.

Connection Pooling With Psycopg2:

Psycopg2 provides us with four classes that can be used to implement connection pooling:

The SimpleConnectionPool, ThreadedConnectionPool and PersistentConnectionPool are the subclasses of AbstractConnectionPool

1. AbstractConnectionPool:

As the name suggests, it is an abstract class. All the other classes can inherit from this class and need to implement its methods.
To implement customized pooling, we can use this class to implement its method according to our own usecase.

Python3

import psycopg2

class psycopg2.pool.AbstractConnectionPool(minconn, maxconn, *args, **kwargs):
    pass

minconn:- minimum numbers of connections required
maxconn:- maximum numbers of connections supported

Available methods that can be implemented by subclasses:

getconn(key=None)

This method can be used to get a free or available connection from the pool.
The optional parameter key can be used to associate it with a connection. Calling the method with same key again will return the same connection again.

putconn(conn, key=None, close=False)

This method is used to put away or return a connection to the pool.
The key parameter should be used consistently with getconn(). If close parameter is set to true, the connection will be discarded from the pool that is it will be closed.

closeall()
This method is used to simply close all the connections from the pool.

2. SimpleConnectionPool:

As mentioned above, it is a subclass of AbstractConnectionPool and can be used readily.
This pool class can be used only for single-threaded applications i.e it can’t be shared across different threads.

Python3

class psycopg2.pool.SimpleConnectionPool(minConnection, maxConnection, *args, **kwargs):
    pass

3. ThreadedConnectionPool:

Another subclass of AbstractConnectionPool, that can be used in a multi-threaded environement. This class can be implemented if we have a multi-threaded application thus providing more flexibilty over the SimpleConnectionPool.

Python3

class psycopg2.pool.ThreadedConnectionPool(minconn, maxconn, *args, **kwargs):
    pass

4. PersistentConnectionPool:

It is also a ready-to-use subclass of AbstractConnectionPool that can be used across threads just like ThreadedConnectionPool. But in this class, each thread gets a single connection from the pool. Thus each thread can be used just once.
A thread id is generated in this class by using key to keep record of the usability of thread.

Python3

class psycopg2.pool.PersistentConnectionPool(minConnection, maxConnection, *args, **kwargs):
    pass

Example:

To understand this connection pooling with an example of SimpleConenctionPool class, let’s first write some code:

Python3

import psycopg2
from psycopg2 import pool
simple_pool = None
try:
    simple_pool = psycopg2.pool.SimpleConnectionPool(
        minconn=1,
        maxconn=10,
        user="test_user",
        password="password#123",
        host="127.0.0.1",
        port="5432",
        database="test_database"
    )
    # Use getconn() to get available connections from pool
    pool_collection = simple_pool.getconn()
    if (pool_collection):
        print("\nsuccessfully recived connection from connection pool\n")
        pool_cursor = pool_collection.cursor()
        pool_cursor.execute("CREATE TABLE STUDENT(id serial PRIMARY KEY, name VARCHAR ( 50 ) NOT NULL, age INT, gender VARCHAR ( 20 ));")
        pool_cursor.execute("INSERT INTO STUDENT(name, age, gender) values('Moira', 12, 'F'), ('John', 14, 'M');")
        pool_cursor.execute("SELECT * FROM STUDENT;")
        students = pool_cursor.fetchall()
        print("Printing all students\n")
        for student in students:
            print(student, '\n')
        pool_cursor.close()
        # Use putconn() to release the connection object and send back to connection pool
        simple_pool.putconn(pool_collection)
        print("Succesfully put away connection\n")
except (Exception, psycopg2.DatabaseError) as e:
	print(e)
	print("Error while connecting to PostgreSQL\n")
finally:
    # closing database connection
    if simple_pool:
        # use closeall() method to close all the active connection if you want to turn of the application
        simple_pool.closeall()
    print("Connection pool is closed\n")

OUTPUT:

Now let’s go through the codebase:

simple_pool = psycopg2.pool.SimpleConnectionPool(
        minconn=1,
        maxconn=10,
        user="test_user",
        password="password#123",
        host="127.0.0.1",
        port="5432",
        database="test_database"
    )

Here we are creating instance of SimpleConnectionPool class by passing the database details and number of minimum connections(minconn) and maximum connections(maxconn) to manage the number of connections.

pool_collection = simple_pool.getconn()

We requested a new available connection by using the getconn() method.

pool_cursor = pool_collection.cursor()
pool_cursor.execute("SELECT * FROM STUDENTS;")
students = pool_cursor.fetchall()
print("Printing all students")
for student in students:
    print(student)
    pool_cursor.close()

After that, we execute a simple query to get all records and print it using the connection we created and closing the connection once we have retrieved detail of all students.

simple_pool.putconn(pool_collection)

Once our process is completed, we can put back or release all the connections back to the pool.

Similarly, we can use other classes to implement pooling to improvise the performance of our database.

Conclusion:

Connection Pooling is very important to implement when handling with real time databases to handle the requests efficiently in a fast manner and implementing the same is a piece of cake in psycopg2.
Hope you enjoyed the article :)