PostgreSQL provides two built-in ways to stop a running backend: pg_cancel_backend and pg_terminate_backend. On the surface, both appear to do the same thing—stop a query. In practice, however, their behavior is dramatically different. One may take several seconds or appear to do nothing at all, while the other stops the backend almost instantly.
This difference is not accidental. It is a direct consequence of how PostgreSQL's executor, signal handling, and safety guarantees are designed. To understand why, we need to look at how cancellation actually works inside the backend process.
Cancellation in PostgreSQL Is Cooperative, Not Preemptive
When pg_cancel_backend(pid) is called, PostgreSQL does not interrupt the query at the operating system level. Instead, it sends a SIGINT signal to the target backend and marks the process as having a pending query cancellation.
At this point, nothing stops immediately.
The backend continues running until it reaches a safe interruption point, where it explicitly checks whether a cancellation has been requested. Only then does it abort the query.
This design is intentional. PostgreSQL never allows a query to be stopped in the middle of a critical internal operation. Doing so could leave memory contexts corrupted, locks half-released, or internal data structures in an inconsistent state. Instead, the executor must voluntarily acknowledge the cancellation request.
Where PostgreSQL Actually Checks for Cancels
Inside the executor and lower storage layers, PostgreSQL inserts explicit interrupt checkpoints. These checks are not automatic; they are manually placed at locations considered safe.
The executor typically checks for interrupts:
- Between tuple fetches
- During expression evaluation
- At certain access method boundaries
- When control returns to higher executor nodes
If execution stays inside a tight loop without reaching one of these checkpoints, the backend simply does not notice the cancel request.
This explains a common production observation: simple queries cancel immediately, while complex analytical queries appear immune to cancellation.
Why Some Queries Ignore pg_cancel_backend for a Long Time
Some executor nodes are designed to process large volumes of data without yielding control. Examples include:
- Hash aggregation building large in-memory hash tables
- Sort nodes consuming all input before producing output
- Materialization nodes writing large temporary files
- Queries dominated by heavy sequential I/O
While these operations are running, the executor may not reach an interrupt checkpoint for a long time. The cancel signal is already pending, but the backend has not yet reached a place where it is safe to act on it.
In effect, pg_cancel_backend is waiting for the executor to say, "I'm at a safe point now."
Why pg_terminate_backend Feels Immediate
pg_terminate_backend takes a completely different path.
Instead of sending SIGINT, it sends SIGTERM. This is not a request to stop the query—it is an instruction to shut down the backend process.
When the backend receives SIGTERM, PostgreSQL sets an internal flag (ProcDiePending) that indicates the process must exit. When the backend reaches its next interrupt checkpoint and processes this flag, it immediately abandons normal executor flow and jumps directly into emergency shutdown logic. The current transaction is aborted, locks are released, and the session is terminated.
While pg_terminate_backend technically still uses the same interrupt checking mechanism as pg_cancel_backend, the key difference is that ProcDiePending has higher priority than QueryCancelPending. Once the backend processes this signal, it doesn't just cancel the query—it begins terminating the entire session. This more aggressive shutdown path is why termination appears immediate even when cancellation does not.*
*Note: Both mechanisms rely on the backend reaching CHECK_FOR_INTERRUPTS() calls, but pg_terminate_backend triggers a more forceful response that prioritizes session termination over graceful query cancellation.
Why PostgreSQL Does Not Use Termination for Normal Cancels
It may seem tempting to ask: why not always stop queries this way?
The answer is safety.
Forcefully terminating a backend:
- Aborts transactions abruptly
- Skips normal executor cleanup paths
- Can disrupt application connection pools
- Leaves cleanup work to be handled asynchronously
PostgreSQL allows this behavior only when explicitly requested, because it trades correctness and grace for immediacy. Using this approach for normal query cancellation would make PostgreSQL fragile under load.
pg_cancel_backend exists precisely to avoid these risks.
The Practical Difference in Real Systems
What DBAs experience in production aligns perfectly with this design:
- pg_cancel_backend works instantly for simple queries
- Cancellation is delayed for heavy aggregates, sorts, or scans
- Multiple cancel attempts do nothing until execution reaches a safe point
- pg_terminate_backend always works, regardless of query state
This is not a failure of PostgreSQL's cancellation mechanism. It is a consequence of a system that prioritizes correctness over preemptive interruption.
Choosing the Right Tool
pg_cancel_backend should be used when you want to stop a query safely, without killing the session. It is appropriate for user-initiated cancellations, long reports, and normal operational control.
pg_terminate_backend should be reserved for situations where a backend must stop no matter what—runaway queries, blocking sessions, or emergency intervention. It is effective, but intentionally destructive.
If you find yourself relying on termination frequently, it often indicates deeper issues such as poorly shaped queries, missing timeouts, or executor paths that do not yield control frequently enough.