Django DB Pool on Heroku
Recently I switched my Django application that runs on Heroku to use production PostgreSQL database. According to the documentation common queries to such database should take less than 10ms. Unfortunately, Django requests were taking about 45ms (each request executed a single SQL query, with no joins and a single row result. The row lookup was done by an indexed column, and all data fit in the Postgres cache).
Just by enabling persistent DB connection with Django DB Pool, request processing time for the application was reduced to about 10ms! An application that uses dj_database_url (recommended by Heroku), can enable the pool with following settings:
DATABASES = {'default': dj_database_url.config()}
DATABASES['default']['ENGINE'] = 'dbpool.db.backends.postgresql_psycopg2'
DATABASES['default']['OPTIONS'] = {
'MAX_CONNS': 1
}
Background
Django makes a separate connection to a database for each incoming request, this introduces a substantial (35ms in my case) per-request overhead for a connection establishment and authentication. In addition, Postgres maintains a query execution plan cache for each connection, if an application runs the same query for each request but over a different connection, benefits of the plan caching are lost, plans are recomputed each time.
There is a long debate about this issue in the Django community. The core team argues that connection management is not Django’s business and should be handled by an external connection pooler. I agree with this, but I also agree that the default and the only out-of-the-box option (close a connection after each request) is a bad choice. Django should keep the database connection open, and if this is not optimal in some setups, an external pooler can be used to close connections. Because Django closes a DB connection after each request, a pooler can only reduce an overhead of a pooler<->db connection setup, there is still per request overhead of a Django<->pooler connection setup, which can be cheaper, but is still suboptimal.
Running an external pooler only to have (somehow) persistent DB connections is also an additional burden. Fortunately, thanks to the Django DB Pool, this is unnecessary, and we can have fully persistent connections in Django!
