The disappearing customer: How unstable sorting breaks pagination

I have recently discovered a strange issue where a record in my database mysteriously disappears from a paginated API endpoint. And in fact, this is an issue that could happen to you as well, if your sorting is unstable. But let's start from the beginning.

The Starting Point

In my application, I have an endpoint that returns all customers, sorted by customer group. So each customer is assigned to a customer group, has a first name, a last name and some other fields. Because we have quite a few customers in the system, we also added pagination, so that the endpoint only returns 10 records at a time.

Note: The fact that I am using Laravel and the Eloquent ORM does not matter, it happens with any SQL query.

My query using the Eloquent Query Builder looks like this:

Customer::orderBy('customer_group_id')->paginate(10);

Which is equivalent to the following SQL query:

SELECT * FROM customers

ORDER BY customer_group_id

LIMIT 10 OFFSET 0;

The Problem: Unstable Sorting

At first, it seems logical. We first load all customers of group 1, then all customers of group 2 etc. But here's the catch: Although we loaded all pages in the frontend, one customer never showed up. Nowhere, on none of the pages - although they were in the database!

How is this possible? The reason is the following:

⚠️ For customers with the same customer_group_id, the database has no clear rule for how to order them within the group.

This is called unstable sorting and leads to a non-deterministic order of records. In our case that meant that for every page, the order of customers within a specific group was not consistent. While one customer did not get returned on any of the pages, others were returned on multiple pages. Not great.

The Solution: Add a unique tie-breaker

So let's look at how to fix it. To make your pagination stable and reliable, you need to ensure that sorting is deterministic. That means every record has a well-defined position in the order.

The fix? Add so many ORDER BY clauses that there isn't any ambiguity.

Laravel Query Builder:

Customer::orderBy('customer_group_id')

    ->orderBy('last_name')

    ->orderBy('first_name')

    ->orderBy('id')

    ->paginate(10);

Plain SQL:

SELECT * FROM customers

ORDER BY customer_group_id, 

last_name, first_name, id

LIMIT 10 OFFSET 0;

Without specifying the ID as a tie-breaker, you could still have unstable results if there are multiple customers who have the same first and last name. Since the ID is unique, this guarantees a consistent order across pages.

Final Thoughts

This issue is easy to miss, especially if there are lots of entries and only a few of them are non-unique. Debugging non-deterministic issues is never fun. But once you know what causes this problem, it's super easy to fix.

To save yourself the headache, here's my take-away for you:

➡️ If your sorting relies on a non-unique field, always add a unique, secondary ORDER BY column.️️

Written by Raphael Fleischmann, Team Lead Custom Software at Moonshiner.

This post was originally published on Raphael's personal blog: https://www.raphaelf.xyz/the-disappearing-customer-how-unstable-sorting-breaks-pagination/