Pagination Pitfalls: Preventing Data Loss

I recently ran into a peculiar problem at work. I had written a database query to bulk load millions of records from a data warehouse which worked by paginating over a large dataset. Everything seemed to be working okay... until we noticed some data was missing.

I initially wondered if it was a bug with the database (naive and unlikely I know) and after some investigation we finally discovered the problem. Pagination and ordering. It’s obvious in hindsight and felt like a silly mistake, so I wanted to share my learning.

The Problem: Ordering by a Non-unique Column

When working with databases and paginated queries, it's essential to consider the order in which your data is retrieved. If you're ordering your results by a non-unique column, you might encounter a common issue – missing rows. This happens because pages of data may return the same row that was already included in a previous set, leading to gaps in your results.

Imagine you have a table of user records and you want to display them in alphabetical order by last name. If you're fetching these records in chunks for a paginated user list, and two users have the same last name, you could end up with one of those users appearing on two pages and one of those users missing from your paginated results altogether.

Let's break down why this happens:

1. Initial Order: You start by ordering your data by the non-unique column (in this case, the last name) and fetching the first page.
2. Paginating: As you move through subsequent pages, you request the next set of data, but since the order is not unique, the database may return rows that were already included in the previous pages.
3. Missing Rows: This redundancy in rows can lead to some records not being displayed at all in your paginated results. Users with the same last name as others may end up being skipped, causing gaps in your list.

The Solution: Multi-column Ordering

To avoid missing rows in your paginated database queries, you can introduce multi-column ordering. Instead of relying solely on a non-unique column, add another column that provides a unique order. This could be using a combination of columns that are unique together, or to be safe you can use an auto-incrementing primary key, UUID or a timestamp that you know is guaranteed to be unique.

Here's how it works:

1. Ordering by Multiple Columns: When you order your data by multiple columns, you ensure that the combination of these columns is unique. In our example, you might be able to order by both last name and first name. This combination is far less likely to produce duplicate rows, but may not be impossible depending on your implementation. To be 100% sure, you could order by last name and user ID. This way we’re guaranteed that the ordering is unique.
2. Consistent Pagination: Now, when you paginate through your data, you're guaranteed to receive a distinct set of records on each page. There's no risk of missing rows, even when two users share the same last name.

Conclusion

This is such a fairly simple concept in the end, and super simple to implement. That said, it’s also easy to miss and it could cause all sorts of problems with missing data. In the world of paginated database queries, ensuring that your results are complete and accurate is crucial.

By implementing multi-column ordering, combining a non-unique column with a unique one, you can prevent missing rows and provide a seamless, error-free experience for your users. So, next time you're working on a paginated query, remember the importance of multi-column ordering to ensure the integrity of your data retrieval process.