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.
> 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.
I think pagination is only predictable under these conditions:
1) The offset used for the next fetch must be based on a pointer to a unique key. We can't rely on the number of rows previously seen.
With this rule, deletes which occur to rows in previous pages will not cause unpredictable contractions.
2) The paged result set must be sorted based on a monotonically increasing field, like created_at, plus enough other fields to create a unique key. You could lean on the PK for this, i.e.: ORDER BY (created_at, id) ASC.
With this rule, new inserts which occur during page enumeration will only affect unseen pages (and we'll see them eventually)
The API call looks roughly like this:
/orders/?region=US&offset=(2022-03-12T07:05:58Z&ord_1234)&limit=100
SELECT *
FROM orders
WHERE (created_at, id) > (:offset_created_at, :offset_id)
OR (
:offset_created_at IS NULL
AND :offset_id IS NULL
)
ORDER BY (created_at, id) ASC
LIMIT :page_size
The solution is usually simple: assume it doesn't matter. Write a spec for your feature and explicitly state that "In the solution we assume it doesn't matter if the same record is ocassionally reported twice or a record is missing from the pagination in some cases". Done.
If I do a search, a paginated search result can have an ID so I can paginate between the data without a new data messing up my pagination.
But for normal entities, simple pagination is (mostly) more than enough.
The solution you are describing is overkill and almost no benefit at all.
b) 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. 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 ensures consistency from one API call to the next.