alessandro-peyrachia-rnc commented on issue #65010:
URL: https://github.com/apache/airflow/issues/65010#issuecomment-4235172419
Hi @kaxil ,
I have gathered the output required, have a look here below.
I'm available to dig deeper.
```
app=> SELECT state, count(*) AS cnt
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state
ORDER BY cnt DESC;
state | cnt
--------+-----
idle | 20
active | 1
(2 rows)
```
```
app=> SELECT state, wait_event_type, wait_event,
left(query, 100) AS query_prefix,
age(now(), query_start) AS duration
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start;
state | wait_event_type | wait_event |
query_prefix |
duration
---------------------+-----------------+------------+------------------------------------------------------------------------------------------------------+--------------
----
idle | Client | ClientRead | COMMIT
|
00:04:23.4034
78
idle | Client | ClientRead | ROLLBACK
|
00:04:18.3534
14
idle | Client | ClientRead | ROLLBACK
|
00:04:17.9472
52
idle | Client | ClientRead | SELECT 1
|
00:04:17.6435
47
idle | Client | ClientRead | ROLLBACK
|
00:03:48.3836
62
idle | Client | ClientRead | COMMIT
|
00:03:47.8090
68
idle | Client | ClientRead | COMMIT
|
00:03:47.7953
92
idle | Client | ClientRead | SELECT
ceil(COUNT(*)::decimal / 32) FROM task_instance WHERE (state='running' OR
state='queued') AND | 00:03:47.7107
23
idle | Client | ClientRead | ROLLBACK
|
00:03:47.6354
18
idle | Client | ClientRead | ROLLBACK
|
00:03:47.6234
39
idle | Client | ClientRead | COMMIT
|
00:03:43.7063
49
idle | Client | ClientRead | COMMIT
|
00:03:43.7055
76
idle | Client | ClientRead | COMMIT
|
00:03:21.2596
13
idle | Client | ClientRead | COMMIT
|
00:00:31.7996
02
idle | Client | ClientRead | COMMIT
|
00:00:02.4373
78
idle | Client | ClientRead | SELECT
ceil(COUNT(*)::decimal / 32) FROM task_instance WHERE (state='running' OR
state='queued') AND | 00:00:01.9965
34
idle | Client | ClientRead | COMMIT
|
00:00:00.3019
44
idle | Client | ClientRead | COMMIT
|
00:00:00.0795
6
active | | | SELECT state,
wait_event_type, wait_event,
+| 00:00:00
| | | left(query,
100) AS query_prefix,
+|
| | | age(now()
|
idle in transaction | Client | ClientRead | DELETE FROM
dag_warning USING dag WHERE dag_warning.dag_id = dag.dag_id AND dag.is_stale =
true | -00:00:00.001
897
idle | Client | ClientRead |
|
(21 rows)
```
```
app=> SELECT blocked.pid, left(blocked.query, 80) AS blocked_query,
blocking.pid AS blocking_pid, left(blocking.query, 80) AS
blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype AND gl.relation = bl.relation
AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid
WHERE blocked.datname = current_database();
pid | blocked_query | blocking_pid | blocking_query
-----+---------------+--------------+----------------
(0 rows)
```
We use cnpg with posgres 18, oids are not enabled by default
```
app=> SELECT relname, n_live_tup, n_dead_tup,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'log';
ERROR: column "oid" does not exist
LINE 2: pg_size_pretty(pg_total_relation_size(oid)) AS total_...
```
```
app=> SELECT now(), count(*), state
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;
now | count | state
-------------------------------+-------+--------
2026-04-13 08:48:38.289699+00 | 1 | active
2026-04-13 08:48:38.289699+00 | 20 | idle
(2 rows)
```
```
app=> SHOW idle_in_transaction_session_timeout;
SHOW tcp_keepalives_idle;
idle_in_transaction_session_timeout
-------------------------------------
0
(1 row)
tcp_keepalives_idle
---------------------
7200
(1 row)
```
I run the queries directly on the db, not through pgbouncer
What is unusual is the behaviour when job gets queued.
This morning we added a batch of around 200 jobs, using a pool of 20 slots
The db showed this behaviour here, which was not present before
<img width="2468" height="416" alt="Image"
src="https://github.com/user-attachments/assets/5f0c62ec-c09d-43ad-99b1-81b3aaa155d6";
/>
Let me know how can I help further,
Thanks
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
