Online Schema Changes in Vitess Using VReplication: A Step‑by‑Step Guide

Problem: Changing a schema without stopping traffic

In a sharded Vitess deployment, adding a column to a heavily used table traditionally requires a maintenance window or a complex dual‑write scheme. Vitess provides VReplication, a built‑in mechanism that streams data from source tablets to target tablets while the source continues to serve reads and writes. By defining a workflow in JSON, you can apply the new DDL to the target first, copy existing data, and then cut over traffic with only a brief write‑only pause.

Thesis

Using a VReplication workflow you can perform an online schema change (e.g., adding a nullable column) with minimal impact, verify the copy via tablet logs and row counts, and understand the trade‑offs of extra source load and a short cut‑over window.

How VReplication works for schema changes

VReplication operates at the tablet level:

1. The controller creates a target table (or uses an existing one) in the target keyspace.
2. It applies the DDL to the target first, so the new schema is ready before any data is copied.
3. A streaming job copies rows from the source tablet to the target tablet using the VTGate query service.
4. When the copy reaches Ready state, a cut‑over operation atomically redirects VTGate to read from the target and, after a short pause, to write to the target.
5. Source tablets can be decommissioned after the cut‑over if desired.

Because the stream uses MySQL’s replication protocol, it works with any storage engine MySQL supports and preserves cross‑shard transactional consistency.

Worked example: Adding a column to a sharded table

Assume a keyspace commerce with a sharded table orders (columns: order_id BIGINT PRIMARY KEY, customer_id BIGINT, amount DECIMAL(10,2)). We will add a nullable status VARCHAR(20) column.

1. Prepare the VReplication manifest

Create a JSON file add_status.json:

{
  "source_keyspace": "commerce",
  "target_keyspace": "commerce",
  "tables": [
    {
      "source_table": "orders",
      "target_table": "orders",
      "mutation": "ALTER TABLE orders ADD COLUMN status VARCHAR(20) NULL"
    }
  ],
  "filter": ""
}

The mutation field tells VReplication to apply the DDL to the target before copying data.

2. Start the workflow

Assuming you have a local Vitess cluster running via the official vitess/examples/docker-compose file and the vtctlclient binary in your PATH, run:

vtctlclient ApplyVReplication -workflow add_status -manifest ./add_status.json

Required permission: the Vitess user must have SUPER or appropriate privileges to execute DDL on the target tablets.

Expected check: the command returns immediately with a workflow ID; you can list it with:

vtctlclient ShowVReplication -workflow add_status

The output will show a state progression from Init → Copying → Ready.

3. Verify the copy

While the workflow is in Copying state, you can:

• Inspect VTTablet logs for lines containing VReplication and Copying to confirm the stream is active.
• Run a row‑count check on source and target tablets (replace TABLET_ALIAS with the actual alias, e.g., commerce-0000000100):

  vtctlclient ExecuteFetchAsDba -tablet TABLET_ALIAS \
    "SELECT COUNT(*) FROM commerce.orders;"
  

  The counts should converge as the copy proceeds.

When the state reaches Ready, the target table already contains the new column and all existing rows.

4. Perform the cut‑over

To switch traffic, execute:

vtctlclient ExecuteVReplication -workflow add_status -op cutover

This blocks new writes for a short period (typically a few seconds while the switch is atomic), then redirects both reads and writes to the target tablets. After the cut‑over completes, the workflow state becomes Completed.

Limitations and practical checklist

• Extra load on source tablets: The streaming job adds read load; monitor CPU, I/O, and replication lag on source tablets during the Copying phase. If latency spikes, consider throttling with the max_rows_per_second option in the manifest.
• Brief write‑only window: The cut‑over blocks writes until the switch finishes. Design your application to tolerate a short pause or implement retry logic.
• Resource headroom: Ensure the target keyspace has sufficient storage and compute capacity to hold a full copy of the table during the stream.
• Verification steps:
  • Check VTTablet logs for VReplication entries.
  • Confirm row counts match source and target after Ready.
  • Run ShowVReplication to ensure the workflow reached Completed.

Actionable closing

When you need to evolve a Vitess schema without downtime, start by drafting a VReplication manifest that includes the desired DDL, launch the workflow with ApplyVReplication, monitor the copy via tablet logs and row‑count checks, and finish with a cut‑over. Keep an eye on source tablet load and plan for the brief write‑only pause. Following these steps lets you add columns, change data types, or reshard tables while keeping your application online.