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

51–60 of 150 posts

Re: API pagination design

#51

Post by altdatathrow »

dmitryminkovsky wrote: post #20

The Use The Index Luke material about this is essential background reading, but in terms of actually implementing: My favorite SQL implementation of cursor-based keyset pagination can be found on this Hasura issue: https://github.com/hasura/graphql-engine/issues/141 , specifically this comment: https://github.com/hasura/graphql-engine/issues/141#issuecom... . Something so elegant about this SQL. I enjoy it a lot. Als…

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

Re: API pagination design

#52

Post by Too »

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…

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 results and will narrow down the search instead. When was the last time you looked beyond even the first 5 results on google yourself, i'd rather adjust the search term than go to page 2.

Re: API pagination design

#53

Post by ralusek »

Too wrote: post #52
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…

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…

If your ability to query can manage sorting on two fields, this can be resolved by sorting on the field first, then the primary key as a secondary field.

If I'm sorting by last name, and I sort by last name, then id, I can save the cursor as last name + id, and be able to continue right where I left off.

Re: API pagination design

#54

Post by e12e »

ajitid wrote: post #44
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…

I have question, lets say we have page 1 elements: A B C D E page 2 elements: F G H I J so 5 elements in each page. Suppose I'm on page 2. If I insert a new element Q and it gets pushed as first then page 1 will have Q A B C D. Now if I go back to page 1, I'll get A B C D E and also a token/pointer to go back one more time only to retrieve Q. So while cursor solved the issues you mentioned, it still will have this ca…

A cursor is either valid in a transaction, or will cache its results - either way it will give a consistent view of data:

https://www.postgresql.org/docs/13/sql-declare.html

Re: API pagination design

#55

Post by rualca »

Too wrote: post #52
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…

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…

> One way to get around this is to drop the notion of page number. Instead do greater than last element on last page.

That's the whole point of a cursor, isn't it?

And pagination just means querying the records to return a limited subset.

Re: API pagination design

#57

Post by dub »

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…

If the API takes a cursor and a number of items to fetch, the idea of a "page" or how large it is exists entirely on the client. You can fetch 40 results in one query and say it corresponds to the next four "pages", if you're configured to show ten items per page

It seems worth noting a high number of concurrent queries to the same database shard as part of the same overall page load can be very wasteful of CPU as database load increase, due to the cost of context switching. https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-... dives into that.

Re: API pagination design

#58

Post by [deleted] »

rualca wrote: post #55
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…

> One way to get around this is to drop the notion of page number. Instead do greater than last element on last page. That's the whole point of a cursor, isn't it? And pagination just means querying the records to return a limited subset.

[deleted]

Re: API pagination design

#60

Post by lenkite »

chrismorgan wrote: post #24

Sometimes you want a cursor so you can resume where you left off without worrying about new entries that may have come in which would otherwise mess up your pagination. Sometimes you want position-based queries, because you explicitly do want everything to be positional. Sometimes you want to combine the two techniques, e.g. if you jump to the middle of a large, ever-changing list, and then want to retrieve the next…

This approach still requires a full table scan right ?
Post reply on HN