Why You Shouldn’t Use OFFSET and LIMIT For Your Pagination

Gone are the days when we wouldn’t need to worry about database performance optimization.

With the advance of times and every new entrepreneur wanting to build the next Facebook combined with the mindset of collecting every possible data-point to provide better Machine Learning predictions, we, as developers, need to prepare our APIs, better than ever, to provide reliable and efficient endpoints that should be able to navigate through huge amounts of data without a sweat.

If you have been doing backend or database architecture for a while you have probably already done paging queries, like this:

Right?

But if you did build your paginations such as this, I am sorry to say but you have been doing it wrong.

You don’t agree with me? You don’t need to. Slack, Shopify and Mixmax are paginating their APIs with this same concept we will be talking about today.

I challenge you to name a single backend developer who hasn‘t ever had to deal with OFFSET and LIMIT for pagination purposes. For pagination in MVPs and low-data listings it “just works“.