4 ms·

> Pagination is the devil. The state of the server can change while the user is paginating, leading to fragile clients. Don't paginate your API. The problem is

> Pagination is the devil. The state of the server can change while the user is paginating, leading to fragile clients. Don't paginate your API. The problem is not pagination, and the solution is not to avoid pagination. The problem is offset-based pagination, and the solution is to use cursor-based pagination. > If you think you have a need to paginate, have one API call return a list of IDs, and have a separate API call return a list of resources for a given list of IDs, where the second API call accepts some maximum number of IDs as a query parameter. This has the same problem as you described above. The state of the server can change between fetching the list of IDs and operating on them.

solatic 5y ago
Cursor-based pagination doesn't solve your state issue, it forces the server to create a copy of state for the cursor request. This is complex to implement correctly - for example, if the user does not actually paginate through all the entries, when do you dump the unused cursor? If the user issues the same request over and over, do you return a cached cursor or re-copy the state into a new cursor? If you re-copy the state, are you defended from a malicious actor who sends 1,000 new requests? Of course, all these concerns can be mitigated, but it's easier to just design without pagination in the first place if you can. > The state of the server can change between fetching the list of IDs and operating on them. Right, for example, a returned ID may have been deleted before the user can issue a query for that resource. But this is usually far more comprehensible to clients, particularly if the resource requires authorization such that only the client is permitted to delete that resource.
- JimDabell 5y ago
  Cursor-based pagination doesn’t require anything at all like you describe. Are you sure you aren’t mistaking it for something else?
- sigmaml 5y ago
  You appear to be referring to a database cursor. It is quite simple to implement pagination in the application layer using a unique record identifier (primary key or ULID or ...) as an anchor for the navigation. From that unique ID, we can then fetch the previous `n` or the next `n` records, depending on the direction of the navigation. This way, the server remains stateless, since the anchor (possibly sent as an encoded / obfuscated token, which can include some other parameters such as page size) is supplied by the client with each pagination request. Unless I am missing something in your argument.
  - borrow 5y ago
    What if that particular unique ID is deleted right before the client requests the next page?
    - pronik 5y ago
      If the IDs are monotonous, it doesn't matter.
      - rattray 5y ago
        (for posterity, this appears to have been a typo for monotonic)
    - JimDabell 5y ago
      Pagination only makes sense in the context of an ordered collection; if there is no stable sort order then you can’t paginate. So you identify the last record seen with whatever fields you are ordering by, and if the last record has been deleted, then it doesn’t matter because you are only fetching the items greater than those values according to the sort order. Anyway, there is plenty of documentation out there for cursor-based pagination; Hacker News comments isn’t the right place to explain the implementation details.
    - sigmaml 5y ago
      1. This kind of pagination can be done for any key as long as its data type admits total ordering. 2. The WHERE condition typically uses `>` or `<`, so it doesn't fail even when that record is deleted before the next client request referring to it.
akvadrako 5y ago
You should use stable page boundaries instead of cursors. If you are returning results by timestamps, the page boundary should be the timestamp and secondary ordering keys of the last row returned. Cursors take too many server resources and require client affinity.
- JimDabell 5y ago
  You’re describing cursor-based pagination. It’s got nothing to do with the SQL concept of cursors.

solatic 5y ago

Cursor-based pagination doesn't solve your state issue, it forces the server to create a copy of state for the cursor request. This is complex to implement correctly - for example, if the user does not actually paginate through all the entries, when do you dump the unused cursor? If the user issues the same request over and over, do you return a cached cursor or re-copy the state into a new cursor? If you re-copy the state, are you defended from a malicious actor who sends 1,000 new requests? Of course, all these concerns can be mitigated, but it's easier to just design without pagination in the first place if you can. > The state of the server can change between fetching the list of IDs and operating on them. Right, for example, a returned ID may have been deleted before the user can issue a query for that resource. But this is usually far more comprehensible to clients, particularly if the resource requires authorization such that only the client is permitted to delete that resource.

JimDabell 5y ago
Cursor-based pagination doesn’t require anything at all like you describe. Are you sure you aren’t mistaking it for something else?
sigmaml 5y ago
You appear to be referring to a database cursor. It is quite simple to implement pagination in the application layer using a unique record identifier (primary key or ULID or ...) as an anchor for the navigation. From that unique ID, we can then fetch the previous `n` or the next `n` records, depending on the direction of the navigation. This way, the server remains stateless, since the anchor (possibly sent as an encoded / obfuscated token, which can include some other parameters such as page size) is supplied by the client with each pagination request. Unless I am missing something in your argument.
- borrow 5y ago
  What if that particular unique ID is deleted right before the client requests the next page?
  - pronik 5y ago
    If the IDs are monotonous, it doesn't matter.
    - rattray 5y ago
      (for posterity, this appears to have been a typo for monotonic)
  - JimDabell 5y ago
    Pagination only makes sense in the context of an ordered collection; if there is no stable sort order then you can’t paginate. So you identify the last record seen with whatever fields you are ordering by, and if the last record has been deleted, then it doesn’t matter because you are only fetching the items greater than those values according to the sort order. Anyway, there is plenty of documentation out there for cursor-based pagination; Hacker News comments isn’t the right place to explain the implementation details.
  - sigmaml 5y ago
    1. This kind of pagination can be done for any key as long as its data type admits total ordering. 2. The WHERE condition typically uses `>` or `<`, so it doesn't fail even when that record is deleted before the next client request referring to it.

akvadrako 5y ago

You should use stable page boundaries instead of cursors. If you are returning results by timestamps, the page boundary should be the timestamp and secondary ordering keys of the last row returned. Cursors take too many server resources and require client affinity.

JimDabell 5y ago
You’re describing cursor-based pagination. It’s got nothing to do with the SQL concept of cursors.