Several recommendations on this thread have led me to buy the SQL Performance Explained book.
[deleted]
API pagination design
31–40 of 150 posts
Re: API pagination design
#32I'm pretty sure pagination APIs are the result of people who haven't ever read Stevens writing services.
Re: API pagination design
#33We have explicit cursors and classic pagination in our public facing API. Classic paging is such a thorn in our side, but almost everyone uses it for their v1 unless they ask us for guidance first. We have guides about why pagination is bad, and discourage it in our API rate limits, but it still persists.
Looking back, I would exclude classic pagination from a public API and go full cursor. Possibly include an optional total count that can be requested (counts are expensive in PG though).
Re: API pagination design
#34Given the confusion over the reuse of the term “cursor”, I prefer the terms we (Google) have settled on for Pagination [1]: page token and page size. It’s often awkward to argue about “really, do I need to support pagination from the outset”, but the examples given in that AIP are from real-world battle scars. Even if you just set the page size to a big number, at least you prepare your callers for “one day, you’ll need to handle this”.
Re: API pagination design
#35I've worked with APIs (mainly based on ElasticSearch backends) that implement something like this pattern. There, the "cursor" is called a "ScrollId." Here's what that looks like in ES: https://www.elastic.co/guide/en/elasticsearch/client/java-re... The APIs I generally work will abstract away most of the scroll context configuration stuff, and just return a page of results, plus the ScrollId for the next page.
Elastic search now recommends using “search_after” instead of scrolling for deep pagination, although mechanically they’re pretty similar to use
Elasticsearch’s search_after is a pleasure to use. So easy especially with nullable fields, where the equivalent SQL can get really, really messy with just a few columns.
Re: API pagination design
#36I tried to make cursor based pagination work in a GraphQL API over MySQL and failed. The problem was that it had to work for a very wide range of SQL statements, with arbitrary order clause, where clause and at least one join. Offset pagination works fine in this scenario. But something like `where id > 42 limit 100` fails with an arbitrary sorting order on non-unique columns. All I could do would be to generate the…
I implemented this for Datasette. The key is to use the primary key as a "tie breaker" - so you sort by the specified column first and then by the primary key.
I managed to get this working with compound primary keys too: click "next" at the bottom of this page for a demo: https://latest.datasette.io/fixtures/compound_three_primary_...
The code is pretty complicated - mostly here: https://github.com/simonw/datasette/blob/0.53/datasette/view...
Re: API pagination design
#37Another reason to use cursors is to avoid the problem of repeated or skipped elements caused by concurrent edits. If you use offsets and you’re on page 10, and someone deletes an item on page 1, the whole list shifts and you can accidentally skip an item on page 11. Likewise if someone adds an item on page 1 and you’re on page 10, one of the page 10 items will also show up on page 11. Cursors elegantly sidestep these…
Though you can't avoid seeing an item just before it is deleted and skipping an item just before it is created. This isn't a huge problem but does mean that the list you end up with may not be a coherent view of the database. But if you require guarantees like that then you might be better of just using SQL directly. Or just include everything in a single response, HTTPS was designed to handle arbitrary length payloa…
If you care about that kind of guarantee you might want to keep a connection open and stream new updates.
Re: API pagination design
#38This is trumpeted around and actually put into production every once in a while.
The reason opaque pagination is an antipattern is because you can’t optimistically fetch resources.
So your customer, the person that paying you for your product, needs to wait for some number of synchronous reads.
With non-opaque offsets these can be done in parallel. If the typical request requires 4 pages, these can be done 4 at a time and of it is less than 4 pages those can be discarded.
This is a clever hack that ends up being user hostile in actual practice. Remember APIs are designed for the benefit of the consumer vs the benefit of the maintainers.
Re: API pagination design
#39Given the confusion over the reuse of the term “cursor”, I prefer the terms we (Google) have settled on for Pagination [1]: page token and page size. It’s often awkward to argue about “really, do I need to support pagination from the outset”, but the examples given in that AIP are from real-world battle scars. Even if you just set the page size to a big number, at least you prepare your callers for “one day, you’ll n…
[deleted]
Re: API pagination design
#40Is an auto increment sequential number good enough for a 'cursor' or is there something I'm missing here.