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.

The same rule from a second engine (added 2026-08-20)

citus states the identical preference in different words, which is worth recording because two independent systems converging is stronger than one vendor's documentation. Its executor is called adaptive: when every co-located distributed table in a query carries the same filter on the distribution column, Citus "will automatically detect that and send the whole query to the worker node that stores the data" — push-down taken to its limit, the entire statement rather than a fragment. Without such a filter, "each shard is queried in parallel."

Two things Citus adds to the picture Vitess drew:

Push-down is cheaper when the router lives in the engine. Citus's shards are ordinary Postgres tables, so what gets pushed down is plain SQL that the worker's own planner optimizes. Vitess pushes down to MySQL too — the difference is that Citus does not have to reimplement anything to decide what is pushable, because it is Postgres.
shard-colocation is the design lever. Vitess's answer to "stop scattering" is a vindex on the right column; Citus's is co-locating the tables on the same distribution column. Both are the same instruction to the application developer: make the routable path cover your real queries, because the fallback is a merge someone pays for centrally.

And the standing gap survives the second source: Citus's README gives no cost model for the non-routable case either — no measurement of what a scatter costs, and nothing at all on deep offsets. Two engines now describe the mechanism; neither prices it.

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.

database-sharding · shard-colocation · vitess · vitess-query-serving-docs · citus · sharded-pagination-interview-post · planetscale-768-servers · operational-databases · synthesis