Validating VSchema Changes in CI

A VSchema is production configuration, and the cheapest place to catch a routing regression is a pull request — not a latency page after a table silently started scattering to every shard. A CI gate that plan-tests the VSchema turns “we think this still routes” into “the build proves it does.”

Context

This page sits under async VSchema validation workflows, the broader practice of keeping the applied VSchema correct and in sync. Where automating VSchema sync with Python scripts covers pushing a validated VSchema to the topology, this page covers the gate before that push: the checks that must pass in CI so a bad document never reaches the sync step. Both belong to VSchema configuration and routing-rule management.

The core insight is that VTGate computes a query plan purely from the VSchema and the table schema — no live cluster required. vtexplain exposes exactly that planner offline, so CI can assert the plan for a set of business-critical queries, not just that the JSON parses. A change that turns a single-shard route into a scatter is valid JSON and a valid VSchema; only a plan assertion catches it.

Why this belongs in CI rather than a pre-deploy check comes down to feedback latency and blast radius. A VSchema regression that reaches production does not throw an error — the query still returns correct rows, it just fans them out of every shard, so the failure surfaces as creeping latency and saturated connection pools rather than an exception. By the time an on-call engineer correlates a latency page back to a VSchema merge, the change is already live and possibly hours old. Moving the same plan check left to the pull request converts a subtle production incident into a red build with a one-line explanation, at a fraction of the cost. The gate is cheap precisely because the planner is pure: no cluster to stand up, no data to seed, just the document and the schema.

The three gates

A useful VSchema gate runs three checks in increasing depth:

  1. Structural lint — the document is valid JSON, every table’s vindexes reference a vindex that is actually defined, and every vindex names a known type. This catches typos and dangling references with no cluster and no schema.
  2. Plan assertions — for each critical query, vtexplain produces a plan and the harness asserts the expected routing: single-shard where it must be single-shard, and no query silently degraded to scatter.
  3. Diff-aware review flags — surface which tables’ plans changed versus the last committed VSchema, so a reviewer sees the routing impact of the diff, not just the text diff.

Structural lint

Start with the check that needs nothing but the file. It parses the VSchema, verifies each column_vindexes entry points at a defined vindex, and rejects any vindex whose type is not in the known set:

import json
import sys

# The vindex types this deployment permits. Reject anything else early —
# an unknown type here usually means a typo or a version-mismatched vindex.
KNOWN_VINDEX_TYPES = {
    "hash", "xxhash", "numeric", "unicode_loose_md5", "binary", "binary_md5",
    "lookup", "lookup_unique", "consistent_lookup", "consistent_lookup_unique",
    "reverse_bits",
}

def lint_vschema(path: str) -> list[str]:
    errors: list[str] = []
    with open(path) as fh:
        try:
            doc = json.load(fh)
        except json.JSONDecodeError as e:
            return [f"{path}: invalid JSON: {e}"]

    vindexes = doc.get("vindexes", {})
    for name, spec in vindexes.items():
        vtype = spec.get("type")
        if vtype not in KNOWN_VINDEX_TYPES:
            errors.append(f"vindex '{name}' has unknown type '{vtype}'")

    for table, tspec in doc.get("tables", {}).items():
        for cv in tspec.get("column_vindexes", []):
            ref = cv.get("name") or cv.get("vindex")
            if ref and ref not in vindexes:
                errors.append(
                    f"table '{table}' column_vindex references "
                    f"undefined vindex '{ref}'"
                )
    return errors

if __name__ == "__main__":
    problems = lint_vschema(sys.argv[1])
    for p in problems:
        print(f"FAIL {p}", file=sys.stderr)
    sys.exit(1 if problems else 0)

This alone stops the most common self-inflicted outage: a vindex renamed in one place but not the other, which leaves the table pointing at a name VTGate will not find and silently scatter.

Plan assertions with vtexplain

The gate that actually protects routing runs each critical query through vtexplain and asserts the opcode. vtexplain --output-mode json emits a machine-readable plan per query; the harness reads the Opcode and fails the build when it is Scatter for a query that must stay targeted.

import json
import subprocess

# Each critical query and the routing it MUST keep. If a VSchema change
# turns any of these into a scatter, the build fails.
CRITICAL_QUERIES = [
    ("SELECT * FROM orders WHERE customer_id = 42",       "single"),
    ("SELECT * FROM orders WHERE order_id = 90218",       "single"),
    ("SELECT * FROM customers WHERE id = 7",              "single"),
]

SCATTER_OPCODES = {"Scatter", "SelectScatter"}

def plan_opcode(sql: str, vschema: str, schema: str, shards: int) -> str:
    out = subprocess.run(
        ["vtexplain", "--vschema-file", vschema, "--schema-file", schema,
         "--shards", str(shards), "--output-mode", "json", "--sql", sql],
        capture_output=True, text=True, check=True,
    )
    plan = json.loads(out.stdout)
    # vtexplain nests the plan; pull the primitive opcode for the route.
    return _extract_opcode(plan)

def _extract_opcode(plan: dict) -> str:
    # Walk the plan tree and return the first routing opcode found.
    stack = [plan]
    while stack:
        node = stack.pop()
        if isinstance(node, dict):
            if "Opcode" in node:
                return node["Opcode"]
            stack.extend(node.values())
        elif isinstance(node, list):
            stack.extend(node)
    return "Unknown"

def assert_plans(vschema: str, schema: str, shards: int) -> list[str]:
    failures: list[str] = []
    for sql, expect in CRITICAL_QUERIES:
        op = plan_opcode(sql, vschema, schema, shards)
        scattered = op in SCATTER_OPCODES
        if expect == "single" and scattered:
            failures.append(f"UNEXPECTED SCATTER: {sql!r} planned as {op}")
        if expect == "scatter" and not scattered:
            failures.append(f"expected scatter but got {op}: {sql!r}")
    return failures

Every hot-path query that the business depends on belongs in CRITICAL_QUERIES with its expected routing. The list is the contract: a VSchema change is only allowed to ship if it preserves it.

Diff-aware review flags

The lint and plan gates answer “is the new VSchema acceptable?” A reviewer also wants “what did this change actually do to routing?” — a signal the raw text diff cannot give, because a one-line vindex edit can silently re-plan a dozen queries. Compute the plan for every critical query against both the committed VSchema and the proposed one, and report only the queries whose opcode changed:

import subprocess

def opcodes_for(ref: str, queries, schema, shards) -> dict:
    """Plan every critical query against the VSchema at a git ref."""
    blob = subprocess.run(
        ["git", "show", f"{ref}:vschema/commerce.json"],
        capture_output=True, text=True, check=True,
    ).stdout
    with open("/tmp/base_vschema.json", "w") as fh:
        fh.write(blob)
    return {sql: plan_opcode(sql, "/tmp/base_vschema.json", schema, shards)
            for sql, _ in queries}

def routing_diff(queries, schema, shards) -> list[str]:
    before = opcodes_for("origin/main", queries, schema, shards)
    after = {sql: plan_opcode(sql, "vschema/commerce.json", schema, shards)
             for sql, _ in queries}
    changes = []
    for sql in after:
        if before.get(sql) != after[sql]:
            changes.append(f"{sql!r}: {before.get(sql)} -> {after[sql]}")
    return changes

Post the returned list as a PR comment. A change from EqualUnique to Scatter is a red flag a reviewer would otherwise miss; a change from Scatter to EqualUnique is the intended payoff of adding a vindex and confirms the work landed. Either way the routing delta is now visible next to the code delta, which is where review attention should go.

Wiring it into CI

The bash entry point installs vtexplain at a pinned version, runs the lint, then runs the plan assertions, and fails on the first problem. Pinning the Vitess version matters — planner behavior can shift between releases, so CI must match production.

#!/usr/bin/env bash
set -euo pipefail

VSCHEMA="vschema/commerce.json"
SCHEMA="vschema/commerce_schema.sql"
SHARDS="${PROD_SHARD_COUNT:-4}"     # keep in lockstep with production

echo "==> Structural lint"
python ci/lint_vschema.py "$VSCHEMA"

echo "==> Plan assertions (vtexplain, ${SHARDS} shards)"
python - "$VSCHEMA" "$SCHEMA" "$SHARDS" <<'PY'
import sys
from plan_checks import assert_plans
failures = assert_plans(sys.argv[1], sys.argv[2], int(sys.argv[3]))
for f in failures:
    print(f"FAIL {f}", file=sys.stderr)
sys.exit(1 if failures else 0)
PY

echo "==> VSchema gate passed"

Run this as a required status check on the pull request so the VSchema cannot merge red. The PROD_SHARD_COUNT variable is deliberately sourced from the same config production uses, because a plan validated at the wrong shard count proves nothing (see the edge cases).

Edge cases and gotchas

  • vtexplain shard-count drift. vtexplain plans against the --shards you give it, which sets the number and boundaries of simulated shards. If CI runs at 4 shards but production runs at 16, a query can plan single-shard in CI and scatter in production (or vice versa). Pin --shards to the real production count and update CI in the same change that reshards.
  • False positives from intentional scatter. Some queries should scatter — a full aggregate over all shards has no single-shard plan. Mark these explicitly ("scatter" expectation) rather than excluding them, so the harness still fails if they unexpectedly become single-shard, which can equally signal a bug.
  • Schema and VSchema out of step. vtexplain needs the CREATE TABLE statements too; if the --schema-file lags the VSchema (a new column referenced by a vindex is missing), plans fail for the wrong reason. Generate the schema file from the same source of truth in the same CI job.
  • Bind-variable normalization. vtexplain may plan a literal query differently from the parameterized form the app actually sends. Write the critical queries with the same shape (literals vs placeholders) the application uses, or normalize both, so the assertion reflects production routing.
  • Opcode names across versions. Older Vitess prints SelectScatter/SelectEqualUnique; newer versions use Scatter/EqualUnique. Keep the SCATTER_OPCODES set aligned with the pinned vtexplain version, or the check silently passes because it never matches.
  • Vindex defined but write_only. A lookup vindex that is still write_only is not used for reads, so a query on that column plans as scatter even though the VSchema “has” the vindex. That is correct behavior, not a bug — do not add such a query to the single-shard contract until the vindex is externalized.
  • The gate protects routing, not correctness of results. A plan assertion proves a query stays single-shard; it does not prove the query returns the right rows. Keep the CI gate scoped to routing shape and leave data-correctness to integration tests against a real cluster — conflating the two makes the gate slow and flaky without making it more trustworthy.
  • Keep the critical-query list under review. The gate is only as good as the queries in it. A new hot-path query added to the application but not to CRITICAL_QUERIES is unprotected, and a query removed from the app but left in the list wastes build time and can block merges for a route nobody uses. Treat the list as code the same team owns, and audit it when access patterns change.

Verification

Prove the gate actually fails on a bad change, not just that it passes on a good one. The reliable way is a negative test: take a known-good VSchema, break one vindex reference, and confirm CI goes red.

# Negative test: corrupt a vindex name and expect a non-zero exit
cp vschema/commerce.json /tmp/broken.json
python - <<'PY'
import json
d = json.load(open("/tmp/broken.json"))
# Point orders at a vindex that does not exist -> must be caught by lint
d["tables"]["orders"]["column_vindexes"][0]["name"] = "does_not_exist"
json.dump(d, open("/tmp/broken.json", "w"))
PY
if python ci/lint_vschema.py /tmp/broken.json; then
  echo "GATE BROKEN: lint accepted a dangling vindex reference" >&2
  exit 1
fi
echo "OK: lint correctly rejected the broken VSchema"

A gate that has never been shown to fail is not a gate. Pair the negative lint test above with a negative plan test — remove a lookup vindex and assert that the corresponding query now trips the UNEXPECTED SCATTER failure — and keep both in the CI suite so a future refactor that neuters the check is itself caught.

← Back to Async VSchema Validation Workflows