DEV Community

Cover image for Day 82: Handling Schema Evolutions in ClickHouse® Distributed Clusters
Kanishga Subramani
Kanishga Subramani

Posted on

Day 82: Handling Schema Evolutions in ClickHouse® Distributed Clusters

#clickhouse #devops #database #analytics

Introduction

As applications evolve, database schemas must evolve alongside them. New business requirements introduce additional columns, outdated fields become obsolete, data types need to be refined, and table structures change over time. While these modifications are relatively straightforward in a single-node database, they become significantly more complex in a distributed ClickHouse® deployment.

A distributed ClickHouse cluster consists of multiple shards and replicas working together. Every schema modification must be propagated consistently across all nodes to prevent replication issues, query failures, or inconsistent table definitions. Without careful coordination, even a simple ALTER TABLE command can leave different nodes with different schemas, resulting in operational challenges.

This process of safely modifying database structures over time while preserving data integrity is known as schema evolution.

In this article, you'll learn how schema evolution works in ClickHouse distributed clusters, how to perform common schema changes safely, and the best practices for maintaining zero-downtime production environments.

What Is Schema Evolution?

Schema evolution refers to modifying an existing table definition without recreating the table or losing stored data.

Typical schema changes include:

  • Adding new columns
  • Removing obsolete columns
  • Renaming existing columns
  • Modifying data types
  • Updating default values
  • Changing column comments
  • Adjusting logical column order

These operations allow applications to continue evolving while keeping historical data intact.

Why Schema Evolution Is More Challenging in Distributed Clusters

In a standalone ClickHouse server, an ALTER TABLE command immediately updates the table definition.

In a distributed cluster, however, the same operation must:

  • Execute on every shard and replica
  • Keep replicated tables synchronized
  • Avoid interrupting queries on Distributed tables
  • Handle nodes that may be temporarily unavailable

If only part of the cluster receives the schema update, different replicas begin using different table definitions. This can result in:

  • Replication failures
  • Query execution errors
  • Inconsistent metadata
  • Application failures

Maintaining schema consistency across every node is therefore critical.

The Golden Rule: Always Use ON CLUSTER

Whenever you modify a table in a distributed ClickHouse deployment, always include the ON CLUSTER clause.

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
ADD COLUMN discount Float64 DEFAULT 0.0;
Enter fullscreen mode Exit fullscreen mode

The ON CLUSTER clause instructs ClickHouse Keeper to distribute the DDL command to every node automatically.

Avoid running:

ALTER TABLE default.orders
ADD COLUMN discount Float64 DEFAULT 0.0;
Enter fullscreen mode Exit fullscreen mode

Without ON CLUSTER, only the connected node receives the change, leaving the remainder of the cluster with an outdated schema.

Sample Cluster Setup

Throughout this article, consider the following replicated table:

CREATE TABLE default.orders
ON CLUSTER cluster_1S_3R
(
    order_id UInt32,
    customer String,
    amount Float64,
    order_date Date
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/shard_1/orders',
    '{replica}'
)
ORDER BY order_id;
Enter fullscreen mode Exit fullscreen mode

A Distributed table can then expose this data across the cluster.

CREATE TABLE default.orders_distributed
ON CLUSTER cluster_1S_3R
AS default.orders
ENGINE = Distributed(cluster_1S_3R, default, orders, rand());
Enter fullscreen mode Exit fullscreen mode

Adding New Columns

Adding columns is generally the safest schema evolution operation.

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
ADD COLUMN IF NOT EXISTS discount Float64 DEFAULT 0.0,
ADD COLUMN IF NOT EXISTS order_status LowCardinality(String)
DEFAULT 'pending';
Enter fullscreen mode Exit fullscreen mode

Existing rows automatically use the specified default values without rewriting stored data.

To verify the schema across nodes:

SELECT
    name,
    type,
    default_expression
FROM system.columns
WHERE database='default'
AND table='orders'
ORDER BY position;
Enter fullscreen mode Exit fullscreen mode

Using IF NOT EXISTS makes schema migrations idempotent and prevents failures if some nodes already contain the new column.

Adding Materialized Columns

Sometimes a newly introduced column should be derived automatically from existing data.

For example:

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
ADD COLUMN IF NOT EXISTS order_year UInt16
MATERIALIZED toYear(order_date);
Enter fullscreen mode Exit fullscreen mode

Every future row automatically computes order_year from order_date.

Existing rows can be populated using:

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
MATERIALIZE COLUMN order_year;
Enter fullscreen mode Exit fullscreen mode

Materialized columns eliminate redundant application logic while ensuring consistency.

Dropping Columns Safely

Dropping columns permanently removes them from the schema.

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
DROP COLUMN IF EXISTS discount;
Enter fullscreen mode Exit fullscreen mode

Although straightforward, dropping columns should follow a gradual process:

  1. Remove application dependencies.
  2. Update dashboards and reports.
  3. Monitor usage.
  4. Remove the column only after confirming it is no longer required.

This minimizes the risk of unexpected failures.

Renaming Columns

Columns can be renamed without recreating the table.

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
RENAME COLUMN order_status TO status;
Enter fullscreen mode Exit fullscreen mode

However, renaming introduces a breaking change for every application, report, API, and Materialized View that references the original column name.

Update all dependent systems before or immediately after renaming.

Modifying Column Types

Changing a column's data type is considerably more expensive than adding a new column.

Example:

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
MODIFY COLUMN amount Decimal(18,2);
Enter fullscreen mode Exit fullscreen mode

Type modifications trigger background mutations that rewrite existing data.

Before performing a type conversion:

  • Validate compatibility with existing values.
  • Test on a staging cluster.
  • Schedule maintenance during off-peak hours.
  • Monitor mutation progress until completion.

Large datasets may require significant processing time.

Controlling Column Placement

By default, ClickHouse appends new columns to the end of the table.

To insert a column after another column:

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
ADD COLUMN IF NOT EXISTS currency
LowCardinality(String)
DEFAULT 'USD'
AFTER amount;
Enter fullscreen mode Exit fullscreen mode

This only changes the logical column order and does not affect stored data.

Changing Default Values

Default expressions can also evolve over time.

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
MODIFY COLUMN order_status DEFAULT 'new';
Enter fullscreen mode Exit fullscreen mode

Only future inserts use the updated default.

Previously stored rows remain unchanged.

Handling Offline Nodes During Schema Changes

Distributed clusters inevitably encounter maintenance windows, restarts, or temporary outages.

What happens if a node is offline while an ALTER TABLE ... ON CLUSTER command executes?

ClickHouse stores distributed DDL commands inside ClickHouse Keeper.

The process works like this:

ALTER TABLE ... ON CLUSTER
           │
           ▼
Distributed DDL Queue
           │
           ▼
Replica 1
Replica 2
Replica 3
           │
           ▼
Schemas Synchronized
Enter fullscreen mode Exit fullscreen mode

Online replicas execute the command immediately.

Offline replicas replay pending DDL tasks automatically after reconnecting.

This mechanism ensures schema consistency without requiring manual intervention.

Monitoring Distributed DDL

Monitor pending schema changes using:

SELECT
    entry,
    query,
    host,
    status,
    exception_code
FROM system.distributed_ddl_queue
WHERE status != 'Finished'
ORDER BY entry DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

To inspect failures:

SELECT *
FROM system.distributed_ddl_queue
WHERE status='Error'
ORDER BY entry DESC;
Enter fullscreen mode Exit fullscreen mode

These system tables provide valuable insight into cluster-wide schema deployment.

Updating Materialized Views

Schema changes do not automatically propagate to Materialized Views.

Suppose a new discount column is introduced.

The Materialized View must also be updated.

A common workflow is:

  1. Drop the existing Materialized View.
  2. Modify the target summary table.
  3. Recreate the Materialized View with the updated query.

This ensures future inserts populate the additional column correctly.

Keep in mind that recreated Materialized Views process only new incoming data unless historical data is backfilled separately.

Achieving Zero-Downtime Schema Evolution

Many production systems cannot tolerate downtime.

Fortunately, several schema changes are naturally online.

Safe zero-downtime operations

  • Adding columns with default values
  • Updating default expressions
  • Adding materialized columns
  • Reordering columns logically

Operations requiring additional planning

  • Dropping columns
  • Renaming columns
  • Modifying data types

For these operations:

  • Update application code first.
  • Remove dependencies gradually.
  • Schedule maintenance windows where necessary.
  • Monitor mutations until completion.

Schema Evolution Checklist

Before applying schema changes to production, verify the following:

  • Always use ON CLUSTER.
  • Use IF EXISTS and IF NOT EXISTS.
  • Test every migration on staging.
  • Check system.distributed_ddl_queue.
  • Verify identical schemas across all nodes.
  • Update dependent Materialized Views.
  • Monitor system.mutations.
  • Remove application dependencies before dropping columns.

A disciplined migration process significantly reduces operational risk.

Common Mistakes

Mistake Impact
Running ALTER TABLE on a single node Cluster-wide schema inconsistency
Skipping ON CLUSTER Replication failures
Dropping columns prematurely Permanent data loss
Ignoring Materialized Views Broken analytical pipelines
Changing incompatible data types Failed mutations
Skipping post-migration validation Inconsistent replicas

Avoiding these mistakes ensures reliable production deployments.

Best Practices

For production ClickHouse clusters:

  • Always execute DDL using ON CLUSTER.
  • Prefer adding new columns over modifying existing ones.
  • Make migrations idempotent with IF EXISTS and IF NOT EXISTS.
  • Test every schema migration on a staging cluster.
  • Schedule expensive mutations during low-traffic periods.
  • Validate schema consistency after every deployment.
  • Update Materialized Views whenever source tables change.
  • Continuously monitor distributed DDL queues and mutations.

Following these practices keeps distributed environments predictable and resilient.

Real-World Example

Imagine an e-commerce platform running a distributed ClickHouse cluster across multiple regions.

A new feature requires tracking discounts applied to every order. Instead of rebuilding the table, the engineering team introduces a new discount column using an ALTER TABLE ... ON CLUSTER statement with a default value. Because the command is propagated through ClickHouse Keeper, every shard and replica receives the update consistently.

Later, the analytics team updates its Materialized Views to include discount metrics, verifies the distributed DDL queue for successful execution, and confirms that all nodes expose the same schema. The entire migration completes without interrupting ongoing queries or data ingestion, allowing the new feature to roll out seamlessly across the production environment.

Conclusion

Schema evolution is an inevitable part of every growing application, and in distributed ClickHouse® clusters, it requires careful coordination to maintain consistency across shards and replicas. By using ON CLUSTER, leveraging ClickHouse Keeper for distributed DDL, and following safe migration practices, most schema changes can be performed with minimal operational impact.

Adding columns, updating defaults, and introducing materialized expressions are generally straightforward, while operations such as dropping columns or modifying data types require additional planning and monitoring. Regularly validating schema consistency, updating dependent Materialized Views, and monitoring the distributed DDL queue help ensure that every node remains synchronized.

As your ClickHouse deployment grows in size and complexity, treating schema evolution as a controlled, repeatable process will help you minimize downtime, prevent inconsistencies, and maintain reliable analytical performance in production.

Top comments (1)

Collapse
 
mads_hansen_27b33ebfee4c9 profile image
Mads Hansen •

Good practical overview. ON CLUSTER is the big rule, but the operational runbook around it matters just as much.

For production I would want every schema change to include checks against system.distributed_ddl_queue and system.mutations before and after the migration, plus an application rollout plan: additive column first, deploy readers/writers that tolerate both shapes, backfill/materialize, then remove old dependencies later.

One small caveat: IF NOT EXISTS makes migrations idempotent, but it can also hide drift if a column exists with the wrong type/default. I like pairing it with an explicit system.columns assertion.