All about pagination — Database Performance optimization

Offset Based Approach

SELECT  * FROM  items WHERE  [condition] ORDER BY  date DESC OFFSET  40  LIMIT 10

What is wrong with OFFSET and LIMIT?

Time-based paging

Cursor-based paging

SELECT  * FROM  items WHERE  id > 10  LIMIT 10
  • Unique. This value must be unique for all rows in the table.
  • Orderable. Every value of this column must be able to be compared to every other value. This is because we need to sort by this column in order to get a list of results to then return in pages.
  • Immutable. The value in this column cannot change, otherwise, the pages wouldn’t return the right information.

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store