All posts
Why ClickHouse® ALTER and DELETE Operations Take Time

Why ClickHouse® ALTER and DELETE Operations Take Time

November 19, 20269 min readReshma M
Share:

1. The problem

An ALTER UPDATE, ALTER DELETE, or DELETE FROM query can return before the data changes. Sometimes the same query waits for a long time before returning.This behavior can cause problems in production scripts.

A script may run an update and immediately read the table. The SELECT query can still return old data because the mutation has not finished.The opposite problem can also occur. A script waits for a mutation to finish on every replica. One inactive or slow replica can delay the response.

These behaviors are related to how ClickHouse processes data changes. ClickHouse does not always update or delete rows immediately. It often schedules the work as a background operation.

This article explains how to check mutation progress, control how long queries wait, and handle long-running operations.

2. Why it happens

ClickHouse uses MergeTree data parts to store table data. An ALTER TABLE ... UPDATE or ALTER TABLE ... DELETE operation creates a mutation.

A mutation does not normally rewrite the data while the client waits. ClickHouse records the mutation and processes affected parts in the background.

The query can return after ClickHouse accepts the mutation, and the data changes become visible as the affected parts are processed. This is why a SELECT query can still read old values immediately after the ALTER query returns.

Different settings control waiting behavior depending on the query type:

  • mutations_sync: Controls how long an ALTER UPDATE or ALTER DELETE query waits.
  • lightweight_deletes_sync: Controls how long a DELETE FROM query waits. Lightweight deletes use a delete mask instead of immediately rewriting all affected data parts.
  • alter_sync: Controls waiting for other ALTER operations on MergeTree-family tables. It is different from mutations_sync, which controls data mutations.
  • distributed_ddl_task_timeout: Controls how long an ON CLUSTER query waits for responses from distributed cluster hosts.

These settings control different operations. Use the correct setting for the query type.

3. How to confirm

First, check whether a mutation is still running by querying the system.mutations table:

SELECT
    database,
    table,
    mutation_id,
    command,
    create_time,
    is_done,
    parts_to_do,
    latest_failed_part,
    latest_fail_time,
    latest_fail_reason
FROM system.mutations
WHERE database = 'company'
    AND table = 'employee'
ORDER BY create_time DESC;

Replace the database and table names with your own values and inspect these columns:

  • is_done: Shows whether the mutation has finished. A value of 0 means the mutation is not complete.
  • parts_to_do: Shows how many data parts still need processing. A non-zero value indicates remaining work.
  • latest_failed_part: Identifies the most recent part where the mutation failed.
  • latest_fail_time: Shows when the most recent failure occurred.
  • latest_fail_reason: Shows the reason for the failure.

Next, check whether any mutations have failed:

SELECT
    database,
    table,
    mutation_id,
    command,
    latest_fail_time,
    latest_fail_reason
FROM system.mutations
WHERE latest_fail_time > toDateTime(0)
ORDER BY latest_fail_time DESC;

A failure can prevent a mutation from completing. Check the failure reason before changing synchronization settings.

Finally, inspect your current session settings:

SELECT
    name,
    value,
    changed
FROM system.settings
WHERE name IN
(
    'mutations_sync',
    'alter_sync',
    'lightweight_deletes_sync',
    'distributed_ddl_task_timeout'
)
ORDER BY name;

The value column shows the current session values. The changed column indicates whether a setting differs from its default.

4. The settings

The following defaults may vary, Verify each value on ClickHouse 26.8.38.9 LTS before publishing.

SettingDefaultWhat it controlsRestart needed
mutations_sync0Controls whether ALTER UPDATE and ALTER DELETE wait for mutationsNo
alter_sync1Controls how ALTER operations wait for execution on replicasNo
lightweight_deletes_sync2Controls whether lightweight DELETE operations wait for completionNo
distributed_ddl_task_timeout180 secondsSets how long an ON CLUSTER DDL query waits for host responsesNo

Understanding the synchronization values

The mutations_sync and lightweight_deletes_sync settings use these values:

ValueBehavior
0Do not wait. Execute the operation asynchronously.
1Wait for the operation to finish on the current server.
2Wait for the operation to finish on all replicas, if they exist.

The alter_sync setting uses similar values:

ValueBehavior
0Do not wait.
1Wait for the operation on the current server.
2Wait for the operation on all replicas.

For distributed_ddl_task_timeout:

  • A positive value sets the timeout in seconds.
  • 0 enables asynchronous execution.
  • A negative value means an unlimited wait.

Check the actual defaults on your server. Use system.settings for session settings and system.server_settings for server-level settings.

5. How to fix it

Wait for ALTER UPDATE and ALTER DELETE

Use mutations_sync when a script must read updated data immediately after an ALTER UPDATE or ALTER DELETE. Set it to 1 when the script only needs to wait for the current server.

SET mutations_sync = 1;
 
ALTER TABLE company.employee
UPDATE department = 'Engineering'
WHERE employee_id = 1001;

The query waits for the mutation to finish on the current server before returning. Use 2 when the script must wait for all replicas:

Use 2 when the script must wait for all replicas.

SET mutations_sync = 2;

This can take longer because ClickHouse must wait for replica execution. Use it only when the next operation requires the change on every replica.

Wait for lightweight DELETE operations

A DELETE FROM query uses lightweight deletes. Set lightweight_deletes_sync when the client must wait for the delete operation. Use 1 to wait for the current server.

SET lightweight_deletes_sync = 1;

Then run:

DELETE FROM company.employee
WHERE employee_id = 1001;

Use 2 when the operation must finish on all replicas.

SET lightweight_deletes_sync = 2;

Do not confuse lightweight deletes with ALTER TABLE ... DELETE. The latter uses mutations and follows mutations_sync.

Control waiting for other ALTER operations

Use alter_sync for ALTER operations that require synchronization across replicas.

For example, use 2 when a schema change must complete on all replicas before the script continues.

SET alter_sync = 2;

This setting does not replace mutations_sync for ALTER UPDATE and ALTER DELETE.

Control ON CLUSTER DDL waiting

If an ON CLUSTER query returns a timeout, check the distributed DDL queue and the status of the affected hosts.

The distributed_ddl_task_timeout setting controls how long the query waits for responses.

For example, set a five-minute timeout for one session:

SET distributed_ddl_task_timeout = 300;

Then run the DDL operation:

ALTER TABLE company.employee
ADD COLUMN location String
ON CLUSTER company_cluster;

A longer timeout gives slow hosts more time to respond. It does not fix a host that cannot execute the DDL.

A timeout does not always mean the DDL operation stopped. ClickHouse can continue executing unfinished tasks asynchronously. Check the distributed DDL queue before retrying the operation.

Apply settings to a settings profile

For scripts that use the same ClickHouse user, you can configure a settings profile in users.xml or a file under users.d.

For example:

<clickhouse>
    <profiles>
        <maintenance_profile>
            <mutations_sync>1</mutations_sync>
            <lightweight_deletes_sync>1</lightweight_deletes_sync>
            <alter_sync>1</alter_sync>
            <distributed_ddl_task_timeout>300</distributed_ddl_task_timeout>
        </maintenance_profile>
    </profiles>
</clickhouse>

Assign this profile to the required user through the existing user configuration.

These are example values. Choose synchronization levels based on the script's consistency requirements. Do not apply synchronous waiting to every user without testing its impact.

You can also use the SETTINGS clause for one query. For example:

ALTER TABLE company.employee
UPDATE department = 'Engineering'
WHERE employee_id = 1001
SETTINGS mutations_sync = 1;

Use this approach when only one operation needs synchronous execution.

Changes to settings profiles do not require a server restart. Reload the configuration and reconnect the client when required.

6. How to verify the fix

Run the operation and check its progress in system.mutations.

SELECT
    mutation_id,
    command,
    is_done,
    parts_to_do,
    latest_fail_time,
    latest_fail_reason
FROM system.mutations
WHERE database = 'company'
    AND table = 'employee'
ORDER BY create_time DESC;

Confirm that is_done becomes 1 and parts_to_do reaches 0.

Next, read the affected rows to confirm that the returned values match the requested change:

SELECT
    employee_id,
    department
FROM company.employee
WHERE employee_id = 1001;

Confirm that the returned values match the requested change.

For replicated tables, check the mutation status on the required replicas. A mutation that finishes on the current server may still be running on another replica.

For ON CLUSTER operations, check the distributed DDL queue and confirm that every required host completed the task.

If a mutation fails, inspect latest_fail_reason before running it again.

Stop a mutation when necessary

You can stop a mutation with KILL MUTATION. First, identify the exact mutation ID.

KILL MUTATION
WHERE database = 'company'
    AND table = 'employee'
    AND mutation_id = 'mutation_123.txt';

Replace the example mutation ID with the actual value from system.mutations.

Stopping a mutation does not roll back changes already completed. Check the table state before submitting a replacement mutation.

7. Risks and trade-offs

Synchronous operations make scripts easier to control. However, they can increase query duration and hold client connections for longer.

Consider these risks before changing the settings:

  • Large mutations take time: ClickHouse must rewrite all affected data parts, causing synchronous queries to block for extended periods.
  • Replica delays block execution: Setting mutations_sync = 2 or lightweight_deletes_sync = 2 can cause queries to hang while waiting for slow or inactive replicas.
  • Timeouts do not stop execution: A client timeout (distributed_ddl_task_timeout) only stops the client from waiting; it does not stop ClickHouse from finishing the task in the background or guarantee that all hosts completed it.
  • Failed mutations persist: A failed mutation remains in the queue until you resolve the underlying error or kill it.

Avoid synchronous waiting for large maintenance operations unless the application requires it. For large tables, background execution with progress monitoring can be a better choice.

8. Summary

  • Mutations run in the background by default.
  • Use system.mutations to check progress and failures.
  • Use mutations_sync for ALTER UPDATE and ALTER DELETE operations.
  • Use lightweight_deletes_sync for DELETE FROM operations.
  • Use alter_sync and distributed_ddl_task_timeout for their respective DDL operations.
  • Use synchronous waiting only when the next step requires completed changes.
Share: