How do I end long-running queries in my Amazon RDS for PostrgreSQL or Aurora PostgreSQL DB instance?

Short description

PostgreSQL offers two functions to end queries:

• pg_cancel_backend(pid): Ends a query but keeps the connection alive.
• pg_terminate_backend(pid): Ends a query and closes the connection. Running this function ends the full connection and so might affect other running queries. Use as a last resort.

To use these functions, you must have one of the following permissions:

• You're an rds_superuser or a member of the default role pg_signal_backend.
• You're connected to the database as the same database user of the session that must be canceled.

Notes:

• The pid is the process ID of the backend process that runs to cancel the query. Use the pid column of the pg_stat_activity view to find the process ID of an active backend process. For more information, see pg_stat_activity on the PostgreSQL website.
• pg_signal_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to the backend process identified by the process ID. For more information, see Server Signaling Functions on the PostgreSQL website.

Resolution

Review the following examples for the pg_cancel_backend(pid) and pg_terminate_backend(pid) functions:

Example: pg_cancel_backend(pid)

The following command, when run from another session, cancels the query that's run from the database backend with pid 8121. This function returns true when the query is canceled correctly. This function returns false if the query isn't there anymore or if the database connection doesn't exist.

postgres=> SELECT pg_cancel_backend(8121);
 pg_cancel_backend 
------------------------
 t
(1 row)

Example: pg_terminate_backend(pid)

The following command, when run from another session, ends the database connection with pid 8121. This function returns true whether the process has actually canceled. The response indicates only that the SIGTERM signal was successfully sent. The command doesn't immediately interrupt the backend process. Instead, it waits for the backend process to tend gracefully during CHECK_FOR_INTERRUPTS. This makes sure that no process is canceled, which leaves the shared memory in an inconsistent state

postgres=> SELECT pg_terminate_backend(8121);
 pg_terminate_backend 
------------------------
 t
(1 row)

How to cancel a long-running process that doesn't end

Sometimes the functions, pg_cancel_backend( ) and pg_terminate_backend( ), don't cancel the query. This is because the backend process for the function is running in an uninterruptible section. For example, the process might be waiting to acquire a lightweight lock or a read/write system call against a network storage device. The backend process isn't signaled and stalls indefinitely.

Restart the database engine to end the process.

Some best practices

• Tune timeout parameters like statement_timeout, idle_in_transaction_statement_timeout and idle_session_timeout (from PostgreSQL, version 14 onwards). It's a best practice to also set client-side timeouts and server-side timeouts (tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count) appropriately.
• The parameter statement_timeout can be set at appropriate levels (statement, user level, database, or instance).
  Note: It isn't best practice to set a short timeout at an instance level or database level as it cancels intentional long-running queries. If log_min_error_statement is set to ERROR or lower, the statement that timed out gets logged. For more information, see Statement Behavior on the PostgreSQL website.

Related information

How do I check running queries and diagnose resource consumption issues for my Amazon RDS for PostgreSQL or Aurora PostgreSQL DB instance?

How do I identify and troubleshoot performance issues and slow-running queries in my RDS for PostgreSQL or Aurora PostgreSQL instance?