> 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.
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.
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.
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.
Cursors take too many server resources and require client affinity.
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.