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