`pg_stat_activity` shows more idle connections than the application is configured to make

[DnPlas]

Steps to reproduce

1. Deploy postgresql-k8s from 14/stable (rev495) and integrate with pgbouncer-k8s
2. Deploy temporal following the steps from this guide - all Temporal apps should be scaled up to 3 units
3. Scale down temporal-k8s
4. Configure temporal-k8s' persistence-max-conns and persistence-max-idle-conns to 1
5. Check the output of SELECT datname,usename,state,state_change FROM pg_stat_activity;
6. Observe how we have more connections than the application is configured for

Expected behaviour

The number of connections match the actual number the application is supposed to make.

Actual behaviour

There are more connections than the actual number the application is configured to make.

Versions

Operating system:

• Ubuntu 24.04 LTS

Juju CLI: 3.6.11-genericlinux-amd64

Juju agent: 3.6.11

postgresql-k8s charm revision: 495
pgbouncer-k8s charm revision: 408

microk8s:

We are using Canonical k8s instead: 1.34-classic/stable

Log output

Juju debug log:

Additional context

• This issue was first reported in Temporal server exceeds database connections limits, requiring a manual fix temporal-k8s-operator#55

• I made a couple experiments with different permutations of the settings:

# Configuring persistence-max-conns and persistence-max-idle-conns to 1, one unit

postgres=# SELECT datname, usename, state, state_change
FROM pg_stat_activity
WHERE usename = 'relation_id_60';
     datname     |    usename     | state |         state_change          
-----------------+----------------+-------+-------------------------------
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:19:46.343597+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:19:45.158492+00
(2 rows)

# Configuring persistence-max-conns and persistence-max-idle-conns to 2, one unit

postgres=# SELECT datname, usename, state, state_change
FROM pg_stat_activity
WHERE usename = 'relation_id_60';
     datname     |    usename     | state |         state_change          
-----------------+----------------+-------+-------------------------------
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:20:23.590172+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:20:23.583447+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:20:23.581298+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:20:23.582197+00
(4 rows)

# Configuring persistence-max-conns and persistence-max-idle-conns to 2, two units

postgres=# SELECT datname, usename, state, state_change
FROM pg_stat_activity
WHERE usename = 'relation_id_60';
     datname     |    usename     | state |         state_change          
-----------------+----------------+-------+-------------------------------
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:22:01.08341+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:23:33.570402+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:20:23.581298+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:23:33.577423+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:23:35.174074+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:23:35.172342+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:21:45.307657+00
 temporal-k8s_db | relation_id_60 | idle  | 2025-10-22 01:21:45.320707+00
(8 rows)

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-8730.

This message was autogenerated