Idle connections are not released after the time specified in db-pool-max-idletime

[laurenceisla]

Environment

• PostgreSQL version: 16.5
• PostgREST version: 12.2.3
• Operating system: Ubuntu 20.04

Description of issue

According to the Pool docs:

If a connection is unused for a period of time (db-pool-max-idletime), it will be released.

But the connection still lingers even for a small value of idletime, e.g. db-pool-max-idletime = 3. It will stay idle and won't close until db-pool-max-lifetime is reached or until a new HTTP request is done.

To reproduce this is in local environment:

PGRST_DB_POOL_MAX_IDLETIME=3 PGRST_LOG_LEVEL=debug postgrest-with-postgresql-16 -f ./test/spec/fixtures/load.sql postgrest-run

After waiting for 3 seconds, the connection won't be terminated. Doing a select * from pg_stat_activity where application_name ilike '%postgrest%'; will return the connection as idle. It's only after I make a request that the connection is released for "idletime". For example:

# Request sent: curl 'localhost:3000/projects'
04/Feb/2025:16:56:15 -0500: Connection ad029d0b-847f-49ad-ba89-9ef996b98936 is terminated due to max idletime
04/Feb/2025:16:56:15 -0500: Connection 814f4b9f-d3b7-4adf-85b3-685dcc9fab35 is being established
04/Feb/2025:16:56:15 -0500: Connection 814f4b9f-d3b7-4adf-85b3-685dcc9fab35 is available
04/Feb/2025:16:56:15 -0500: Connection 814f4b9f-d3b7-4adf-85b3-685dcc9fab35 is used
04/Feb/2025:16:56:15 -0500: Connection 814f4b9f-d3b7-4adf-85b3-685dcc9fab35 is available
127.0.0.1 - postgrest_test_anonymous [04/Feb/2025:16:56:15 -0500] "GET /projects HTTP/1.1" 200 - "" "curl/8.7.1"
# Request sent: curl 'localhost:3000/projects'
04/Feb/2025:16:57:09 -0500: Connection 814f4b9f-d3b7-4adf-85b3-685dcc9fab35 is terminated due to max idletime
04/Feb/2025:16:57:09 -0500: Connection 695c451d-dda5-4345-8a5c-6727f9b50bb0 is being established
04/Feb/2025:16:57:09 -0500: Connection 695c451d-dda5-4345-8a5c-6727f9b50bb0 is available
04/Feb/2025:16:57:09 -0500: Connection 695c451d-dda5-4345-8a5c-6727f9b50bb0 is used
04/Feb/2025:16:57:09 -0500: Connection 695c451d-dda5-4345-8a5c-6727f9b50bb0 is available
# Request sent: curl 'localhost:3000/projects'
127.0.0.1 - postgrest_test_anonymous [04/Feb/2025:16:57:09 -0500] "GET /projects HTTP/1.1" 200 - "" "curl/8.7.1"
04/Feb/2025:16:57:26 -0500: Connection 695c451d-dda5-4345-8a5c-6727f9b50bb0 is terminated due to max idletime
04/Feb/2025:16:57:26 -0500: Connection 3caa7c94-fc5c-4f74-9903-fe018fd1843e is being established
04/Feb/2025:16:57:26 -0500: Connection 3caa7c94-fc5c-4f74-9903-fe018fd1843e is available
04/Feb/2025:16:57:26 -0500: Connection 3caa7c94-fc5c-4f74-9903-fe018fd1843e is used
04/Feb/2025:16:57:26 -0500: Connection 3caa7c94-fc5c-4f74-9903-fe018fd1843e is available
127.0.0.1 - postgrest_test_anonymous [04/Feb/2025:16:57:26 -0500] "GET /projects HTTP/1.1" 200 - "" "curl/8.7.1"

Note the "Connection ... is terminated due to max idletime" messages. The termination is executed after ~1 minute even when it's set at 3 seconds and it's only triggered right after a request is done.

[steve-chavez]

It's only after I make a request that the connection is released for "idletime"

IIRC, the idle pool connections are only recycled when AppState.usePool is called (which in turn calls Hasql.Pool.use).

Maybe this should be documented, although ideally idle connections should be recycled as soon max-idletime is reached. One option could be to call https://hackage.haskell.org/package/hasql-pool-1.3/docs/Hasql-Pool.html#v:release in a background thread periodically (maybe in max-idletime periods), but likely that will affect performance.

[laurenceisla]

IIRC, the idle pool connections are only recycled when AppState.usePool is called (which in turn calls Hasql.Pool.use).

Oh so it's expected then. I was expecting not releasing idle connections to be the main issue, but it shouldn't be a problem since the connections can be reused. The case I got filled the pool with idle connections and also, somehow, is creating more connections than allowed by db-pool. I'll tag as need-repro to see if I can find a way to reproduce this.