CODEWITHSUNDEEP
pythonpostgresqlmultithreadingpsycopg2
Patryk Organiściak
Patryk Organiściak

Reputation: 5

Postgres connections in many threads

I need advaice in a special case.

I have a program like this:

data = [...]
multithread.Pool(n, data)

def slow_function(data)
    db = psycopg2.connect(credentials)
    cursor = db.cursor()
    new_data = realy_slow_func()
    some_query = "some update query"
    cursor.execute(some_query )

Is opening new connection in each thread safe? It doesn't matter if it's slow, and faster approaches exists.

Upvotes: 0

Views: 2849

Answers (1)

shoaib30
shoaib30

Reputation: 975

You should be using a connection pool, which will create a pool of connections and reuse the same connections across your thread. I would suggest using a ThreadPool too so that the number of threads running at a time is equal to the number of connections available in the DB Connection Pool. But for the scope of this question, I will talk about DB Connection Pool

I have not tested the code, but this is how it would look. You first create a connectionPool and then get a connection from it within your thread, and once complete release the connection. You could also manage the get connection and release, outside of the thread and just pass the connection as parameter, and release once thread completes

Highlighting ThreadedConnectionPool as the class used to create the pool as the name suggests works with threads.

From docs:

A connection pool that works with the threading module. Note This pool class can be safely used in multi-threaded applications.

import psycopg2
from psycopg2 import pool

postgreSQL_pool = psycopg2.pool.ThreadedConnectionPool(1, 20, user="postgres",
                                                         password="pass@#29",
                                                         host="127.0.0.1",
                                                         port="5432",
                                                         database="postgres_db")

data = [...]
multithread.Pool(n, data)

def slow_function(data):
    db = postgreSQL_pool.getconn()
    cursor = db.cursor()
    new_data = realy_slow_func()
    some_query = "some update query"
    cursor.execute(some_query)
    cursor.close()
    postgreSQL_pool.putconn(db)

Source: https://pynative.com/psycopg2-python-postgresql-connection-pooling/

Docs: https://www.psycopg.org/docs/pool.html

Upvotes: 1

Related Questions