Native connection pooling in Django 5 with PostgreSQL

My Django skills are getting rusty (because I haven't used it for any work projects in years) but I heard you can scrap PgBouncer and django-db-connection-pool and just set:

  ...
  "CONN_MAX_AGE": 0,
  "OPTIONS": {
      "pool": True,
  }
  ...

...on the settings.DATABASES and that's all you have to do.

Simple benchmark

I created a very simple view that does a very simple Django ORM query. It looks like this:

def db_debug(request):
    query = Category.objects.all().order_by("-id")
    for x in query.values_list("id", flat=True)[:1]:
        return json_response({"last_id": x})

Then, I ran a benchmark against http://localhost:8000/api/v1/__db_debug__ and record the requests per second.

With NO connection pooling

I.e.

{'CONN_HEALTH_CHECKS': False,
 'CONN_MAX_AGE': 0,
 'DISABLE_SERVER_SIDE_CURSORS': False,
 'ENGINE': 'django.db.backends.postgresql',
 'HOST': 'localhost',
 'NAME': 'peterbecom',
 'PASSWORD': '',
 'PORT': '',
 'USER': ''}

Using oha, running it a bunch of times, this is what I get:

❯ oha -n 1000 http://localhost:8000/api/v1/__db_debug__
Summary:
  Success rate: 100.00%
  Total:    5134.5693 ms
  Slowest:  342.7820 ms
  Fastest:  13.7949 ms
  Average:  250.5163 ms
  Requests/sec: 194.7583

Then, I change the setting to:

{'CONN_HEALTH_CHECKS': False,
 'CONN_MAX_AGE': 0,
 'DISABLE_SERVER_SIDE_CURSORS': False,
 'ENGINE': 'django.db.backends.postgresql',
 'HOST': 'localhost',
 'NAME': 'peterbecom',
+'OPTIONS': {'pool': True},
 'PASSWORD': '',
 'PORT': '',
 'USER': ''}

Same oha run:

❯ oha -n 1000 http://localhost:8000/api/v1/__db_debug__
Summary:
  Success rate: 100.00%
  Total:    948.3304 ms
  Slowest:  58.2842 ms
  Fastest:  4.0791 ms
  Average:  46.2348 ms
  Requests/sec: 1054.4848

In both benchmarks, I use gunicorn with wsgi and 2 workers.

Side note

As a "baseline check", what would the Django view (using WORKERS=2 gunicorn), yield if you don't do any database queries at all? I changed the benchmark Django view to:

def db_debug(request):
    return json_response({"max_id": 100})

and run the benchmark a bunch of times:

❯ oha -n 1000 http://localhost:8000/api/v1/__db_debug__
Summary:
  Success rate: 100.00%
  Total:    630.1889 ms
  Slowest:  37.6904 ms
  Fastest:  2.7664 ms
  Average:  30.7311 ms
  Requests/sec: 1586.8259

Conclusion

Adding 'OPTIONS': {'pool': True}, to the DATABASES['default'] config made this endpoint 5.4 times faster.

Doing that one simple SQL query makes that view 1.5 times slower, which makes sense because it's doing something.

Questions

I don't know why but I had to switch to psycopg[binary,pool]>=3.2.9 to make this work. Before, I used to use psycopg2-binary==2.9.10.

To be honest, I don't know why setting...

DATABASES["default"]["MAX_CONN_AGE"] = 60

...instead of the default 0, didn't make it better.