In this lesson, you will learn a connection pool and how to implement a PostgreSQL database connection pool using Psycopg2 in Python.
Further Reading:
Table of contents
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 aconnect ()
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. Thekey
parameter is optional, and if used, the connection associated with the key will be returned. TheKey
parameter used inPersistentConnectionPool
class.putconn(connection, key=None, close=False)
: To Put away a connection. i.e., return a connection to the connection pool. If theclose
parameter is toTrue
, discard the connection from the pool. If akey
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 operationsminConnection
: 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 aconnect()
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.