In this article we will explore the concept of connection pool and how to implement it for a PostgreSQL database in Python with Psycopg2. A connection pool is a cached database connection that is created and maintained so that you do not need to recreate it for new queries. Implementing and using connection pooling in a Python application that works with a PostgreSQL database provides several benefits. The main one is improved time and performance. As you know, creating a connection to a PostgreSQL database is a resource- and time-consuming operation. And with pooling, you can reduce the query and response time for database applications in Python. Let’s look at how to implement it. The psycopg2 module has 4 classes to manage connection pooling. You can use them to easily create and manage a pool. Alternatively, the same result can be achieved by implementing an abstract class.
Table of Contents
The psycopg2 connection pool management classes
The psycopg2 module has four classes for connection pool management:
- AbstractConnectionPool.
- SimpleConnectionPool.
- ThreadedConnectionPool.
- PersistentConnectionPool.
Note: SimpleConnectionPool, ThreadedConnectionPool, and PersistentConnectionPool are subclasses of AbstractConnectionPool and implement methods from it. Let’s look at each of them individually.
AbstractConnectionPool
This is a base class that implements a generalized key-based pool code.
psycopg2.pool.AbstractConnectionPool(minconn, maxconn, *args, **kwargs)AbstractConnectionPool is an abstract class. The inherited ones must implement the methods declared in it. If you want to create your own implementation of the connection pool, you need to inherit from it and implement those methods. minconn is the minimum required number of connection objects. * args, *kwargs – arguments you need for the connect() method which is responsible for connecting to a PostgreSQL database.
SimpleConnectionPool
This is a subclass of AbstractConnectionPool that implements its methods. It can already be used for a connection pool. This class is only suitable for single-threaded applications. This means that if you want to create a connection pool using this class, it cannot be passed between threads. Syntax:
psycopg2.pool.SimpleConnectionPool(minconn, maxconn, *args, **kwargs)ThreadedConnectionPool
It is also a subclass of the AbstractConnectionPool class and implements its methods. This class is used in a multithreaded environment, i.e. a pool created with this class can be shared between multiple threads.
psycopg2.pool.ThreadedConnectionPool(minconn, maxconn, *args, **kwargs)PersistentConnectionPool
Another subclass of AbstractConnectionPool that implements its methods. This class is used in multithreaded applications with a pool that distributes persistent connections to different threads. As the name suggests, each thread receives one connection from the pool. Thus, one thread can have no more than one connection from the pool. The connection pool generates a key using the thread ID. This means that for each thread, the connection does not change when called. Note: this class is mainly designed to interact with Zope and is probably not suitable for normal applications. Syntax:
psycopg2.pool.PersistentConnectionPool(minconn, maxconn, *args, **kwargs)Let’s see how to create a connection pool.
Psycopg2 methods for connection pool management
The following methods are presented in the Psycopg2 module and are used to manage it.
getconn(key=None)– to get an available connection from the pool. Thekeyparameter is optional. When this parameter is used,getconn()returns the connection associated with this key. Key is used in thePersistentConnectionPoolclass.putconn(connection, key=None, close=False)– to return the connection back to the pool. If thecloseparameter isTrue, the connection is also removed from the pool. If a key was used to receive the connection, the same key must be passed when returning the connection.closeall()– closes all used pool connections.
Creating a connection pool using psycopg2
This example uses the SimpleConnectionPool to create a connection pool. Before we do this, it is worth looking at the arguments that are required to make it work. We need to specify the minimum and maximum number of connections, username, password, host, and database.
minconnis the lower limit of the number of connections.maxconnis the maximum number of possible connections.- *
args,**kwargs– arguments for theconnect()method, required to create a connection object. Here you need to specify hostname, user, password, database and port.
Example of creating and managing a PostgreSQL connection pool
Let’s see how to use the SimpleConnectionPool class to create and manage a connection pool from Python.
import psycopg2
from psycopg2 import pool
try:
# Connect to an existing database
postgresql_pool = psycopg2.pool.SimpleConnectionPool(1, 20,
user="postgres",
# password you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
if postgresql_pool:
print("The connection pool was created successfully.")
# Use getconn() to get a connection from the connection pool.
connection = postgresql_pool.getconn()
if connection:
print("Connection established")
cursor = connection.cursor()
cursor.execute("select * from mobile")
mobile_records = cursor.fetchall()
print ("Display rows from the mobile table")
for row in mobile_records:
print(row)
cursor.close()
# use this method to let go of the connection object
# and send it back to the connection pool
postgresql_pool.putconn(connection)
print("PostgreSQL connection returned to the pool")
except (Exception, psycopg2.DatabaseError) as error :
print ("Error connecting to PostgreSQL", error)
finally:
if postgresql_pool:
postgresql_pool.closeall
print("PostgreSQL connection pool closed")
Conclusion:
Connection pool created successfully
Connection established
Display rows from the mobile table
(1, 'IPhone 12', 800.0)
(2, 'Google Pixel 2', 900.0)
PostgreSQL connection returned to the connection pool
PostgreSQL connection pool is closed
Let's break down an example. The following values were passed to the method:
- Minimum connection = 1. This means that at least one connection is created when the pool is created.
- Maximum connection = 20. You can have a total of 20 connections to PostgreSQL.
- Other connection parameters.
- The constructor of the
SimpleConnectionPool class returns an instance of the pool.
- Using
getconn() a connection request is made from the pool
- After that, the database operations are performed.
- And at the end, all active and passive connection objects are closed to close the application.
Creating a multithreaded connection pool
Let's create a connection pool that will work in a multithreaded environment. This can be done with the ThreadedConnectionPool class.
import psycopg2
from psycopg2 import pool
try:
# Connect to an existing database
postgresql_pool = psycopg2.pool.ThreadedConnectionPool(5, 20,
user="postgres",
# password you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
if postgresql_pool:
print("The connection pool was created successfully.")
# Use getconn() to get a connection from the connection pool.
connection = postgresql_pool.getconn()
if connection:
print("Connection established")
cursor = connection.cursor()
cursor.execute("select * from mobile")
mobile_records = cursor.fetchall()
print ("Display rows from the mobile table")
for row in mobile_records:
print(row)
cursor.close()
# use this method to let go of the connection object
# and send it back to the connection pool
postgresql_pool.putconn(connection)
print("PostgreSQL connection returned to the pool")
except (Exception, psycopg2.DatabaseError) as error :
print ("Error connecting to PostgreSQL", error)
finally:
if postgresql_pool:
postgresql_pool.closeall
print("PostgreSQL connection pool closed")
The output will be the same: 
