Support for postgres connection pools and persistent connections

[Tharinda Seth Wijesekera]

We are encountering issues with database connection pooling in Django when using psycopg3. The connections are being closed after each request, preventing effective pooling. Attempting to set CONN_MAX_AGE to a value greater than 0 results in an ImproperlyConfigured error, indicating that "Pooling doesn't support persistent connections."

Steps to Reproduce:

Configure Django to use psycopg3 with PostgreSQL.
Set CONN_MAX_AGE to a value greater than 0 in the DATABASES settings.
Observe that connections are closed after each request and pooling is not functioning as expected.
Expected Behavior:
Database connections should persist and be reused across requests according to the pooling configuration.

Actual Behavior:
Connections are closed after each request, and an error is raised when setting CONN_MAX_AGE to a non-zero value.

Configuration:

Python 3.10
Django 5.1
psycopg3 3.2.1 (with pooling enabled)
PostgreSQL

Config:

RDS_DB_ENGINE = "django.db.backends.postgresql"

if USE_SSL and not TESTING:
    OPTIONS = {
        "sslmode": "verify-full",
        "sslrootcert": SECURE_RDS_CERTIFICATE_FILE,
        "pool": True
    }
else:
    OPTIONS = {
        "sslmode": "disable",
    }

DATABASES = {
    "default": {
        "ENGINE": IAM_DB_ENGINE if USE_RDS_IAM_AUTH else RDS_DB_ENGINE,
        "NAME": RDS_DB_NAME,
        "USER": RDS_DB_USER,
        "PASSWORD": RDS_DB_PASSWORD,
        "HOST": RDS_HOST,
        "PORT": RDS_PORT,
        "OPTIONS": OPTIONS,
    },
    "read-db": {
        "ENGINE": IAM_DB_ENGINE if USE_RDS_IAM_AUTH else RDS_DB_ENGINE,
        "NAME": RDS_DB_NAME,
        "USER": RDS_DB_USER,
        "PASSWORD": RDS_DB_PASSWORD,
        "HOST": RDS_READ_HOST,
        "PORT": RDS_READ_PORT,
        "OPTIONS": OPTIONS,
    },
}

Error and Pooling Logic: Django.db.backends.postgres.base

    @property
    def pool(self):
        pool_options = self.settings_dict["OPTIONS"].get("pool")
        if self.alias == NO_DB_ALIAS or not pool_options:
            return None

        if self.alias not in self._connection_pools:
            if self.settings_dict.get("CONN_MAX_AGE", 0) != 0:
                raise ImproperlyConfigured(
                    "Pooling doesn't support persistent connections."
                )
            # Set the default options.
            if pool_options is True:
                pool_options = {}

Connection Close Logic:Django.db.backends.base.base

max_age = self.settings_dict["CONN_MAX_AGE"]
self.close_at = None if max_age is None else time.monotonic() + max_age

def close_if_unusable_or_obsolete(self):
    if self.connection is not None:
        self.health_check_done = False
        if self.get_autocommit() != self.settings_dict["AUTOCOMMIT"]:
            self.close()
            return

        if self.errors_occurred:
            if self.is_usable():
                self.errors_occurred = False
                self.health_check_done = True
            else:
                self.close()
                return

        if self.close_at is not None and time.monotonic() >= self.close_at:
            self.close()
            return

[Tharinda Seth Wijesekera]

was able to get the code running after few adjustment to the codebase.

[Tharinda Seth Wijesekera]

was able to get the code running after few adjustment to the codebase.

[Sarah Boyce]

I believe what you're asking for is to have the connection pools work with persistent connections.

As this is not what it was designed to do, this is a new feature request rather than a bug report. To have this accepted, you should first discuss the request with the community. To do that, please consider starting a new conversation on the ​Django Forum, where you'll reach a wider audience and likely get extra feedback. It's a mixture of "is this wanted" and "is this feasible" that you will need to discuss. I would also recommend sharing git diffs or raw code rather than screenshots of code
If there is an agreement for the feature request and it's deemed to be feasible, you are welcome to come back to the ticket and point to the forum topic, so we can then re-open it. For more details, please see ​the documented guidelines for requesting features.

This makes me think we could/should update the ​docs around connection pools to have a bit more information as to what they do ​similar to persistent connections and that they cannot be used alongside persistent connections.

[Florian Apolloner]

Supporting for CONN_MAX_AGE in the pool makes no sense. Pooled connections utilize max_lifetime from the pool configuration itself. The ticket description is a bit confusing, so I cannot say what the actual problem is.