pg_cancel_backend vs pg_terminate_backend Explained

TL;DR

• pg_cancel_backend(pid) sends SIGINT. It kills the running statement; the session stays connected.
• pg_terminate_backend(pid) sends SIGTERM. It kills the whole backend process and drops the client connection.
• Both are cooperative. They're acted on at CHECK_FOR_INTERRUPTS() points, so a backend stuck in a blocking syscall will ignore both until it comes up for air.
• The return value means "signal sent", not "query stopped". If the PID isn't a backend you get a warning and false.
• Rollback in Postgres is cheap because there is no undo log. What it costs you is bloat and the autovacuum work that follows.
• Never kill -9 a backend. Set statement_timeout instead and stop doing this by hand.

📖 Read the full guide: pg_cancel_backend vs pg_terminate_backend Explained

I walked through this whole decision tree with live psql output in a companion video, if you'd rather watch than read: pgdba on YouTube.

First, find the right PID

At 2am the failure mode isn't hesitating. It's killing the wrong session. Here's the triage query I keep in ~/.psqlrc as :runaway:

SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       age(now(), xact_start)  AS xact_age,
       age(now(), query_start) AS query_age,
       pg_blocking_pids(pid)   AS blocked_by,
       left(query, 120)        AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND state <> 'idle'
ORDER BY xact_start NULLS LAST;

  pid  | usename | application_name |        state        | wait_event_type | wait_event |   xact_age   |  query_age   | blocked_by |                query
-------+---------+------------------+---------------------+-----------------+------------+--------------+--------------+------------+--------------------------------------
 41022 | app_rw  | orders-api       | active              | Lock            | transactio | 00:00:41.2   | 00:00:41.2   | {40988}    | UPDATE orders SET status = 'shipped'
 40988 | app_rw  | psql             | idle in transaction |                 |            | 00:22:07.9   | 00:21:55.1   | {}         | SELECT * FROM orders WHERE id = 91
 41104 | report  | metabase         | active              |                 |            | 00:14:33.0   | 00:14:33.0   | {}         | SELECT o.id, sum(l.qty) FROM orders

Three things that query buys you. backend_type = 'client backend' filters out walsenders, autovacuum workers and the logical replication launcher, none of which you want to signal casually. pid <> pg_backend_pid() stops you from cancelling yourself, which is embarrassing but harmless. And pg_blocking_pids(pid) tells you who the actual culprit is: in that output, 41022 is a victim. The problem is 40988, sitting idle in transaction for 22 minutes.

One gotcha: pg_stat_activity.query is truncated to track_activity_query_size bytes, default 1024. A generated ORM query will look cut off mid-clause. That's the setting, not a bug, and raising it costs shared memory per connection slot.

pg_cancel_backend vs pg_terminate_backend: what each does

Both functions do one thing: send a signal and return whether the send succeeded.

SELECT pg_cancel_backend(41104);

Client side:

ERROR:  canceling statement due to user request
SQLSTATE: 57014
report=> SELECT 1;
 ?column?
----------
        1

The session lives. Contrast with terminate:

SELECT pg_terminate_backend(40988);

FATAL:  terminating connection due to administrator command
SQLSTATE: 57P01
server closed the connection unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.

	pg_cancel_backend	pg_terminate_backend
Signal	SIGINT	SIGTERM
Scope	current statement	entire backend process
Session survives?	yes	no
Client sees	ERROR 57014 query_canceled	FATAL 57P01 admin_shutdown
Permission	superuser, pg_signal_backend, or member of the owning role	same
Timeout argument	no	yes, since PG 14

Members of pg_signal_backend cannot signal a superuser's backend. That bites during incidents when the runaway query is being run by the DBA who went home.

And in psql, Ctrl+C is not a client-side abort. It sends a cancel request over the protocol, which is exactly pg_cancel_backend aimed at your own backend.

The myth I want to kill: "rollback takes as long as the write"

You will read this on Stack Overflow roughly once a week: don't cancel that 40-minute UPDATE, the rollback will take another 40 minutes replaying it backwards.

That's Oracle thinking. Postgres has no undo log. Aborting a transaction writes the xid as aborted in pg_xact and releases its locks. That's it. It doesn't matter whether the transaction touched 12 rows or 120 million; the abort itself is essentially constant time.

What actually happened is that every row the UPDATE wrote is now a dead tuple sitting in the heap and the indexes. So:

• You bloated the table and its indexes by the size of the write.
• You handed autovacuum a job it will do later, on its schedule, competing with your production traffic.
• The WAL for all of it was already written and already streamed to your replicas. WAL is generated regardless of eventual commit or abort. Cancelling doesn't un-ship it.

So cancel that runaway UPDATE when you need the locks back. It'll return in seconds. Just know you've traded a long write for a vacuum bill, and postgres cancel query rollback is fast for exactly this reason.

When cancel doesn't work, and why terminate usually won't either

Interrupts are processed at CHECK_FOR_INTERRUPTS() macros scattered through the executor and utility code. If the backend never reaches one, nothing happens. The real cases:

• Blocked in a syscall against hung storage or a stalled NFS/EBS volume.
• A tight loop inside extension C code that never checks for interrupts. PostGIS geometry work and some FDWs have historically been guilty here.
• A handful of genuine critical sections.

Now the correction I want to make. A very common claim is that a backend waiting on a heavyweight lock ignores cancel. It doesn't. Lock waits are interruptible latch waits, and pg_cancel_backend on a session sitting in wait_event_type = 'Lock' works immediately, every time. Try it yourself with two psql windows and a SELECT ... FOR UPDATE.

The punchline: if a backend ignored your cancel, escalating to terminate rarely helps, because SIGTERM is checked at the same points. If a session is truly wedged, you're looking at a storage problem, not a signalling problem.

The timeout argument (PG 14+)

SELECT pg_terminate_backend(41022, 5000);  -- wait up to 5s for actual exit

With a non-zero timeout it waits for the process to actually exit and returns true, or emits a warning and returns false if the timeout elapsed. That's the difference between "I sent a signal" and "the backend is gone".

An escalation snippet I use, wrapped in DO for one-shot incident work:

DO $$
DECLARE
  target int := 41022;
  gone   boolean;
BEGIN
  PERFORM pg_cancel_backend(target);
  PERFORM pg_sleep(3);
  IF EXISTS (SELECT 1 FROM pg_stat_activity WHERE pid = target AND state = 'active') THEN
    gone := pg_terminate_backend(target, 5000);
    IF NOT gone THEN
      RAISE WARNING 'pid % did not exit; check storage/IO, do NOT kill -9', target;
    END IF;
  END IF;
END $$;

The decision tree I actually use

1. Runaway read-only SELECT. Cancel. Session survives, the app's connection pool doesn't notice.
2. Big write you've decided to abandon. Cancel. Accept the bloat, then check pg_stat_progress_vacuum an hour later.
3. Idle in transaction holding locks. Terminate. Cancel is a no-op here: there is no statement to cancel, and it will not roll back the open transaction or release its locks. I've watched people fire cancel three times at an idle-in-transaction session and conclude Postgres is broken.
4. Blocking chain. Cancel the root blocker from pg_blocking_pids, not the victims. Killing victims just makes the app retry into the same wall.
5. Not sure how far along it is? Check pg_stat_progress_create_index or pg_stat_progress_copy first. A CREATE INDEX at 94% is worth 90 more seconds.

Edge cases that bite

Concurrent index builds. Cancel a CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY and you get an invalid index: unusable for queries, still maintained on every write. Worst of both worlds. Find and clean them:

SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index
WHERE NOT indisvalid;

Then DROP INDEX CONCURRENTLY each one and restart the build.

Prepared transactions. A transaction that reached PREPARE TRANSACTION outlives its backend. Terminating the session releases nothing; the locks stay until ROLLBACK PREPARED or COMMIT PREPARED. Check pg_prepared_xacts before you conclude the lock is a ghost.

PgBouncer transaction pooling. In pool_mode = transaction, a server connection is leased only for the duration of a transaction. The PID you read from pg_stat_activity may be serving a completely different client by the time you signal it. Requery immediately before you act, then read fast, act fast.

Autovacuum workers. Cancelling one stops that run only; the table gets rescheduled. Habitually killing anti-wraparound vacuums is exactly how clusters end up in wraparound trouble.

Retry loops. Half the apps I've seen will re-issue the identical query within two seconds. Fix the query or add a timeout; killing it is theatre.

Never SIGKILL a backend

kill -9 on a backend makes the postmaster assume shared memory may be corrupt. It terminates every other backend and forces crash recovery. You took the whole cluster down to stop one report query. There is no situation where this is the right first move. Neither pg_cancel_backend nor pg_terminate_backend uses SIGKILL, and that's not an accident.

The settings that mean you never have to do this by hand

Setting	Default	Action	SQLSTATE
statement_timeout	0	cancels the statement	57014
lock_timeout	0	aborts statement waiting on a lock	55P03
idle_in_transaction_session_timeout	0	terminates the session	25P03
transaction_timeout (PG 17+)	0	terminates the session

statement_timeout does not cover idle time inside an open transaction, which is why idle_in_transaction_session_timeout exists — the standard fix for a session that shows idle in transaction terminate in monitoring alerts. transaction_timeout on PG 17 closes the last gap: many short statements interleaved with idle time.

Scope them per role, not globally:

ALTER ROLE app_rw     SET statement_timeout = '30s';
ALTER ROLE app_rw     SET lock_timeout = '3s';
ALTER ROLE app_rw     SET idle_in_transaction_session_timeout = '60s';

ALTER ROLE report     SET statement_timeout = '5min';
ALTER ROLE migrations SET statement_timeout = 0;
ALTER ROLE migrations SET lock_timeout = '5s';

ALTER DATABASE prod   SET idle_in_transaction_session_timeout = '10min';  -- backstop

Rough starter values by workload:

• OLTP app role: statement_timeout = 5-30s, lock_timeout = 2-5s, idle_in_transaction_session_timeout = 1-5min
• Reporting/analytics role: statement_timeout = 5-15min
• Migration/maintenance role: leave statement_timeout unset, but still set lock_timeout so a migration doesn't sit forever waiting on a lock during business hours

The docs explicitly warn against setting statement_timeout in postgresql.conf, and this is why: pg_dump, a four-hour CREATE INDEX, and your migration tooling all inherit it. You'll discover this when the nightly backup starts failing at 30 seconds. Role-level settings apply to new sessions; SET LOCAL inside a transaction overrides them when you genuinely need longer.

Recovery conflicts: the standby cancels for you

On a hot standby you'll see cancels you didn't send:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
SQLSTATE: 40001

Same mechanism, just triggered by the server itself instead of by you: a query on the standby is holding up WAL replay past max_standby_streaming_delay. hot_standby_feedback = on trades that for bloat on the primary, because the standby tells the primary to hold back vacuum on rows it still needs. Pick one deliberately; the default gives you neither.

Cheat sheet

-- who's the root blocker? (pg_stat_activity long running query check)
SELECT pid, pg_blocking_pids(pid), age(now(), query_start), state, left(query,80)
FROM pg_stat_activity WHERE backend_type='client backend' AND state<>'idle';

-- runaway SELECT / abandoned write -- kill query postgres, session lives
SELECT pg_cancel_backend(:pid);
-- idle in transaction, or cancel was ignored
SELECT pg_terminate_backend(:pid, 5000);
-- never
kill -9 

If you'd rather have the long runners and blocking chains handed to you instead of typing that query at 2am, MyDBA's health check surfaces both with a single ready-to-run command.

Tags: postgres, database, sql, devops ## Wrapping up

None of this is exotic — it's a handful of signals, a couple of timeouts, and knowing which one applies to the session in front of you. The mistakes that actually hurt come from skipping the diagnosis step: killing a victim instead of the blocker, cancelling an idle-in-transaction session and expecting locks to release, or reaching for kill -9 because cancel "didn't work fast enough." Set your timeouts per role, keep the triage query handy, and you'll rarely need to make these calls under pressure in the first place.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=pg-cancel-backend-vs-pg-terminate-backend

If 2am triage queries aren't something you want to remember by heart, point MyDBA at your cluster and let it surface the blocking chains and long runners for you.