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

Pagination across a sharded Vitess keyspace forces a choice between offset-based paging and keyset bounds, but VTGate does not resolve that choice automatically. When a scatter query carries LIMIT and OFFSET, VTGate pushes both down to each shard and merges the results, so a page at offset n causes every shard to read roughly n + limit rows before the merge. On large tables this multiplies per-shard I/O and memory, and deep offsets can approach query-server limits before VTGate assembles the final page.

Keyset pagination avoids that cost by bounding the query with a WHERE predicate on an indexed, monotonic column, but it requires a suitable column and a deterministic sort order that eliminates ties. If the desired ORDER BY is not the sharding key, Vitess performs a cross-shard merge sort, adding coordination overhead. Because Vitess does not rewrite an OFFSET query into seek bounds or compute a single global offset, the application must decide the strategy per table.

When the required sort column is not the sharding key, what are the practical limits of OFFSET depth before VTGate merging becomes a bottleneck, and how should an application weigh keyset bounds against the need for a non-sharding ORDER BY?