Careeroza

Pagination and Efficient Queries

Never Return Everything

GET /api/products returning 200,000 rows will use huge memory and take seconds. Always paginate list endpoints.

@GetMapping("/api/products")
public Page<ProductDto> list(@RequestParam(required = false) String category,
                             @PageableDefault(size = 20, sort = "name") Pageable pageable) {
    return productService.search(category, pageable);
}

// repository: Spring Data builds LIMIT/OFFSET and a COUNT query
Page<Product> findByCategoryName(String category, Pageable pageable);

Example

curl -s "localhost:8080/api/products?category=mobiles&page=1&size=2&sort=price,desc"

Example output

{"content":[{"id":9,"name":"Galaxy S25","price":79999.00},{"id":3,"name":"Pixel 9","price":54999.00}],
 "page":{"size":2,"number":1,"totalElements":38,"totalPages":19}}

Tip: Cap the page size (spring.data.web.pageable.max-page-size=100) so nobody can ask for size=1000000. Pagination details are covered in the Hibernate & JPA material.

Offset vs Keyset (Seek) Pagination

OFFSET 100000 makes the database read and throw away 100,000 rows. For infinite-scroll feeds, use keyset pagination: "give me 20 items after the last id I saw".

Example

Both methods return the same page, but keyset touches far fewer rows on deep pages:

import java.util.*;
import java.util.stream.*;

public class Main {
    public static void main(String[] args) {
        List<Integer> ids = IntStream.rangeClosed(1, 100_000).boxed().toList();   // product ids, sorted
        int size = 5;

        // OFFSET: skip page * size rows
        int page = 10_000;
        int[] scanned = {0};
        List<Integer> offsetPage = ids.stream().peek(x -> scanned[0]++)
                .skip((long) page * size).limit(size).toList();
        System.out.println("offset page " + page + ": " + offsetPage + " rows read=" + scanned[0]);

        // KEYSET: WHERE id > lastSeenId ORDER BY id LIMIT 5 (an index jumps straight there)
        int lastSeenId = 50_000;
        int start = Collections.binarySearch(ids, lastSeenId) + 1;            // index lookup
        List<Integer> keysetPage = ids.subList(start, start + size);
        System.out.println("keyset after " + lastSeenId + ": " + keysetPage + " rows read=" + size);
    }
}

Output

offset page 10000: [50001, 50002, 50003, 50004, 50005] rows read=50005
keyset after 50000: [50001, 50002, 50003, 50004, 50005] rows read=5
@Query("select p from Product p where p.id > :afterId order by p.id")
List<Product> nextPage(@Param("afterId") Long afterId, Limit limit);   // Spring Data 3.2+: Limit.of(20)

Other Quick Wins

  • Return DTO projections instead of whole entities for lists.
  • Fix N+1 queries with JOIN FETCH or @EntityGraph (see Hibernate & JPA).
  • Add database indexes for columns used in WHERE and ORDER BY.
  • Turn on spring.jpa.properties.hibernate.generate_statistics=true in a test run to count queries per request.