PostgreSQL idle connections not being reused

[michaelhays]

I've had some issues the past few days with maxing out my number of Postgres connections on a Heroku app of mine, and I realized that Daphne 2.0 doesn't seem to be reusing idle connections. I upgraded the app to use Channels/Daphne 2.0 a few days ago, and didn't have this issue when I was using Channels/Daphne 1.0.

So, I ran the app locally using Gunicorn as well as Daphne 2.0, with everything else the same, and had the same results.

---

Here's what I mean.

Running gunicorn myapp.wsgi:application

When I log in to my app, Postgres creates a row for this connection:

 datid |  pid  | application_name |         backend_start         |          query_start          | state  |                 query
-------+-------+------------------+-------------------------------+-------------------------------+--------+-----------------------------------------
 58587 | 30741 | psql             | 2018-02-05 22:25:20.867637-06 | 2018-02-05 22:37:07.852926-06 | active | SELECT datid, pid, application_name, ...
 58587 |  1182 |                  | 2018-02-05 22:36:56.636173-06 | 2018-02-05 22:37:05.913544-06 | idle   | COMMIT

When I log out, Postgres reuses the idle row (notice how query_start is updated):

 datid |  pid  | application_name |         backend_start         |          query_start          | state  |                 query
-------+-------+------------------+-------------------------------+-------------------------------+--------+-----------------------------------------
 58587 | 30741 | psql             | 2018-02-05 22:25:20.867637-06 | 2018-02-05 22:38:47.205046-06 | active | SELECT datid, pid, application_name, ...
 58587 |  1182 |                  | 2018-02-05 22:36:56.636173-06 | 2018-02-05 22:38:45.388847-06 | idle   | COMMIT

---

Running daphne myapp.asgi:application

Every time I log in or out, a new connection is created:

 datid |  pid  | application_name |         backend_start         |          query_start          | state  |                 query
-------+-------+------------------+-------------------------------+-------------------------------+--------+-----------------------------------------
 58587 | 30741 | psql             | 2018-02-05 22:25:20.867637-06 | 2018-02-05 22:34:43.40421-06  | active | SELECT datid, pid, application_name, ...
 58587 |   338 |                  | 2018-02-05 22:32:50.138649-06 | 2018-02-05 22:33:43.281797-06 | idle   | COMMIT
 58587 |   444 |                  | 2018-02-05 22:33:03.325871-06 | 2018-02-05 22:33:03.447394-06 | idle   | COMMIT
 58587 |   455 |                  | 2018-02-05 22:33:13.427165-06 | 2018-02-05 22:33:13.450794-06 | idle   | COMMIT
 58587 |   506 |                  | 2018-02-05 22:33:20.931278-06 | 2018-02-05 22:33:21.050843-06 | idle   | COMMIT
 58587 |   521 |                  | 2018-02-05 22:33:24.86167-06  | 2018-02-05 22:33:24.877495-06 | idle   | COMMIT
 58587 |   572 |                  | 2018-02-05 22:33:31.274512-06 | 2018-02-05 22:33:31.378542-06 | idle   | COMMIT
 58587 |   641 |                  | 2018-02-05 22:33:35.322522-06 | 2018-02-05 22:33:35.339607-06 | idle   | COMMIT

---

Is there something I'm misunderstanding here? Or does this seem to be a bug in Daphne?

Edit: The command I'm running to view the connections in psql is SELECT datid, pid, application_name, backend_start, query_start, state, query FROM pg_stat_activity WHERE datname = 'mydatabase' ORDER BY backend_start;

[michaelhays]

As a better comparison, I just ran the tests again using Channels/Daphne 1.0 (daphne myapp.asgi:channel_layer), and that reused the idle connections like Gunicorn does.

[andrewgodwin]

This is not Daphne, but Channels - I suspect it's because we're not calling close_old_connections. Moved issue over here: django/channels#871