Learn Vitess - Query Routing & VTGate

#Introduction

Episode 4 organized data into shards. Now we dissect the engine that makes this distribution transparent to applications: VTGate. VTGate orchestrates every query — it decides which shard a query goes to, how results are merged when multiple shards are involved, and how queries are broken up so each shard's MySQL only receives the relevant work.

Episode 5 roadmap: VTGate's role in routing, the single-shard fast path, cross-shard queries and scatter-gather, query planning and SQL support, then the failure modes to watch out for. This is the episode you'll reach for most often when debugging queries in production.

#VTGate's Role in Routing

VTGate is a stateless MySQL proxy facing the application. Its three core tasks: parsing (understanding the SQL), planning (building a cross-shard execution plan), and execution (sending to shards and merging results). Because it's stateless, VTGate is easy to scale — add VTGate Pods, add capacity.

Applications see one "giant MySQL". VTGate carries the complexity behind the scenes, including picking the right shard through a combination of VSchema and Topology Service.

#Fast Path: Single-shard Query

A query that filters on the sharding key column is the fast path — VTGate computes the shard from the vindex value and sends the query to a single shard:

SELECT name FROM users WHERE user_id = 42

Because user_id is the sharding key column with the hash vindex, VTGate knows exactly which shard stores user_id = 42. One round-trip, no merging. This is why schema design emphasizes a sharding key that queries use frequently.

#Cross-shard Queries and Scatter-gather

A query without the sharding key column must touch all shards — this is called scatter or scatter-gather. For example:

SELECT COUNT(*) FROM users WHERE name = 'Arman'

VTGate sends the query to every shard, receives partial results, then merges them. For COUNT(*) the results are summed; for ORDER BY and LIMIT VTGate performs a global sort and trim. This works, but the cost grows with the number of shards.

Important to understand: cross-shard JOINs are supported, with conditions. Joins between co-located tables (same sharding key) can be resolved in one shard. Joins that cross different shards force VTGate to gather and join in memory — it works but it's expensive.

vtctlclient VtGateExecute -json "SELECT COUNT(*) FROM users" commerce

The vtctlclient VtGateExecute command executes a query through VTGate from the admin side — useful for testing how VTGate handles a query without an application.

#Query Planning and SQL Support

VTGate rewrites and splits queries before sending them to shards. The main technique is query splitting: a global query is broken into per-shard queries that only carry the relevant data. For example, a query with WHERE user_id IN (1, 2, 3) is split per shard based on where each value lives.

VTGate's SQL support is broad, but not total. Here's what you need to know:

• Fully supported: basic SELECT, INSERT, UPDATE, DELETE, aggregations, co-located JOIN, single-shard transactions.
• Limited: cross-shard JOIN (works but expensive), cross-shard GROUP BY (supported for certain aggregations), subqueries in certain forms.
• Not supported: some MySQL functions that depend on global state, such as operations that cross shards without a steering column (for example, a mass UPDATE without a sharding key filter) — Vitess will firmly reject these to prevent accidental updates beyond bounds.

When a query is rejected, the error message is usually clear. This is a safety feature, not a bug: Vitess prefers refusing a dangerous query over executing it incorrectly.

#Failure Modes to Recognize

Some of the most common failures you'll hit at VTGate:

• Query timeout. VTGate has a default time limit per query. Slow cross-shard queries easily hit the timeout.
• Scatter overload. Too many scatter queries overwhelm VTGate and every shard. Monitor the scatter metric from episode 7.
• Primary not serving. If a shard primary is down, VTGate rejects write queries until failover completes.
• Topology service down. VTGate uses a topology cache, so queries usually keep working, but operations that need fresh metadata are disrupted.

To check shard status and query health, vtctlclient is your best friend:

vtctlclient ListShardHealth
vtctlclient GetTablet -tablet <alias>

#Query Splitting in Practice

One of the techniques VTGate uses most is query splitting. Take a query with repeated values:

SELECT * FROM users WHERE user_id IN (5, 1000, 2500000)

If those three values live in different shards, VTGate splits the query into three per-shard queries, each loading only the relevant values, then merges the results. Each shard only works on its own portion — no shard carries the full query.

This pattern also applies to UPDATE and DELETE with IN — as long as the column is the sharding key. That's why a WHERE user_id IN (...) filter is actually recommended, not avoided. VTGate handles the splitting and merging automatically.

#Determining Keyspace and Table Access

For queries that don't mention a keyspace, VTGate uses the keyspace selected via USE or the default. The keyspace-to-table mapping can be inspected directly:

vtctlclient GetVschema commerce

vtctlclient GetVschema displays the active VSchema — the map of tables, vindexes, and sharding status. When debugging query routing, this is the first place to verify that the configuration matches expectations.

#Closing

In this episode 5 you understood how VTGate routes queries: the single-shard fast path for queries with the sharding key column, scatter-gather for cross-shard queries, query planning with splitting, plus the limits of SQL support and common failure modes.

Key takeaways:

• VTGate is stateless and horizontally scalable; it carries all the routing complexity.
• Queries with the sharding key column take the single-shard fast path — schema design chases this.
• Queries without the sharding key column spread to all shards and have their results merged.
• Cross-shard JOINs work but are expensive; co-located JOINs are far preferred.
• Vitess rejects UPDATE/DELETE without a sharding key filter for safety.
• Know your failure modes: timeout, scatter overload, and primary down.

In the next episode, episode 6, we keep availability high: replication and failover — primary and replica topology, automatic failover with vtctld and pseudo-GTID, and backup and recovery strategies. See you there!