Cross-shard queries
What happens to a query the shard key can’t route. database-sharding describes the happy path — hash a column, send the query to one shard, return the answer — and this is the other path, which is where a sharded database’s costs actually live.
Push down, or fan out and merge
Vitess states the rule as a preference with a fallback: push as much
work as possible into the underlying MySQL instances, and “when this is not possible… collect input
from multiple sources and merge the results.” Both halves matter. A WHERE user_id = 42 becomes
one MySQL query on one shard and the proxy does nothing but pass it through. A GROUP BY over all
users becomes N partial aggregations plus a merge the proxy performs itself, in its own memory, on
whatever the shards send back.
So the cost of a sharded query is set by how much work survives the push-down. The proxy is single-threaded relative to your query, holds no data, and can only combine what it is given.
Scatter has a precise definition
A routing operator naming a sharded keyspace without a vindex scatters to every shard. The vindex — the mapping from a column value to a shard — is the thing whose absence causes it. Vitess’s docs are careful that fan-out is not the same as scatter: a query can legitimately touch several shards because it named several keys, and only the ones that couldn’t be routed count as scatter.
The practical reading: scatter is not a property of how many shards answered, it is a property of whether the planner knew which ones to ask.
Why deep pagination is the worst ordinary case
ORDER BY created_at DESC LIMIT 1000000, 10 names no shard key, so it scatters. Each shard can sort
its own rows, and the merge has to happen centrally because no shard knows the global order. To
return rows 1,000,000–1,000,010 the proxy needs every shard’s first 1,000,010 rows in that order, so
the work grows with the offset while the result stays ten rows.
sharded-pagination-interview-post poses exactly this and is paywalled; the mechanism above is
what the Vitess documentation supports, and it is the shape of the answer rather than a measured
cost — no Vitess page this wiki has read gives a cost model for deep offsets or recommends a
replacement.
That gap is worth stating precisely, because the well-known fix (carry the last row’s sort key forward as a cursor and ask each shard for rows after it, so every shard returns at most the page size) is not sourced here. It is the obvious consequence of the mechanism, and until a source states it, this wiki holds the problem and not the remedy.
You can measure it
VEXPLAIN TRACE runs the statement and reports ShardsQueried per operator alongside call
counts and row counts; VEXPLAIN KEYS reports, without executing, which columns a query filters,
joins and groups on — “potential candidates for indexes (or vindexes), primary keys, or sharding
keys” vitess-query-serving-docs.
Between them those two answer the design question this page is about. TRACE says how badly the current query scatters; KEYS says which column would have to become the vindex for it to stop. The shard key is chosen once and constrains every query afterwards — which is the read-path version of the point planetscale-768-servers makes about capacity.
Related
database-sharding · vitess · vitess-query-serving-docs · sharded-pagination-interview-post · planetscale-768-servers · operational-databases · synthesis