Efficient pagination for tables with complex primary keys

Let’s assume a table with PRIMARY KEY(a, b DESC, c) (or alternatively the same with a regular index).

I’d like to paginate the table (or read the values in the index’s order), e.g. 1000 rows each, but OFFSET N is expensive after a while. Instead, I’d like to use WHERE a > @last_a ORDER BY a, except the full WHERE a > @a OR (a = @a AND b < @b) OR (a = @a AND b = @b AND c > @c) ORDER BY a, b DESC, c.

Is there a better way to express the same pagination expression with a single condition that handles the order and the implied cursor’s expression properly?

1 Like