Dynamic Routing Rules and Query Rewriting

A sharded topology has to move traffic without moving the application. During a keyspace split, a table migration, or a read-offload rollout, the SQL your services emit does not change — but the target that SQL resolves to must. Vitess handles this with two cooperating layers inside the VTGate routing layer: a routing rules table that redirects a logical keyspace.table reference to a different physical target, and a query rewriting stage that normalizes and annotates each statement before the planner turns it into shard routes. Together they let you cut over a MoveTables migration, split reads onto replicas, or fence off a table — all by applying a JSON artifact to the topology server, with zero application redeploys. This page shows how the resolution pipeline works, how to author and apply routing rules safely, and how to catch the failure classes that silently degrade a targeted query into a scatter. It is a direct companion to Debugging VSchema Routing Rule Conflicts, which owns the diagnosis half of this workflow.

Prerequisites #

Before applying dynamic routing changes to a live keyspace, confirm the following:

• Vitess 16.0 or later. The vtctldclient ApplyRoutingRules, ApplyShardRoutingRules, and ApplyKeyspaceRoutingRules commands and their Get* counterparts are stable from v16. Earlier releases used the deprecated vtctlclient verbs and lack keyspace-level rules entirely.
• A committed baseline for every rule set. Routing rules, shard routing rules, and keyspace routing rules are each a single global object in the topology server. Treat each as a version-controlled artifact — you always apply a complete replacement, never a partial patch, so the last-known-good JSON must be recoverable.
• A defined VSchema per keyspace. Routing rules redirect between targets that must already exist in the VSchema. A rule that points at an undeclared table or keyspace is accepted by the topology write but fails at plan time.
• Familiarity with the routing model. You should be comfortable with the abstractions in VSchema Configuration & Routing Rule Management — keyspaces, primary vs. lookup vindexes, tablet types (primary, replica, rdonly) — and with how VTGate compiles a VSchema plus rules into an execution plan.
• Read access to the serving graph. Verification depends on VEXPLAIN and on reading the live rules back, so you need at least read credentials against vtctld and a reachable VTGate.

The Resolution and Rewriting Pipeline #

Every statement that reaches VTGate passes through the same ordered pipeline before it becomes a set of shard queries. Routing rules and query rewriting sit at two distinct stages, and understanding the order is what prevents surprises.

1. Parse. The raw SQL is lexed and parsed into an abstract syntax tree. Syntax errors are rejected here, before any topology lookup.
2. Normalize (query rewriting). Literal values in the statement are replaced with bind variables so that WHERE id = 42 and WHERE id = 99 share one cached plan. Normalization is what makes the plan cache effective; without it every distinct literal would compile a fresh plan and thrash VTGate CPU.
3. Apply comment directives. Inline hints of the form /*vt+ ... */ are read off the statement and attached to the plan context — timeouts, workload class, scatter limits. These directives are the per-query half of “dynamic” routing: they let a single call opt into different behavior without any topology change.
4. Resolve table references through routing rules. Each qualified table reference (keyspace.table, optionally suffixed @replica or @rdonly) is rewritten through the active routing rules to its real target keyspace and table, and — if shard routing rules are in play — to a specific target shard. This happens before planning, which is why a MoveTables cutover can redirect a table without the planner or the application knowing.
5. Plan. The resolved references are planned against the target keyspace’s VSchema. The planner evaluates predicates against vindexes and classifies the query as a single-shard route, a targeted multi-shard route, or a scatter across all shards.
6. Execute. The plan is dispatched to the relevant VTTablet instances and results are gathered.

Three kinds of routing rules operate at stage 4, and they are separate objects:

• Routing rules (table-level) redirect a keyspace.table reference, optionally per tablet type. These drive MoveTables traffic switching and read/write splitting.
• Shard routing rules redirect a keyspace.table on a specific source shard to a target shard. These drive Reshard cutovers, letting reads and writes for a key range switch shards independently.
• Keyspace routing rules redirect an entire keyspace reference to another keyspace, used when a MoveTables moves every table at once.

Query rewriting (stages 2–3) is orthogonal: it shapes how a statement executes — its timeout, its workload class, whether a scatter is permitted — while routing rules shape where it executes.

Step-by-Step Implementation #

The steps below assume a commerce keyspace whose orders table is being migrated into a new orders keyspace via MoveTables, plus a read-offload requirement that analytical reads land on rdonly tablets. Each step is independently verifiable.

1. Read the current rules before changing anything #

Routing rules are a single global object; you always replace the whole thing, so start by capturing the baseline.

vtctldclient --server localhost:15999 GetRoutingRules > routing_rules.baseline.json
vtctldclient --server localhost:15999 GetShardRoutingRules > shard_routing_rules.baseline.json

Commit both files. If a change goes wrong, re-applying the baseline is the rollback.

2. Author a table-level redirect #

To point reads and writes for commerce.orders at the new keyspace during a MoveTables cutover, write a rules document that maps both the bare name and the source-keyspace-qualified name to the target.

{
  "rules": [
    {
      "from_table": "commerce.orders",
      "to_tables": ["orders.orders"]
    },
    {
      "from_table": "orders",
      "to_tables": ["orders.orders"]
    }
  ]
}

Each from_table is the reference an application might emit; each to_tables entry is the real physical target. A single-element to_tables is a hard redirect. Multi-element lists are only valid mid-migration, when Vitess is keeping two copies consistent and needs to read from both.

3. Split reads onto replicas and rdonly tablets #

Tablet-type-qualified rules are how you offload reads without touching application connection strings. Suffix the from_table with @replica or @rdonly to give that tablet type a different target from the primary.

{
  "rules": [
    { "from_table": "commerce.orders",         "to_tables": ["orders.orders"] },
    { "from_table": "commerce.orders@rdonly",   "to_tables": ["orders.orders"] },
    { "from_table": "commerce.orders@replica",  "to_tables": ["orders.orders"] }
  ]
}

When a session issues USE commerce@rdonly (or connects with the @rdonly target), VTGate matches the @rdonly-qualified rule first and falls back to the unqualified rule otherwise. This is the deterministic mechanism behind read/write splitting: writes travel the unqualified path to a primary, analytical reads travel the @rdonly path to batch replicas.

4. Apply the rules #

ApplyRoutingRules replaces the entire object atomically and pushes a RebuildVSchemaGraph so every VTGate reloads.

vtctldclient --server localhost:15999 ApplyRoutingRules \
  --rules-file routing_rules.json

# Shard-level redirect for a Reshard cutover (source shard 0 → shards -80 and 80-)
vtctldclient --server localhost:15999 ApplyShardRoutingRules \
  --rules-file shard_routing_rules.json

The apply is transactional at the topology layer, but VTGate reload is eventual: each proxy re-reads the serving graph on the next watch tick, so a fleet converges over a few seconds rather than instantly.

5. Use comment directives for per-query behavior #

Some routing decisions belong to a single call, not the global topology. Comment directives let a query opt into a workload class, a tighter timeout, or a scatter allowance without any rule change.

-- Route this analytical read through the OLAP execution path (streaming, no row cap)
SELECT /*vt+ WORKLOAD=OLAP */ region, sum(total) FROM orders GROUP BY region;

-- Cap a potentially expensive scatter and fail fast rather than exhaust VTGate memory
SELECT /*vt+ QUERY_TIMEOUT_MS=2000 MAX_SCATTER_HOPS=8 */ * FROM orders WHERE status = 'PENDING';

-- Explicitly permit a scatter that the planner would otherwise reject under --no_scatter
SELECT /*vt+ ALLOW_SCATTER */ count(*) FROM orders;

Directives are read at stage 3 of the pipeline and never persist — they affect only the statement that carries them, which makes them safe to inject from an orchestration layer per request. For automated pipelines, integrating directive injection and rule application into infrastructure-as-code keeps routing behavior reproducible, in the same way Async VSchema Validation Workflows keep schema promotion reproducible.

Configuration Reference #

Routing rules themselves carry no flags — they are data. The behavior around them is governed by VTGate flags and per-query directives. The values below are the ones that most often need tuning away from their defaults in a sharded deployment.

Two flags deserve emphasis. --no_scatter turns an accidental full-fan-out query into an error at the gateway rather than a load event that fans across every shard — the single highest-leverage guardrail on an OLTP VTGate. --transaction_mode=SINGLE is the equivalent guardrail for writes, rejecting any transaction that would span shards before it opens a distributed commit.

Failure Modes #

Routing rules fail quietly. A malformed rule is rejected at apply time, but a well-formed but wrong rule is accepted and only surfaces as misrouted queries under traffic. The named scenarios below are the ones worth building alerts for.

Silent scatter after a partial redirect. A tablet-type-qualified rule (@rdonly) is applied but the corresponding unqualified rule is missing or points elsewhere. Symptom: rdonly reads route correctly while primary reads for the same table land on the old keyspace, producing split-brain result sets. Observable via a jump in vtgate_queries_processed on the source keyspace that should have gone quiet. Mitigation: always apply the qualified and unqualified rules for a table in the same document, and diff against the baseline before applying.

Stale plan cache after apply. VTGate reloads rules on its watch tick, but a plan compiled just before the reload can serve a redirected table for a few seconds. Symptom: a brief window of queries hitting the pre-cutover target after ApplyRoutingRules returns. Observable in vtgate_queries_routed labeled by the old keyspace. Mitigation: treat cutover as eventually consistent — do not tear down the source until the source-keyspace query rate reaches zero.

Scatter storm from a lost predicate. A rule redirects a table to a keyspace whose VSchema lacks a matching vindex for the query’s WHERE column, so the planner degrades a formerly single-shard query into a scatter. Symptom: P99 latency spike and connection-pool saturation with no code change. Observable as rising vtgate_queries_processed{plan="Scatter"}. Mitigation: confirm the target VSchema carries an equivalent primary or lookup vindex before switching traffic; guard the gateway with --no_scatter.

Orphaned prepared transactions on a mid-cutover write. A cross-shard write lands mid-reshard while shard routing rules are split, fracturing a two-phase commit boundary. Symptom: ResolveTransaction warnings and stuck metadata records. Mitigation: sequence shard-rule application so writes switch atomically, and reconcile any strays with vtctldclient GetUnresolvedTransactions followed by ResolveTransaction.

Rule / DDL race during a keyspace change. A schema change lands on the target keyspace after the routing rule switches but before the VSchema reload completes, leaving VTGate planning against stale metadata. Aligning rule cutover with Online DDL orchestration — so DDL propagation finishes before the rule flips — eliminates this window. Deeper diagnosis of overlapping and precedence conflicts lives in Debugging VSchema Routing Rule Conflicts.

Verification #

Confirm three things after any routing change: the rules landed, the resolution is correct, and no query silently became a scatter.

Read the live rules back. The apply is only trustworthy once you have re-read the persisted object.

vtctldclient --server localhost:15999 GetRoutingRules

Trace resolution with VEXPLAIN. VEXPLAIN PLAN shows the compiled plan for a statement, including which keyspace and shard it resolves to after routing rules apply. Run it against VTGate for a query you expect to be redirected:

VEXPLAIN PLAN SELECT * FROM orders WHERE id = 42;

A correct single-shard cutover shows the target keyspace and a Route operator of type EqualUnique or Equal. If you see Scatter, the predicate is not resolving against a vindex on the target keyspace — stop the rollout and fix the target VSchema first. For read-split verification, prefix the session with USE commerce@rdonly; and re-run the VEXPLAIN to confirm the @rdonly rule is chosen.

Watch the routing metrics. During and after a cutover, watch vtgate_queries_routed and vtgate_queries_processed{plan="Scatter"} in Prometheus. The source keyspace’s query rate should trend to zero as the target’s rises, and the scatter counter should stay flat. A rising scatter rate is the earliest signal that a rule redirected a table onto a keyspace without the vindex the query needs. Sustained high-QPS routing correctness is covered in depth in Optimizing VIndex Performance for High QPS.

Related #

• Debugging VSchema Routing Rule Conflicts — isolate overlapping rules, precedence collisions, and ambiguous matches when routing goes wrong.
• Mastering VSchema Syntax and Structure — the keyspace, table, and vindex definitions that routing rules redirect between.
• Configuring Lookup Vindexes for Cross-Shard Joins — keep redirected queries targeted instead of scattering across shards.
• Async VSchema Validation Workflows — validate rule and VSchema changes out of band before they reach production.
• VTGate Routing Architecture Deep Dive — the full query-resolution pipeline these rules plug into.

← Back to VSchema Configuration & Routing Rule Management