Definition
- Offset pagination -- uses
LIMITandOFFSET(JPA'ssetMaxResultsandsetFirstResult) to skip N rows and return the next page, with cost growing linearly as the offset increases. - Keyset pagination -- also called seek or cursor pagination, uses a
WHEREpredicate on the last seen sort key (WHERE id > :lastId ORDER BY id LIMIT n) for constant-time page fetches. - Deep page degradation -- with offset, page 90,001 of a million-row table forces the database to scan and discard 900,000 rows before returning 10.
- Position instability -- offset anchors on row position, so concurrent inserts can cause the same row to appear on two pages or a row to be skipped entirely.
- Tiebreaker -- a unique column (usually the primary key) added to the ORDER BY clause to ensure deterministic ordering when the primary sort has ties.
At National Bank of India, Rajesh's bank statement has a million transactions. Offset pagination is like the clerk counting past the first 900,000 entries one by one every time Rajesh asks for page 90,001. Keyset pagination is like the clerk using a bookmark that says "last transaction you saw was number 900,000" and jumping straight to the next one. The bookmark approach is instant no matter how deep you go.
Understanding the Concepts
What is offset pagination?
Offset pagination uses LIMIT and OFFSET to skip a fixed number of rows and return the next page. In JPA, this is setFirstResult(offset) and setMaxResults(pageSize). It is simple to implement and supports jumping to any page number, but the cost grows linearly with the offset because the database must scan and discard all skipped rows.
package com.bank.service;
import com.bank.model.Transaction;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class OffsetPaginationService {
@PersistenceContext
private EntityManager em;
@Transactional(readOnly = true)
public List<Transaction> getStatementPage(
String accountId, int page, int size) {
return em.createQuery(
"SELECT t FROM Transaction t "
+ "WHERE t.accountId = :acct "
+ "ORDER BY t.createdAt DESC, t.id DESC",
Transaction.class
).setParameter("acct", accountId)
.setFirstResult(page * size) // 0-based offset
.setMaxResults(size)
.getResultList();
}
}
Page 0 (first page):
SQL: SELECT * FROM transactions
WHERE account_id = 'SAV2001'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0
Cost: scan 20 rows. Fast.
Page 90000 (deep page):
SQL: SELECT * FROM transactions
WHERE account_id = 'SAV2001'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 1800000
Cost: scan 1,800,000 rows, discard 1,799,980, return 20.
Slow! Time grows linearly with offset.
What is keyset (cursor/seek) pagination?
Keyset pagination remembers the sort key of the last row returned and uses a WHERE predicate to seek directly to the next page. With an index on the sort columns, this is an index seek with constant cost regardless of how deep you are in the result set.
package com.bank.service;
import com.bank.model.Transaction;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.Instant;
import java.util.List;
@Service
public class KeysetPaginationService {
@PersistenceContext
private EntityManager em;
@Transactional(readOnly = true)
public List<Transaction> getNextPage(
String accountId, Instant lastCreatedAt,
Long lastId, int size) {
if (lastCreatedAt == null) {
// First page: no cursor yet
return em.createQuery(
"SELECT t FROM Transaction t "
+ "WHERE t.accountId = :acct "
+ "ORDER BY t.createdAt DESC, t.id DESC",
Transaction.class
).setParameter("acct", accountId)
.setMaxResults(size)
.getResultList();
}
// Subsequent pages: seek from last seen key
return em.createQuery(
"SELECT t FROM Transaction t "
+ "WHERE t.accountId = :acct "
+ "AND (t.createdAt < :lastTs "
+ " OR (t.createdAt = :lastTs AND t.id < :lastId)) "
+ "ORDER BY t.createdAt DESC, t.id DESC",
Transaction.class
).setParameter("acct", accountId)
.setParameter("lastTs", lastCreatedAt)
.setParameter("lastId", lastId)
.setMaxResults(size)
.getResultList();
}
}
First page:
SQL: SELECT * FROM transactions
WHERE account_id = 'SAV2001'
ORDER BY created_at DESC, id DESC
LIMIT 20
Returns 20 rows. Last row: created_at='2024-01-10', id=54321
Next page (cursor = '2024-01-10', 54321):
SQL: SELECT * FROM transactions
WHERE account_id = 'SAV2001'
AND (created_at < '2024-01-10'
OR (created_at = '2024-01-10' AND id < 54321))
ORDER BY created_at DESC, id DESC
LIMIT 20
Index seek: jumps directly to the right position.
Cost: constant, regardless of how many rows came before.
How is offset pagination different from keyset pagination?
| Aspect | Offset Pagination | Keyset Pagination |
|---|---|---|
| SQL mechanism | LIMIT + OFFSET | WHERE key > :lastKey LIMIT |
| Cost of deep pages | O(offset) -- scans all skipped rows | O(1) -- index seek to last key |
| Random page access | Yes (jump to page 500) | No (forward/backward only) |
| Stability under inserts | Unstable (rows shift) | Stable (anchors on value) |
| Implementation complexity | Simple | Moderate (composite cursor) |
| UI pattern | Page numbers (1, 2, 3, ...) | Infinite scroll, Load More |
| Index requirement | Optional (helps performance) | Required on sort columns |
// Offset: simple but slow at depth
// Good for: admin dashboards, small result sets, page-number UIs
// Bad for: million-row tables, deep scrolling
// Keyset: fast at any depth
// Good for: infinite scroll, mobile statements, large exports
// Bad for: "jump to page 500" UIs (no random access)
When does OFFSET kill performance?
OFFSET kills performance on large tables with deep paging. The database must physically scan and discard every row before the offset, even if only 10 rows are returned. This is a common production incident with report tools and export features.
package com.bank.service;
import com.bank.model.Transaction;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class OffsetPerformanceDemo {
@PersistenceContext
private EntityManager em;
@Transactional(readOnly = true)
public List<Transaction> getDeepPage() {
return em.createQuery(
"SELECT t FROM Transaction t ORDER BY t.createdAt DESC",
Transaction.class
).setFirstResult(5_000_000)
.setMaxResults(20)
.getResultList();
// DB scans 5,000,000 rows, discards 4,999,980, returns 20.
// On a 10M row table: may take 30+ seconds!
}
}
Performance comparison on 10 million rows:
Offset page 1 (OFFSET 0): ~2 ms
Offset page 1000 (OFFSET 20000): ~50 ms
Offset page 50000 (OFFSET 1000000): ~2 seconds
Offset page 250000 (OFFSET 5000000): ~15 seconds
Offset page 500000 (OFFSET 10000000): ~30 seconds (timeout!)
Keyset at any depth: ~2 ms (constant)
What about position instability with offset?
Offset pagination anchors on row position, not row values. If new rows are inserted between two page requests, the same row can appear on two consecutive pages, or a row can be skipped entirely.
Scenario: Priya views page 2 of her statement (20 per page)
Page 1: rows 1-20
Between page 1 and page 2 requests:
New transaction inserted at position 5
Page 2: OFFSET 20, LIMIT 20
Row 20 is now at position 21
Row 20 appears on BOTH page 1 and page 2! (duplicate)
Row 21 shifts to position 22 and is skipped.
Keyset pagination does not have this problem:
Page 1 returns transactions with id > X
Page 2 asks: WHERE id < lastSeenId
New inserts with id > lastSeenId do not affect page 2.
No duplicates, no skips.
What are common mistakes with pagination?
The most common mistakes involve missing tiebreakers, using offset on large tables, and paginating over JOIN FETCH queries.
// WRONG: no tiebreaker in ORDER BY
"SELECT t FROM Transaction t ORDER BY t.createdAt DESC"
// Two transactions at the same timestamp?
// Ordering is non-deterministic. Same row may appear on two pages.
// CORRECT: add a unique tiebreaker
"SELECT t FROM Transaction t ORDER BY t.createdAt DESC, t.id DESC"
// Even if createdAt is identical, id ensures deterministic order.
// WRONG: offset pagination on a 50M row ledger
query.setFirstResult(49_000_000).setMaxResults(20);
// Scans 49 million rows. Timeout!
// CORRECT: keyset pagination for deep scrolling
"WHERE createdAt < :lastTs OR (createdAt = :lastTs AND id < :lastId)"
// Index seek: constant time regardless of depth.
// WRONG: paginating a JOIN FETCH query
"SELECT a FROM Account a JOIN FETCH a.transactions"
// Page<Account> with Pageable
// JOIN multiplies rows. Hibernate paginates IN MEMORY.
// WARNING: HHH000104: firstResult/maxResults with collection fetch
// Loads ALL rows into memory, then paginates. OutOfMemoryError!
// CORRECT: paginate root entity, then fetch collections
"SELECT a FROM Account a WHERE a.status = 'ACTIVE'"
// Paginate accounts first, then batch-fetch transactions
How does Spring Data support keyset pagination?
Spring Data (since version 3.1) supports keyset scrolling through the ScrollPosition and Window APIs. For older versions, use custom queries with cursor parameters.
package com.bank.repository;
import com.bank.model.Transaction;
import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Window;
import org.springframework.data.jpa.repository.JpaRepository;
public interface TransactionRepository
extends JpaRepository<Transaction, Long> {
// Spring Data 3.1+ keyset scrolling
Window<Transaction> findByAccountId(
String accountId,
ScrollPosition position,
Sort sort
);
}
package com.bank.service;
import com.bank.model.Transaction;
import com.bank.repository.TransactionRepository;
import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Window;
import org.springframework.stereotype.Service;
@Service
public class KeysetScrollService {
private final TransactionRepository txnRepo;
public KeysetScrollService(TransactionRepository txnRepo) {
this.txnRepo = txnRepo;
}
public Window<Transaction> firstPage(String accountId) {
return txnRepo.findByAccountId(
accountId,
ScrollPosition.keyset(), // start from beginning
Sort.by(Sort.Direction.DESC, "createdAt", "id")
);
}
public Window<Transaction> nextPage(
String accountId, Window<Transaction> current) {
return txnRepo.findByAccountId(
accountId,
current.positionAt(current.size() - 1), // cursor
Sort.by(Sort.Direction.DESC, "createdAt", "id")
);
}
}
What about indexing for pagination?
Both pagination strategies benefit from proper indexes, but keyset pagination absolutely requires one. The index must cover the sort columns plus the WHERE clause filter.
Recommended index for keyset pagination:
CREATE INDEX idx_txn_keyset
ON transactions (account_id, created_at DESC, id DESC);
This index supports:
1. WHERE account_id = ? (equality filter)
2. ORDER BY created_at DESC, id DESC (sort)
3. Keyset seek: AND (created_at < ? OR (created_at = ? AND id < ?))
Without this index:
Keyset pagination falls back to sequential scan.
Performance advantage over offset disappears.
With this index:
Both first page and page 500,000 return in ~2ms.
Visual Overview
Offset pagination:
┌────────────────────────────────────────────────┐
│ Page 1: OFFSET 0, LIMIT 20 │
│ DB scans: [1..20] -> returns 20 rows. Fast. │
│ │
│ Page 90001: OFFSET 1800000, LIMIT 20 │
│ DB scans: [1..1800020] │
│ Discards: [1..1800000] │
│ Returns: [1800001..1800020] │
│ Slow! Scanned 1.8M rows for 20 results. │
└────────────────────────────────────────────────┘
Keyset pagination:
┌────────────────────────────────────────────────┐
│ Page 1: ORDER BY created_at DESC, id DESC │
│ LIMIT 20 │
│ Returns 20 rows. Last: ts=Jan10, id=54321 │
│ │
│ Page 2: WHERE (created_at < 'Jan10' │
│ OR (created_at = 'Jan10' AND id < 54321))│
│ ORDER BY created_at DESC, id DESC │
│ LIMIT 20 │
│ Index seek -> instant. Same speed at any depth. │
└────────────────────────────────────────────────┘
Performance graph (10M rows):
Time
30s │ ╱ Offset
│ ╱
15s │ ╱
│ ╱
2ms│────────────────────────────── Keyset (flat)
└──────────────────────────────────────
Page 1 1000 50000 250000 500000
Key Takeaway
There are two main pagination strategies and they scale very differently. Offset pagination uses LIMIT and OFFSET, which in JPA is setMaxResults and setFirstResult. It is simple and supports jumping to any page number, but the database must physically scan and discard every row you skip, so the cost grows linearly with page depth. For the first few pages it is fast, but deep into a large table it becomes a serious performance bottleneck.
Keyset pagination anchors on the sort key of the last row returned. The next page is a query with a WHERE clause like id less than the last seen id, ordered and limited. With an index on the sort columns, it is an index seek that returns a page in constant time regardless of how deep you are. The trade-off is that you cannot jump to an arbitrary page number, so it suits infinite scroll and next/previous navigation rather than page-number UIs.
In our National Bank of India net-banking statement, Rajesh may scroll through years of transactions. Offset paging is acceptable for the first few pages, but deep scrolling forces the database to scan hundreds of thousands of rows per page. Keyset pagination with a composite cursor on created_at and id, supported by a matching composite index, keeps every page fast no matter how far back he scrolls.
Remember one sentence: offset pagination scans and discards all skipped rows making deep pages slow, while keyset pagination seeks directly to the last seen key for constant-time pages at any depth.
---