VTGate Routing Architecture Deep Dive

VTGate is the stateless SQL proxy that every client talks to, and the single component that decides whether a query becomes a cheap targeted route to one shard or an expensive scatter-gather across the whole keyspace. This page resolves the operational problem that sits underneath almost every Vitess latency incident: understanding exactly how VTGate parses a statement, evaluates it against the VSchema, computes a keyspace ID, and dispatches to a VTTablet — so you can tune the router for predictable p99 latency and reason about why a query fanned out when you expected it to stay on one shard. It is written for database platform engineers and MySQL SREs who own the query path and the on-call pager when a routing regression lands. The physical shard layout is assumed settled here — this page is about the decision engine that sits on top of it.

Prerequisites #

Before tuning routing behaviour, confirm the following are in place:

• Vitess 18.0 or later — the vtctldclient command surface, the Gen4 query planner as default, and the HealthCheck v2 discovery path all assume a recent release. Earlier versions use vtctlclient and the older V3 planner, whose plan shapes differ.
• A running topology server — an etcd (v3) or Consul cluster reachable from every cell — that stores the keyspace, shard, and tablet records VTGate reads to build its serving graph. The structural role of the topology server is covered in Vitess Sharding Architecture & Topology Design.
• A sharded keyspace with a valid VSchema. Every routable table needs a primary vindex; without one VTGate cannot compute a keyspace ID and scatters unconditionally. If the keyspace layout is unsettled, work through Designing Horizontal Shard Topologies first.
• A chosen partitioning model. Whether shard boundaries are semantic (range) or arithmetic (hash) changes how predicates map to shards — see Understanding Vitess Keyspace Partitioning Models.
• vtexplain available in your deploy pipeline so you can inspect a plan offline before it ships, without a live cluster.

Query Resolution Pipeline and VSchema Evaluation #

At the core of VTGate’s operation is a deterministic, multi-stage resolution pipeline. On receipt of a client statement the parser normalizes the syntax, extracts bind variables (so WHERE id = 42 and WHERE id = 99 share one plan-cache entry), and evaluates routing predicates against the active VSchema. The VSchema is the logical contract for each keyspace: it defines which tables are sharded, which column carries the sharding key, and which vindex maps that column to a keyspace ID.

VTGate classifies every statement into one of three execution shapes:

• Targeted (single-shard). An equality predicate on the sharding-key column lets the planner compute one keyspace ID, find the one shard whose range contains it, and issue a single query. This is the hot path and the whole point of the topology.
• Multi-shard subset. An IN list or a bounded key set resolves to a handful of shards; VTGate sends to only those and merges the results.
• Scatter-gather. No usable sharding-key predicate means the statement fans out to every shard, and VTGate aggregates, sorts, and limits the combined result set in its own memory.

The precision of this classification depends entirely on the partition boundaries and vindex definitions being correct. A misaligned partitioning model or a stale VSchema turns what should be targeted reads into scatter fan-outs, and scatter latency is always dominated by the slowest shard.

The classification logic that decides a query’s execution shape is summarized below — the presence and cardinality of the sharding key in the predicate is what separates a cheap targeted route from an expensive scatter-gather.

Core Mechanism: The Serving Graph and How a Predicate Becomes a Route #

Routing is only correct if VTGate’s picture of the live cluster matches reality. VTGate keeps a local, in-memory serving graph that maps each keyspace to its shards, and each (keyspace, shard, tablet_type) tuple to the current healthy VTTablet endpoint. Two independent subsystems keep that graph fresh, and understanding the split is what lets you tune it.

Topology watch — the shape of the deployment. The set of keyspaces, shards, and their key ranges lives in the topology server. VTGate caches this through a component called srv_topo, refreshed by a watch/TTL loop rather than a hot-path read. When a Reshard splits -80 into -40 and 40-80, or a SwitchTraffic moves reads to a new shard set, the change lands in the topology server and srv_topo picks it up on its next refresh. This is why a freshly reshared keyspace can briefly route against the old range if the cache TTL is set too high.

HealthCheck — which tablet is serving. Which tablet currently answers for a shard-and-type is decided by the HealthCheck subsystem. Each VTGate streams health from the tablets it discovers and continuously ranks them by serving state and replication lag. When VTOrc promotes a replica during a primary failure, the old primary stops advertising PRIMARY, the new one starts, and HealthCheck reroutes on the health stream — no topology write is needed on the read path. Draining a lagging replica (--discovery_low_replication_lag) happens here too.

For a single equality query the pipeline is therefore: parse → compute keyspace ID via the primary vindex → look up the owning shard range in the serving graph → pick the healthiest tablet of the requested type from HealthCheck → dispatch. With no usable predicate, the shard-lookup step returns all shards and the query scatters. Everything you tune about routing is really tuning one of these two freshness loops or the planner that consumes them.

Step-by-Step: Making a Query Route the Way You Intend #

The sequence below takes a table that scatters and makes it route to a single shard, then verifies each change independently so you can halt and inspect state before proceeding.

1. Confirm the table has a primary vindex. The primary vindex is what makes a table routable at all. Inspect the live VSchema for the keyspace:

vtctldclient GetVSchema commerce

If the target table has no column_vindexes entry, VTGate has no way to compute a keyspace ID for it and will scatter every query. Everything else on this page is moot until this is fixed.

2. Bind the sharding-key column to a vindex. Declare the primary vindex and attach it to the column your hot-path queries filter on. A hash vindex gives uniform distribution for a high-cardinality integer key:

{
  "sharded": true,
  "vindexes": {
    "hash": { "type": "hash" }
  },
  "tables": {
    "orders": {
      "column_vindexes": [
        { "column": "customer_id", "name": "hash" }
      ]
    }
  }
}

Apply it:

vtctldclient ApplyVSchema --vschema-file commerce.vschema.json commerce

3. Verify the plan offline before it ships. Use vtexplain to see how VTGate will route the query against this VSchema, without touching a live cluster. A query with the sharding-key predicate must produce a single-shard Route, not a Scatter:

vtexplain --vschema-file commerce.vschema.json --schema-file schema.sql \
  --shards 4 --sql "SELECT * FROM orders WHERE customer_id = 42"

4. Tune the freshness loops for your failover profile. Set the srv_topo cache TTL low enough that reshard boundary changes propagate within your tolerance, and the health-check timeout tight enough that a dead tablet is drained before it accumulates errors. These are the two knobs that most often cause “stale routing” incidents (see the reference table below).

5. Pick the planner and normalization behaviour. Keep the Gen4 planner (default on 18.0+) for correct cross-shard aggregation plans, and keep --normalize_queries on so literals collapse into bind variables and share plan-cache entries — a workload of WHERE id = <literal> variants otherwise thrashes the plan cache and inflates parse CPU.

6. Integrate at the application layer. VTGate speaks the MySQL protocol, so any driver compliant with the Python Database API Specification v2.0 connects to it directly. Because VTGate is stateless, orchestration code can pool connections across a fleet of routers with no session affinity, and push shard-aware retry logic above the proxy:

import pymysql

# VTGate speaks the MySQL wire protocol; the driver treats it as one logical DB.
conn = pymysql.connect(host="vtgate.internal", port=3306, db="commerce")

def targeted_read(customer_id: int):
    # Equality on the sharding key -> VTGate computes one keyspace ID
    # and issues a single-shard route rather than a scatter.
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, total FROM orders WHERE customer_id = %s",
            (customer_id,),
        )
        return cur.fetchall()

Keep retries idempotent and bounded: a stateless router means any VTGate instance can serve the retry, but an unbounded retry budget against a scatter query amplifies load on an already-struggling shard set.

Configuration Reference #

The flags below are the ones that most directly shape routing behaviour and failover latency. Defaults are the shipped Vitess values; recommended values assume a production sharded keyspace with active reparents.

Failure Modes Specific to Routing #

Silent scatter from a missing or unused vindex. Root cause: a table without a primary vindex, or a query whose predicate does not match the sharding-key column, so VTGate cannot compute a keyspace ID. Symptoms: p99 latency scales with shard count; the single-shard hit-rate metric collapses; --warn_memory_rows fires and VtgateApi histograms widen. Mitigation: add a primary vindex to every routable table; for secondary access paths add a lookup vindex so the hot query still resolves to one shard; catch omissions with vtexplain in the deploy pipeline.

Stale routing after a reshard. Root cause: --srv_topo_cache_ttl set too high, so VTGate keeps routing against pre-split shard ranges after SwitchTraffic. Symptoms: reads on the reshared range return stale or missing rows for a window matching the TTL; different VTGate instances disagree. Mitigation: lower the cache TTL; confirm all routers observe the new serving graph before completing the write cutover.

Routing storms during failover. Root cause: a primary election or network partition, with buffering disabled, so in-flight writes to the affected shard fail hard and clients retry in a tight loop. Symptoms: a spike in VtgateApiErrorCounts correlated with the reparent; connection churn against the new primary. Mitigation: enable --enable_buffering with --buffer_max_failover_duration tuned to your reparent time so VTGate pauses affected writes for a few seconds instead of erroring; let VTOrc complete the promotion before traffic resumes.

Plan-cache thrash from unnormalized literals. Root cause: --normalize_queries disabled, or an ORM emitting inlined literals, so every distinct value compiles a fresh plan. Symptoms: high parse/plan CPU on VTGate; plan-cache eviction rate climbs; latency variance under otherwise steady load. Mitigation: keep normalization on; parameterize queries in the application driver.

Scatter OOM on the router. Root cause: an unbounded scatter (missing LIMIT, or an aggregate over every shard) whose merged result exceeds --max_memory_rows. Symptoms: the query is killed with a memory-rows error, or VTGate RSS spikes; often preceded by --warn_memory_rows log lines. Mitigation: keep --max_memory_rows set so the router fails one query rather than OOM-killing itself; find the offending pattern from the warn logs and give it a sharding-key predicate or a lookup vindex.

Multi-shard writes escalating cost. Root cause: a transaction that spans shards, forcing distributed coordination. Symptoms: commit latency climbs, lock hold times grow across shards. Mitigation: keep writes single-shard by design; where cross-shard atomicity is genuinely required, understand the commit protocol in Handling Cross-Shard Transactions in Vitess before enabling TWOPC.

Verification #

Confirm the router is resolving queries as designed before declaring a change production-ready.

Hot-path queries route to one shard. Inspect the plan offline — the sharding-key predicate must produce a single-shard Route:

vtexplain --vschema-file commerce.vschema.json --schema-file schema.sql \
  --shards 4 --sql "SELECT * FROM orders WHERE customer_id = 42"

The serving graph reflects current tablets. List tablets by type and confirm each shard reports one healthy PRIMARY and at least one serving REPLICA, matching what VTGate should be routing to:

vtctldclient GetTablets --keyspace commerce --tablet-type primary

Scatter rate is low and stable. Watch VTGate’s VtgateApi latency histogram and the --warn_memory_rows log stream. A healthy sharded workload shows most queries as single-shard with a tight latency band; a rising scatter rate or widening tail is an early signal of a VSchema or predicate regression, well before it becomes a latency page. When a shard is genuinely unavailable, graceful degradation of the route is handled by Implementing Fallback Routing for Shard Outages.

Related #

• Handling Cross-Shard Transactions in Vitess — the two-phase commit lifecycle VTGate runs when a write cannot stay on one shard.
• Understanding Vitess Keyspace Partitioning Models — how range, hash, and lookup distribution shape the predicates that route cleanly.
• Designing Horizontal Shard Topologies — the physical shard layout the router dispatches across.
• Configuring Lookup Vindexes for Cross-Shard Joins — adding a secondary vindex so non-key access paths still resolve to one shard.
• Implementing Fallback Routing for Shard Outages — keeping the router serving when a shard’s tablets go unhealthy.

← Back to Vitess Sharding Architecture & Topology Design