Table Versioning & Blue/Green Migrations

Overview

Changing a table's storage layout (engine or sorting key) in ClickHouse requires a full table rewrite. Doing it in-place can block or slow concurrent reads and writes due to heavy merges and metadata changes, creating real risk for production workloads. Blue/Green avoids this by creating a new versioned table and migrating data live via a materialized view, so traffic continues uninterrupted.

When to use it:

• Change the table engine (e.g., MergeTree → ReplacingMergeTree)
• Update ORDER BY fields (sorting keys) to better match query patterns
• Reshape primary keys or perform type changes that require a rewrite

How Moose does it:

1. Define a new table with the same logical name and a bumped version, setting the new
   TypeScriptPython

   orderByFields
   and/or engine (Table modeling).
2. Create a Materialized view that selects from the old table and writes to the new one; Moose backfills once and keeps the view live for new inserts.
3. Later on, cut over readers/writers to the new export and clean up old resources (Applying migrations).

High-level workflow

Example: change sorting key (ORDER BY)

Assume the original events table orders by id only. We want to update the sorting key to optimize reads by ordering on id, createdAt.

Original table (version 0.0)

New table (bump to version 0.1)

Create a new table with the same logical name, but set version: "0.1" and update the ordering to id, createdAt. Moose will create events_0_1 in ClickHouse.

Create the materialized view to migrate data

Create a materialized view that:

• SELECTs from the old table (events_v0)
• copies fields 1:1 to the new table
• writes into the versioned target table (events_v1)

What happens when you export this view:

• Moose creates the versioned table if needed
• Moose creates the MATERIALIZED VIEW and immediately runs a one-time backfill (INSERT INTO ... SELECT ...)
• ClickHouse keeps the view active: any new inserts into events automatically flow into events_0_1

Cutover and cleanup

• Update readers to query the new table export (eventsV1).
• Update writers/streams to produce to the new table if applicable.
• After verifying parity and retention windows, drop the old table and the migration view.

Notes and tips

• Use semantic versions like 0.1, 1.0, 1.1. Moose will render events_1_1 as the physical name.
• Keep the migration view simple and deterministic. If you need complex transforms, prefer explicit SQL in the selectStatement.
• Very large backfills can take time. Consider deploying during low-traffic windows.