What are the differences and use cases between keyset seeker and offset operations?

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: keyset seeker与offset操作区别与使用场景有哪些呢

| username: virvle

What are the differences and usage scenarios between keyset seeker and offset operations?

| username: xiaoqiao | Original post link

Keyset pagination is suitable for pagination interfaces where users navigate forward and backward but does not support random access, allowing users to jump to any specific page.
Offset pagination is a common method of implementing pagination in a database where

| username: DBAER | Original post link

Main web pagination

| username: stephanie | Original post link

Keyset seeker should be used in cases where deep pagination might occur.

| username: TiDBer_JUi6UvZm | Original post link

May I ask if keyset seeker is a concept at the TIKV level? Or is it also a concept at the SQL syntax level? OFFSET should be at the syntax level, right?

| username: dba远航 | Original post link

Multi-page display on the web

| username: 洪七表哥 | Original post link

KeySet Seeker can improve pagination performance. Conventional pagination update SQL generally uses the primary key or unique index for sorting, combined with the offset in the LIMIT syntax, to split pages by a fixed number of rows. Then, the pages are wrapped into independent transactions to achieve flexible pagination updates. However, the disadvantages are also obvious: since sorting by the primary key or unique index is required, the further back the page, the more rows will be involved in the sorting. This can be particularly resource-intensive when dealing with large volumes of data in batch processing.

| username: TiDBer_RjzUpGDL | Original post link

Keyset seeker is suitable for sequential forward and backward paging, while offset is suitable for directly jumping to a specified page number.

| username: Jack-li | Original post link

Keyset Seeker implements stable pagination by referencing a set of key values; Offset Operation performs simple pagination based on row number/offset.

| username: wangkk2024 | Original post link