Spring에서 페이징 API를 설계할 때 고민한 것들

@GetMapping("/orders")
public ResponseEntity<Page<OrderSummaryResponse>> getOrders(Pageable pageable) {
    return ResponseEntity.ok(orderQueryService.getOrders(pageable));
}

public Page<OrderSummaryResponse> getOrders(Pageable pageable) {
    // 1) content 쿼리 + 2) count(*) 쿼리, 총 2번 실행된다
    return orderRepository.findAllByOrderByCreatedAtDesc(pageable)
        .map(OrderSummaryResponse::from);
}

-- 1) content: 실제로 보여줄 한 페이지
SELECT *
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;
 
-- 2) count: 전체 페이지 수를 계산하기 위한 별도 쿼리
SELECT count(*)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'PAID';

-- 100,000번째부터 20건을 보려면
-- DB는 앞의 100,000행을 읽어서 버린 뒤 그다음 20행을 돌려준다
SELECT *
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

public Page<OrderSummaryResponse> getOrders(OrderSearchCond cond, Pageable pageable) {
    List<OrderSummaryResponse> content = orderRepository.search(cond, pageable);
 
    // count 쿼리를 항상 실행하지 않고, 마지막 페이지가 아닐 때는 추정값을 쓴다
    JPAQuery<Long> countQuery = orderRepository.countQuery(cond);
 
    // content 크기가 size보다 작으면 굳이 count를 다시 세지 않는다
    return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
}

// Slice는 count(*) 를 실행하지 않는다
Slice<OrderSummary> findByStatusOrderByCreatedAtDesc(OrderStatus status, Pageable pageable);

public SliceResponse<OrderSummaryResponse> getFeed(OrderStatus status, Pageable pageable) {
    Slice<OrderSummary> slice =
        orderRepository.findByStatusOrderByCreatedAtDesc(status, pageable);
 
    return new SliceResponse<>(
        slice.map(OrderSummaryResponse::from).getContent(),
        slice.getNumber(),
        slice.getSize(),
        slice.hasNext()
    );
}

-- before: offset 기반 (앞 행을 읽고 버린다)
SELECT *
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;
 
-- after: cursor(키셋) 기반 (마지막으로 본 (created_at, id) 다음부터)
SELECT *
FROM orders
WHERE status = 'PAID'
  AND (created_at, id) < (:lastCreatedAt, :lastId)
ORDER BY created_at DESC, id DESC
LIMIT 20;

public List<OrderSummary> findByCursor(OrderStatus status, OrderCursor cursor, int size) {
    return queryFactory
        .select(orderSummary(order))
        .from(order)
        .where(
            order.status.eq(status),
            cursorPredicate(cursor) // 첫 페이지면 null → 조건 미적용
        )
        .orderBy(order.createdAt.desc(), order.id.desc())
        .limit(size + 1) // 다음 페이지 존재 여부 판단용으로 한 건 더
        .fetch();
}
 
private BooleanExpression cursorPredicate(OrderCursor cursor) {
    if (cursor == null) {
        return null; // 첫 페이지
    }
    // (createdAt < c) OR (createdAt = c AND id < cId) — 튜플 비교를 풀어 쓴 형태
    return order.createdAt.lt(cursor.createdAt())
        .or(order.createdAt.eq(cursor.createdAt())
            .and(order.id.lt(cursor.id())));
}

SELECT o
FROM Order o
WHERE o.status = :status
  AND (o.createdAt < :lastCreatedAt
       OR (o.createdAt = :lastCreatedAt AND o.id < :lastId))
ORDER BY o.createdAt DESC, o.id DESC

public record CursorResponse<T>(
    List<T> content,
    String nextCursor,  // base64로 인코딩한 (createdAt, id), 없으면 null
    boolean hasNext
) {
}

public CursorResponse<OrderSummaryResponse> getFeed(
        OrderStatus status, String cursorToken, int size) {
 
    OrderCursor cursor = OrderCursor.decode(cursorToken); // 첫 요청이면 null
    List<OrderSummary> rows = orderRepository.findByCursor(status, cursor, size);
 
    boolean hasNext = rows.size() > size;
    List<OrderSummary> page = hasNext ? rows.subList(0, size) : rows;
 
    // 마지막 항목의 정렬 키를 그대로 다음 cursor로 인코딩한다
    String nextCursor = hasNext
        ? OrderCursor.from(page.get(page.size() - 1)).encode()
        : null;
 
    return new CursorResponse<>(
        page.stream().map(OrderSummaryResponse::from).toList(),
        nextCursor,
        hasNext
    );
}

public record OrderCursor(Instant createdAt, Long id) {
 
    public static OrderCursor from(OrderSummary row) {
        return new OrderCursor(row.createdAt(), row.id());
    }
 
    public String encode() {
        String raw = createdAt.toEpochMilli() + ":" + id;
        return Base64.getUrlEncoder().withoutPadding()
            .encodeToString(raw.getBytes(StandardCharsets.UTF_8));
    }
 
    public static OrderCursor decode(String token) {
        if (token == null || token.isBlank()) {
            return null; // 첫 페이지
        }
        String raw = new String(Base64.getUrlDecoder().decode(token), StandardCharsets.UTF_8);
        String[] parts = raw.split(":");
        return new OrderCursor(Instant.ofEpochMilli(Long.parseLong(parts[0])), Long.parseLong(parts[1]));
    }
}

public record OrderListRequest(
    @Min(0) int page,
    @Min(1) @Max(100) int size
) {
}

public record PageResponse<T>(
    List<T> content,
    int page,
    int size,
    long totalElements,
    int totalPages,
    boolean hasNext
) {
}

public record SliceResponse<T>(
    List<T> content,
    int page,
    int size,
    boolean hasNextPage
) {
}