Reducing Scatter-Gather Query Fan-Out

Every query that omits the sharding key fans out to every shard, so scatter reduction is the single highest-leverage routing optimization on a sharded keyspace: turn a query that touches all N shards into one that touches exactly one, and its cost stops growing every time you split.

Context

This page sits under the VTGate Routing Architecture Deep Dive, which explains how VTGate builds a plan from the VSchema; here the focus is the practical work of moving hot-path queries from scatter to targeted. The mechanism is the primary vindex: when the sharding key appears as an equality predicate, VTGate computes the keyspace ID and sends the query to one shard; when it does not, VTGate has no choice but to fan out and merge. Because a scatter’s latency is bounded by its slowest shard and its cost scales with shard count, a workload dominated by scatters gets worse as you scale out — exactly backwards from the goal of sharding.

Why the query scatters, and the four levers

A query scatters for one reason: VTGate cannot resolve it to a single keyspace ID. That happens when the sharding key is absent from the WHERE clause, when it is present but only as a range or inequality, or when the filter is on a column that is not a vindex. There are four levers to pull, in rough order of preference:

  1. Add the sharding-key predicate — free when the application already has the key.
  2. Add a secondary lookup vindex — when the query must filter by a different column.
  3. Denormalize or materialize — when neither the key nor a lookup fits the access pattern.
  4. Find and measure offenders — so you fix the queries that actually cost you.
Reducing fan-out: scatter before versus targeted after Left panel shows a scatter to four shards from a query with no sharding-key predicate. Right panel shows the same logical query, now with a sharding-key predicate, resolving to one shard. Before — scatter WHERE order_id = 1082337 VTGate -40 40-80 80-c0 c0- all 4 shards touched After — targeted WHERE customer_id = 42 AND order_id = ... VTGate -40 40-80 c0- 80-c0 1 shard touched

Lever 1: add the sharding-key predicate

The cheapest fix is usually the application already knowing the sharding key but not passing it. A lookup by order_id alone scatters; if the caller also has the customer_id that the table is sharded on, adding it to the predicate lets VTGate resolve one shard.

-- Scatter: no sharding-key predicate, fans out to every shard
SELECT * FROM orders WHERE order_id = 1082337;

-- Targeted: sharding key present, VTGate computes one keyspace ID
SELECT * FROM orders WHERE customer_id = 42 AND order_id = 1082337;

This is the ideal outcome because it costs nothing structural — no new vindex, no extra table — just carrying the key through the call path. Audit hot endpoints for cases where the key is available upstream and simply not threaded into the query.

Lever 2: add a secondary lookup vindex

When the application genuinely does not have the sharding key at query time — an order-status page that has only the order_id — a lookup vindex maintains a mapping table from the secondary column to the keyspace ID, so VTGate can still target one shard. The lookup vindex names the table and columns that store the mapping, and Vitess keeps it consistent on writes.

{
  "sharded": true,
  "vindexes": {
    "hash": { "type": "hash" },
    "order_id_lookup": {
      "type": "consistent_lookup_unique",
      "params": {
        "table": "commerce.order_id_lookup",
        "from": "order_id",
        "to": "keyspace_id"
      },
      "owner": "orders"
    }
  },
  "tables": {
    "orders": {
      "column_vindexes": [
        { "column": "customer_id", "name": "hash" },
        { "column": "order_id", "name": "order_id_lookup" }
      ]
    }
  }
}

With this in place, SELECT * FROM orders WHERE order_id = 1082337 first consults the lookup vindex to find the keyspace ID, then targets the owning shard — two round trips instead of an N-way scatter. The design, ownership, and consistency semantics of lookup vindexes, and how to keep the mapping table itself from becoming a hotspot, are covered in Configuring Lookup Vindexes for Cross-Shard Joins.

Lever 3: denormalize or materialize

Some access patterns fit neither the sharding key nor a clean lookup — a dashboard that filters orders by warehouse_id, a dimension orthogonal to the customer sharding key. Adding a lookup vindex for every such column multiplies write cost, so the alternative is to denormalize: maintain a purpose-built table sharded on the column you query by, populated by a VReplication Materialize workflow that keeps it current from the source. Reads against the materialized table are targeted on its own key; the write path pays a controlled, asynchronous cost instead of a per-query scatter.

Materialization is the right tool when a read pattern is high-volume, latency-sensitive, and tolerant of small replication lag — reporting reads, denormalized read models, and search-style projections. It trades storage and a background stream for turning a chronic scatter into a targeted read.

A lighter form of the same idea is to carry a redundant copy of the sharding key onto the child table. If order_items is queried by order_id but the natural sharding key is customer_id, storing customer_id on order_items and sharding it the same way co-locates each order’s items with the order itself — a targeted read and a single-shard join, at the cost of writing the extra column. This co-location trick is often cheaper than either a lookup vindex or a full materialized table, and it is the standard pattern for keeping related rows on the same shard so joins between them never fan out.

Choosing between the levers is a cost trade. Adding a predicate costs nothing but requires the caller to hold the key. A lookup vindex costs write amplification on the owned column and a second round trip on read. Denormalization and materialization cost storage plus a background stream and introduce replication lag. Co-location costs a redundant column and a schema change. Rank a scatter offender by how often it runs and how much it costs, then reach for the cheapest lever that makes it targeted — usually the predicate first, the vindex second, and materialization only when the access pattern genuinely fits neither.

Lever 4: find the offenders with --warn_sharded_only

You cannot fix scatters you cannot see. VTGate’s --warn_sharded_only flag flags any query that fans out across shards, so you can surface unintended scatters in staging (or in a canary) before they page you in production.

vtgate --warn_sharded_only

With the flag on, scatter queries increment a warning counter and appear in the query log, letting you build a ranked list of the fan-out queries by frequency and cost, then work down it. This is a discovery tool, not a production guard — leave it on where you are hunting offenders, and drive the ongoing signal from metrics: the ratio of scatter queries to total is the number that tells you whether the workload is drifting back toward fan-out, and it deserves a standing alert as described in Alerting on Scatter-Query Ratio.

Edge cases and gotchas

  • Aggregates and ordered LIMITs are inherently scatter. A COUNT(*) or ORDER BY ... LIMIT with no sharding-key predicate must touch every shard to be correct — you cannot predicate your way out of it. Serve those from a materialized summary rather than pretending a predicate will target them.
  • IN lists fan out to the union of shards. WHERE customer_id IN (...) targets only the shards owning those keys, which is better than a full scatter but still multi-shard for a wide list. Batch by shard where the list is large.
  • Range predicates on a hash vindex still scatter. WHERE customer_id BETWEEN 1 AND 100 cannot resolve to one shard under a hash vindex because hashing destroys ordering. Only equality (or IN) on a hash-vindexed column targets.
  • Lookup vindex write amplification. Every insert/update to the owned column writes the mapping table too. A lookup on a high-churn column can cost more than the scatters it saves — measure before adding one, and see the performance tuning in the lookup vindex guide.
  • Stale materialized reads. A Materialize target lags its source by the stream’s replication delay. Do not route reads that require read-your-writes consistency to a materialized table.
  • The predicate must be on the vindex column itself. Filtering on a function of the key (WHERE MD5(customer_id) = ...) or a cast defeats routing — VTGate cannot invert it, so it scatters. Keep the sharding-key predicate a plain equality on the raw column.

Verification

Confirm a query is actually targeted, not just that it looks like it should be. Inspect the plan offline against the VSchema before shipping, then watch the live scatter ratio:

# Show the routing plan without touching a live tablet
vtexplain --vschema-file vschema.json --schema-file schema.sql \
  --shards 4 --sql "SELECT * FROM orders WHERE order_id = 1082337"

A targeted query reports a plan touching a single shard (a Route with a SelectEqualUnique or lookup-backed opcode); a scatter reports SelectScatter across all shards. After deploying the fix, the VTGate scatter-to-total ratio should drop for that query pattern and stay down — if it climbs back, a new predicate-less caller has appeared and belongs on the offender list.

← Back to VTGate Routing Architecture Deep Dive