Why pagination sometimes has overlapping pages

A deeper look inside the database

Joel Clermont
Joel Clermont

Let's say you want to present a list of records, sorted by when they were created, and using pagination to show 10 records at a time.

But let's throw a wrinkle in our setup: our first 12 records all have an identical timestamp, all the way down to the exact second.

In this situation, page 1 will show 10 of those records with the exact timestamp, as expected. But when you navigate to page 2, the first 2 records you see will not necessarily be records 11 and 12. In fact, you may very well see two of the records you already saw on page 1.

Why does this happen? Why is there a seeming "overlap" of pages? When you tell a database to sort by a column, it cannot differentiate between identical values. Running the same ordered query twice could return the same records in a different order.

If you want there to be a consistent record order in a case like this, you'll need to add a second unique column to your sort order. A common choice is the primary key for the table.

Here to help,


P.S. Did you know about the Mastering Laravel Youtube channel?

Toss a coin in the jar if you found this helpful.
Want a tip like this in your inbox every weekday? Sign up below 👇🏼

Level up your Laravel skills!

Each 2-minute email has real-world advice you can use.