I was reading about Django 4.2’s Psycopg 3 support and was excited about the potential for performance/efficiency improvements that updates to that library could bring. I was curious to understand how many of these new features are utilized now or planned in the future.
No but that might become a necessity to add proper async support to the ORM which is still using thread pools to interact with the database backend. @apollo13 has likely more to add here.
Any other performance ramifications users should be aware of when moving to the new database driver?
You can find some benchmarks on the PR thanks to @pauloxnet. Support is still very early and we (both Django and Psycopg devs) are eager to get feedback from users trying it out more on real world applications.
It should be used whenever server-side bindings are used. For client-side cursors I honestly do not know – should be easy to check though if one enables query logs on the pg instance.
EDIT:// Thinking more about it, we are probably only using binary parameters, running the testsuite with binary=True for return values would be interesting.
Here are some of the possible performance improvements that you might experience when using psycopg3 with Django 4.2:
Faster connection time: psycopg3 offers faster connection times to the database, which can help improve the overall performance of your Django application.
Reduced memory usage: psycopg3 is designed to use less memory than its predecessor, which can be particularly helpful if your application is running on a system with limited resources.
Improved query execution time: psycopg3 includes several optimizations that can help reduce the time it takes to execute queries against the database. This can help improve the performance of your Django application, particularly for complex queries or queries that are executed frequently.
I used to have a setup with PgBouncer as a sidecar but it is a pain on GCP CloudRun. My container is having up to 80 connections and this means up to 80 concurrent postgres connections. I would love to see the native new psycopg_pool added as an option in the out of the box Django driver so that I can limit each container to 5 or so connections and not pay for establishing a new connection with each request.
Even without async support would it not be possible for the thread pool to grab the connection from psycopg_pool?
I do not think that you will get rid of PgBouncer anytime soon. First off all the psycopg connection pool only helps you if you are inside one process, at which point you are limited by the GIL. So you’d then switch back from threads to processes at which point the psycopg connection pool no longer helps you at all.
Granted, for async you still need a pool because your one thread needs to serve multiple requests.
But for all of this to ever become useful we imo need to get rid of the GIL, then we can spawn a new thread per request and use a process global connection pool. In the meantime I think deploying processes with persistent connections and an external pool is as good as it gets performance wise.
This is my setup:
Docker Container on Cloud run with 1cpu running gunicorn --workers 1 --threads 16
My views are a mix of API and templates, all of them use the DB, some of them also use other external IO (cache, external APIs, blob storage, pub sub). Looking at sentry, database queries are 10% of the time of a request on average.
My CONN_MAX_AGE is 0, this means that new connections are created for each request. ideally I would like to have CONN_MAX_AGE to 300 or even None. Because only 10% of the time is spent doing queries I should only need 2 active connections per Container, maybe 3 to be safe. I would really love to find a way to do this within the docker container itself rather than an external sidecar.
I’m dealing with a similar issue and struggling to understand your response here. If the problem is that connections are being created on every request when they should be reused, why doesn’t a connection pool running in a Django/Gunicorn process solve it? What you describe seems to amount to undesired serialization of requests to the database due to the GIL, but doesn’t that impact IO/database calls regardless of whether an in-process connection pool is used?
Also, last year in a discussion last year about ASGI, persistent connections and pooling, you (I think?) wrote “we are finally at the point where we need a connection pool in Django. I’d strongly recommend to use something like psycopg_pool” (#33497 (Database persistent connections do not work with ASGI in 4.0) – Django). I’m not sure how to reconcile that recommendation with “deploying processes with persistent connections and an external pool is as good as it gets”.
So: given a Django 4.1 application running in Docker on Gunicorn+Uvicorn as an ASGI app – what is the current recommended best practice for reusing database connections instead of creating them for each request?
I guess we are mixing up things here, so my answer will go into a bit more detail. I was mostly answering to the sync case while you (as written below) are trying to work with async. As a starting point let us assume the following scenarios of how to deploy Django (I think those are all that are possible, if I miss something please say so – though I guess they might be variants):
Sync case with a static threadpool
Sync case with a dynamic threadpool
Async case, usually with one handling thread and a threadpool to run tasks which are not async safe (though the threadpool isn’t really important here).
Additionally you can deploy each of the above variants with multiple processes. One of the most common variants is probably option 1 with multiple processes and a poolsize of 1 (gunicorn sync workers). We will not consider multiple processes in the further discussion since multiple processes simply multiply the required connections to the postgres server by the number of processes. The amount of connections in that case can only be controlled by an external connection pool. And this is the main reason why I said one can’t just get rid of the external pool.
Let us look through our cases from above:
Case 1: This case is relatively easy. With a thread pool size of 1 set persistent connections and be done with it (this is as good as it gets). If you increase the thread size it gets more interesting:
If you are CPU bound then this is the wrong deployment method since the GIL will ever only allow a single request to be handled at a time (well it allows you to handle multiple requests by switching between threads every X instructions but the total time to respond to multiple parallel requests will still be as if they arrived serially).
If the main I/O you are doing is database I/O then a connection pool inside Django only helps partially. Python will switch on I/O between threads, but if your I/O is database access then your connection pool size limits the number of threads that can work in parallel. Bluntly put in this special case you’d be better of with persistent connections again.
If your I/O pattern is more towards non-database access then a connection pool becomes a bit more useful. Eg you could have a thread pool of size 10 where 5 threads are waiting on file I/O and 5 threads are doing database I/O. In this case you could have a connection pool with size 5 and everything would be okay. Please note that this requires that the threads doing file I/O are not having a checked out connection from the connection pool as well, because then you’d again be limited by your connection pool size and not your thread pool size.
Case 2: In this case a new thread gets spawned for every request. As that results in different thread ids every thread will get a new database connection and persistent connections will not get cleaned up as it currently stands. A connection pool would be useful here. All points with regard to GIL of Case 1 still apply. Historically this deployment scenario is not that common I think.
Case 3: Basically the same as Case 2, but probably going to be more common.
You have to see those replies in their context and don’t see them together. In this thread I was mostly answering to the sync case where I still think that persistent connections + external pool is the best (note that this operates under the assumption that the deployment use multiple processes, ie Gunicorn sync workers). For the async/threading case the story is a bit different: You need a connection pool and that is what I said in the ticket.
But to spin this a bit further and this is where it gets muddy: If you are using a connection pool, then your connections are by definition again persistent (not what we usually mean when we talk about persistent connections in Django) and if you have more than one ASGI process running you will benefit from an external pool as well to further increase reuse of the connections. So basically you are back to “deploying processes with persistent connections and an external pool is as good as it gets” – though the definition of persistent is different in this case
The way I see it:
Use an external pool and ensure that connection creation to that pool is really fast to offset the cost of still having to use a new connection each request.
Use your database drivers native async capabilities and their pooling. This also means handwriting queries and not using Models.
Probably both not what you like to hear, but that is IMO (and I fully might miss some things) all you can do currently.
I hope this long answer helps and clears up some things?
Thank you for the detailed and rapid reply! I will try to distill the lessons here, please tell me if I’m still not getting it:
Persistent database connections are tied to the Python thread handling a request via thread id.
With a static number of threads, there is a 1:1 correspondence of thread to connection, a connection will be reused among requests to its thread, and cleaned up appropriately after “conn_max_age” is reached.
With a dynamic thread pool, new requests spawn new threads with new ids, the existing db connections will not correspond, thus a new db connection gets created, even if there are idle db connections in the process.
When using WSGI(sync) + dynamic thread pool, or when using ASGI(async), persistent database connections will not be cleaned up and will eventually exhaust available connections on the database server. The “close_old_connections” function cannot clean up these old connections because…?
Even though the event loop in ASGI is single threaded, there is a threadpool for sync tasks:
As the pool allocates threads, they will also create new database connections.
Without persistent connections enabled, the handling thread will create a fresh db connection whenever it needs to talk to the db.
With persistent connections, the main handling thread will reuse the connection, but it does not know how to close it because the boundaries of an async request are not well defined in comparison to sync. IOW, the main thread doesn’t really understand that the connection can be released, so it leaves it running and creates a new one after “conn_max_age” has been reached.
An external database connection pool is the only way to control the total number of connections to the database when there are multiple web server processes running (e.g. Uvicorn workers). This is even more true when there are multiple processes and also multiple hosts in a cluster.
In the absence of an external pool, one can use an in-process pool (e.g., psycopg_pool) and benefit from reused connections. However, as worker count and server count increase for greater parallelism, the total count of necessary connections may exceed what the database server allows. So with greater number of workers and servers, the database parameters must be changed to keep up. This is manual maintenance and can easily lead to errors if changes are not kept in sync.
So your recommendations for ASGI are either to use an external pool, or bypass the ORM and use low level database pooling. In our case we are heavily reliant on the ORM with a large application, so the second choice is a non-starter. I am confused that you would present it as an option though, since it suffers the same weakness as any in-process pool in that it could exceed the db server’s connection limit with additional parallelism. Why would it be any better in the ASGI use case than psycopg_pool?
A related question — would you expect to see memory leaks, or excessive memory consumption, in a Django+Uvicorn application in which new db connections are constantly being created? Just intuitively it seems like this creates more work for reference counting or garbage collection to deal with.
Thanks again, I’m grateful for your time and hopefully this conversation will be helpful to others!
Hi, you are mostly spot on so I will answer just where there is something to add/clarify.
Because the way Django works is that is has a dictionary of thread id -> connection object basically. close_old_connections is only able to clean connections for it’s own thread id, it has no clue about those other ids and as such does not touch them (afaik there is also no way to enumerate active ids).
This is not necessarily true, we do know when a request is finished. The main thing here is that our thread local behaves differently in ASGI in the sense that every co-routine now pretends to be a thread.
An in-process pool always has an upper limit on the number of connections. So essentially you would say: “yeah my DB server can handle 10 connections and then limit the pool to 10”. In the end you will always be limited somehwere/somehow. In the case of async where you usually cannot easily limit the threads you get limited elsewhere. Doing that at the DB level is as good as elsewhere.
Yes, the dict thread id -> connection increases beyond bounds with persistent connections.
Thanks for the followup! I installed django-postgrespool2 and the application is much faster with lower resource utilization and memory leaks are all but gone.
