PostgreSQLLongRunningQuery #

Meaning #

Alert is triggered when a PostgreSQL query runs for an extended period.

Impact #

• Block WAL file rotation

• Could block vacuum operations

• Could block other queries due to locks

• Could lead to replication lag on replica

Diagnosis #

1. Open PostgreSQL server live dashboard

2. Click on the query to get details

Mitigation #

1. Cancel the query

   SELECT
       pg_cancel_backend(pid),
       usename,
       datname,
       application_name,
       client_addr,
       client_port,
       state,
       wait_event_type,
       wait_event,
       state_change,
       query
   FROM pg_stat_activity
   WHERE pid = <replace_with_pid>;
   

2. If query is not cancelled, kill the query

   SELECT
       pg_terminate_backend(pid),
       usename,
       datname,
       application_name,
       client_addr,
       client_port,
       state,
       wait_event_type,
       wait_event,
       state_change,
       query
   FROM pg_stat_activity
   WHERE pid = <replace_with_pid>;
   

Additional resources #

n/a