ClickHouse KILL QUERY: Syntax, Permissions, and Monitoring

KILL QUERY is how you cancel a running query in ClickHouse — a runaway SELECT, a stuck INSERT, or any statement consuming more CPU, memory, or time than you can afford. It works by selecting target queries from system.processes using a WHERE clause and signaling them to stop cooperatively.

Cancellation in ClickHouse is not an instant kill -9. The server marks the query as cancelled, and the query checks for that signal at safe points during execution. Understanding this cooperative model — plus the grants required and how to confirm the query actually stopped — is the difference between a clean cancellation and a confusing one where the query "won't die."

Basic Syntax

KILL QUERY [ON CLUSTER cluster]
  WHERE <expression over system.processes>
  [SYNC|ASYNC|TEST]
  [FORMAT format]

The WHERE clause is evaluated against the system.processes table, so you can target queries by any column it exposes: query_id, user, query, elapsed, memory_usage, and more.

The most common form targets a specific query by its query_id:

KILL QUERY WHERE query_id = '550e8400-e29b-41d4-a716-446655440000';

Finding the Query to Kill

You can only kill a query you can identify, and identification means getting its query_id from system.processes. List active queries first:

SELECT
    query_id,
    user,
    elapsed,
    formatReadableSize(memory_usage) AS mem,
    read_rows,
    substring(query, 1, 100) AS query_preview
FROM system.processes
ORDER BY elapsed DESC;

SHOW PROCESSLIST is a convenience alias that returns the same data. From there, copy the query_id of the offending query into your KILL QUERY statement.

You can also kill by criteria instead of a single ID. For example, cancel every query from a particular user, or every query running longer than five minutes:

-- Kill all queries from a specific user
KILL QUERY WHERE user = 'reporting_app';

-- Kill long-running queries (elapsed is in seconds)
KILL QUERY WHERE elapsed > 300;

Be deliberate with broad WHERE clauses — a loose predicate can cancel far more than you intended.

SYNC, ASYNC, and TEST

The modifier after the WHERE clause controls how KILL QUERY behaves:

Use TEST to confirm your WHERE clause matches exactly what you expect before committing:

KILL QUERY WHERE elapsed > 300 TEST;

Use SYNC when you need confirmation that the query is gone before proceeding (for example, in a script that frees a resource afterward):

KILL QUERY WHERE query_id = 'abc-123' SYNC;

How Cancellation Actually Works

ClickHouse cancellation is cooperative. KILL QUERY marks the target query as cancelled; the query then checks for that flag at safe checkpoints inside the execution engine and unwinds when it reaches one. This is why a query rarely disappears instantly:

• A query streaming data through a pipeline typically stops within a fraction of a second.
• A query stuck in a tight I/O loop, waiting on a slow remote disk, or blocked inside an external call can take several seconds — occasionally longer — to reach a cancellation checkpoint.

In SYNC mode you will see a kill_status column reflecting this:

• waiting — the cancel signal was sent and ClickHouse is waiting for the query to stop at its next checkpoint.
• finished — the query has actually stopped.
• Any other value explains why termination did not succeed.

A query that lingers in waiting is not ignoring you; it simply hasn't reached a safe point yet.

Verifying the Query Was Cancelled

After an ASYNC kill (the default), confirm the result yourself. First, check that it's no longer running:

SELECT query_id, is_cancelled, elapsed
FROM system.processes
WHERE query_id = '550e8400-e29b-41d4-a716-446655440000';

If the row is gone, the query has finished cancelling. If it's still present with is_cancelled = 1, the cancellation is in progress but hasn't completed.

Once the query leaves system.processes, it lands in system.query_log. A cancelled query is recorded with an exception there, which is the authoritative record that it stopped before completing:

SELECT
    query_id,
    type,
    exception_code,
    exception,
    query_duration_ms
FROM system.query_log
WHERE query_id = '550e8400-e29b-41d4-a716-446655440000'
ORDER BY event_time DESC;

A cancelled query commonly surfaces as QUERY_WAS_CANCELLED. For more query-log inspection patterns, see ClickHouse query_log: handy queries.

Required Grants

KILL QUERY is a GLOBAL-level privilege. By default:

• A user can always cancel their own queries without any special grant.
• To cancel other users' queries, the user needs the KILL QUERY grant.

GRANT KILL QUERY ON *.* TO operator_role;

Because it's a global privilege, KILL QUERY cannot be scoped to a single database or table — it's granted on *.*. Treat it as an operational/administrative privilege and hand it out only to roles that need to manage running workloads. There is a separate KILL TRANSACTION privilege governing transaction cancellation.

Killing on a Cluster

system.processes is local to each node. A query that fans out across a cluster (for example, a distributed SELECT) has parts executing on multiple replicas, and a plain KILL QUERY only affects the node you're connected to.

Use ON CLUSTER to propagate the cancellation to every node:

KILL QUERY ON CLUSTER my_cluster
  WHERE query_id = '550e8400-e29b-41d4-a716-446655440000';

When tracing a distributed query across nodes, initial_query_id in system.processes links the child queries back to the original statement, which is useful for building a WHERE clause that catches every related fragment.

KILL MUTATION: Cancelling ALTER UPDATE/DELETE

KILL QUERY does not stop mutations. Mutations — ALTER TABLE ... UPDATE, ALTER TABLE ... DELETE, and similar — run as background operations, not as foreground queries, so they don't appear in system.processes. They live in system.mutations, and you cancel them with KILL MUTATION:

KILL MUTATION
  WHERE <expression over system.mutations>
  [TEST]
  [FORMAT format]

First find the stuck mutation:

SELECT
    database,
    table,
    mutation_id,
    command,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE is_done = 0;

Then cancel it:

KILL MUTATION
  WHERE database = 'analytics'
    AND table = 'events'
    AND mutation_id = '0000000123';

Important caveats:

• Changes already applied are not rolled back. A mutation that partially rewrote parts before you killed it leaves those parts as they are.
• A kill does not guarantee the mutation has fully stopped. A mutation can remain in an unfinished (is_done = 0) state if it's blocked behind another long-running mutation on the same table. On ClickHouse Cloud, system.mutations also exposes an is_killed column that flags mutations a kill has targeted.

For why mutations are expensive in the first place and how to avoid having to kill them, see ClickHouse mutations: UPDATE/DELETE performance impact.

Common Issues

The query keeps appearing in system.processes after KILL. This is the cooperative-cancellation model at work. The query hasn't reached a safe checkpoint yet. Give it time, watch is_cancelled = 1, and avoid issuing repeated KILL QUERY statements — they don't speed anything up. If a query truly never stops, that points at a deeper hang; see Debugging a hanging ClickHouse server or query.

KILL QUERY returns nothing. Your WHERE clause matched no rows in system.processes. The query may have already finished, or you're connected to a different node than the one running it. On a cluster, use ON CLUSTER or check the node where the query originated.

You can see the query but can't kill it. You likely lack the KILL QUERY grant and the query belongs to another user. Run with TEST to confirm whether it's a permissions issue.

You killed a mutation but it's still there. A kill doesn't make a mutation vanish instantly. Re-check system.mutations for is_done (and is_killed on ClickHouse Cloud), and look for an earlier mutation on the same table holding it up.

Best Practices

1. Identify before you kill. Always pull the query_id from system.processes (or run with TEST) so you cancel exactly the right query — not a broad predicate that takes down healthy workloads.

2. Use TEST for any criteria-based kill. When killing by user or elapsed rather than a single query_id, dry-run it first to see the blast radius.

3. Prefer SYNC in automation. Scripts that depend on a query being gone before the next step should use SYNC and check kill_status = 'finished'.

4. Use ON CLUSTER for distributed queries. A local kill won't reach replicas executing parts of the same statement.

5. Don't confuse queries with mutations. If KILL QUERY finds nothing, the operation you want to stop may be a mutation — switch to KILL MUTATION against system.mutations.

6. Treat the need to kill as a signal. Queries you regularly have to cancel usually point at missing limits. Set max_execution_time, max_memory_usage, and per-user quotas so runaway queries fail on their own rather than requiring manual intervention.

How Pulse Helps

Knowing the KILL QUERY syntax is the easy part. The hard part is spotting the runaway query early, understanding why it went rogue, and preventing the same pattern from recurring. Pulse monitors ClickHouse query activity continuously, surfaces long-running and memory-heavy queries before they destabilize a node, and correlates them with the workload and configuration that produced them. Instead of discovering a problem when a dashboard times out and then scrambling through system.processes, you get early warning and the context to set the right limits — so killing queries by hand becomes the exception rather than the routine.

Frequently Asked Questions

Q: Why does my query still show in system.processes after I run KILL QUERY?

ClickHouse cancellation is cooperative. The query is marked cancelled (is_cancelled = 1) and stops only when it reaches a safe checkpoint in its execution pipeline. Queries blocked on slow I/O can take several seconds to exit. Watch the row disappear rather than re-issuing the kill.

Q: Do I need a special grant to kill my own query?

No. Any user can cancel their own running queries without privileges. You only need the KILL QUERY grant (a GLOBAL privilege granted on *.*) to cancel queries belonging to other users.

Q: What's the difference between KILL QUERY and KILL MUTATION?

KILL QUERY cancels foreground queries listed in system.processes (SELECT, INSERT, etc.). Mutations like ALTER ... UPDATE/DELETE run in the background, appear in system.mutations, and must be stopped with KILL MUTATION. The two are not interchangeable.

Q: How do I confirm a query was actually cancelled?

Check that it's gone from system.processes, then look it up in system.query_log — a cancelled query is logged with an exception (commonly QUERY_WAS_CANCELLED). In SYNC mode, a kill_status of finished is the confirmation.

Q: Will killing a query or mutation roll back its changes?

A cancelled SELECT simply stops and returns no result. A killed mutation does not roll back changes it already applied — parts it rewrote before cancellation stay rewritten. Plan mutations carefully, since killing one doesn't undo its partial work.

Q: Why does KILL QUERY return nothing on my cluster?

system.processes is per-node. If the query runs on a different replica, your WHERE clause matches no local rows. Use KILL QUERY ON CLUSTER <name> to propagate the cancellation across all nodes.