Pagination and stable sorting

1. Pagination and projection: different tasks

When you first start building lists in an application, it is very easy to mix up two layers of optimization. It seems that if “we made a Page”, then we have already sped everything up, and we can happily return Page<Product>. In practice, pagination and projection solve different tasks, and that is exactly why they complement each other so well instead of replacing each other.

Projection answers the question: which columns (and which data) do we read. Pagination answers the question: how many rows do we read per query and how do we “slice” the overall data set into pages. If you use only pagination but return an entity, you limit the number of rows, but each row is still “heavy” — with managed semantics and a potential graph. If you use only projection but no pagination, you will honestly read the needed columns... but you may read 50 000 rows at once and bring the server to its knees.

To avoid confusion, it helps to keep a small mini-table in mind:

Page<ProductListRow>

And a small “life” formula for lists:

First choose the read model (projection), then add pagination.

2. Pageable and Page: structure

Inside Spring Data JPA, pagination looks very friendly: add a Pageable parameter — and you get a Page<T>. But to predict SQL and not be surprised by behavior in production, it is important to understand that this is not magic, but very concrete LIMIT/OFFSET math (in PostgreSQL) and a separate COUNT(...) query if you need information about the number of pages.

Pageable is a “request” for a page. It has three important things: page number, page size, and sorting. Page is the result: a list of elements + metadata (how many total, which page is current, whether there is a next one, and so on). And yes, pages are numbered from zero. This is one of those cases where programmers again made it “more convenient for the computer”, not for humans. But at least the offset formula becomes simple:

# offset is calculated like this:
offset = pageNumber * pageSize

If pageNumber=0 and pageSize=20, then offset=0. If pageNumber=1, then offset=20. Logical, but not intuitive if you think “the first page is 1”. Therefore, in services people usually either accept a 0-based page number directly, or adapt it: “the external API gives 1-based, and internally we convert it to 0-based”. In this course, we will assume that you call the service yourself and pass page as already 0-based.

Let’s look at a small code fragment that creates a Pageable:

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

// page — page index (0-based), size — page size
int page = 0;
int size = 20;

// Important: sorting must be stable (at the end a unique tie-breaker, usually id)
Sort sort = Sort.by("name").ascending()
        .and(Sort.by("id").ascending());

// Pageable contains page number, size, and sorting — LIMIT/OFFSET will be based on this
PageRequest pageable = PageRequest.of(page, size, sort);

Two most important rules are already hidden here: page size (so we do not read “everything”) and stable sorting (so pages do not “shuffle”). We will talk about sorting separately now, because this is the place where a backend developer grows up, stops believing “well, the database will sort it somehow”, and starts asking the right questions.

And here is the minimal example of what is inside Page:

import org.springframework.data.domain.Page;

Page<?> pageResult = /* repository call */;

// Page number (0-based)
System.out.println(pageResult.getNumber());         // 0
// Page size
System.out.println(pageResult.getSize());           // 20
// Total number of elements across the whole result (usually requires COUNT)
System.out.println(pageResult.getTotalElements());  // for example 137
// Total number of pages (also follows from COUNT)
System.out.println(pageResult.getTotalPages());     // for example 7

Important nuance: getTotalElements and getTotalPages usually mean that a COUNT(...) was executed somewhere nearby. This is neither “bad” nor “good” — it is simply the price of knowing “how many rows there are in total”. If you do not need this knowledge, Spring Data has other options, but today we stay within Page and Pageable, because these are what are used most often in admin lists.

3. Stable sorting: order by name is not enough

Pagination without sorting is like trying to arrange books on shelves while someone keeps shaking the rack. Formally, the pages will be “some pages”, but you will not be able to guarantee that when you move to the next page, you will see what was not on the previous one. Therefore the rule is strict: a paginated list must have a defined order.

But there is a beginner’s trap here: “I sorted by name, so the order is defined.” Alas, if name is not unique, then the order is not fully defined. The database may return two rows with the same name in different order in different queries (especially with concurrent changes, a different execution plan, or simply because it is more convenient for it). And this is where a term appears that saves nerve cells: stable sorting.

Stable sorting for pagination means that your ORDER BY must ultimately produce a unique row order. The simplest way is to add a unique field to the end of the sorting, most often id.

Imagine a very real-life situation in our Commerce Persistence Lab: you have two products with the name "Cable". If you sort only by name, then both products are “the same” from the perspective of sorting. And then the fun begins: you open page 0, see one "Cable", open page 1 — and sometimes you see the second one, and sometimes the first one again. And you think: “I have a pagination bug.” In reality, the bug is that the order was not unique.

A stable rule for a product list in an admin panel usually looks like this:

import org.springframework.data.domain.Sort;

// name may not be unique — so we close the sorting on the unique id field
Sort sort = Sort.by("name").ascending()
        .and(Sort.by("id").ascending());

For an order list, people often want “newest on top”, and there it is also important to close with a unique field:

import org.springframework.data.domain.Sort;

// createdAt may also match (batches, rounding, identical timestamps) — we protect ourselves with id again
Sort sort = Sort.by("createdAt").descending()
        .and(Sort.by("id").descending());

Even if createdAt is “unique enough” on average, in real systems it very easily becomes non-unique (for example, batch import, migrations, time rounding, identical timestamps under high load). id is a simple safety net.

To visually reinforce why this is critical, you can imagine the following flow:

flowchart TD
  %% If the order is not unique, the page boundary "floats", and elements can move between pages
  A["Page 0 request: ORDER BY name LIMIT 20 OFFSET 0"] --> B["Rows with name='Cable' landed on the page boundary"]
  B --> C["Page 1 request: ORDER BY name LIMIT 20 OFFSET 20"]
  C --> D["The database can change the order of identical names"]
  D --> E["You see duplicates/gaps between pages"]

Adding id to the sorting makes the order deterministic, and this scenario collapses: equal name values will still be ordered by id.

4. Page and DTO projection: a paginated list

Now let’s put everything together in the form we want to see for a “grown-up” read use case: the list should be thin (projection), paginated (pagination), and predictable (stable sort). The good news is that Spring Data JPA lets us do this quite directly.

DTO for a list row

A DTO (read model) for a product list in an admin panel is usually very simple. For example, we need id, sku, name. Let’s show the DTO class (getters can be generated in the IDE, but we will not bloat the code in the example):

package com.example.commerce.catalog.dto;

public class ProductListRow {
    // DTO is a read model: no managed semantics, no lazy links, no "accidental UPDATE"
    private final Long id;
    private final String sku;
    private final String name;

    public ProductListRow(Long id, String sku, String name) {
        // Store only what is needed for the list row
        this.id = id;
        this.sku = sku;
        this.name = name;
    }
}

The key idea: this is not an entity, it is not managed, and it cannot be “accidentally changed and cause an UPDATE”. It is just a result shape.

Repository: return Page<ProductListRow>

Now in the repository we will create a method that returns a page of DTOs. In Commerce Persistence Lab it makes sense to put this in com.example.commerce.catalog.repository.ProductRepository.

package com.example.commerce.catalog.repository;

import com.example.commerce.catalog.dto.ProductListRow;
import com.example.commerce.catalog.entity.Product;
import com.example.commerce.catalog.entity.ProductStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("""
           select new com.example.commerce.catalog.dto.ProductListRow(p.id, p.sku, p.name)
           from Product p
           where p.status = :status
           """)
    // Important: we do not hard-code sorting (ORDER BY) here — it will come from Pageable
    Page<ProductListRow> findListRowsByStatus(ProductStatus status, Pageable pageable);
}

Pay attention to an important point: there is no order by in the query. This is done intentionally: we will pass sorting through Pageable, so the sorting rule remains part of the read use case. This is convenient for reuse (and for tests), but it requires discipline: you must set sorting when constructing the PageRequest.

Read service: define a stable default sort

In our project we try to separate “reading” and “writing” at least at the level of approach, so we will create a thin ProductQueryService. It is important that this is a read-only operation: we do not want reading to drag along unnecessary machinery.

import com.example.commerce.catalog.dto.ProductListRow;
import com.example.commerce.catalog.entity.ProductStatus;
import com.example.commerce.catalog.repository.ProductRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.annotation.Transactional;

public class ProductQueryService {

    private final ProductRepository productRepository;

    public ProductQueryService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    @Transactional(readOnly = true) // Tell the infrastructure: this is pure reading
    public Page<ProductListRow> findActiveProducts(int page, int size) {
        // Default stable sorting: name + tie-breaker id
        Sort sort = Sort.by("name").ascending().and(Sort.by("id").ascending());

        // Pageable defines both LIMIT/OFFSET and ORDER BY (if the repository supports sorting)
        PageRequest pageable = PageRequest.of(page, size, sort);

        return productRepository.findListRowsByStatus(ProductStatus.ACTIVE, pageable);
    }
}

Yes, id is in the sorting again. Yes, it looks “like something extra”. And yes, this is exactly the kind of “extra” that later saves hours of debugging “why is it sometimes empty, sometimes duplicated on my page”.

5. SQL with Page: LIMIT/OFFSET and COUNT

At the Java code level, pagination looks easy. At the database level, it is usually two separate queries. The first query reads the page content via LIMIT/OFFSET. The second query reads the total number of rows via COUNT(...), so that Spring Data can build a Page with metadata.

This matters for two reasons. First, you must understand that a “page” is not one query in a vacuum, but most often two. Second, you must be able to distinguish the problem “we are reading too many columns” (solved by projection) from the problem “we are making too expensive a count” (solved by other approaches, but today we are simply noting that count exists).

Very approximately, SQL will look like this (this is not an exact copy of your log, but a clear form):

-- content query (data page): LIMIT/OFFSET + ORDER BY for deterministic order
select p.id, p.sku, p.name
from product p
where p.status = 'ACTIVE'
order by p.name asc, p.id asc
limit 20 offset 0;

-- count query (how many rows total): needed to fill Page metadata (totalElements/totalPages)
select count(p.id)
from product p
where p.status = 'ACTIVE';

And here projection helps again: the content query reads exactly three columns, not the whole product (plus potential associations). Hibernate does not create a managed entity, does not bloat the persistence context, and you get a “thin” result object. But the count query still exists, because Page promises you getTotalElements.

One more nuance that is useful to say out loud: sorting belongs to the read case, not to the mapping. If you try to “fix” list problems by adding @OrderBy on collections or global settings in an entity, you will quickly end up in a situation where one screen needs sorting by name, another by price, and a third by creation time. An entity does not have to be a universal read model, and sorting is a great example of why.

6. Typical pagination and sorting mistakes

Mistake #1: “I made a Page<Product>, so the list is optimized”.
A very common story: a developer honestly adds Pageable, sees limit/offset in SQL, and decides that everything is fine now. But in reality they keep reading managed entities, which can drag along lazy associations, dirty checking, and generally “too much life” for a list. In our course, the correct starting thought is different: first choose the read model (projection), and only then slice it into pages.

Mistake #2: sort only by a non-unique field and catch “floating pages”.
order by name looks nice until two products with the same name appear. After that, pages may duplicate elements, skip them, or swap them around on repeated requests. This is usually fixed the same way every time: add a unique “tie-breaker” to the end of the sort, usually id. This is not a “trick”, but a required condition for deterministic order.

Mistake #3: confuse page numbering and pass page=1, expecting “the first page”.
PageRequest.of(page, size) expects a 0-based index. Therefore page=0 is the first page. If you make an internal call from a service or test and pass 1, you will get the second page, and then you will spend a long time suspecting a “bug in the query”. The simplest way to avoid this is to name the parameters honestly (pageIndex) and print input values in logs during lab scenarios.

Mistake #4: assume Page is always one SQL query.
Almost always, Page means not only SELECT ... LIMIT/OFFSET, but also SELECT COUNT(...). When you look at the SQL trace and suddenly see “why two queries?”, the answer is often exactly here. And that is normal: Page buys you metadata about the number of rows at the cost of an additional query. That is not a reason to avoid Page everywhere, but it is a reason to remember that the cost exists.

Mistake #5: “I’ll do pagination and let the user sort however they want”.
Sorting through Pageable can indeed be dynamic, but if you do not set at least a stable default, you risk getting a non-deterministic “default” order. For admin lists it is usually better to have an explicitly defined base order, and only then allow additional options. Even when sorting is dynamic, the rule “close on a unique field” still applies.

1

Task

Hibernate deep-dive, level 9, lesson 3

Locked

Active products page through projection

Active products page through projection

Open

1

Task

Hibernate deep-dive, level 9, lesson 3

Locked

Orders Page with countQuery and Stable Order

Orders Page with countQuery and Stable Order

Open