For example you have a forum post with 1000 messages starting in 2007, and you are dying to know what happened in the discourse in 2011. So basically you need to jump in the middle by bisection.
1000 is tiny by DB standards but solutions like this one are often bolted without considerations.
That's a great example. I understand the use case now.
I note this use case can be solved using keyset pagination instead of offset/limit, because the ordering of messages is stable: messages are never inserted in the middle of the list; they are appended at the end. We can attribute a number to each message, and use this message to filter and sort.
You keep it and mark it "deleted", and you show "deleted" in place of the message. This is what most forums do already, independently of the underlying pagination mechanism.
1000 is tiny by DB standards but solutions like this one are often bolted without considerations.