How to use keyset pagination with ordering if the key is a string

Question

Given a table that looks like the following:

+---------------+----------+
|title          |enabled   |
+---------------+----------+
|Four           |N         |
|Two            |N         |
|Three          |N         |
|One            |Y         |
|Five           |Y         |
|Six            |Y         |
+---------------+----------+

Using keyset pagination and sorting by enabled, how would I would I be able use the title as the key?

For example, using

select title, enabled
from my_table
where title > 'Three'
order by enabled;

returns

+---------------+----------+
|title.         |enabled   |
+---------------+----------+
|Two            |N         |
+---------------+----------+

but this removes all rows that have a title that is after Three alphabetically, but I would like for it to look like the original result and sreturn the last 3 rows instead.

Answer 1

In Keyset Pagination, the WHERE > and ORDER BY must be in the same columns, otherwise you are not actually paginating properly.

select
  mt.title,
  mt.enabled
from my_table
where mt.title > 'Three'
order by
  mt.title
limit 100;

You must have an index on (title) INCLUDE (enabled) or similar, otherwise performance will suffer.

Or you can sort by enabled, title but then you need a filter on both as well, and an index (enabled, title).

select
  mt.title,
  mt.enabled
from my_table
where (mt.enabled, mt.title) > (false, 'Three')
order by
  mt.enabled,
  mt.title
limit 100;