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

11–20 of 150 posts

Re: API pagination design

#11

Post by yencabulator »

ccleve wrote: post #10

Cursors are stateful. This greatly complicates the design of the backend. First, you have to maintain state in some kind of session on the app server. Second, if you have more than one app server, you'll have to share the sessions across them using Redis or Hazelcast or something. Third, cursors have to be closed, which means you have to know when the user is done with the result set. This is impossible to know. The…

Not that kind of cursor. There is no state on the server / in the database and nothing to close.

> Generally, you have some ordering criteria, for example, product id. In this case, you’ll encode your product id with some reversible algorithm (let’s say hashids). And on receiving a request with the cursor you decode it and generate a query like `WHERE id > :cursor LIMIT 100`.

Re: API pagination design

#12

Post by [deleted] »

ccleve wrote: post #10

Cursors are stateful. This greatly complicates the design of the backend. First, you have to maintain state in some kind of session on the app server. Second, if you have more than one app server, you'll have to share the sessions across them using Redis or Hazelcast or something. Third, cursors have to be closed, which means you have to know when the user is done with the result set. This is impossible to know. The…

[deleted]

Re: API pagination design

#13

Post by vbezhenar »

hirundo wrote: post #7

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

You sort by your fields and then by id. You also include those field values along with id into your “cursor”. This is complicated for general case but possible to implement.

Re: API pagination design

#14

Post by vbezhenar »

dudul wrote: post #5

> One deficiency you can see is that it’s impossible to generate a “previous page” link It is perfectly possible to have a "previous page" link. Your pagination needs to support "before" cursor semantic, allowing a consumer to retrieve the N items before the row identified by the cursor.

Yep, just inverse your conditions and sorting and you’ve got previous page.

Re: API pagination design

#15

Post by dudul »

ccleve wrote: post #10

Cursors are stateful. This greatly complicates the design of the backend. First, you have to maintain state in some kind of session on the app server. Second, if you have more than one app server, you'll have to share the sessions across them using Redis or Hazelcast or something. Third, cursors have to be closed, which means you have to know when the user is done with the result set. This is impossible to know. The…

This is not about database cursors.

Re: API pagination design

#16

Post by crb002 »

ccleve wrote: post #10

Cursors are stateful. This greatly complicates the design of the backend. First, you have to maintain state in some kind of session on the app server. Second, if you have more than one app server, you'll have to share the sessions across them using Redis or Hazelcast or something. Third, cursors have to be closed, which means you have to know when the user is done with the result set. This is impossible to know. The…

You could enable a batch hint in the API. The server has the option to allocate a cursor if one is available. Best of both worlds.

Re: API pagination design

#17

Post by dmitryminkovsky »

amelius wrote: post #6

How long will the cursor be valid?

Indefinitely. It's not the kind of cursor that a database gives out, which is a resource. A cursor in this context is just a pointer to some position in a set of results, something like "all results where date created is greater than $X". For SQL implementations, check out:

https://github.com/hasura/graphql-engine/issues/141#issuecom...

or

https://stackoverflow.com/questions/38017054/mysql-cursor-ba...

Re: API pagination design

#18

Post by dudul »

hirundo wrote: post #7

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

All the columns you order by should be part of your cursor value, and the id can be used to tie break non unique columns.

Re: API pagination design

#19

Post by ccleve »

yencabulator wrote: post #11
ccleve wrote: post #10

Cursors are stateful. This greatly complicates the design of the backend. First, you have to maintain state in some kind of session on the app server. Second, if you have more than one app server, you'll have to share the sessions across them using Redis or Hazelcast or something. Third, cursors have to be closed, which means you have to know when the user is done with the result set. This is impossible to know. The…

Not that kind of cursor. There is no state on the server / in the database and nothing to close. > Generally, you have some ordering criteria, for example, product id. In this case, you’ll encode your product id with some reversible algorithm (let’s say hashids). And on receiving a request with the cursor you decode it and generate a query like `WHERE id > :cursor LIMIT 100`.

Oops -- this is embarrassing -- I did not read the article closely. You're right, the article does not recommend using a normal database cursor. Never mind.

Re: API pagination design

#20

Post by dmitryminkovsky »

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.

Also really enjoy this answer about how to implement keyset pagination on multiple columns:

https://stackoverflow.com/questions/38017054/mysql-cursor-ba...

Post reply on HN