JDBC Driver ↔ ReplicatedMergeTree: Zero‑Downtime Schema Evolution via Online ALTER or Drop‑and‑Swap

Resolution

To achieve zero-downtime schema evolution with a Java application using the official ClickHouse JDBC driver, you should avoid "drop-and-swap" in favor of EXCHANGE TABLES (if supported by your version) or a metadata-only ALTER. The JDBC driver does not provide transparent retries for write-lock exceptions; therefore, any transient failures during DDL execution must be handled by application-level retry logic with exponential backoff.

JDBC Driver and Lock Handling

The ClickHouse JDBC driver operates as a pass-through for SQL execution. It does not automatically detect or retry queries that fail due to server-side write locks or DDL conflicts. If an ALTER TABLE operation triggers a lock that causes an INSERT to fail, the driver will throw a SQLException. To maintain availability, implement a retry wrapper around your data access layer.

Drop-and-Swap vs. Atomic Exchange

A traditional drop-and-swap creates a race condition where the table name is temporarily missing, leading to immediate SELECT failures. While connection pools generally do not cache table names, they may cache Prepared Statements. If a statement is bound to a table that is dropped, it will become invalid.

• Recommended Strategy: Use EXCHANGE TABLES table_old AND table_new. This is an atomic operation that swaps the names of two existing tables, ensuring the application always sees a valid table.
• Pool Management: To ensure the new schema is recognized, set a reasonable maxLifetime on your connection pool (e.g., HikariCP) to cycle connections and clear any driver-level metadata caches.

Materialized View Write Amplification

Using a Materialized View (MV) to sync a new schema during migration introduces write amplification proportional to the number of insert blocks. Every block inserted into the source table triggers a corresponding insert into the MV target. This increases:

• Disk I/O: Data is written to two separate sets of parts.
• Merge Pressure: The background merge process must now manage parts for both the source and the target tables.
• Replication Traffic: Log entries for both tables must be synchronized across the cluster.

Implementation Steps

1. Verify Lock Type: Check if your ADD COLUMN is a metadata-only change. In recent versions, adding a column with a constant DEFAULT is nearly instantaneous and does not rewrite data.
2. Prepare New Schema: Create the new table structure under a temporary name (e.g., table_v2).
3. Sync Data: Create a Materialized View to pipe new writes from table_v1 to table_v2, then backfill historical data.
4. Atomic Swap: Execute EXCHANGE TABLES table_v1 AND table_v2.
5. Cleanup: Drop the old table and the Materialized View once the application is verified on the new schema.

Missing Diagnostic Detail

To refine this recommendation, please provide the ClickHouse server version. Whether an ALTER is treated as a metadata-only change or a heavy mutation depends entirely on the version and the specific column definition.