Building the load more mechanism for an infinite scroll

Let's set up the problem first. You have a list of posts and you want to show them "infinite-scroll"-style sorting from the newest post to the oldest post.

Well, obviously, you can't just load every single post into the page at once. Therefore, you need to "paginate" it or, in other words, supports the load more mechanism.

The problem is somewhat non-trivial when we think about it a bit more deeply.

At first glance, we might be able to use the oldest timestamp (called a cursor) as a condition to fetch the next 10 items whose timestamps are older than (not equal to) the cursor. But this approach is obviously flaw because multiple posts might have the same timestamp.

We could solve it by fetching more than 10 items if the oldest timestamp matches multiple posts. This approach is okay-ish. It might fetch more than 10 items. While it's unlikely that many posts would have the same timestamp to cause a perf issue, as an ex-Twitter engineer, who built a chronological ordered timeline many times, I can't stand the impreciseness like this.

As you can see, the fundamental problem here is that using timestamps doesn't result in a deterministic order of the posts since some posts might have the same timestamp. It's impossible to build a robust load more mechanism without a deterministic order. We simply have to achieve that.

One way of achieving a deterministic order is to introduce a tie breaker. More than often, a post ID is perfect for the job because it is guaranteed to be unique.

With the above in mind, we can formulate the mechanism as follows:

1. We sort the posts by timestamp (in descending order) and post ID (in descending order)
2. We load more using 2 cursors: max timestamp and exclusive max post ID
3. We query the next 10 items with the following conditions: item.timestamp < maxTimestamp || (item.timestamp == maxTimestamp && item.id < exclusiveMaxPostID)
4. We query with a certain limit that is needed for your page for the obvious performance reason.

And there you are: a robust load more mechanism for your infinite-scroll surface.

Bonus point: we can expand this to build a timeline that comes from more than one sources. This is left as an exercise for the readers. If you use Postgres, one caution is that string sorting algorithm in Postgres is often not identical to string sorting in your favourite programming language.