Problem
MediaWiki's IndexPager uses an offset URL parameter to determine where the next page should start. This strategy is described well in this blog post.
The strategy has a lot of benefits. Primarily it is much faster and prevents the user from getting duplicated results as new data is added to the table while they are paginating the results. However, there are some drawbacks. The biggest one is that it only works properly on unique database columns. If the sorting column is non-unique, it shows the user duplicate content, which is the very problem it seeks to solve.
During the development of T237300 it became clear that there is a need to be able to sort a paginated table of data on a non-unique field.
Proposed Solution
Obviously it is better for developers to use the seek method whenever possible (and that should be the default).
However, MediaWiki, could provide the option of allow sorting on non-unique fields by using the SQL OFFSET. This is only a major performance problem if the user goes beyond a reasonable number (50?) of pages. To limit the performance impact, this feature should be limited to the UI and not available in the API. It should also be limited to read-only database queries so the replicas are used. This feature could also be restricted to only authenticated users.
Alternative Solutions
T244492 will add support for using a multi-column offset, which could be used with a primary key to achieve non-unique field pagination, however, one of our uses cases is to paginate a query with a GROUP BY statement which results in an ambiguous primary key.
Work Around
We could extend IndexPager in CheckUser extension and implement an SQL OFFSET based pagination within the extension itself. This would localize the pattern to only where it is truly needed.