Vitess Cross-Shard Pagination: Limits of OFFSET Merging Versus Keyset Bounds

Short answer

There is no fixed OFFSET depth at which VTGate merging "breaks." The practical ceiling is where shards × (offset + limit) rows exceed the tightest of four budgets: per-shard query-server memory, result-size caps, network transfer, or your latency SLO. Shard count and row width dominate that number, so a threshold measured on a 4-shard test keyspace will not transfer to a 64-shard production keyspace. Keyset bounds are the better default when a deterministic total order exists and the sort column is indexed on every shard; when it is not, you are choosing between deep-offset cost and cross-shard merge-sort cost, not between a good and a bad option.

What is confirmed vs. likely

Confirmed scatter semantics: a query carrying LIMIT and OFFSET pushes both to each shard, and VTGate merges the returned slices. Keyset pagination replaces the offset with an indexed range predicate, so each shard returns at most limit rows.

Likely, and worth measuring rather than assuming: VTGate's merge is a heap-based merge over sorted shard streams, so its own CPU scales with rows consumed, not with offset alone. The dominant cost is usually the shards scanning, sorting, or buffering offset + limit rows before returning their slice. Planner rules, limit pushdown, and merge behavior vary by release and configuration, so treat any specific number as version- and workload-specific.

Decision rule for a non-sharding ORDER BY

1. If the sort column is indexed on every shard and you can add a unique tiebreaker, use keyset. Page cost becomes roughly shards × limit and stops growing with depth. The remaining cost is the scatter and merge sort, which keyset does not remove.
2. If the sort column is not indexed per shard, keyset can degrade to per-shard scans and may not beat OFFSET. Fix index coverage first, or accept a capped OFFSET.
3. If keyset is impractical, cap OFFSET depth, tighten filters so the candidate set is small, or change the data model (materialize or shard by the required sort key). Do not let offsets grow without a ceiling.

Verification

Run read-only plans and compare. On a shard's MySQL endpoint, or through VTGate for the scatter form:

-- OFFSET form
EXPLAIN SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100000;

-- Keyset form (expanded predicate)
EXPLAIN SELECT id, created_at, title
FROM articles
WHERE created_at < ?
   OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Confirm the keyset plan uses the intended composite index and that each shard returns at most limit rows. Then measure at increasing offsets against a representative keyspace, watching per-shard rows examined, query-server memory, and p99 latency; the knee is your real ceiling. Check VTGate and vttablet query statistics and result-size settings for your release, since defaults change between versions.

Tie-breaking test: insert several rows sharing the same sort value and page through them with ORDER BY sort_col, unique_id. If any row is skipped or repeated, your tiebreaker is not unique.

Uncertainty and one question

Two caveats. OFFSET is not a stable pagination contract under concurrent writes: rows shift between pages even when the query succeeds. Keyset assumes the sort key and tiebreaker stay stable for the session. Both should be reviewed against your Vitess version before you commit to a threshold, and row-constructor predicate forms may index differently than the expanded form depending on the MySQL version underneath.

One detail changes the recommendation: is the ORDER BY column indexed on every shard for the exact predicate and sort direction? If yes, keyset is almost always the right default. If no, the honest answer is a capped OFFSET plus a data-model change, and index work should come before pagination tuning.