DEV Community

Discussion on: Why You Shouldn't Use OFFSET and LIMIT For Your Pagination

zakwillis profile image

Hi there thanks for the post.

Strangely, many developers have never had to deal with index based pagination. I have, but many haven't.

The main challenge with this approach is, almost never will a primary key/incrementing column be continuous after any kind of filtering, normally scope related. A good example would be an online trading account. A user wants to see their trades?
So you may end up having to use an analytic function (rownumber over) to create the user trade Id. Requires full resultset availability? This is how offset works?
Alternatively this would need to be a scope based guaranteed continuously incremented value? This becomes a design time choice?
Where this works is if you store a daily table of data which all users will access with different pagination. Then this is powerful.

Good Post and the other guy who wrote something similar.