Paging Through the Data Deluge: Offset vs Keyset Pagination in Spring Data JPA

·

Brian

When “Next Page” Becomes a Performance Bug

In the quiet hours of a late-night sprint, pagination rarely feels like the glamorous part of a Spring Boot project. It’s the sort of feature you wire up with a sigh, using Pageable, ship to production, and forget about. Until the product takes off, the tables swell into millions of rows, and suddenly your “Next” button is the most reliable way to reproduce a performance incident.

Modern applications are voracious: infinite scroll feeds, analytics dashboards, admin back‑offices, all demanding fast, precise slices of data. The way you paginate – offset or keyset – becomes more than an implementation detail; it’s a design decision that shapes user experience and infrastructure cost. As Thorben Janssen notes, Spring Data JPA gives you offset pagination out of the box, but keyset pagination – the more performant sibling – you have to craft yourself (Offset and Keyset Pagination with Spring Data JPA).

This is the story of those two approaches, and why serious developers can no longer afford to treat pagination as an afterthought.

Offset Pagination: The Familiar Workhorse

Offset pagination is the default in Spring Data JPA. It’s what you get when you write:

Page<Post> page = postRepository
    .findByAuthor("alice", PageRequest.of(5, 20));

Under the hood, this translates to something akin to:

SELECT * 
FROM posts 
WHERE author = 'alice'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100;

It is conceptually simple: skip the first offset rows, then return limit rows. Spring’s Pageable abstraction makes this pattern almost frictionless (Spring Boot Pagination Performance in REST Endpoints | Medium). You get total pages, total elements, current page, and a nice JSON envelope for your REST endpoints. For admin tools, small datasets, or internal systems, this is often “good enough”.

The trouble begins when “small dataset” becomes “production-scale dataset”. Every time you ask the database for page 5,000, you’re effectively asking it to walk past 100,000 records just to get to the starting line. As Alexander Obregón points out, proper performance testing of pagination in Spring Boot REST endpoints quickly reveals that offset-based queries degrade significantly with large datasets (Spring Boot Pagination Performance in REST Endpoints | Medium).

It’s like owning a diet app that promises weight loss but secretly adds a few extra calories to every meal. The user interface looks healthy enough; the numbers underneath tell another story.

Keyset Pagination: The Athlete’s Diet of Data Retrieval

Keyset pagination takes a different approach. Instead of saying “give me page N”, you say “give me the next N rows after this one specific row”. Rather than skipping forwards by counting, you advance by reference to a stable key – usually an indexed column such as an ID or timestamp.

Vlad Mihalcea describes keyset pagination as a technique that leverages WHERE clauses on indexed columns instead of OFFSET, making it dramatically faster and more predictable at scale (Keyset Pagination with Spring – Vlad Mihalcea). The SQL looks more like:

SELECT *
FROM posts
WHERE author = 'alice'
  AND created_at < :lastCreatedAt
ORDER BY created_at DESC
LIMIT 20;

No OFFSET, no counting through a mountain of rows. The database jumps directly to where it needs to be. For data-heavy applications – think social feeds, transaction histories, or logs – this is the equivalent of swapping from a random crash diet to a well-structured, sustainable nutrition plan.

If offset pagination is the “OFFSET Nutrition” of data retrieval – promising something simple and universal, but potentially painful at scale – keyset pagination is the carefully tuned regimen that actually holds up when your dataset gains 10 million rows instead of losing 10 kilos (OFFSET Nutrition).

Spring Data JPA: Offset by Default, Keyset by Design

Spring Data JPA is firmly in the offset camp by default. Its Pageable abstraction is built around page numbers and page sizes. For many teams, this is where the story ends. But as Janssen points out, while Spring doesn’t support keyset pagination out of the box, nothing stops you from implementing it yourself (Offset and Keyset Pagination with Spring Data JPA).

One practical pattern is to expose a cursor in your REST API – a key such as lastId or lastCreatedAt – and accept it as a request parameter:

@GetMapping("/posts")
public List<PostDto> getPosts(
        @RequestParam(required = false) Long lastId,
        @RequestParam(defaultValue = "20") int size) {

    if (lastId == null) {
        return postRepository
            .findFirstPage(size);
    }
    return postRepository
        .findNextPage(lastId, size);
}

Backed by repository methods such as:

@Query("""
    SELECT p FROM Post p
    WHERE (:lastId IS NULL OR p.id < :lastId)
    ORDER BY p.id DESC
    """)
List<Post> findNextPage(@Param("lastId") Long lastId,
                          Pageable pageable);

This is the heart of keyset pagination: always stepping forward from a known last element, never counting from the beginning.

Developers working in Kotlin and JPA Specifications have gone even further, combining typed queries and specifications to keep keyset pagination expressive and type-safe, while still enjoying Spring Data’s conveniences (Keyset Pagination with JPA Specifications and … – Medium).

Developer Experience: From Offset Apps to Keyset Thinking

There is a curious parallel between pagination and the world of nutrition apps. OFFSET, a German app that brands itself as a “digitaler Begleiter” – a digital companion – for healthy eating, offers recipes, guidance, and motivation on the go (OFFSET-App). It is not just about calories; it is about sustained, structured progress.

Offset pagination, in contrast, is more like a quick-fix mindset: “just give me page 10”. It works, until it doesn’t. Keyset pagination demands a more deliberate design: you think in terms of cursors, stable ordering, and index usage. It requires more thought up front, but rewards you with a system that scales gracefully, like a well-designed meal plan.

When you’re building a modern API, this shift in thinking matters. You’re no longer just returning “page 3”; you’re building a contract with your consumers: “Here is your current slice of data, and here is the key you’ll need to ask for the next one”.

Choosing Your Pagination Strategy

So where does this leave the busy Spring developer trying to ship features without wrecking the database?

  • Use offset pagination when your datasets are small to moderate, your users genuinely need random access to arbitrary pages, and the cost of counting is acceptable. It’s quick to implement with Spring Data JPA’s Pageable, and ideal for admin UIs and back‑office tools (Offset and Keyset Pagination with Spring Data JPA).
  • Use keyset pagination when you are dealing with large tables, infinite scroll, or time-ordered feeds, and you mostly move forwards (or backwards) from the current position. It requires custom queries, but pays off in predictable, stable performance (Keyset Pagination with Spring – Vlad Mihalcea; Keyset Pagination with JPA Specifications and … – Medium).

In performance testing, this decision is not academic. As Obregón notes, realistic benchmarking of Spring Boot REST endpoints must consider how pagination behaves under heavy load and large datasets (Spring Boot Pagination Performance in REST Endpoints | Medium). Offset might look fine in development; keyset is what keeps your production database from gasping for air.

In a world where “load more” is one of the most frequently tapped buttons, pagination is no longer boilerplate. It is infrastructure. And in Spring Data JPA, the move from offset to keyset is less a refactor and more a change in mindset: from counting pages to following keys, from naive iteration to deliberate navigation.

Works Cited

Keyset Pagination with JPA Specifications and … – Medium. <a href=’https://medium.com/@zgza778/keyset-pagination-with-jpa-specifications-and-typedqueries-a-kotlin-spring-data-jpa-guide-ee0d5a735c67′>https://medium.com/@zgza778/keyset-pagination-with-jpa-specifications-and-typedqueries-a-kotlin-spring-data-jpa-guide-ee0d5a735c67</a>. Accessed via Web Search.

Keyset Pagination with Spring – Vlad Mihalcea. <a href=’https://vladmihalcea.com/keyset-pagination-spring/’>https://vladmihalcea.com/keyset-pagination-spring/</a>. Accessed via Web Search.

OFFSET-App. <a href=’https://offset-nutrition.com/pages/offset-app’>https://offset-nutrition.com/pages/offset-app</a>. Accessed via Web Search.

OFFSET Nutrition. <a href=’https://offset-nutrition.com/’>https://offset-nutrition.com/</a>. Accessed via Web Search.

Offset and Keyset Pagination with Spring Data JPA. <a href=’https://thorben-janssen.com/offset-and-keyset-pagination-with-spring-data-jpa/’>https://thorben-janssen.com/offset-and-keyset-pagination-with-spring-data-jpa/</a>. Accessed via Web Search.

Spring Boot Pagination Performance in REST Endpoints | Medium. <a href=’https://medium.com/@AlexanderObregon/pagination-performance-testing-in-spring-boot-rest-endpoints-8aedd293c1fb’>https://medium.com/@AlexanderObregon/pagination-performance-testing-in-spring-boot-rest-endpoints-8aedd293c1fb</a>. Accessed via Web Search.

Categories:

Leave a Reply