Is it safe to kill a Postgres query?

Yes, when you do it through the server. pg_cancel_backend stops the running statement and leaves the session connected; pg_terminate_backend ends the session and rolls its transaction back. Both are ordinary supported operations and neither risks the data. What is not safe is reaching for the operating system: killing a backend from the shell takes the whole cluster down with it.

Try the polite one first, and know when it will not work

Cancelling asks the backend to abandon what it is doing at its next opportunity. Most of the time that is immediate. It will not work if the backend is not in a position to notice, which in practice means it is blocked inside a call that does not check for interruptions, or it is waiting on something outside the database entirely.

Terminating is the stronger request and it is still a request. If a cancel was ignored, a terminate frequently is too, and at that point you are no longer troubleshooting a slow query, you are troubleshooting a stuck process. Escalating faster does not help.

Rollback is not free either. A transaction that has written a great deal has to undo its in-memory state and release its locks, and on a very large write the delay between issuing the terminate and seeing the session disappear is real work rather than a hung command.

The one that takes the server down

A backend killed with an uncatchable signal from the shell cannot clean up shared memory, so the server assumes shared memory may be inconsistent and does the only safe thing: it terminates every other backend and runs recovery. Every connection in the system drops. This is correct behaviour and it is a very expensive way to end one query.

The exception people remember wrongly is that a normal termination signal to a single backend behaves like the supported function. It does, and it is still the wrong habit, because the next person to use the shell uses the other signal.

Permissions matter as much as mechanics: a monitoring role cannot end another user’s session unless it has been granted the right to signal backends, and discovering that during an incident is the standard way to lose ten minutes. Since PostgreSQL 14 the terminate function also takes an optional wait, so a script can ask for the session and find out whether it actually went.

Before ending anything, it is worth knowing whether this query is the problem or a victim of it. Lock contention and blocking trees covers reading the tree from the root, and wait event types is how to tell a query that is working from one that is queued behind somebody else.