Keep pagination repeatable if change operations are performed

Question

If one wants to paginate results from a data source that supports pagination we have to go to a process of:

1. defining the page size - that is the number of results to show per page;
2. fetch each page requested by the user using an offset = page number (0 based) * page size
3. show the results of the fetched page.

All this is works just fine not considering the fact that an operation may affect the backend system that screws up the pagination taking place. I am talking about someone inserting data between page fetches or deleting data.

page_size = 10;
get page 0 -> results from 0 to 9;
user inserts a record that due to the query being executed goes to page 0 - the one just shown;
get page 1 -> results from 10 to 19 - the first results on the page is the result on the old page 0.

The described behavior can cause confusion to the viewer. Do you know any practical solution to workaround this problem.

Answer 1

There are a few schools of thought o this.

1. data gets updated let it be
2. You could implement some sort of caching method that will hold the entire result set (This might not be an option if working with really large Datasets)
3. You could do a comparison on each page operation and notify the user if the total record count changes

.

Answer 2

If the updates you are concerned with are primarily new insertions (for example, StackOverflow itself seems to suffer from this problem when paging through questions and new questions come in) one way to handle it is to capture a timestamp when you issue the first pagination query, and then limit the results of requests for subsequent pages to items which existed before that timestamp.

Answer 3

As long as users understand that the underlying data is always changing, they won't be confused. So just do it the straightforward way.

You could cache the first few pages of the result and use that for subsequent views, but then the results will be out of sync with the database, which is even more confusing.