How to Resolve "pkill / pg_terminate_backend Not Killing Database Query"

You call pg_terminate_backend() on a runaway query expecting it to stop immediately, but the process lingers, still consuming resources, sometimes for a surprisingly long time. Termination in PostgreSQL isn't always instant — certain operations can't be interrupted mid-step, and some states specifically resist a clean, quick termination.

The Problem

You identify a problematic long-running query and try to stop it:

SELECT pg_terminate_backend(48213);

 pg_terminate_backend
-----------------------
 t

The function returns true, suggesting success, but checking again moments later shows the process is still there:

SELECT pid, state, query FROM pg_stat_activity WHERE pid = 48213;

 pid   | state  | query
-------+--------+-------------------------
 48213 | active | UPDATE large_table SET...

Why It Happens

PostgreSQL's pg_terminate_backend() sends a termination signal to the backend process, but the process only actually dies at the next point it checks for interrupt signals — which isn't necessarily immediate. This behavior comes from a few specific scenarios:

• The backend is mid-way through an uninterruptible low-level operation — certain I/O operations, especially large disk writes or specific internal locking sequences, don't check for interrupts until they complete a step.
• The process is waiting on a lock held by another transaction, and while waiting for locks is normally interruptible, certain combinations of nested locking can introduce delay.
• A large rollback is in progress — if the query being terminated had done substantial work, PostgreSQL needs to roll that work back before the process can actually exit, and this rollback itself takes real time proportional to how much was done.
• You called pg_cancel_backend() instead of pg_terminate_backend() (or vice versa) without understanding the difference — cancel is a gentler request that only stops the current query, while terminate kills the entire backend connection.

The Fix

Start with the gentler option first — pg_cancel_backend() asks the query to stop cleanly, which is often faster and safer than a full termination since it doesn't need to tear down and clean up the entire connection:

SELECT pg_cancel_backend(48213);

Give it a few seconds and check whether the query actually stopped:

SELECT pid, state, query FROM pg_stat_activity WHERE pid = 48213;

If cancel doesn't work within a reasonable window, escalate to terminate, which is more forceful and closes the connection entirely:

SELECT pg_terminate_backend(48213);

If it's still not gone after a reasonable wait (tens of seconds, not instant), check what state it's actually in — a process rolling back significant work needs time proportional to how much it had done:

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE pid = 48213;

pid   | state       | wait_event_type | wait_event
------+-------------+------------------+------------
48213 | active      | IO               | DataFileWrite

A wait_event_type of IO means it's genuinely doing disk work it can't abandon mid-operation — waiting it out is usually the only option at the database level. If you need to be absolutely certain the process ends and PostgreSQL's own termination isn't cutting it, only as an absolute last resort, kill the underlying OS process directly:

sudo kill -9 $(ps aux | grep '48213' | awk '{print $2}')

Be aware that a hard kill -9 on a PostgreSQL backend process bypasses PostgreSQL's own clean shutdown handling for that connection, and depending on what it was doing, this can force PostgreSQL's postmaster to restart the whole database cluster and go through crash recovery as a safety measure — a far more disruptive outcome than just waiting a bit longer for a graceful termination.

Still Not Working?

If terminating individual backends isn't resolving the underlying problem because new problematic queries keep appearing, address the root cause instead of playing whack-a-mole with individual process IDs. Set a statement timeout at the role or database level so runaway queries are automatically cut off before they become a manual intervention problem:

ALTER ROLE app_user SET statement_timeout = '30s';

This applies going forward to new connections from that role, automatically cancelling any single statement that runs longer than the configured threshold, which prevents the entire class of problem rather than requiring you to manually terminate each runaway query as it shows up.