The disappearing customer: How unstable sorting breaks pagination

Raphael — Thu, 17 Jul 2025 19:05:15 +0000

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/

Your First Moves in Enterprise Architecture – A Strategic Guide to Getting Started

Raphael — Thu, 17 Apr 2025 09:23:03 +0000

So you want to get started with Enterprise Architecture (EA)? This guide will help you take the first steps successfully. The beginning feels like hacking your way through a dense jungle of many applications, technologies, teams and processes. But with the right tools and a clear strategy, you can cut a path towards a future-proof business. So let's start this expedition!

🌀 Define Your Objectives: Find Your Why

First things first: Why are you diving into EA? Before moving on, take a step back and look at the big picture. What is the organization trying to achieve? Are you aiming to align your business and IT? Perhaps you're on a quest to make your organization more agile, or double-down on digital transformation? Or maybe you're striving to reduce costs – or the opposite: expand rapidly?

Whatever the reasons, make sure to clarify them with top management and articulate them clearly. These objectives are your North Star, guiding you through the fog of enterprise complexity. Write them down, share them widely, and refer to them often. Without understanding the business strategy, architecture efforts will lack direction.

Your success depends on your relationships with upper management. It's office politics, but in a positive way. Show them your superpowers. These are your capabilities to bring structure into chaos, to lead initiatives and to explain complex technical topics in simple words. Once they trust you, you'll have the backing you need to make real, impactful changes.

🦕 Assess Your Current State: Digital Archaeology

Time to play detective! Investigate your current application landscape, business processes, information systems, and infrastructure. It's like archaeology, but instead of dinosaur bones, you're discovering legacy systems and forgotten databases. Create a comprehensive map of all your existing applications, their functions, and how they interact.

Also document their "ingredients": the current technology stack. List out languages, frameworks, infrastructure providers, databases and standard software. You might find some ingredients that are well past their expiration date!

🔠 Categorize and Prioritize: What's Hot, What's Not?

Categorize your applications and tech stacks. Which ones are strategically important, and which ones are the Internet Explorer of your organization (in desperate need of change)? Focus on the important ones - you're an architect, not a magician. You cannot fix everything at once. To make prioritization easier, you might want to take a look at Gartner's TIME framework.

🔮 Develop a Vision for the Future

Based on your objectives and current state, now it's all about your vision for the future. Ensure this vision aligns with your organization's overall strategy.

Establish a set of architecture principles that will guide you through the transformation. These principles are not just goals, they're your architectural mantras. Cover aspects like technology standards, information management and security. Make them clear, memorable, and maybe even a little witty.

Next, paint a picture of your future architecture. This is where you get to channel your inner Da Vinci (but with more rectangles and arrows). Which applications and technologies do you want to improve, replace or sunset?

🗺️ How will you get there? Create a Roadmap

Now that you know where you are and where you want to go, it's time to figure out how to get there. Break down your journey into manageable phases and projects, with some milestones that you can use as checkpoints. One thing is clear: for this step you need your application experts on board, to get a realistic estimation of effort and lead time. Finally, you need management approval to get the necessary resources to make your ideas come true.

🤝 Collaboration, Support and Governance

It's time to establish processes for quality gates, architecture reviews and compliance checks. If needed, create a list of approved services and a process for exceptions. But remember, you're here to improve the organization, not to be the architecture police. Support projects, offer guidance and closely collaborate with architects, developers and product managers. Maybe you can even provide your teams with a convenient shared platform that they can build upon, to simplify and speed up their development.

📈 Try Out, Improve, Repeat.

Nothing is perfect from the beginning, but this should give you a good start into Enterprise Architecture. You can always iterate and continuously improve the status quo. Listen to what your colleagues say (and maybe what they think but don't say), monitor progress and read up about best practices and EA frameworks. Then you will definitely be successful! I wish you all the best!

Written by Raphael Fleischmann, Team Lead Custom Software at Moonshiner.
This post was originally published on Raphael's personal blog: https://www.raphaelf.xyz/your-first-moves-in-enterprise-architecture/

DEV Community: Raphael