Moving forward and back in a cursor (pages) is easy... but how about random pages?
i.e. this forum, HN. If there were ten pages of comments, going from page 1 to page 2, or vice versa is trivial with a "after" or "before" query. But what about jumping directly to page 3 without previously visiting pages 2 or 4?
So far I've implemented a hybrid... pages still exist, but next and previous page (the most common actions) are cursor.
If you use ULIDs for IDs (sortable by time, millisecond resolution) and a tombstone field (nullable deleted_at is a good one) then you have a very stable collection that guarantees no new objects will be inserted/deleted before the write head - it is append only in effect.
You can then do some cool things, especially if your objects are evenly distributed and especially if you don't need exact page sizes or can over-query and hide them as needed.
If you then know the approximate frequency of objects, you can then map some linear scaling to an approximate range of ULIDs. Basically German tank problem in reverse.
IMO if a solution requires users to address data by page, it's a sign that the search functionality is not good enough.
In general a user doesn't want to find "page 3", they are looking for a record that has certain characteristics. They shouldn't need to think about pages, just search terms.
i.e. this forum, HN. If there were ten pages of comments, going from page 1 to page 2, or vice versa is trivial with a "after" or "before" query. But what about jumping directly to page 3 without previously visiting pages 2 or 4?
So far I've implemented a hybrid... pages still exist, but next and previous page (the most common actions) are cursor.
Is there a better way?