Python PostgreSQL Connection Pooling Using Psycopg2

In this article, We will cover the basics of connection pooling using connection pooling in Python applications, and provide step-by-step instructions on how to implement connection pooling using Psycopg2. Whether you are building a small-scale application or a large-scale enterprise application, understanding how to implement connection pooling with Psycopg2 can help you optimize the performance and scalability of your Python PostgreSQL applications.

Required Modules:

Installing Postgresql, Run the below command in the terminal in Ubuntu:

sudo apt-get install postgresql postgresql-contrib

Installing psycopg2  in the terminal: 

pip install psycopg2

If it does not work, try this: 

pip install psycopg2-binary

What is Connection Pooling?

Cached database connections were generated and maintained for PostgreSQL connection pools so that they could be utilized for future requests rather than having to establish a new connection each time. Implementing and utilizing a connection pool for your PostgreSQL-using Python program has a number of benefits. Improvements in time and performance are the main advantages. Database-centric Python applications’ request and response times can be slashed via Connection Pooling.

Psycopg2’s Connection Connection Pooling Classes: 

The Psycopg2 module has the following classes:

• AbstractConnectionPool: The AbstractConnectionPool class provides a basic interface for managing a pool of PostgreSQL database connections, and you can derive from it to create your own custom connection pool class with your desired functionality.
• SimpleConnectionPool: The SimpleConnectionPool class in psycopg2 is a connection pooling implementation that allows you to reuse database connections in your Python applications, which can improve performance by reducing the overhead of establishing a new connection for every database operation.
• ThreadedConnectionPool: This class is utilized in a multithreaded environment, as the name would imply. In other words, the connection pool built with the help of this class can be shared by several threads.

Database and Table:

After successful installation, we start using PostgreSQL with a user in the terminal.

sudo su postgres
[sudo] password for username:**enter your password here**

 After this type command “psql” to enter the interface, here our username is “postgres”. Now create a database. \ l  command shows a list of all databases.

psql
postgres=# CREATE DATABASE test;
postgres=# \l

Creating a Database using PostgreSQL

Here, we created a database named “test”. After we start testing the database on a server with localhost as the host address, 5432 as the port number, and “postgres” as the username. This is done using the command as shown below:

psql -h localhost -p 5432 -U postgres test

Starting Server for the database

Suppose, In the database, we create a table person and insert the following data:

Person
First_Name	Last_Name	Sex
Pankaj	Chowdhury	M
Rachna	Agarwal	F
Raj	Shamanic	M

Creating a Table and Inserting values

Tables can be easily seen with PostgreSQL as shown below:

Output of Table

Post to understand PostgresSQL Basic Commands.

Stepwise Explanation

Step 1: This line imports the psycopg2.pool module, which provides the SimpleConnectionPool class for creating a connection pool in Python. Create a connection pool: This section creates a connection pool using the SimpleConnectionPool class. It sets the minimum and maximum number of connections to be maintained in the pool as 2 and 3, respectively. It also provides the necessary database connection details such as the username, password, host, port, and database name.

Python3

import psycopg2.pool

# Create a connection pool with a minimum of 2 connections and
#a maximum of 3 connections
pool = psycopg2.pool.SimpleConnectionPool(
    2, 3, user='postgres', password='test123',
    host='localhost', port='5432', database='test')

Step 2: This section obtains a connection from the pool using the pool.getconn() method. It assigns the returned connection object to connection1. This connection can be used to interact with the database. Execute a query with the first connection: This section creates a cursor using the cursor() method on connection1 and uses it to execute a query on the database. The results are fetched using the fetchall() method and printed to the console.

Python3

connection1 = pool.getconn()

# Use the connection to execute a query
cursor = connection1.cursor()
print("Results from Connection1: \n")
cursor.execute('SELECT * FROM person ORDER BY id')
results = cursor.fetchall()
for data in results:
    print(data)
    print()

Step 3: This section follows a similar pattern as the previous section, but this time it obtains two more connections from the pool using pool. get conn () and assigns them to connection2 and connection3, respectively. It then uses each connection to execute a query on the database and prints the results to the console.

Python3

connection2 = pool.getconn()

# Use the connection to execute a query
cursor = connection2.cursor()
print("Results from Connection2: \n")
cursor.execute('SELECT * FROM person ORDER BY id')
results = cursor.fetchall()
for data in results:
    print(data)
    print()

connection3 = pool.getconn()

# Use the connection to execute a query
cursor = connection3.cursor()
print("Results from Connection3: \n")
cursor.execute('SELECT * FROM person ORDER BY id')
results = cursor.fetchall()
for data in results:
    print(data)
    print()

Step 4: After using the connections, it is important to release them back to the pool using the pool.putconn() method. This section releases connection1, connection2, and connection3 back to the pool, making them available for reuse by other parts of the application.

Python3

pool.putconn(connection1)
pool.putconn(connection2)
pool.putconn(connection3)

Complete Code

Python3

import psycopg2.pool

# Create a connection pool with a minimum of 2 connections and
#a maximum of 3 connections
pool = psycopg2.pool.SimpleConnectionPool(
    2, 3, user='postgres', password='test123',
    host='localhost', port='5432', database='test')

# Get a connection from the pool
connection1 = pool.getconn()

# Use the connection to execute a query
cursor = connection1.cursor()
print("Results from Connection1: \n")
cursor.execute('SELECT * FROM person ORDER BY id')
results = cursor.fetchall()
for data in results:
    print(data)
    print()

connection2 = pool.getconn()

# Use the connection to execute a query
cursor = connection2.cursor()
print("Results from Connection2: \n")
cursor.execute('SELECT * FROM person ORDER BY id')
results = cursor.fetchall()
for data in results:
    print(data)
    print()

connection3 = pool.getconn()

# Use the connection to execute a query
cursor = connection3.cursor()
print("Results from Connection3: \n")
cursor.execute('SELECT * FROM person ORDER BY id')
results = cursor.fetchall()
for data in results:
    print(data)
    print()

# Since maximum number of connections in the pool #
#is 3 and three connections
#are already made and not released yet.
#So, requesting for a fourth connection gives error.

# connection4 = pool.getconn()
# cursor = connection4.cursor()
# print("Results from Connection3: \n")
# cursor.execute('SELECT * FROM person ORDER BY id')
# results = cursor.fetchall()
# for data in results:
#     print(data)
#     print()


# Release the connection back to the pool
pool.putconn(connection1)
pool.putconn(connection2)
pool.putconn(connection3)

Output: 

The output of Connection Pooling

What if the number of Connections exceeds the maximum limit of the pool? :

Note that the three connections are not released yet and the maximum number of connections is 3. So if another connection is requested from the pool (sat Connection4), it gives an exception.

PoolError when connections are more than the pool maximum limit