How to use Connection Pool for Postgresql in Python for Performance Boosting

I have been working on an IoT project where I developed the backend and have been maintaining it since then.

So, the idea was to receive sensor data from all the devices (around 200 and counting) and then store these sensor data in a PostGreSQL server.

In the initial code, I created each new connection for each payload. For a few hundred devices, that works fine. However, if there are many more devices, it can create a bottleneck and at some point the Postgres database may not be able to handle it.

So, we see, it is all about a scaling issue when considering new connection for each payload. To avoid that, we can create connection pools so that we use one connection for storing data for a number of received payloads.

Here’s a sample code to create a connection pool using the psycopg2 module.

import psycopg2
from psycopg2 import pool

# Initialize the connection pool
conn_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=100,
    host='localhost',
    port='5432',
    database='mydb',
    user='myuser',
    password='mypassword'
)

# Function to get a connection from the pool
def get_conn():
    return conn_pool.getconn()

# Function to release a connection back to the pool
def release_conn(conn):
    conn_pool.putconn(conn)

# Example usage
def main():
    conn = get_conn()
    cur = conn.cursor()
    cur.execute('SELECT * FROM mytable')
    result = cur.fetchall()
    release_conn(conn)

Just one note here. You may face an error if you directly use psycopg2.pool. I do not know exactly why, it seems like psycopg2.pool doesn’t work by design.

To avoid that error, directly import pool from the module and then use it. You can find the issue discussion here.

Reference

1. https://pynative.com/psycopg2-python-postgresql-connection-pooling/
2. https://github.com/psycopg/psycopg2/issues/582