PolarDB-X Efficient Pagination — Keyset Pagination & Large Table Traversal

Implement efficient pagination for PolarDB-X 2.0 Enterprise Edition (AUTO mode) that maintains constant performance regardless of page depth. Covers Keyset pagination strategies, per-shard traversal, index requirements, and production-ready Java code.

Scope: PolarDB-X 2.0 Enterprise Edition + AUTO mode database only.

Why LIMIT M, N Fails for Deep Pagination

• Standalone DB cost: O(M+N) — must scan M rows before returning N rows.
• Distributed DB cost: O(M+N) × number of shards — each shard returns M+N rows to CN for merge-sort.
• Result: Performance degrades linearly as page number increases; unacceptable for large tables.

For small data volumes with shallow pagination (< 1000 offset), LIMIT M, N is acceptable.

Core Workflow