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

61–70 of 150 posts

Re: API pagination design

#61

Post by growse »

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…

> So your customer, the person that paying you for your product, needs to wait for some number of synchronous reads.

The customer is not always right.

It's also not clear what the practical issue with sequential requests are? Multiple parallel requests may get caught in a rate limiter, and impose much more work on the backend than a cursor (multiple unnecessarysort/discards). It's not a given that spamming a service gets you all the data any faster than using a cursor.

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

If the typical request requires 4 pages, then your page size is suboptimal.

Re: API pagination design

#62

Post by Too »

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.

Depends how long the cursor is alive. Url containing ?date=lt:2012-12-27 is stateless, user readable and more likely to outlive ?cursorId=xyaxjeasu&page=2.

Now it might not be as accurate as having a cursor but it is a lot easier to implement, and still beats simply ?page=2 without cursor, which is the level you see in most apis today.

Re: API pagination design

#63

Post by lenkite »

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 ?

Re: API pagination design

#65

Post by lenkite »

e12e wrote: post #54
ajitid wrote: post #44

Earlier quoted context omitted.

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

These cursors are not database cursors. I too was confused by the bad terminology...

Re: API pagination design

#66

Post by swyx »

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?

Re: API pagination design

#67

Post by djeiasbsbo »

altdatathrow wrote: post #51
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...

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

Re: API pagination design

#68

Post by chrismorgan »

lenkite wrote: post #60
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 ?

No: it becomes just an added `WHERE id >= anchor` constraint, which with the usual indexes is cheap.

Re: API pagination design

#69

Post by vincnetas »

I think author misses crucial point while using OFFSET. At least postgres doc has this to say (https://www.postgresql.org/docs/13/queries-limit.html)

  When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.
And looks like pagination queries provided by author do not take sorting in to account, which means that items on page 100 will be with id greater than 10000, but in undefined order.

  explain analyze select id from product where id > 10000 limit 100

Re: API pagination design

#70

Post by e12e »

lenkite wrote: post #65
e12e wrote: post #54

Earlier quoted context omitted.

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

These cursors are not database cursors. I too was confused by the bad terminology...

Ah, I assumed from the topic, term and discussion that this would be implemented via sql cursors. Apparently elastic search has a similar concept - but like sql cursors it cannot be stateless (because you cannot ask for Nth to Mth result of searching the collection as of state Z -if you don't want to capture/supply state Z).

This means any stateless pagination api is fundamentally broken. Please don't make promises you cannot keep.

For elastic: https://www.elastic.co/guide/en/elasticsearch/reference/curr...

Post reply on HN