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...

JustBeingHelpful · Accepted Answer · 2024-06-10 01:50:07Z

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

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.
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()
```
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 
```
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

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. — Toby Speight
– Toby Speight, Commented Jun 7, 2024 at 10:19
@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. — JustBeingHelpful
– JustBeingHelpful, Commented Jun 9, 2024 at 2:46
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. — Toby Speight
– Toby Speight, Commented Jun 9, 2024 at 5:16

Stack Exchange Network

Simple Connection Pooling with psycopg2 and PostgreSQL

Code

Config

Usage

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Simple Connection Pooling with psycopg2 and PostgreSQL

Code

Config

Usage

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions