@Rocky Why is keyset pagination better than offset pagination in Primary Key tables? How much better is it? Does tuple comparison work? Like I have 3 columns in my PK table, so Im thinking for keyset pagination, use (col1, col2, col3) > (val1, val2, val3)

Keyset pagination (also called the Seek Method) is significantly more efficient than offset pagination in StarRocks, particularly for Primary Key tables. 1. Why it is better In a distributed database like StarRocks, Offset Pagination (

LIMIT 1000, 10

) is inefficient because: * Scan-and-Discard: The system must scan, sort, and process all $M+N$ rows (1,010 in this case) and then throw away the first $M$ (1,000) rows. As you go deeper into the result set, the overhead grows linearly. * Distributed Overhead: In a cluster, every BE (Backend) involved must potentially send its top $M+N$ rows to the FE (Frontend) to ensure global ordering before discarding the offset. This creates a massive network and memory bottleneck. Keyset Pagination uses a unique identifier (like your Primary Key) to jump directly to the starting point: * O(1) Seek: It uses the Primary Key index or Prefix Index to "seek" the exact disk location of the first row of the next page. * Minimal Data Transfer: Only the requested $N$ rows are processed and sent over the network. 2. How much better is it? The performance gap depends on the offset depth: * Small Offsets (e.g., first 5 pages): The difference is negligible. * Large Offsets (e.g., page 1,000+): Keyset pagination is typically orders of magnitude faster. * Primary Key Tables: Since PK tables use a persistent index (HashMap) for mapping keys to locations, seeking is extremely fast. StarRocks docs note that PK tables can be 3–10x faster than Unique Key tables for general queries; for deep pagination, this multiplier can be much higher because you avoid the linear cost of the offset entirely. 3. Does Tuple Comparison work? Standard SQL tuple comparison like

(col1, col2, col3) > (val1, val2, val3)

is often not the best way to write this in StarRocks, as older versions may not support inequality on row constructors or might not optimize the index scan correctly. Recommended Approach 1: Expanded Logic (Traditional) For the best compatibility and performance across all versions, use the decomposed boolean logic:

sql
SELECT * 
FROM your_table
WHERE (col1 > val1)
   OR (col1 = val1 AND col2 > val2)
   OR (col1 = val1 AND col2 = val2 AND col3 > val3)
ORDER BY col1, col2, col3
LIMIT 10;

Recommended Approach 2:

encode_sort_key

(New in v4.0) If you are on v4.0 or later, StarRocks introduced the

encode_sort_key

function. It creates a single binary key that preserves the sort order of multiple columns. This is the cleanest and most efficient method for multi-column pagination: 1. Add a generated column to your table:

sql
    ALTER TABLE your_table ADD COLUMN page_key VARBINARY(1024)
    AS (encode_sort_key(col1, col2, col3));

2. Use it in your query:

sql
    SELECT * FROM your_table
    WHERE page_key > [binary_val_from_last_row]
    ORDER BY page_key
    LIMIT 10;

Tip: Ensure your

ORDER BY

columns match the prefix of your Sort Key (defined in

ORDER BY()

at table creation) to ensure the system doesn't have to perform an expensive disk sort. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • knowledge/usage/best-practice.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md • sql-reference/sql-functions/json-functions/json-operators.md