https://prefect.io logo
Join Slack
Channels
Powered by
Hey everyone :wave: I'm using a self-hosted Pre...
# ask-community
w
Hey everyone 👋 I'm using a self-hosted Prefect v3 setup with PostgreSQL + PGbouncer. I’m trying to understand the best way to configure connection pooling in this environment. Does anyone have guidelines or best practices? Here's my scenario: • I have two Prefect instances: ◦ Instance 1 has 1 worker, 1 pool, and always shows 5 connections (the default pool size), which occasionally jumps to 7 when a flow runs. ◦ Instance 2 has 4 workers, 4 pools, and seems to keep 9 connections open all the time. My main confusion is that workers don't directly connect to the database, so I'm not sure why the total connections rise when flows start running, is there any predictable number of connections added when a flow start to run? Recently, Instance 2 reached 20 pending connections, and PGbouncer started timing out. My settings are configurable for 5 connection and an overflow of 10, but it feels like the overflow isn’t fully respected. Does anyone have more details on how Prefect Server handles DB connections, or a rule of thumb for sizing PGbouncer pool/overflow in relation to the number of workers and flows? Any insight would be greatly appreciated!
n
Hey William, I don't work at prefect, but I hit this issue recently as well
our problem, is that we were using the django orm in our flows, and it did not automatically close the connections after a flow/task ended, so we ended up with a bunch of db connections taking up all our slots as welll
the fix for us was to manually close all django connections in a task or flow that uses the django orm
we use this decorator:
Copy code
from django.db import connection as django_connection
from functools import wraps

def close_django_db_connection(task_func):
    """Decorator to ensure the DB connection is closed after task execution"""
    @wraps(task_func) # this preserves the original function info
    def wrapper(*args, **kwargs):
        try:
            return task_func(*args, **kwargs)  # Run the task
        finally:
            django_connection.close()  # Close the DB connection after execution
    return wrapper
 
 
## Which you can add to any task like this:
from prefect import task 

@task
@close_django_db_connection
def your_task(
    x: str,
):
    # db connection happens
    return x
since you aren't using django, it will be different, but maybe you can do something similar!
w
Thanks Nicolas! Yeap indeed restarting the applications free up the resources, but that would be temporarly fix since the connections will be used again. Also this is the table meant to be used by the prefect server only, so I was expecting that only the prefect server would consume connections, it got me by surprise that the number of workers in idle consumes more connections.
n
ah, sorry, i missed you were doing this for a self hosted prefect server. good luck !
7 Views
Open in Slack
PreviousNext
Powered by