JPA keyset pagination untuk large table

E-commerce dengan tabel order 100 juta row — pagination pakai OFFSET 95000 bikin Postgres scan 95rb row dulu, baru skip. Keyset (cursor) pagination jump langsung ke target via index. Snippet ini implementasi di Spring Boot dengan dukungan Window API + repository custom.

Kode

// PesananEntity.java
package id.kodekarawaci.entity;

import java.time.Instant;
import jakarta.persistence.*;

@Entity
@Table(name = "pesanan", indexes = {
    @Index(name = "idx_pesanan_created_id", columnList = "created_at DESC, id DESC"),
    @Index(name = "idx_pesanan_user_created", columnList = "user_id, created_at DESC, id DESC")
})
public class PesananEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private Long userId;

    @Column(name = "total", nullable = false)
    private Long total;

    @Column(nullable = false)
    private String status;

    @Column(name = "created_at", nullable = false)
    private Instant createdAt;

    // Getters & setters
    public Long getId() { return id; }
    public Long getUserId() { return userId; }
    public Long getTotal() { return total; }
    public String getStatus() { return status; }
    public Instant getCreatedAt() { return createdAt; }
}

// KeysetCursor.java — wrapping cursor (encode untuk URL-safe)
package id.kodekarawaci.repository;

import java.time.Instant;
import java.util.Base64;
import com.fasterxml.jackson.databind.ObjectMapper;

public record KeysetCursor(Instant createdAt, Long id) {

    private static final ObjectMapper MAPPER = new ObjectMapper().findAndRegisterModules();

    public String encode() {
        try {
            byte[] json = MAPPER.writeValueAsBytes(this);
            return Base64.getUrlEncoder().withoutPadding().encodeToString(json);
        } catch (Exception e) {
            throw new RuntimeException("Encode cursor fail", e);
        }
    }

    public static KeysetCursor decode(String token) {
        try {
            byte[] json = Base64.getUrlDecoder().decode(token);
            return MAPPER.readValue(json, KeysetCursor.class);
        } catch (Exception e) {
            throw new IllegalArgumentException("Cursor tidak valid", e);
        }
    }
}

// PesananRepository.java
package id.kodekarawaci.repository;

import id.kodekarawaci.entity.PesananEntity;

import java.time.Instant;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface PesananRepository extends JpaRepository<PesananEntity, Long> {

    /**
     * Pagination keyset — sort by (created_at DESC, id DESC).
     * Cursor: (lastCreatedAt, lastId) dari halaman sebelumnya.
     */
    @Query("""
        SELECT p FROM PesananEntity p
        WHERE (:lastCreatedAt IS NULL)
           OR (p.createdAt < :lastCreatedAt)
           OR (p.createdAt = :lastCreatedAt AND p.id < :lastId)
        ORDER BY p.createdAt DESC, p.id DESC
        """)
    List<PesananEntity> findKeysetPage(
        @Param("lastCreatedAt") Instant lastCreatedAt,
        @Param("lastId") Long lastId,
        org.springframework.data.domain.Limit limit
    );

    @Query("""
        SELECT p FROM PesananEntity p
        WHERE p.userId = :userId
          AND ((:lastCreatedAt IS NULL)
            OR (p.createdAt < :lastCreatedAt)
            OR (p.createdAt = :lastCreatedAt AND p.id < :lastId))
        ORDER BY p.createdAt DESC, p.id DESC
        """)
    List<PesananEntity> findByUserKeysetPage(
        @Param("userId") Long userId,
        @Param("lastCreatedAt") Instant lastCreatedAt,
        @Param("lastId") Long lastId,
        org.springframework.data.domain.Limit limit
    );
}

// PesananService.java
package id.kodekarawaci.service;

import id.kodekarawaci.entity.PesananEntity;
import id.kodekarawaci.repository.KeysetCursor;
import id.kodekarawaci.repository.PesananRepository;

import java.util.List;

import org.springframework.data.domain.Limit;
import org.springframework.stereotype.Service;

@Service
public class PesananService {

    private final PesananRepository repo;

    public PesananService(PesananRepository repo) {
        this.repo = repo;
    }

    public record PageResult<T>(List<T> items, String nextCursor) {}

    public PageResult<PesananEntity> listPesanan(String cursorToken, int size) {
        int requestSize = Math.min(size, 100);
        KeysetCursor cursor = cursorToken == null ? null : KeysetCursor.decode(cursorToken);

        // Ambil size+1 untuk tahu ada halaman berikut
        List<PesananEntity> rows = repo.findKeysetPage(
            cursor == null ? null : cursor.createdAt(),
            cursor == null ? null : cursor.id(),
            Limit.of(requestSize + 1)
        );

        boolean hasNext = rows.size() > requestSize;
        List<PesananEntity> items = hasNext ? rows.subList(0, requestSize) : rows;

        String nextCursor = null;
        if (hasNext) {
            PesananEntity last = items.get(items.size() - 1);
            nextCursor = new KeysetCursor(last.getCreatedAt(), last.getId()).encode();
        }

        return new PageResult<>(items, nextCursor);
    }

    public PageResult<PesananEntity> listPesananByUser(Long userId, String cursorToken, int size) {
        int requestSize = Math.min(size, 100);
        KeysetCursor cursor = cursorToken == null ? null : KeysetCursor.decode(cursorToken);

        List<PesananEntity> rows = repo.findByUserKeysetPage(
            userId,
            cursor == null ? null : cursor.createdAt(),
            cursor == null ? null : cursor.id(),
            Limit.of(requestSize + 1)
        );

        boolean hasNext = rows.size() > requestSize;
        List<PesananEntity> items = hasNext ? rows.subList(0, requestSize) : rows;

        String nextCursor = null;
        if (hasNext) {
            PesananEntity last = items.get(items.size() - 1);
            nextCursor = new KeysetCursor(last.getCreatedAt(), last.getId()).encode();
        }

        return new PageResult<>(items, nextCursor);
    }
}

Pemakaian

// REST controller
@RestController
@RequestMapping("/api/pesanan")
public class PesananController {

    private final PesananService service;

    public PesananController(PesananService service) {
        this.service = service;
    }

    @GetMapping
    public PesananService.PageResult<PesananEntity> list(
        @RequestParam(required = false) String cursor,
        @RequestParam(defaultValue = "20") int size
    ) {
        return service.listPesanan(cursor, size);
    }

    @GetMapping("/user/{userId}")
    public PesananService.PageResult<PesananEntity> listByUser(
        @PathVariable Long userId,
        @RequestParam(required = false) String cursor,
        @RequestParam(defaultValue = "20") int size
    ) {
        return service.listPesananByUser(userId, cursor, size);
    }
}

# Page 1
curl http://localhost:8080/api/pesanan?size=20
# {
#   "items": [...],
#   "nextCursor": "eyJjcmVhdGVkQXQi..."
# }

# Page 2 — pakai cursor dari response sebelumnya
curl http://localhost:8080/api/pesanan?size=20&cursor=eyJjcmVhdGVkQXQi...

# Page X — latency tetap ~30ms berapapun deep-nya

Kapan dipakai

• API yang serve list panjang (riwayat order, riwayat transaksi).
• Mobile app sync — cursor stabil walau data terus bertambah.
• Export pagination batch — jangan andalkan OFFSET di tabel besar.
• Dashboard reporting yang query 100rb+ row.

Catatan

• Index harus match ORDER BY — (created_at DESC, id DESC) di index match query. Kalau ASC vs DESC tidak match, Postgres bisa fallback ke scan + sort.
• Composite cursor wajib kalau sort kolom bisa duplikat. Tanpa id tiebreaker, row dengan timestamp sama bisa skip / duplicate.
• Limit class — Spring Data 3.1+ punya Limit interface. Untuk versi lama, pakai Pageable + PageRequest.of(0, size, sort).
• Encode cursor — base64 + JSON cukup. Untuk security extra, sign dengan HMAC supaya tidak bisa di-tamper.
• Tidak bisa “loncat ke page 50” — keyset forward-only (atau backward dengan logic mirror). Untuk jump pagination admin, pakai OFFSET dengan limit.

Hibernate 6.5+ punya native ScrollableResults dengan keyset. Untuk migrasi besar, refactor ke Window<T> API lebih clean dari custom @Query.