stkvtflw stkvtflw - 3 months ago 11
SQL Question

Postgres: dynamic offset

I have a table with news. Let's call this table

news
. Bunch of users are continuously inserting new rows into
news
. A user currently browsing news feed (data from
news
table). How can i make pagination consistent, keeping in mind the fact, that while a user browsing, say, first page, other users are inserting more data in it. So, if the first page had, say 18 news, then i need to use
OFFSET 18
, but in this case i will retrieve wrong data, because new news already have been inserted.

How do i implement pagination properly?

Answer

Fill the first and last row id or timestamp of the page in the application client. If it is a web app then the next and previous links would be something like:

<a href="example.com/news?firstRowId=40">Previous</a>
<a href="example.com/news?lastRowId=70">Next</a>

Then pass the value of the one you receive from the client to the query and the other as null:

select *
from news
where 
    (uid > :lastRowId or :lastRowId is null)
    and
    (uid < :firstRowId or :firstRowId is null)
order by uid desc
limit 20

The same logic for the insertion timestamp. It is very unlikely that two insertions would happen at the same millisecond but if the id is sequential then use the id.

Comments