Real World Example

OFFSET paging gets slower on deep pages: limit 100000,20 makes the database read and throw away 1 lakh rows. Feeds (Instagram, Twitter/X, Flipkart reviews) use keyset (seek) pagination: "give me 20 rows older than the last one I saw". ShopEase uses it for product reviews.

public interface ReviewRepository extends JpaRepository<Review, Long> {

    // first page
    List<ReviewView> findTop20ByProductIdOrderByIdDesc(Long productId);

    // next pages: everything older than the last id the client saw
    List<ReviewView> findTop20ByProductIdAndIdLessThanOrderByIdDesc(Long productId, Long lastSeenId);
}

@GetMapping("/api/products/{id}/reviews")
public ReviewPage reviews(@PathVariable Long id, @RequestParam(required = false) Long after) {
    List<ReviewView> rows = (after == null)
            ? reviews.findTop20ByProductIdOrderByIdDesc(id)
            : reviews.findTop20ByProductIdAndIdLessThanOrderByIdDesc(id, after);
    Long next = rows.size() == 20 ? rows.get(19).getId() : null;
    return new ReviewPage(rows, next);
}

-- uses the index (product_id, id) and touches only 20 rows, however deep the page
select r1_0.id,r1_0.rating,r1_0.comment,r1_0.author_name
from reviews r1_0
where r1_0.product_id=? and r1_0.id<?
order by r1_0.id desc
limit ?