How to Identify and Cancel Long-Running PostgreSQL Queries

Database connections are a finite resource. When queries get stuck in an idle transaction or run longer than expected, they hold onto those connections. As more queries pile up waiting for the stuck ones to complete, your available connections get consumed. Once the connection limit is reached, your application can no longer communicate with the database and becomes unresponsive to users.

This situation can happen for several reasons. A poorly optimized query might take hours instead of seconds. A transaction might start but never commit, leaving it idle. A background job could fail partway through and hang indefinitely. Whatever the cause, you need to identify these problematic queries and terminate them before they exhaust your connection pool.

Prerequisites

  • SSH access to your PostgreSQL database server
  • Sudo or PostgreSQL superuser privileges
  • PostgreSQL 14 or later (for pg_stat_activity view)

Step 1: Connect to PostgreSQL

SSH into your database server and switch to the postgres user:

ssh dbserver.example.com
sudo su - postgres
psql

Step 2: Identify Long-Running and Stuck Queries

Use the pg_stat_activity view to see all active database connections and their current queries:

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    NOW() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

This query shows you all non-idle connections sorted by how long they’ve been running. The columns tell you:

  • pid: The process ID you’ll use to cancel or terminate the query
  • usename: The database user running the query
  • application_name: The application or tool that initiated the connection
  • client_addr: The IP address of the client machine
  • state: The current state of the connection (active, idle in transaction, etc.)
  • duration: How long the query has been running
  • query: The actual SQL query being executed

Pay close attention to these warning signs:

Queries stuck in idle transactions: Look for state = 'idle in transaction'. These connections started a transaction but never committed or rolled back. They may hold locks that block other queries.

Long-running queries: Check the duration column. If a query has been running for hours when it should take seconds or minutes, something is wrong.

Repeated identical queries: Multiple instances of the same query with long durations might indicate a problem with your application code or a deadlock situation.

Step 3: Cancel the Query

Once you’ve identified a problematic query, first try to cancel it gracefully using pg_cancel_backend(). Replace <pid> with the process ID from Step 2:

SELECT pg_cancel_backend(12345);

The pg_cancel_backend() function sends a SIGINT signal to the backend process, which attempts to cancel the current query. The connection remains open and the client receives an error message indicating the query was canceled. This is the safer option because it allows the application to handle the cancellation and potentially retry.

If the function returns true, the cancellation request was sent successfully. Run the query from Step 2 again to verify the process is gone.

Step 4: Terminate the Connection (If Canceling Fails)

If pg_cancel_backend() doesn’t work—the process might be stuck in a state where it can’t respond to cancellation—you’ll need to forcefully terminate the entire connection:

SELECT pg_terminate_backend(12345);

The pg_terminate_backend() function sends a SIGTERM signal that forcefully closes the connection. This immediately ends the backend process and rolls back any uncommitted transactions. The client will lose its connection to the database.

Use termination as a last resort because it’s more disruptive than cancellation. However, when a query is truly stuck and consuming resources, termination is necessary to free up the connection.

Preventing the Problem

While knowing how to terminate stuck queries is useful, preventing them in the first place is better. The first step is to audit your application and determine why queries are running longer than expected, then optimize those queries at the source. Timeouts and forced cancellation are guardrails, not a substitute for query tuning.

It’s also important to understand that these safeguards can have unintended consequences. If your timeout values are too aggressive, PostgreSQL may cancel legitimate queries before they finish, especially jobs you already know are expected to run longer.

Set statement timeouts: Configure statement_timeout in your PostgreSQL configuration to automatically cancel queries that run longer than a specified duration:

ALTER DATABASE myapp SET statement_timeout = '5min';

Set idle transaction timeouts: Use idle_in_transaction_session_timeout to automatically close connections stuck in idle transactions:

ALTER DATABASE myapp SET idle_in_transaction_session_timeout = '10min';

Monitor connection usage: Set up monitoring to alert you when your connection pool is getting close to the maximum. This gives you time to investigate before connections are exhausted.

Use connection pooling: Tools like PgBouncer can help manage connections more efficiently and prevent runaway queries from consuming all available connections.

Identifying and terminating problematic queries is an essential skill for maintaining a healthy PostgreSQL database. With these tools, you can quickly diagnose connection issues and restore your application to normal operation before users are significantly impacted.