Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

API pagination design

solovyov.net

Post reply on HN
Print view View original

71–80 of 150 posts

Re: API pagination design

#71

Post by a_c »

swyx wrote: post #66
momokoko wrote: post #38

This 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 tim…

non backend dev here. could someone elaborate what exactly is so opaque about pagination? and what makes offsets less opaque? does this term have meaning I don't know about? and why does the DB need to wait for some number of synchronous reads?

"offset" can be passed transparently to db to retrieve a range of records while "cursor" is customarily implemented in the API layer. I think that's what "opaque" meant in GP's context.

A starting read is needed to obtain the first cursor, hence the synchronous read.

Re: API pagination design

#72

Post by codesnik »

swyx wrote: post #66
momokoko wrote: post #38

This 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 tim…

non backend dev here. could someone elaborate what exactly is so opaque about pagination? and what makes offsets less opaque? does this term have meaning I don't know about? and why does the DB need to wait for some number of synchronous reads?

if you ask for next page of results, with parameters like offset=20&limit=10, then you, as a client, can try to reason and manipulate those parameters. Ask for multiple pages in parallel, ask for offset=18 etc. Make calculations on those parameters. If you only providing a token, like "next_page=abcdef1234" with some encoded structure, you're limiting your client in what it can actually do, but simultaneously simplify backend architecture and make it more forward compatible with future changes in backend (after all, that next_page token can actually be just an offset and limit encoded)

Re: API pagination design

#73

Post by rossmohax »

djeiasbsbo wrote: post #67
altdatathrow wrote: post #51

Earlier quoted context omitted.

fyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]: > For row comparisons, (a, b) > (x, y) is equivalent to: > (a > x) OR ((a = x) AND (b > y)) [1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...

Just in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like: SELECT * EXCEPT FOO_ID FROM FOO; I have always wanted this but have never vome across it...

Maybe

WHERE NOT EXIST (SELECT ...)

can help?

Re: API pagination design

#74

Post by swyx »

codesnik wrote: post #72
swyx wrote: post #66

Earlier quoted context omitted.

non backend dev here. could someone elaborate what exactly is so opaque about pagination? and what makes offsets less opaque? does this term have meaning I don't know about? and why does the DB need to wait for some number of synchronous reads?

if you ask for next page of results, with parameters like offset=20&limit=10, then you, as a client, can try to reason and manipulate those parameters. Ask for multiple pages in parallel, ask for offset=18 etc. Make calculations on those parameters. If you only providing a token, like "next_page=abcdef1234" with some encoded structure, you're limiting your client in what it can actually do, but simultaneously simplif…

ah i see so some kind of tradeoff between power to the API consumer vs power to the API maintainer. thank you!

i don't see anyone arguing for "next_page=abcdef1234". realistically it's more like "cursor=abcdef1234&limit=10". slightly less opaque. still your point about asking for multiple pages in parallel still stands.

i think i agree with mostly everyone here in that this is a fine tradeoff to make and so would favor cursors over offsets. (unclear how cursors relate to "keysets")

Re: API pagination design

#75

Post by swyx »

a_c wrote: post #71
swyx wrote: post #66

Earlier quoted context omitted.

non backend dev here. could someone elaborate what exactly is so opaque about pagination? and what makes offsets less opaque? does this term have meaning I don't know about? and why does the DB need to wait for some number of synchronous reads?

"offset" can be passed transparently to db to retrieve a range of records while "cursor" is customarily implemented in the API layer. I think that's what "opaque" meant in GP's context. A starting read is needed to obtain the first cursor, hence the synchronous read.

gotcha, thank you!

Re: API pagination design

#76

Post by boudin »

rossmohax wrote: post #73
djeiasbsbo wrote: post #67

Earlier quoted context omitted.

Just in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like: SELECT * EXCEPT FOO_ID FROM FOO; I have always wanted this but have never vome across it...

Maybe WHERE NOT EXIST (SELECT ...) can help?

I think the question was about excluding columns, not rows.

I don't know any way to do that personally.

Re: API pagination design

#77

Post by rossmohax »

lenkite wrote: post #63
eatonphil wrote: post #9

Keyset pagination is described in a few places online in detail. The one that comes to mind is in Use the Index, Luke [0]. [0] https://use-the-index-luke.com/no-offset

One cannot go to an arbitrary page with this method - doesn't it destroy use-ability quite a bit ?

token based pagination is how infinity scrolling pages are implemented. It definitely has usability problems, but beyond a certain scale offset based pagination is not viable from a performance point of view, especially if users are given option to chose sorting order.

Re: API pagination design

#78

Post by alexchamberlain »

ucarion wrote: post #23

From an API evolution standpoint, cursors are a no-brainer over explicit offsets: 1. Nothing stops you from having your cursors be implemented via a SQL offset under the hood. 2. You can have your cursors be base64 data, and embed a cursor version in the ID. You can use this to change how your pagination works without breaking clients during the transition period. (You'll need to document+enforce a maximum validity f…

We've even used cursors to migrate implementations; in our case, we were migrating from solr5 to solr7. When you started a session, you were allocated to solr5 or solr7 according to the business rules in effect; the solr7 cursors were prefixed with solr7-, so that in subsequent requests we could send you to the same major version. It could work the same way with load balancing too I guess.
Post reply on HN