News for hackers. Stuff that matters.
Re:Paginating Requests in APIs (2020) by terandle on Saturday May 28, 2022 @12:53PM (#31541822)
Wish this article would have gone into details about how cursor based pagination is actually implemented. Like they did for the other options by showing SQL. Is this something the database engine has to provide support for?
Re:Paginating Requests in APIs (2020) by gabetax on Saturday May 28, 2022 @01:07PM (#31541935)
Check https://use-the-index-luke.com/no-offset as a better reference.

But in most SQL databases, cursors are something you implement and parse at the application layer, and translate to a WHERE clause on the (hopefully indexed) column you're ordering on. That turns the O(N) "OFFSET" into a O(log(n)) index seek.

Re:Paginating Requests in APIs (2020) by lazide on Saturday May 28, 2022 @05:29PM (#31544047)
Many databases have actual cursor implementations which are transactional. That means you’ll get a consistent view at the point in time you created the cursor.

That said, they tend to live only as long as the db connection (with some exceptions), so yeah you need some application work to make it sane.

Re:Paginating Requests in APIs (2020) by nafey on Saturday May 28, 2022 @02:16PM (#31542470)
How is a cursor different from key set in that case?
Re:Paginating Requests in APIs (2020) by cdash on Saturday May 28, 2022 @02:58PM (#31542749)
It's not in that case, cursors can be implemented however you want though. Its an abstraction, that prevents you from having to change the schema of the API when you change the implementation.
Re:Paginating Requests in APIs (2020) by andrewmackrodt on Saturday May 28, 2022 @01:28PM (#31542094)
You can do something like the following in application code.

Imagine a table with the following schema/data: id=1,created_at='2022-28-05T18:00Z' id=2,created_at='2022-28-05T18:00Z' id=3,created_at='2022-28-05T18:00Z'

To retrieve articles in order of descending creation time, our sort order would be: created_at DESC, id DESC

The last item in the ORDER BY query should be a unique key to ensure we have consistent ordering across multiple queries. We must also ensure all supported sort columns have an INDEX for performance reasons.

Assume our application returns one article at a time, we have already retrieved the first article in the result set. Our cursor must include information for that last records ORDER BY values, e.g. serialize('2022-28-05T18:00Z,3'), for example purposes I will use base64, so our cursor is MjAyMi0yOC0wNVQxODowMFosMw==.

When the user requests the next set of results, they will supply the cursor and it will be used to construct a WHERE (AND) expression: created_at < '2022-28-05T18:00Z' OR (created_at = '2022-28-05T18:00Z' AND id < 3)

So our query for the next set of results would be: SELECT * FROM articles WHERE (created_at < '2022-28-05T18:00Z' OR (created_at = '2022-28-05T18:00Z' AND id < 3) ORDER BY created_at DESC, id DESC LIMIT 1

For queries with more than 2 sort columns the WHERE expression will slightly increase in complexity but it need only be implemented once. If the sort order direction is flipped, make sure to adjust the comparator appropriately, e.g. for 'DESC' use '<' and for 'ASC' use '>'.

Re:Paginating Requests in APIs (2020) by delusional on Saturday May 28, 2022 @02:31PM (#31542575)
Isn't that basically just a trivial extension of what they article calls "keyset based pagination"?

I'm not complaining since this is also what I usually do, I'm just wondering of theres more to it.

Re:Paginating Requests in APIs (2020) by cortesoft on Saturday May 28, 2022 @02:54PM (#31542725)
Oftentimes under the hood, cursor based paging IS keyset paging. The difference is that the keyset is obfuscated, which can allow the implementation to change without changing the API call.
Re:Paginating Requests in APIs (2020) by catlifeonmars on Saturday May 28, 2022 @02:12PM (#31542444)
Makes total sense. It also seems trivially automatable on the surface. Echoing GP: Is this an existing DBMS feature? If not, why not?
Re:Paginating Requests in APIs (2020) by TravelPiglet on Saturday May 28, 2022 @02:47PM (#31542676)
How do you handle removal?
Re:Paginating Requests in APIs (2020) by andrewmackrodt on Saturday May 28, 2022 @03:09PM (#31542849)
I haven't had a need to implement this but something like the following should work to provide the same* records regardless of insertion/deletion.

* data of the record may have been updated, but if there are 1,000 records total, paginating in either direction will always return those same 1,000 records regardless of insertion/deletion.

- Filter by created_at <= time the endpoint first returned a result.

- Utilize soft deletes, where deleted_at IS NULL OR deleted_at < time the endpoint first returned a result.

Any keys allowed to be used in the ORDER BY query should also be immutable.

Re:Paginating Requests in APIs (2020) by sgtfrankieboy on Saturday May 28, 2022 @01:09PM (#31541950)
Some database engines will support Cursors like this, but from what I can gather Cursor based navigation is a wrapper around KeySet or page navigation, but base64 encoded parameters.

Making the implementation details of pagination unimportant to the API layer the user uses.

Re:Paginating Requests in APIs (2020) by qsort on Saturday May 28, 2022 @01:02PM (#31541904)
No need for anything special, you would just query with something like "SELECT whatever FROM Table WHERE cursor>value ORDER BY cursor LIMIT n". Obviously there needs to be an index over the columns of the cursor, but any reasonable engine should allow you to add that effortlessly.
Re:Paginating Requests in APIs (2020) by mbStavola on Saturday May 28, 2022 @01:06PM (#31541927)
The complication comes from having a cursor over results that are sorted on multiple columns. It's not incredibly difficult to implement, but it can certainly be annoying to do, especially if you want to support before+after cursor as well.