pg_cancel_backend vs pg_terminate_backend Explained

2026-08-07

pg_cancel_backend vs pg_terminate_backend: what actually happens when you pull the trigger

Quick answer

pg_cancel_backend(pid) stops the current statement and leaves the session connected. pg_terminate_backend(pid) kills the entire session, aborting any open transaction and dropping the client's connection. Use cancel first whenever a statement is running; reach for terminate only when a session is stuck idle-in-transaction or ignoring the cancel entirely.

That's the two-sentence version. The rest of this is what actually causes incidents: why neither signal is instant, why "the rollback takes as long as the write" is wrong on Postgres, why your intervention shows up on the disk graphs two hours later, and how to pick the right PID out of 400 rows so you don't cancel the victim instead of the blocker.

If you need the decision right now, I recorded a three-minute version of this on YouTube. This is the long form: exact SQL, permissions, error codes, edge cases, and the timeout settings that mean nobody has to make this call by hand again.

pg_cancel_backend vs pg_terminate_backend Explained

What each function actually does

pg_cancel_backend(pid) sends a cancel request, implemented as SIGINT, to the named backend. It asks the backend to abandon its current statement. The session survives, the connection stays open, the transaction aborts if the statement was inside one, and the client gets an error for that statement only.

pg_terminate_backend(pid) sends SIGTERM. The session ends entirely. Any open transaction is aborted, locks release when the process exits, and the client connection is closed out from under whatever pool owns it.

Both return boolean, and this is the most misread part of the interface. The return value tells you the signal was sent — not that the query stopped, and not that the backend exited. A true return with the same PID still sitting in pg_stat_activity twenty seconds later is normal.

Ctrl-C in psql uses the same machinery as pg_cancel_backend. So does Statement.cancel() in pgJDBC and cancel() in most drivers. There's no separate, more privileged cancel path reserved for clients.

| | pg_cancel_backend | pg_terminate_backend | |---|---|---| | Signal | SIGINT | SIGTERM | | Scope | Current statement | Entire session | | Session survives | Yes | No | | Open transaction | Aborted | Aborted | | Locks released | At transaction abort | At process exit | | Client sees | ERROR on that statement, connection usable | FATAL, connection closed | | SQLSTATE | 57014 query_canceled | 57P01 admin_shutdown | | Works on "idle in transaction" | No | Yes | | Timeout argument | No | Yes, PG14+ |

Neither signal is instant — CHECK_FOR_INTERRUPTS is the whole story

The folk model says cancel is the polite request and terminate is the one that actually works. That's wrong, and believing it leads to the worst version of this incident: someone cancels a session in postgres, sees nothing after four seconds, terminates, sees nothing after another four seconds, and reaches for kill -9.

Both requests are recorded as a flag and acted on at the next interrupt checkpoint. Postgres code calls CHECK_FOR_INTERRUPTS() throughout executor loops, lock waits, buffer reads and network I/O. If the backend is between two of those calls, it processes nothing — which means a backend ignoring your cancel is, in most cases, about to ignore your terminate for exactly the same reason.

Interruptible in practice:

Heavyweight lock waits — this is why Ctrl-C works on a session blocked behind another transaction
Most buffer and file I/O waits inside normal executor paths
pg_sleep, and long executor loops like sequential scans, sorts, hash joins

Not reliably interruptible:

Tight loops inside third-party C functions that never call the interrupt macro
Syscalls hung on storage that's stopped answering — an NFS mount or a degraded EBS volume holds a backend in D state where no signal arrives
Writes blocked against a client that opened a cursor, stopped reading, and never disconnected

Send the cancel, then wait 10–30 seconds and re-read pg_stat_activity. Don't escalate to terminate on the assumption it's a stronger hammer — mechanically, it isn't. If the backend hasn't reacted to cancel in that window, it likely won't react to terminate either, and you're now troubleshooting a stuck process, not a slow query.

If you do escalate, use the PG14 timeout argument so the function tells you the truth:

-- wait up to 5 seconds for the process to actually exit
SELECT pg_terminate_backend(48213, 5000);

A false return plus a WARNING means the backend was still alive when the timeout expired. Check the host, the storage, and pg_stat_activity.wait_event next — not more signals.

pg_terminate_backend rollback: why Postgres isn't Oracle

You'll hear this in every incident channel: "don't cancel it, the rollback will take another 40 minutes." That advice comes from databases with an undo log. Postgres doesn't have one.

When a transaction aborts, Postgres marks its XID as aborted in pg_xact. That's the abort. The rows it inserted or updated are still sitting in the heap — simply dead, since no snapshot will see them again and VACUUM will reclaim them later. There's no row-by-row reversal, so abort is close to constant time regardless of whether the transaction wrote 40 rows or 40 million. It's a flag flip, not a rewind.

What actually varies is cleanup:

Releasing locks and cleaning up resource owners
Unlinking files created by the aborted transaction — a cancelled CREATE INDEX or CLUSTER on a large relation has to delete what it built
Dropping spilled temp files from sorts and hashes
Aborting large numbers of subtransactions, which is why a loop with a BEGIN ... EXCEPTION block per row aborts less cheaply than a plain UPDATE
Firing or discarding queued AFTER-trigger state

Costs are deferred, not avoided. Take a batch UPDATE that gets 8 million rows into a 60-million-row table, average row width 300 bytes, three indexes, before you cancel it. The abort returns in under a second. You've also just created roughly 2.4GB of dead heap tuples plus matching index entries, WAL already written and streamed to replicas, and an autovacuum run that now competes with your morning traffic.

Cancelling is still the right call — just log it, because the table won't shrink on its own. Dead space becomes reusable after VACUUM and only returns to the filesystem via VACUUM FULL or a rewrite.

How to find the right PID before you kill anything

Never signal a PID you read off a Slack message. Re-derive it.

SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       clock_timestamp() - xact_start  AS xact_age,
       clock_timestamp() - query_start AS query_age,
       clock_timestamp() - state_change AS since_state_change,
       left(regexp_replace(query, '\s+', ' ', 'g'), 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;

backend_type = 'client backend' keeps checkpointer, walwriter and autovacuum workers off your candidate list. Excluding pg_backend_pid() stops you terminating your own psql session — embarrassing but survivable — or the session you were about to run the fix from, which is worse.

Find the actual blocker

SELECT blocked.pid              AS blocked_pid,
       blocked.usename          AS blocked_user,
       clock_timestamp() - blocked.query_start AS blocked_for,
       left(blocked.query, 80)  AS blocked_query,
       blocking.pid             AS blocking_pid,
       blocking.state           AS blocking_state,
       clock_timestamp() - blocking.xact_start AS blocking_xact_age,
       left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid
JOIN pg_stat_activity AS blocking ON blocking.pid = bpid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY blocking_xact_age DESC NULLS LAST;

pg_blocking_pids() finds the actual head of the queue, which usually isn't the session everyone's staring at. People tend to cancel the DDL statement stuck waiting for a lock, when the thing to cancel is the transaction holding it.

Read the three timestamps carefully — they answer different questions. query_start is when the current statement began. xact_start is when the transaction began, and it's the one that matters for locks and the xmin horizon. state_change is when the session last changed state.

The dangerous row is the one where state is idle in transaction, query shows something harmless like SELECT 1, and xact_age is 47 minutes. That session isn't running anything — it's holding every lock it ever took and pinning the vacuum horizon. The query text tells you nothing about the damage.

Decision table

| Situation | Action | Why | |---|---|---| | Runaway analytics SELECT, no locks blocking anyone | Cancel | Session and pool slot survive, app just retries | | Bulk write you regret | Cancel | Terminate saves nothing on the abort; you only lose the connection too | | idle in transaction, old xact_start, holding locks | Terminate | There's no statement to cancel, so cancel is a no-op | | DDL stuck behind a lock queue | Cancel the head-of-line holder, not the DDL | The DDL is a victim; killing it just makes you rerun it | | Autovacuum worker in the way of a deploy | Cancel, reluctantly | The launcher will reschedule the table shortly | | Anti-wraparound autovacuum worker | Leave it alone | It restarts immediately; repeated cancels walk the cluster toward wraparound | | Backend ignoring signals after 60 seconds | Terminate with a timeout, then investigate the host | Suspect storage or a non-cooperative C function | | Locks held with no matching session | Check pg_prepared_xacts, then ROLLBACK PREPARED | No backend owns a prepared transaction, so signals do nothing | | Anything at all | Not kill -9 | See below |

Permissions: who's allowed to pull the trigger

By default, a non-superuser can signal backends owned by its own role or any role it's a member of. Membership in the predefined role pg_signal_backend (added in 9.6) extends that to other roles' backends — with one carve-out: it can't signal superuser-owned backends.

That's enough to build a genuinely useful on-call role with no superuser rights:

CREATE ROLE oncall LOGIN PASSWORD '...';
GRANT pg_read_all_stats  TO oncall;   -- see every session's query text
GRANT pg_signal_backend  TO oncall;   -- cancel/terminate app backends

pg_read_all_stats matters more than people expect. Without it, pg_stat_activity shows null query text for other roles' sessions, and you end up guessing which PID is the bad one.

On managed platforms, the account you're given usually isn't a true superuser. RDS and Aurora hand you rds_superuser; Cloud SQL hands you cloudsqlsuperuser. Both can signal ordinary backends but not genuine superuser-owned ones. Test your on-call role's ability to cancel a real app query during business hours — discovering the gap at 2am is a bad time to file a support ticket.

Never kill -9 a backend

If you take one thing from this article, take this.

SIGINT and SIGTERM sent via kill(1) are equivalent to pg_cancel_backend and pg_terminate_backend, and they're safe. kill -9 is not. SIGKILL gives the backend no chance to clean up shared memory, so the postmaster has to assume shared memory may be corrupt. Its response is to terminate every other backend in the cluster and run crash recovery.

You meant to stop one bad query. You just triggered minutes of total downtime and a reconnect storm across your whole connection pool estate.

Also never signal the postmaster PID — pg_terminate_backend on it is a shutdown request in disguise.

The timeouts that mean you never have to do this manually

Every intervention above is a symptom of a missing timeout. Set these per role, not globally.

statement_timeout — defaults to 0 (disabled), in milliseconds. When it fires it cancels the statement, with identical abort semantics to pg_cancel_backend, including SQLSTATE 57014. Don't set it in postgresql.conf — it will cancel your pg_dump, your CREATE INDEX, your nightly reindex right along with the runaway query.

lock_timeout — aborts a statement that's waited too long for any lock. Raises 55P03, not 57014, so retry logic can tell "couldn't get the lock" from "ran too long." Set it low, 2–5 seconds, on migration roles.

idle_in_transaction_session_timeout (PG 9.6+) — terminates the session, not merely the transaction. This one setting prevents most of the worst incidents.

idle_session_timeout (PG14+) — terminates sessions idle outside a transaction. Keep it above your pool's idle lifetime or you'll generate noise.

transaction_timeout (PG17+) — terminates the session when any transaction exceeds the duration, covering the "many short statements, one endless transaction" pattern that statement_timeout never caught.

client_connection_check_interval (PG14+, Linux) — defaults to 0. Lets the server notice a disconnected client mid-query and abort it. A JDBC socketTimeout, a PgBouncer query_timeout, or a Lambda timeout does not stop the query on its own — the client gives up, but your backend keeps executing and holding locks until this setting or an explicit cancel intervenes.

Scope them by role:

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

ALTER ROLE app_reporting SET statement_timeout = '10min';
ALTER DATABASE analytics SET statement_timeout = '30min';

-- carve-out so backups, reindexes and migrations aren't cut off mid-flight
ALTER ROLE maintenance   SET statement_timeout = 0;
ALTER ROLE maintenance   SET idle_in_transaction_session_timeout = '15min';

And inside a single transaction that legitimately needs longer:

BEGIN;
SET LOCAL statement_timeout = '0';
SET LOCAL lock_timeout = '5s';
CREATE INDEX CONCURRENTLY ...;
COMMIT;

Error codes you'll see afterward

| Cause | Message | SQLSTATE | Severity | Retryable? | |---|---|---|---|---| | pg_cancel_backend, Ctrl-C | canceling statement due to user request | 57014 | ERROR | Yes — connection intact | | statement_timeout | canceling statement due to statement timeout | 57014 | ERROR | Yes | | pg_terminate_backend | terminating connection due to administrator command | 57P01 | FATAL | No — reconnect required | | lock_timeout | canceling statement due to lock timeout | 55P03 | ERROR | Yes | | idle_in_transaction_session_timeout | terminating connection due to idle-in-transaction timeout | 25P03 | FATAL | No | | Standby recovery conflict | canceling statement due to conflict with recovery | 40001 | ERROR | Yes, governed by max_standby_streaming_delay |

A 57014 means the connection is still good and the statement can be retried with backoff. A 57P01 means the socket is gone and the pool must discard and reopen it — pools that retry on the same handle just produce a second, more confusing error. Set log_min_error_statement = 'error' and include %p %u %d %a in log_line_prefix so you're not reconstructing who killed what from memory the next morning.

Two real interventions

A 90-minute UPDATE. A backfill on a 60-million-row table, single transaction, no batching, blocking a queue of writers. We cancelled it — the abort returned in under a second, the queue drained, the API recovered in about fifteen seconds. Then the real bill arrived: ~40GB of new heap and index space that would never hold a live tuple, and a table needing a supervised vacuum before the next scheduled analyze. Nobody paid a second 90 minutes of undo. Everybody paid in vacuum and disk.

An idle-in-transaction ghost. A worker crashed mid-transaction after taking an AccessExclusiveLock for a schema change. The TCP connection stayed established, so the backend sat idle in transaction with a 52-minute-old xact_start and COMMIT in the query column. 200 sessions queued behind it. Someone cancelled it three times and reported "cancel isn't working" — of course not, there was no statement to cancel. One pg_terminate_backend cleared it in two seconds. The fix was one line: idle_in_transaction_session_timeout = '60s' on that worker's role.

The 60-second runbook

1. Identify. Run the pg_stat_activity query above. Note PID, state, wait_event_type, xact_age. 2. Decide blocker or victim. Run the pg_blocking_pids() query. Act on the head of the chain only. 3. Cancel. SELECT pg_cancel_backend(<pid>); 4. Wait 15–30 seconds. Don't send more signals in this window. 5. Verify. Re-run the query — gone or idle means done. 6. Escalate. SELECT pg_terminate_backend(<pid>, 5000); and read the return value. 7. Confirm the row has left pg_stat_activity — that's your proof the locks are released, not the boolean. 8. If nothing worked, investigate the host and storage. Not kill -9. 9. Check the damage: n_dead_tup in pg_stat_user_tables, table size delta, WAL volume. Schedule a vacuum if warranted. 10. Fix the cause. Add or tighten the timeout on that role before you close the incident, or you'll be reading this runbook again next month.

Where MyDBA fits

Instead of hand-running these queries at 2am, a continuous session monitor can surface long-running statements and blocking chains with the real blocker at the head, hand you the exact pg_cancel_backend or pg_terminate_backend call with the correct PID filled in, and recommend statement_timeout, lock_timeout and idle_in_transaction_session_timeout values based on your observed workload. MyDBA does this alongside vacuum, bloat and disk-capacity checks in one graded report, with each finding paired to a runnable fix — so these pages stop recurring in the first place.