polardb/polardbx-sql/polardbx-pagination

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

1. Determine the pagination scenario:

• Does the table use New Sequence (AUTO mode default)? → Scenario A
• Is the sort column potentially non-unique (time column, group sequence ID)? → Scenario B
• Are there extreme stability requirements with many shards? → Per-shard traversal

1. Select the pagination strategy (see Quick Reference below).

1. Verify index exists for the sort columns (critical for performance).

1. Generate code with proper JDBC settings for production use.

Pagination Strategies Quick Reference

Scenario A: New Sequence ID (AUTO mode default)

ID is globally ordered → represents write time order:

-- First batch
SELECT * FROM t1 ORDER BY id LIMIT 1000;
 
-- Subsequent batches (record last_id from previous batch)
SELECT * FROM t1 WHERE id > :last_id ORDER BY id LIMIT 1000;

Cost: O(N) constant, regardless of page depth.

Scenario B: Sort Column May Have Duplicates (time columns, etc.)

Use (sort_column, id) tuple comparison as cursor:

-- First batch
SELECT * FROM t1 ORDER BY gmt_create, id LIMIT 1000;
 
-- Subsequent batches (record last_gmt_create and last_id)
SELECT * FROM t1
WHERE (gmt_create, id) > (:last_gmt_create, :last_id)
ORDER BY gmt_create, id LIMIT 1000;

PolarDB-X supports tuple comparison (col1, col2) > (?, ?) and can leverage composite indexes.

Per-Shard Traversal (Advanced)

For extreme scenarios (many shards, relaxed ordering):

-- 1. Get topology
SHOW TOPOLOGY FROM t1;
 
-- 2. Paginate within each shard using HINT
/*+TDDL:NODE('partition_name')*/
SELECT * FROM t1 WHERE (gmt_create, id) > (?, ?)
ORDER BY gmt_create, id LIMIT 1000;

Index Requirements

-- Create composite index for pagination
ALTER TABLE t1 ADD INDEX idx_page (gmt_create, id);

Method Comparison

Java JDBC Settings

Best Practices

1. Never use LIMIT M,N for deep pagination on large tables — cost grows linearly.
2. Always create composite indexes matching your ORDER BY columns.
3. Use tuple comparison (col, id) > (?, ?) for non-unique sort columns — PolarDB-X supports this natively.
4. Keep autocommit=true for traversal — pagination is long-running; don't create long transactions.
5. Use streaming reads (fetchSize = Integer.MIN_VALUE) to avoid loading entire result sets into memory.
6. Consider Batch Tool for pure data export scenarios — it has built-in PolarDB-X optimizations.

Reference