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 orderByFields order_by_fields 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)

import { Key, OlapTable } from "@514labs/moose-lib";
 
interface EventV0 {
  id: string;
  name: string;
  createdAt: Date;
}
 
export const events = new OlapTable<EventV0>("events", { version: "0.0", orderByFields: ["id"] });

from typing import Annotated
from pydantic import BaseModel
from moose_lib import OlapTable, Key, OlapConfig
 
class EventV0(BaseModel):
    id: str
    name: str
    created_at: str  # datetime in your format
 
events = OlapTable[EventV0]("events", config=OlapConfig(version="0.0", order_by_fields=["id"]))

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.

import { Key, OlapTable } from "@514labs/moose-lib";
 
interface EventV1 {
  id: Key<string>;
  name: string;
  createdAt: Date;
}
 
export const eventsV1 = new OlapTable<EventV1>("events", { version: "0.1", orderByFields: ["id", "createdAt"] });

class EventV1(BaseModel):
    id: Key[str]
    name: str
    created_at: str
 
events_v1 = OlapTable[EventV1]("events", config=OlapConfig(version="0.1", order_by_fields=["id", "created_at"]))

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)

import { MaterializedView, sql } from "@514labs/moose-lib";
import { events } from "../tables/events";        // old table
import { eventsV1, EventV1 } from "../tables/events_v01";  // new versioned table
 
export const migrateEventsToV01 = new MaterializedView<EventV1>({
  materializedViewName: "mv_events_to_0_1",
  selectTables: [events],
  selectStatement: sql`
    SELECT * FROM ${events}
  `,
  targetTable: eventsV1,
});

from moose_lib import MaterializedView, MaterializedViewOptions
from app.tables.events import events
from app.tables.events_v01 import events_v1, EventV1
 
migrate_events_to_v01 = MaterializedView[EventV1](
    MaterializedViewOptions(
        materialized_view_name="mv_events_to_0_1",
        select_statement=(
            f"SELECT * FROM {events.name}"
        ),
        select_tables=[events],
    ),
    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.