Simple Connection Pooling with psycopg2 and PostgreSQL

Question

I am currently using a SimpleConnectionPool from psycopg2 to lease transactions to a PostgreSQL database and would like a review of my current implementation.

Code

from contextlib import contextmanager
from dataclasses import dataclass
from psycopg2.pool import SimpleConnectionPool
@dataclass
class PostgreSQLSimplePool:
 pool: SimpleConnectionPool
 @contextmanager
 def transact_session(self, commit: bool = False):
 conn = self.pool.getconn()
 try:
 yield conn
 if commit:
 conn.commit()
 except Exception:
 conn.rollback()
 raise
 finally:
 self.pool.putconn(conn)

Config

simple_pool = psycopg2.pool.SimpleConnectionPool(
 # testing purposes
 1, 20,
 user=POSTGRES_USER,
 password=POSTGRES_PASS,
 host=POSTGRES_HOST,
 port=POSTGRES_PORT,
 dbname=POSTGRES_DB_NAME,
 sslmode='require'
 )
repo = PostgreSQLSimplePool(pool=simple_pool)

Usage

with repo.transact_session() as connection:
 with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
 # do some stuff with the cursor... 

Answer 1

Your code looks okay, but I would suggest a few things.

1. Don't worry about calling putconn function to put back the connection every time you are managing data in a function. This is expensive from a performance perspective. Instead, figure out what types of operations your application will do. If lots of data related events will occur and then there is a long pause from a user or code, call putconn at that time.

2. It sometimes makes sense to make the pool a global variable, so you are not passing it around everywhere. Define the pool (and potentially conn if you are not threading the app) as a global.

    pool = None
    conn = None
   

   Set the pool when the application starts up or when you are redefining the pool, since the max connections may fluctuate based on many things

    global pool
    pool = get_conn_pool() # return pool in this function
   

   Set the connection at the beginning of major data events as a global (unless you are threading your application processes).

    global conn
    conn = pool.getconn()
   

3. If your entire application catches exceptions in one central place (hint: at the beginning implement a try/except), close the pool to avoid issues. If you have Raised, Uncaught and User exceptions inside the application, it's fine to implement it there.

    except Exception as e:
    if pool: 
    pool.closeall 
   

4. Avoid creating classes in Python just to make your code look fancy or organized, unless absolutely necessary. This is terrible on performance in Python! Because Python is a dynamic language, they do not enforce variable types well, so classes are a huge shell with performance bottlenecks. There are objects that get created in the bytecode that are typically not even used by the majority of program source code. Since PostgreSQLSimplePool could be done using a function, just make it a function.

(# 5 removed per comment - this link is also helpful)

https://security.stackexchange.com/questions/197784/is-it-unsafe-to-use-environmental-variables-for-secret-data/197791#197791

• Toby Speight: While passing user name in environment makes a lot of sense, I'd avoid doing that for private data such as passwords - prefer to read from an appropriately-permissioned file instead.
• JustBeingHelpful: @TobySpeight, feel free to edit #5 in my answer on how that is done. I have a quick question. If you have permissions to open the file, where do you store those permissions if a system is automated without human intervention? I've never done this before, but I'd like to learn.
• Toby Speight: I'm not sure what you mean by "storing permissions". Normally, we just ensure the file is readable only the user whose password is in it (e.g. chmod 600 passwd_file). If we're being thorough, the code to read it could check (with os.fstat() after opening) that its ST_MODE doesn't include any permissions for "group" or "other" users, and abort with a good message if so.