Spring Data Keyset Pagination vs Offset: When to Use Each

How does each approach work?

Both strategies return a bounded slice of an ordered result set. They differ in how the database finds where that slice starts.

• Offset pagination identifies a page by position: "skip N × S rows, return the next S." The database starts at the beginning of the ordered result every time and discards the skipped rows. In Spring Data this is Pageable, returning Page<T> or Slice<T>.
• Keyset pagination identifies a page by content: "return the next S rows whose sort key comes after this one." With a matching index, the database seeks directly to the last row the client saw and reads forward. In Spring Data this is the Scroll API, returning Window<T> from a ScrollPosition.

The sections below cover each mechanism in detail, then the trade-offs, the alternatives and how to verify the choice.

How offset pagination works and where it slows down

Offset pagination asks the database for "page N of size S" by skipping N × S rows and returning the next S. In Spring Data JPA, a Pageable such as PageRequest.of(page, size, sort) is translated into an ORDER BY plus LIMIT/OFFSET (or the dialect's equivalent). It is popular because it maps directly to numbered page links, needs no client-side state beyond a page number, and works with any sort order.

The cost is in the skipping. OFFSET 100000 does not jump to row 100,001. The database still has to produce the first 100,000 rows in sort order, through an index scan or a sort, and then throw them away. Work grows roughly linearly with page depth, so page 1 is fast and page 5,000 is not, even though both return the same number of rows.

Returning a Page<T> adds a second cost. To fill in getTotalElements() and getTotalPages(), Spring Data runs a separate COUNT query. On a large table with selective filters, that count can scan far more data than the page query and dominate latency. A Slice<T> avoids it. It fetches size + 1 rows to determine hasNext() and never counts, which is enough for "load more" or infinite-scroll interfaces that do not show a total.

Offset is also unstable under concurrent writes. If a row is inserted before the current position between two requests, the last row of the previous page shifts onto the next page and the client sees it twice. If a row is deleted, one row slips past unseen.

Copyimport org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Page<T>: runs the page query AND a separate count query.
    //   select ... from orders where status=? order by created_at desc, id desc limit ? offset ?
    //   select count(*) from orders where status=?
    Page<Order> findByStatus(String status, Pageable pageable);

    // Slice<T>: fetches size + 1 rows to compute hasNext(). No count query.
    //   select ... from orders where status=? order by created_at desc, id desc limit ? offset ?
    Slice<Order> readByStatus(String status, Pageable pageable);
}

class OrderService {
    private final OrderRepository repo;
    OrderService(OrderRepository repo) { this.repo = repo; }

    void demo() {
        // Include a unique column (id) as a tiebreaker so the order is deterministic.
        Sort sort = Sort.by(Sort.Order.desc("createdAt"), Sort.Order.desc("id"));

        // Page 5,000 of size 20 => OFFSET 100000: the database produces and
        // discards 100,000 rows before returning 20.
        Pageable deep = PageRequest.of(5_000, 20, sort);

        Page<Order> page = repo.findByStatus("SHIPPED", deep);
        long total = page.getTotalElements(); // populated by the extra COUNT query

        // Cheaper for "load more" UIs: no total, just whether more rows exist.
        Slice<Order> slice = repo.readByStatus("SHIPPED", deep);
        boolean more = slice.hasNext();
    }
}

A Spring Data JPA repository that returns the same offset-based query as a Page, which issues an additional COUNT query, and as a Slice, which fetches one extra row to determine hasNext(); the SQL in the comments is approximate and varies by dialect.

How keyset pagination works

Keyset pagination, also called cursor pagination, replaces "skip N rows" with "continue after this row." The client sends back the sort key of the last row it saw, and the query filters on it: WHERE (created_at, id) > (:lastCreatedAt, :lastId) ORDER BY created_at, id LIMIT 20. With a matching index, the database seeks to that position and reads the next 20 entries. The cost of a page no longer grows with how deep the client has paged.

The figures above are illustrative, assuming a 20-row page size.

The sort key must be unique and deterministic. If two rows share a created_at value, created_at > :last either skips the rows that tie with the boundary or repeats them. Appending the primary key as a tiebreaker gives every row exactly one position in the order.

The index has to match the ORDER BY columns in sequence and direction. For this query that means an index on (created_at, id). If the columns or directions differ, the planner may fall back to scanning and sorting, which removes the benefit. Some databases also optimize the row-value comparison less well than the expanded form created_at > :c OR (created_at = :c AND id > :i). Check the execution plan.

Keyset pagination is stable under concurrent writes because the cursor names a row, not a count. Inserts and deletes that land before the last seen key do not move the boundary. The next page starts exactly where the previous one ended.

CopyOFFSET 10000 LIMIT 20
+-------+    +------------------------------------------------------+
| Query |--->| Index (created_at, id): start at first entry         |
+-------+    |   entries 1..10,000       -> read and discarded      |
             |   entries 10,001..10,020  -> returned                |
             +------------------------------------------------------+
               work grows with depth; inserts before the page shift it

KEYSET  WHERE (created_at, id) > (:lastCreatedAt, :lastId) LIMIT 20
+-------+    +------------------------------------------------------+
| Query |    | Index (created_at, id): entries before cursor        |
+-------+    |   never touched                                      |
    |        +------------------------------------------------------+
    | seek   | cursor = (:lastCreatedAt, :lastId), last row seen    |
    +------->+------------------------------------------------------+
             |   next 20 entries         -> returned                |
             +------------------------------------------------------+
               work roughly constant per page

Index slice, ascending (created_at, id); id breaks ties:
+------------+------------+------------+------------+------------+
| 10:00, 7   | 10:00, 9   | 10:01, 3   | 10:01, 4   | 10:02, 1   |
+------------+------------+------------+------------+------------+
                                ^            ^
                          last seen row   next page starts here
New row (09:59, 12) inserted before the cursor: boundary does not move.

Offset reads and discards every skipped index entry from the start, while keyset seeks to the last seen (created_at, id) entry and reads only the next page, so earlier inserts do not shift its boundary.

Implementing keyset pagination with the Spring Data Scroll API

Spring Data exposes keyset pagination through the Scroll API. A repository method returns Window<T> and takes a ScrollPosition. For example, findFirst20ByStatusOrderByCreatedAtDescIdDesc(String status, ScrollPosition position) returns one window. A Limit parameter can replace the First20 keyword. The first call passes ScrollPosition.keyset(), which is an empty keyset position that means "start at the beginning." Spring Data then generates the comparison predicate against the sort properties for you.

To request the next window, take window.positionAt(window.size() - 1) and pass it back unchanged. That position holds the sort-key values of the last element, here createdAt and id. Use window.hasNext() to decide whether to offer another page, and do not compare the result size against the limit yourself. The sort must end on a unique column such as the primary key. If it does not, rows that tie on createdAt can be skipped or repeated at window boundaries.

Spring Data only builds the keyset predicate for queries it constructs itself, such as derived methods. It does not rewrite a string @Query. For projections, joins or other query shapes it cannot derive, write the predicate by hand. In a native query on a database that supports row-value comparison, write WHERE (created_at, id) < (:createdAt, :id). Standard JPQL has no tuple comparison. For portability, use the expanded form created_at < :createdAt OR (created_at = :createdAt AND id < :id). Both forms produce the same rows, but some planners use an index more effectively with the row-value form.

Either form needs a matching composite index, or every page falls back to a sort. For this example the index is CREATE INDEX idx_orders_status_created_id ON orders (status, created_at DESC, id DESC). The equality column goes first, followed by the sort keys in the query's order and direction.

Copypublic interface OrderRepository extends JpaRepository<Order, Long> {

  // Derived query: Spring Data generates the keyset predicate on (createdAt, id).
  // Depends on: CREATE INDEX idx_orders_status_created_id
  //             ON orders (status, created_at DESC, id DESC);
  Window<Order> findFirst20ByStatusOrderByCreatedAtDescIdDesc(String status, ScrollPosition position);

  // Fallback for query shapes Spring Data cannot derive. Uses a row-value predicate,
  // which only works on databases that support it (e.g. PostgreSQL).
  // Portable form: created_at < :createdAt OR (created_at = :createdAt AND id < :id)
  @Query(value = """
      SELECT * FROM orders
      WHERE status = :status AND (created_at, id) < (:createdAt, :id)
      ORDER BY created_at DESC, id DESC
      LIMIT 20""", nativeQuery = true)
  List<Order> findNextPage(@Param("status") String status,
                           @Param("createdAt") Instant createdAt,
                           @Param("id") Long id);
}

// Service code: walk every window for one status.
void processOpenOrders(OrderRepository repo) {
  ScrollPosition position = ScrollPosition.keyset(); // empty keyset = start at the beginning
  Window<Order> window;
  do {
    window = repo.findFirst20ByStatusOrderByCreatedAtDescIdDesc("OPEN", position);
    window.forEach(this::process);
    if (window.isEmpty()) {
      break;
    }
    // Pass back the last element's position unchanged; it holds its createdAt and id.
    position = window.positionAt(window.size() - 1);
  } while (window.hasNext()); // let Spring Data decide if more rows exist
}

A derived Scroll API method that returns a Window, a loop that takes the next keyset position from the last element, and a native row-value query fallback that relies on the composite index noted in the comments.

Designing API cursors, backward navigation and mixed sort directions

The API should never hand clients raw column values such as ?after_created=...&after_id=.... Raw values lock your schema into the contract and let clients craft arbitrary predicates. Instead, serialize the last row's sort keys together with a format version, the sort specification, and a hash of the active filters. Sign the result with an HMAC and Base64URL-encode it. On each request, verify the signature, the version, and that the sort and filter hash match the current request. Reject any mismatch with a 400 rather than guessing. This catches tampered tokens, and it also catches cursors that were issued under an older sort order. Responses carry a next cursor, plus previous if you support going back. They do not carry page numbers or totals, which keyset pagination cannot produce cheaply.

Backward navigation

To page backward, flip each comparison and each ORDER BY direction. Fetch limit rows, then reverse the list in memory so the client still sees the canonical order. In the Scroll API, a backward keyset position produces this inverted query for you, but you still need to check the ordering your endpoint returns.

Mixed directions and NULLs

With created_at DESC, id ASC you can no longer use a row-value comparison like (created_at, id) < (?, ?). You must expand it to created_at < ? OR (created_at = ? AND id > ?). Each added key adds another OR branch, and you need an index whose column directions match the sort.

NULLs are worse. A comparison against NULL evaluates to unknown, so those rows silently disappear. The default placement of NULLs also varies by database: PostgreSQL sorts them last in ascending order, MySQL first. The simplest fix is to make sort columns NOT NULL. If you can't, use explicit NULLS FIRST/LAST together with separate IS NULL branches in the predicate.

Copyimport javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;
import java.nio.charset.StandardCharsets;
import java.security.GeneralSecurityException;
import java.security.MessageDigest;
import java.time.Instant;
import java.util.Base64;

// Opaque, versioned, HMAC-signed cursor. Map InvalidCursor to HTTP 400 in a controller advice.
public final class CursorCodec {
  public record Position(Instant createdAt, long id) {}
  public static final class InvalidCursor extends RuntimeException { InvalidCursor(String m) { super(m); } }

  private static final String VERSION = "v1"; // bump when the payload layout changes
  private final SecretKeySpec key;
  public CursorCodec(byte[] secret) { key = new SecretKeySpec(secret, "HmacSHA256"); }

  // sort e.g. "created_at:desc,id:asc"; filterHash = hex SHA-256 of the normalized filter params
  public String encode(String sort, String filterHash, Position p) {
    String payload = String.join("|", VERSION, sort, filterHash, p.createdAt().toString(), Long.toString(p.id()));
    byte[] body = payload.getBytes(StandardCharsets.UTF_8);
    Base64.Encoder b64 = Base64.getUrlEncoder().withoutPadding();
    return b64.encodeToString(body) + "." + b64.encodeToString(hmac(body));
  }

  public Position decode(String token, String sort, String filterHash) {
    String[] parts = token.split("\\.", -1);
    if (parts.length != 2) throw new InvalidCursor("malformed");
    byte[] body, sig;
    try { body = Base64.getUrlDecoder().decode(parts[0]); sig = Base64.getUrlDecoder().decode(parts[1]); }
    catch (IllegalArgumentException e) { throw new InvalidCursor("bad encoding"); }
    // Constant-time compare; any tampering with the sort keys fails here
    if (!MessageDigest.isEqual(sig, hmac(body))) throw new InvalidCursor("bad signature");
    String[] f = new String(body, StandardCharsets.UTF_8).split("\\|", -1);
    if (f.length != 5 || !VERSION.equals(f[0])) throw new InvalidCursor("unsupported version");
    // A cursor issued under another sort or filter set would skip or repeat rows: reject, don't guess
    if (!f[1].equals(sort) || !f[2].equals(filterHash)) throw new InvalidCursor("stale cursor");
    return new Position(Instant.parse(f[3]), Long.parseLong(f[4]));
  }

  private byte[] hmac(byte[] data) {
    try { Mac mac = Mac.getInstance("HmacSHA256"); mac.init(key); return mac.doFinal(data); }
    catch (GeneralSecurityException e) { throw new IllegalStateException(e); }
  }
}

A minimal cursor codec that binds the last row's sort keys to a format version, the sort specification and a filter hash, signs the payload with HMAC-SHA256, and rejects tampered, malformed or stale tokens so the endpoint can return 400.

When to use offset, when to use keyset, and when to avoid each

Offset fits bounded, admin-style data: back-office tables, moderation queues, or reports where filters keep the result set to a few thousand rows. In these screens users expect jump-to-page and a total count. At that size, the cost of skipping rows stays small, and the count query is usually acceptable.

Keyset fits anything that grows without a ceiling or gets read deeply. This includes activity feeds, infinite scroll, CSV exports, batch jobs that walk a whole table, and public APIs where clients will page to the end. With keyset, the cost of each page stays flat. A cursor also never skips or repeats a row when inserts land between requests.

Avoid keyset in four cases:

• Users need numbered pages. A cursor cannot express "page 7".
• Users can sort by any column. Each sort order needs its own composite index, and that set of indexes becomes hard to maintain.
• The sort key changes. If you sort on something like updated_at, a row can move behind the cursor and never appear.
• No index serves the comparison. If the sort columns are wrapped in functions such as lower(name) and no matching expression index exists, every page becomes a scan.

If you are stuck with offset, return a Slice so you skip the count query. If keyset suits the data but the UI still wants a total, show an approximate count from planner statistics or a cached value.

Who should use keyset pagination, and who should stay with offset

Keyset pagination is aimed at teams building Spring Data repositories over tables that are large or keep growing, where at least one consumer reads past the first few pages. In practice that means:

• Backend engineers exposing public or partner APIs. External clients often page to the end of a collection, and they need a stable sequence that does not skip or duplicate rows while data changes.
• Engineers writing batch jobs, exports and sync processes. A job that walks a whole table should use a cursor on the primary key or a creation timestamp plus ID. Offset makes each later batch slower and can miss rows that are inserted or deleted mid-run.
• Teams building feeds and infinite-scroll UIs. These screens only need "next," which is exactly what a cursor provides.

Offset remains the better choice for teams whose data is small and bounded, whose users need numbered pages or a total, or who need many user-selectable sort orders without maintaining an index for each one. A team that cannot yet commit to a stable sort key and a matching composite index should also stay with offset (preferably returning Slice) until it can. Adding keyset without the index brings the extra complexity and none of the performance benefit.

Alternatives to offset and keyset pagination

Offset and keyset are not the only ways to deliver large result sets. These options suit specific situations:

• Streaming instead of paging. For internal batch processing that does not need to resume across requests, a Spring Data JPA repository method can return Stream<T>. The stream must be consumed inside a transaction and closed after use, for example with try-with-resources. Combined with a JDBC fetch size, this reads the result incrementally over one connection instead of issuing many page queries. It holds a connection and a transaction open for the whole run, so it does not fit HTTP APIs or very long jobs.
• Offset scrolling in the Scroll API. ScrollPosition.offset() gives the Window<T> programming model with offset semantics underneath. It is a reasonable stepping stone: code written against Window and ScrollPosition can switch to keyset positions later. It keeps offset's cost growth and instability under writes.
• Deferred join for deep offset pages. If you must keep page numbers, apply LIMIT/OFFSET in a subquery that selects only primary keys from a covering index, then join back to the table for the full rows. The database still skips rows, but it skips narrow index entries instead of full rows. This needs a native query and a covering index, and it does not fix duplicates or gaps under concurrent writes.
• Range or time-window pagination. For time-series or log-style data, page by explicit ranges such as one day or one hour at a time, or by ID ranges for batch jobs. Each request is an indexed range scan with clear boundaries. It works when the data distributes evenly enough that each range has a usable size.
• Snapshotting the result set. For exports or reports that must be consistent across many requests, materialize the matching IDs (or the full result) into a temporary or result table once, then page over that snapshot by its own sequence number. This trades storage and a cleanup job for a fixed, consistent result that tolerates any sort order.
• Limiting depth. Many interfaces do not need deep pages at all. Capping offset pagination at a maximum page and asking users to refine filters beyond it keeps offset's simplicity while bounding its worst case.

Verifying the choice with execution plans and measurements

That count is only an estimate. Before you commit to either strategy, confirm what the database actually does with the SQL Spring Data generates. Capture the real statements from the Hibernate SQL log. Don't rely on hand-written equivalents, because the Scroll API builds its own keyset predicate.

Reading the plans

Run each query through EXPLAIN ANALYZE (in PostgreSQL, add BUFFERS). For the offset query, an index scan under a Limit node can look healthy. Check the actual rows the scan produced: it reads and discards every skipped row, so the count grows with the offset.

For the keyset query, look for an index scan whose index condition contains the cursor comparison. The scan should return roughly one page of rows, with no sort node above it. A sort node or a sequential scan means the index column order doesn't match the ORDER BY. That usually happens with a missing tiebreaker column or mixed sort directions the index can't serve.

Measuring at depth

Seed a copy with production-like row counts and value distributions. Then measure p50 and p95 latency at the first page, a middle page and a deep page, both through the endpoint and directly in SQL. Test with a warm cache and a cold cache, since deep offset pages often fall out of memory.

Worked example (illustrative estimate, not a measured result)

Assume 50 million rows, about 200 bytes per row (roughly 40 rows per 8 KB page) and a page size of 50.

At page 100,000, the offset query reads about 1 GB when the pages aren't cached. Keyset stays at a few pages at every depth.

Impact on development cost and engineering time

That gap only matters if someone actually reaches deep pages, so weigh it against what each approach costs the team to build and maintain.

Offset is cheap to build. A Pageable parameter, a page number in the URL, and tests that assert page n returns a known slice of seeded rows are enough. Anyone on the team can reason about it. For a table of a few thousand rows, or a UI where users rarely go past page five, the database cost is noise.

Keyset has ongoing costs that offset avoids:

The savings come later. Without keyset, deep-page queries get rewritten once a table grows. Consumers and batch jobs that page with offset silently skip or reprocess rows while writes are happening, and those bugs are expensive to find. Count queries end up needing caches or approximations.

To keep the costs bounded, support keyset for a small, fixed set of sort orders, such as creation time plus ID. Each supported order then has a known index and cursor format. Reject arbitrary sort parameters on keyset endpoints instead of trying to support them.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to spring data keyset pagination, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.