--critical='360').

En effet, le vacuum est dure souvent plus d’une heure sur certaines grosses tables.

J’ai bien exclu le user “postgres” mais cela ne change rien puisque dans pg_stat_activity le “usename “ est null pour les workers du vacuum :

postgres=#                   SELECT pg_database.datname, pgsa.*,
                    COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) AS seconds
                FROM pg_database LEFT JOIN pg_stat_activity pgsa ON pg_database.datname = pgsa.datname AND (pgsa.query_start IS NOT NULL AND (pgsa.state NOT LIKE 'idle%' or pgsa.state IS NULL))
                where COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) > 10 ORDER BY pgsa.query_start, pgsa.pid DESC;
  datname  | datid |  datname  |   pid   | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend
_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event  | state  | bac
kend_xid | backend_xmin |                    query                    |   backend_type    | seconds
-----------+-------+-----------+---------+------------+----------+---------+------------------+-------------+-----------------+-------------+----------------
---------------+-------------------------------+-------------------------------+-------------------------------+-----------------+-------------+--------+----
---------+--------------+---------------------------------------------+-------------------+---------
 OpenOpcUa | 17968 | OpenOpcUa | 2348268 |            |          |         |                  |             |                 |             | 2023-12-21 14:0
0:07.325223+01 | 2023-12-21 14:00:07.350144+01 | 2023-12-21 14:00:07.350144+01 | 2023-12-21 14:00:07.350145+01 | Timeout         | VacuumDelay | active |
         |    193286775 | autovacuum: VACUUM pg_toast.pg_toast_722575 | autovacuum worker |     970
 OpenOpcUa | 17968 | OpenOpcUa | 2349622 |            |          |         |                  |             |                 |             | 2023-12-21 14:0
1:07.337233+01 | 2023-12-21 14:01:07.937906+01 | 2023-12-21 14:01:07.937906+01 | 2023-12-21 14:01:07.937907+01 | Timeout         | VacuumDelay | active |
         |    193287643 | autovacuum: VACUUM pg_toast.pg_toast_647204 | autovacuum worker |     910
(2 rows)

Est-ce un bug de pg_stat_activity sur ma version (13.3) ?

Est-ce envisageable d’ajouter un paramètre à querytime pour filtrer sur application_name ou query ?

Cordialement