Infinite scrolling/data paging and out-of-sync page results

Question

I'm about to develop infinite scrolling of items (which is just a specific UX of paged items) in an web app, but I'm a bit confused how to approach the problem of new items being added while one user scrolls/pages through items. How do we deal with such unsynced data?

Suppose when I open my page database has 100 items.

1. User 1 navigates to the page which request for the first 10 items and displays them
2. User 1 then starts scrolling and gets to a point when the page requests for next 10 items
3. User 2 adds a new item so database now has 101 items
4. User 1 scrolls requesting next 10 items.

What should happen on the backend?

1. If user scrolled down we could request 10 items from last ID on which is ok
2. If user scrolled up we would request previous 10 items but there's another one at the top now?

How is this solved? Maybe even on Stackexchange sites where content is being paged and stream cache changes while user navigates pages of questions?

Should this question be asked on programmers maybe? I'm not sure...

Answer 1

This is most easily solved by not doing paging traditionally by providing

• page index and
• page size,

but rather providing

• last displayed record ID and
• page size

This is referred to as keyset paging or key seek method paging. Both usually require additional parameters like ordering and filtering, but that's similar for both techniques so it's irrelevant to this answer.

This prevents invalid pages being returned when new items are being added to the top of the list, but it also has a downside to not give you the ability to jump pages (i.e. get page 10, while we're looking at page 5). But that's why this is used in specific scenarios like infinite scrolling where this problem isn't present or even desired.

See performance comparison and lots of explanation regarding the key seek method:
http://use-the-index-luke.com/sql/partial-results/fetch-next-page

So basically what we want to do is something along these lines (suppose we're ordering chronologically in reverse order and assuming CreateDate with such high precision that it's considered unique):

with LastRecord
as (
    select CreatedDate
    from Records
    where Id = @LastRecordId
)
select r.*
from Records r, LastRecord l
where r.CreatedDate < l.CreatedDate
offset 0 rows
fetch first @PageSize rows only;