stkvtflw stkvtflw - 10 months ago 43
SQL Question

Postgres: dynamic offset

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

. Bunch of users are continuously inserting new rows into
. A user currently browsing news feed (data from
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
, but in this case i will retrieve wrong data, because new news already have been inserted.

How do i implement pagination properly?


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="">Previous</a>
<a href="">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
    (uid > :lastRowId or :lastRowId is null)
    (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.