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

131–140 of 150 posts

Re: API pagination design

#131

Post by atombender »

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...

Postgres has a clever trick [1] you can use, though I wouldn't say it's something I would ever use.

[1] https://blog.jooq.org/2018/05/14/selecting-all-columns-excep...

Re: API pagination design

#132

Post by boulos »

graderjs wrote: post #49
boulos wrote: post #34

Given 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…

Not contradicting, just adding that GCP (Datastore) has had cursors for at least 8 years. When I came across that using App Engine many years ago I really liked the cursor thing, and it stuck with me. I found it a little funny this article sounded a bit like "Here's this new thing"

Oh, but that’s an “API” for a Database. So that’s a fine exception to “you should offer cursors”, since that’s what you actually intend to present. A more generic API that happens to be backed by a database with a list of things in it, should not reflect the underlying database decisions if it can help it.

Re: API pagination design

#133

Post by boulos »

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…

I think supporting an explicit (large) page size addresses your concern. It’s not that you want to make four separate requests, it’s that you want the backend to give you more than a tiny amount of data. If you’re going to induce you the 400 results of loading on the backend, backend implementators may as well give you all the results in one go (assuming the output fits in whatever request/response limits you have).

I definitely agree that just having opaque page tokens without the ability to say “I want up to 100” leads to needless pain for clients and overall system inefficiency.

Re: API pagination design

#134

Post by callesgg »

hellcow wrote: post #124
callesgg wrote: post #123

I think is better to not use pagination at all. Design your api or page so that you receive all or more entries than you could possibly need. If the user wants them all pagination is just in the way. If the user does not want them all give them as many as they are likely to be able to handle.

"Woah, our database is slowing to a crawl, requests are deadlocked everywhere. Yeah, some asshole is hitting GET /messages and pulling 2TB in a single request again." "Surely that's what he wanted."

Given that is what the person is doing, that is likly what they are trying to do. Most of the times when I do things, I do it cause I want to do it, not cause I did an opsie.

Re: API pagination design

#135

Post by knorker »

anamexis wrote: post #120
knorker wrote: post #83

Earlier quoted context omitted.

A B C D 2 items per page first page is A&B. Next query is "WHERE ID>'B'". What's the problem?

Aa and Ab are inserted after loading the first page.

That's an orthogonal problem that you need to define correctness for your application.

Because LIMIT...OFFSET will also arguably give you the wrong result. E.g. Aa is inserted. The user will see "B" both as the last item on the first page, and the first item on the second page.

Or with your example:

Aa Ab B C

Now "A" is inserted between page loads. The user will never see "A". What's right for your application? Maybe a notification saying "previous pages have gotten new items". Maybe not. It all depends.

Reddit can be annoying if you go page after page. As stories are bumped down you see them again. That, in my opinion, is a bug. But solving it requires something completely different from merely defining page boundaries.

Re: API pagination design

#136

Post by profquail »

et1337 wrote: post #8

Another 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…

Another approach to solving this issue is with a temporal database (standardized in SQL:2011); supporting a “valid time” in your query gives your user a consistent view of the database tables even if others are modifying it concurrently. It’s also handy if you want to keep audit records of the database (e.g. so users can see who/when/what changes were made).

https://en.wikipedia.org/wiki/SQL:2011

Re: API pagination design

#137

Post by graderjs »

boulos wrote: post #132
graderjs wrote: post #49

Earlier quoted context omitted.

Not contradicting, just adding that GCP (Datastore) has had cursors for at least 8 years. When I came across that using App Engine many years ago I really liked the cursor thing, and it stuck with me. I found it a little funny this article sounded a bit like "Here's this new thing"

Oh, but that’s an “API” for a Database. So that’s a fine exception to “you should offer cursors”, since that’s what you actually intend to present. A more generic API that happens to be backed by a database with a list of things in it, should not reflect the underlying database decisions if it can help it.

It seems to me you might have meant to reply to another comment.

This is not an "exception to 'you should offer cursors'" this is offering cursors.

Re: API pagination design

#138

Post by jiji33i »

atom_arranger wrote: post #27

I'm a fan of the "GraphQL Cursor Connections Specification", which could be applied even if not using GraphQL: https://relay.dev/graphql/connections.htm

"If all you have is a hammer, everything looks like a nail."

Are GraphQL-fans so uncapable you have to put your GraphQL-spamming in every single post? Didn't you learn anything else in your life?

Re: API pagination design

#139

Post by ivoecpereira »

I have written about this as well[1] and even built a Github PoC about it comparing both approaches regarding speed[2] but one of the main flaws is the lack of a deeper consideration for sorting and filtering examples and impact demonstration. Will look into adding more detail about that in an addendum.

[1] https://medium.com/swlh/why-you-shouldnt-use-offset-and-limi... [2] https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC

Re: API pagination design

#140

Post by rualca »

londons_explore wrote: post #81
Too wrote: post #52

Earlier quoted context omitted.

One way to get around this is to drop the notion of page number. Instead do greater than last element on last page. But this has the drawback of only working as long as the sorting field doesn't have many duplicates. You have to beware if timestamps are high resolution enough for your application or if you have 100+ entries with same family name. Usually users are smart(lazy) enough to realize they got more than 100…

What if the last element on a page is deleted when you click "next"?

> What if the last element on a page is deleted when you click "next"?

That makes absolutely no difference. You're still querying the database for elements that come after a value from a data type for which there is an order. You don't need that element to exist to run a comparison. For example, consider a timestamp-based query: you don't need an element with that specific date to exist to search for any element whose timestamp was taken after an arbitrary moment.

Post reply on HN