Python PostgreSQL Connection Pooling Using Psycopg2

In this lesson, you will learn a connection pool and how to implement a PostgreSQL database connection pool using Psycopg2 in Python.

What is Connection Pool

PostgreSQL connection Pool is nothing but cached database connections created and maintained to get reused for coming requests instead of making the new connection every time.

There are various advantages of implementing and using a connection pool for your Python application while working with PostgreSQL. The primary benefit is time and performance improvements.

As you know, creating a PostgreSQL database connection is expensive, i.e., it is a resource-heavy and time-consuming process.

Using Connection Pooling, we can reduce the request and response time of database-centric applications in Python. Let see how to implement the connection pool in Python to work with a PostgreSQL database.

The Psycopg2 module provides four classes to manage a connection pool. i.e., It has ready-to-use classes to create and manage the connection pool directly. Alternatively, we can implement your connection pool implementation using its abstract class.

Psycopg2’s Connection pooling classes

The Psycopg2 module has the following four classes to manage the PostgreSQL connection pool.

• AbstractConnectionPool
• SimpleConnectionPool
• ThreadedConnectionPool
• PersistentConnectionPool

Note: The SimpleConnectionPool, ThreadedConnectionPool, PersistentConnectionPool are all subclass of AbstractConnectionPool class. and implement all methods defined in it.

Let see the use of each class separately.

AbstractConnectionPool

It is a base class implementing generic key-based pooling code.

A AbstractConnectionPool is an abstract class. Subclass need to implements methods defined in it. If you want to create your custom implementation for the connection pool, you can extend this class and implement its methods.

psycopg2.pool.AbstractConnectionPool(minConnection, maxConnection, *args, **kwargs)

• minConnection: Minimum connection objects required.
• *args, **kwargs are the necessary arguments for a connect () method to establish a connection to the PostgreSQL database.

SimpleConnectionPool

It is a subclass of the AbstractConnectionPool class and implements methods defined in it. It is ready to use class for the connection pool.

This class is suitable only for single-threaded applications. i.e., If we create a connection pool using this class, then we can’t share this across different threads. So use this class to manage the connection pool only when you have a single-threaded application.

Syntax:

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

ThreadedConnectionPool

It is also a subclass of the AbstractConnectionPool class and implements methods defined in it. Ready to use for the connection pool.

As the name suggests, this class used in a multithreaded environment. i.e., the connection pool created using this class can be shared between multiple threads.

Syntax and example:

psycopg2.pool.ThreadedConnectionPool(minConnection, maxConnection, *args, **kwargs)

PersistentConnectionPool

It is also a subclass of the AbstractConnectionPool class and implements methods defined in it.

This class is used in the multithread application, where a pool assigns persistent connections to different threads.

As the name suggests, each thread gets a single connection from the pool, i.e., the thread can’t use more than one connection from the pool.

This connection pool generates a key using thread ID.
Note: This pool class is intended to interact with Zope and probably not useful in typical applications.

Syntax and Example

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

Now, Let see how to create a connection pool.

Methods to manage PostgreSQL connection Pool

The Psycopg2 module provides the following methods to manage the Connection pool.

• getconn(key=None): To Get an available connection from the pool. The key parameter is optional, and if used, the connection associated with the key will be returned. The Key parameter used in PersistentConnectionPool class.
• putconn(connection, key=None, close=False): To Put away a connection. i.e., return a connection to the connection pool. If the close parameter is to True, discard the connection from the pool. If a key is used while creating the connection, then specify it while putting a connection.
• closeall(): Close all the connections handled by the pool. This method closes all connections, including connections that are in use.

Create a PostgreSQL Connection Pool in Python

In this example, we are using a SimpleConnectionPool class to create a connection pool. Before creating a connection pool, let’s see the necessary arguments required to create a connection pool.

• username: the username that you use to work with PostgreSQL.
• password:
• hostname: Server name or Ip address on which PostgreSQL is running.
• database: The name of the database to which you want to connect and perform the operations
• minConnection: The minimum PostgreSQL database connection object to create. i.e., the lower limit.
• maxConnection: The maximum PostgreSQL database connection object to create and support. i.e., the upper limit.
• *args, **kwargs: are the arguments required to pass to a connect() method to create a connection object such as Host Name, User, Password, Database, Port.

Python Example to create and manage PostgreSQL Connection Pool

Let see how to use the SimpleConnectionPool class to create and manage a PostgreSQL connection pool in Python.

import psycopg2
from psycopg2 import pool

try:
    postgreSQL_pool = psycopg2.pool.SimpleConnectionPool(1, 20, user="postgres",
                                                         password="pass@#29",
                                                         host="127.0.0.1",
                                                         port="5432",
                                                         database="postgres_db")
    if (postgreSQL_pool):
        print("Connection pool created successfully")

    # Use getconn() to Get Connection from connection pool
    ps_connection = postgreSQL_pool.getconn()

    if (ps_connection):
        print("successfully recived connection from connection pool ")
        ps_cursor = ps_connection.cursor()
        ps_cursor.execute("select * from mobile")
        mobile_records = ps_cursor.fetchall()

        print("Displaying rows from mobile table")
        for row in mobile_records:
            print(row)

        ps_cursor.close()

        # Use this method to release the connection object and send back to connection pool
        postgreSQL_pool.putconn(ps_connection)
        print("Put away a PostgreSQL connection")

except (Exception, psycopg2.DatabaseError) as error:
    print("Error while connecting to PostgreSQL", error)

finally:
    # closing database connection.
    # use closeall() method to close all the active connection if you want to turn of the application
    if postgreSQL_pool:
        postgreSQL_pool.closeall
    print("PostgreSQL connection pool is closed")

Output:

Connection pool created successfully
successfully recived connection from connection pool 
Displaying rows from mobile table
(4, 'LG V30', 800.0)
(5, 'iPhone 8 Plus', 750.0)
(3, 'Samsung Galaxy S9', 850.0)
(1, 'IPhone X', 1000.0)
Put away a PostgreSQL connection
PostgreSQL connection pool is closed

Let’s Understand connection pool example

postgreSQL_pool = psycopg2.pool.SimpleConnectionPool()

We passed the following values while creating a connection pool.

• Minimum connection = 1, i.e., create a minimum one connection at the time of the creation of a connection pool.
• Maximum Connection = 20, i.e., you can use a maximum 20 PostgreSQL connections.
• The SimpleConnectionPool class constructor returns us the connection pool instance.

ps_connection  = postgreSQL_pool.getconn()

• Using a getconn ()  method we requested a new connection from a connection pool.
• After that, we executed database operations.

postgreSQL_pool.putconn(ps_connection)

The putconn() method to release the connection object back to the connection pool.

postgreSQL_pool.closeall ()

In the end, we are closing all the active and passive connection objects.

Create a Threaded PostgreSQL Connection Pool in Python

Use the ThreadedConnectionPool class To develop a connection pool that will work in a multithreading environment.

import psycopg2
from psycopg2 import pool

try:
    threaded_postgreSQL_pool = psycopg2.pool.ThreadedConnectionPool(5, 20, user="postgres",
                                                                    password="pass@#29",
                                                                    host="127.0.0.1",
                                                                    port="5432",
                                                                    database="postgres_db")
    if (threaded_postgreSQL_pool):
        print("Connection pool created successfully using ThreadedConnectionPool")

    # Use getconn() method to Get Connection from connection pool
    ps_connection = threaded_postgreSQL_pool.getconn()

    if (ps_connection):

        print("successfully recived connection from connection pool ")
        ps_cursor = ps_connection.cursor()
        ps_cursor.execute("select * from mobile")
        mobile_records = ps_cursor.fetchmany(2)

        print("Displaying rows from mobile table")
        for row in mobile_records:
            print(row)

        ps_cursor.close()

        # Use this method to release the connection object and send back ti connection pool
        threaded_postgreSQL_pool.putconn(ps_connection)
        print("Put away a PostgreSQL connection")

except (Exception, psycopg2.DatabaseError) as error:
    print("Error while connecting to PostgreSQL", error)

finally:
    # closing database connection.
    # use closeall() method to close all the active connection if you want to turn of the application
    if threaded_postgreSQL_pool:
        threaded_postgreSQL_pool.closeall
    print("Threaded PostgreSQL connection pool is closed")

Output.

Connection pool created successfully using ThreadedConnectionPool
successfully recived connection from connection pool 
Displaying rows from mobile table
(4, 'LG V30', 800.0)
(5, 'iPhone 8 Plus', 750.0)
Put away a PostgreSQL connection
Threaded PostgreSQL connection pool is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.