Django ORM - better handling of connections

[an...@cabine.org]

Hello.

For the last 6 years I've been using gevent's greenlets to serve Django requests, instead of tying up a thread or a process, and this has served me well for web applications that deal with slow streaming clients, or have to call some slow external services mid-request, or are just IO-bound in general. However, when I do have to use the Django ORM, it sort of throws a wrench in my gears.

The default behavior is to open a new DB connection as soon as it is needed while servicing a request, and close it at the end of the request. I don't really want to have a DB connection open for each concurrent request being served (using greenlets it is possible to serve a lot of requests concurrently), and I don't trust that every single one will be correctly closed at the end either (https://code.djangoproject.com/ticket/29069 for example), which would cause a leak. Also, managed DB services typically have a limited amount of DB connections that we can use so I need to limit this number.

I have been using a connection pool (https://github.com/lcd1232/django-postgrespool2) in order to keep the number of DB connection under control, but this relies on Django closing the connection in order for it to be returned to the pool, and therein lies the rub. Django will only close the DB connection at the end of the request. So, in practice, if my requests make use of the ORM my concurrency is limited by the size of the connection pool. Is there a way to force Django to close the DB connection after each operation (unless we're in an atomic block)?

I know there are other options such as relying on external components like pgbouncer or Pgpool-II but I would prefer not to add yet another component to my architecture. And this would also mean that a LOT of connections would need to be managed between Django and this component as well...

Thank you and best regards,

André

[Simon Charette]

Hello André,

> Is there a way to force Django to close the DB connection after each operation (unless we're in an atomic block)?

The only way I can think of achieving that is by bundling your own database backend subclass

and override execute() and the cursor subclass to close the connection when appropriate.

Is there any reason you opted for an in-application connection pool instead of using

pgbouncer or pgpool instead?

Best,

Simon

[an...@cabine.org]

Hello Simon.

On Monday, May 14, 2018 at 6:15:50 PM UTC+1, Simon Charette wrote:

> Is there a way to force Django to close the DB connection after each operation (unless we're in an atomic block)?

The only way I can think of achieving that is by bundling your own database backend subclass

and override execute() and the cursor subclass to close the connection when appropriate.

Thanks for the suggestion, I will take a look at it.

 

Is there any reason you opted for an in-application connection pool instead of using

pgbouncer or pgpool instead?

There are several reasons:

- I would have to configure Django to open a new connection to the pooling component at each request and, well, we've moved past PHP at this time... :)

- I have zero experience with either pgbouncer or pgpool, and they both seem to perform the same tasks, so it would be yet another decision that I would have to make and using only second-hand experience. In addition, none of them seem to have official Docker images.

- Another component that I would have to manage, and I already have an sqlproxy component between Django and the DB when I need to access Cloud SQL instances in GCP.

- Doubling of the number of connections of the server, the same amount of web client connections would be reflected in DB connections to the pooler component.

- All in all IMO the connection pooling problem is better handled in process, and it's not rocket science, there are already libs for this, I'm just missing 10% help from Django.