I am implementing a cursor based pagination.

The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the last page).

The first page is simple:

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

Assuming this query returns IDs 4, 5, 2, 1, 5, then the next query must produce 5 next results from the same query after id 5.

Note: We cannot use LIMIT ... OFFSET after the first page because the offset is relative to the position of the referenced result that we do not know.

The only way I can think of solving this problem is:

Find row number in the dataset for ID 5:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 5;

Then use position to offset the dataset.

All together:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  ) 
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 5
)
LIMIT 10;

The downside of this approach is that data_index needs to load the entire sub-query into memory. Is there a more efficient way?

I am implementing a cursor based pagination.

The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the last page).

The first page is simple:

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

Assuming this query returns IDs 4, 5, 2, 1, 3, then the next query must produce 5 next results from the same query after id 3.

Note: We cannot use LIMIT ... OFFSET after the first page because the offset is relative to the position of the referenced result that we do not know.

The only way I can think of solving this problem is:

Find row number in the dataset for ID 3:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 3;

Then use position to offset the dataset.

All together:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  ) 
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 3
)
LIMIT 10;

The downside of this approach is that data_index needs to load the entire sub-query into memory. Is there a more efficient way?