[Fixed]-Django ORM leaves idle connections on Postgres DB

apparently you don’t disconnect. Using db.close_connection() after query finishes would help. Also If I get it right CONN_MAX_AGE to some short value could help. And consider using some session pooler, eg pgbouncer for django connections. This way if you have too many connections it will wait (or reuse previous, depending on config) instead of aborting execution with error…

update: explanation why I propose it

from docs

each thread maintains its own connection, your database must support
at least as many simultaneous connections as you have worker threads.

So if you have more threads then postgres max_connections, you get mentioned error. Each thread can reuse connection if CONN_MAX_AGE has not passed. Your setting is 0, so connection should be closed after query completion, but you see 100 idle connection. So they are not closing. The big number of connection means that they are not reused either (logic: if you would have 100 parallel queries they would not all be idle, and if you have so many, they are not reused – opening new). So I think django does not close them as prommised – so CONN_MAX_AGE set to 0 does not work in your code. So I propose using db.close_connection() to force the disconnect and setting CONN_MAX_AGE to some small value can change behaviour.

Best guess without more details, but if it’s the same query, and they’re all idle, it feels like you’re doing some kind of async programming, and you’ve hit a deadlock, and specifically your deadlock is manifesting itself in db connections getting saturated.

If you have not defined CONN_MAX_AGE and you’re not using any third party pooler – then this must be an issue somewhere in your code or in a library you’re using. Django by default opens and closes db connection per request. And the fact that you see idle connections in pg_stat_activity doesn’t mean there is a deadlock – instead it means that something has opened these connections and didn’t close it.

I would first make sure if these connections are actually coming from Django by e.g. restarting the app and seeing how it affects pg_stat_activity. If you confirm it, then check if you’re not mixing any async or multiprocessing code there that leaves dangling threads/processes.

This is a Django PostgreSQL Solution using Threading and Middleware.

Database View
You will need to create a view in you DB
Format: app_viewname
I called mine "configuration_terminate_idle_connections"

SELECT row_number() OVER (PARTITION BY true::boolean) AS id,
pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE 
datname = 'database_name'
    AND pid <> pg_backend_pid()
    AND state = 'idle'
    AND extract(epoch from (current_timestamp - query_start)) > 15*60;

Model

class terminate_idle_connections(models.Model):
    pg_terminate_backend = models.BooleanField(default=True)

    class Meta:
        ordering = ['id']
        managed = False
        db_table = 'appname_terminate_idle_connections'
        verbose_name_plural = "Database Terminate Idle Connections"

Admin (Run it manually through /admin)

class terminate_idle_connectionsView(admin.ModelAdmin):
    list_display = ('id', 'pg_terminate_backend')             


admin.site.register(terminate_idle_connections,terminate_idle_connectionsView)

settings.py
Set to what you want

DB_MAX_CONNECTIONS = 700
DB_MAX_IDLE_CONNECTIONS = 150

Middleware (Run it when a request is submitted)
I called mine "DbConnections.py" within the my_project_folder/middleware, the same location as the settings.py file
Full file path: "my_project_folder/middleware/DbConnections.py"

from django.conf import settings
from appname.models import *

class DbConnections:
    
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        response = self.get_response(request)
        return response
    # PROCESSED BEFORE THE REQUESTED VIEW FUNCTION OR CLASS
    def process_view(self, request, view_func, view_args, view_kwargs):
        if settings.DEBUG: print("\nRemove idle Connections")
        dbConection= database_activity.objects.all()
        dbConectionCountAll= dbConection.count()
        dbConectionCountIdle= dbConection.filter(state="idle").count()
        if settings.DEBUG: print("    - Allowed Connections:",settings.DB_MAX_CONNECTIONS, "Actual Connections:",dbConectionCountAll)
        if settings.DEBUG: print("    - Allowed Idle Connections:",settings.DB_MAX_IDLE_CONNECTIONS, "Actual Idle Connections:",dbConectionCountIdle)
        if dbConectionCountAll >= settings.DB_MAX_CONNECTIONS or dbConectionCountIdle >= settings.DB_MAX_IDLE_CONNECTIONS:
            terminateIdelConections = terminate_idle_connections.objects.all()
            if settings.DEBUG: print("\n    - Terminated Connections:", terminateIdelConections.count(),"\n")

Settings Middleware

MIDDLEWARE = [
    'my_project_folder.Middleware.DbConnections.DbConnections',
    'django.middleware.security.SecurityMiddleware',
    ...
]

View Threading (Run it on a timer)
Within you app views.py file:

import threading
from django.conf import settings
from appname.models import *

def removeIdleDbConnections():
    threading.Timer(900, removeIdleDbConnections).start()


    # REMOVE IDLE CONNECTIONS
    try:
        dbConection= database_activity.objects.all()
        dbConectionCountAll= database_activity.objects.all().count()
        dbConectionCountIdle= database_activity.objects.filter(state="idle").count()
        if dbConectionCountAll >= settings.DB_MAX_CONNECTIONS or dbConectionCountIdle >= settings.DB_MAX_IDLE_CONNECTIONS:
            terminateIdelConections = terminate_idle_connections.objects.all()
            if settings.DEBUG: print("Terminated Connections:", terminateIdelConections.count())
    except:
        pass

removeIdleDbConnections()