Part 034 — Pagination, Sorting, and Filtering

Pagination, sorting, dan filtering terlihat seperti fitur API biasa.

Namun di persistence layer enterprise, tiga hal ini adalah sumber utama:

• slow query
• full table scan
• unstable result order
• duplicate/missing rows antar page
• count query mahal
• SQL injection via dynamic sort
• inconsistent API contract
• memory pressure
• timeout
• data leakage jika tenant/soft-delete filter lupa

Core principle:

Pagination bukan sekadar LIMIT OFFSET. Pagination adalah kontrak antara API, query planner, index, consistency expectation, dan user experience.

Dalam sistem seperti CPQ, quote, order, catalog, pricing, billing, dan workflow enterprise, list/search endpoint biasanya cepat menjadi hot path.

Contoh endpoint:

GET /quotes?status=SUBMITTED&sort=createdAt,desc&page=3&size=50

Pertanyaan persistence engineer:

• Apakah order stabil?
• Apakah query tenant-scoped?
• Apakah soft-deleted row difilter?
• Apakah index mendukung filter + sort?
• Apakah count query mahal?
• Apakah page 3 akan tetap konsisten saat data berubah?
• Apakah dynamic sort aman dari SQL injection?
• Apakah query JPA menghasilkan SQL yang diharapkan?
• Apakah MyBatis dynamic SQL tetap readable?

1. Pagination sebagai API Contract

Pagination bukan hanya query implementation.

API harus menjelaskan:

• ukuran page maksimum
• default sort
• allowed sort fields
• filter semantics
• apakah total count dikembalikan
• apakah pagination offset/page-based atau cursor-based
• apakah result konsisten saat data berubah
• apakah cursor opaque
• apakah deleted/inactive/expired data disertakan

Contoh response offset pagination:

{
  "items": [],
  "page": 3,
  "size": 50,
  "totalItems": 1283,
  "totalPages": 26
}

Contoh cursor pagination:

{
  "items": [],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA3LTExVDEwOjAwOjAwWiIsImlkIjoiLi4uIn0=",
  "hasMore": true
}

Offset pagination cocok untuk UI page navigation.

Cursor/keyset pagination cocok untuk infinite scroll, export, event-like list, dan table besar.

2. Offset Pagination

Offset pagination memakai LIMIT dan OFFSET.

SELECT id, quote_number, status, created_at
FROM quote
WHERE tenant_id = :tenantId
ORDER BY created_at DESC, id DESC
LIMIT :limit
OFFSET :offset;

Keunggulan:

• mudah dipahami
• mudah implementasi
• mendukung jump to page
• umum untuk UI table

Risiko:

• large offset mahal
• result bisa bergeser saat data berubah
• perlu stable ordering
• count query sering mahal
• page dalam bisa timeout

Large offset problem:

LIMIT 50 OFFSET 500000;

Database tetap harus melewati banyak row sebelum mengambil 50 row.

Offset pagination cocok jika:

• dataset kecil/menengah
• user butuh page number
• filter cukup selektif
• offset dibatasi
• index mendukung order/filter

Offset pagination berbahaya jika:

• table sangat besar
• user bisa lompat ke page sangat jauh
• sort field tidak terindeks
• data sering berubah
• endpoint dipakai export massal

3. Stable Ordering

Pagination tanpa stable ordering adalah bug.

Bad:

SELECT *
FROM quote
WHERE tenant_id = :tenantId
LIMIT 50 OFFSET 0;

Database tidak menjamin urutan row tanpa ORDER BY.

Masih kurang aman:

ORDER BY created_at DESC

Jika banyak row punya created_at sama, urutan antar page bisa tidak stabil.

Better:

ORDER BY created_at DESC, id DESC

id menjadi tie-breaker.

Rule:

Semua query paginated harus punya deterministic ORDER BY dengan tie-breaker unique atau near-unique.

Contoh sorting umum:

ORDER BY updated_at DESC, id DESC
ORDER BY quote_number ASC, id ASC
ORDER BY status ASC, created_at DESC, id DESC

Review question:

If two rows have the same sort value, is the next ordering column deterministic?

4. Keyset Pagination

Keyset pagination mengambil page berikutnya berdasarkan nilai terakhir dari page sebelumnya.

First page:

SELECT id, quote_number, status, created_at
FROM quote
WHERE tenant_id = :tenantId
ORDER BY created_at DESC, id DESC
LIMIT :limit;

Next page:

SELECT id, quote_number, status, created_at
FROM quote
WHERE tenant_id = :tenantId
  AND (
        created_at < :lastCreatedAt
        OR (created_at = :lastCreatedAt AND id < :lastId)
      )
ORDER BY created_at DESC, id DESC
LIMIT :limit;

Keunggulan:

• lebih cepat untuk dataset besar
• tidak membayar biaya offset besar
• lebih stabil untuk infinite scroll
• cocok untuk export incremental

Trade-off:

• tidak mudah jump to arbitrary page
• cursor harus membawa sort key
• implementation lebih kompleks
• dynamic sorting lebih sulit

Keyset pagination sangat cocok untuk:

• audit log
• event list
• quote/order list besar
• notification list
• export chunking
• reconciliation jobs

5. Cursor Pagination

Cursor pagination adalah API-level abstraction di atas keyset atau continuation token.

Cursor sebaiknya opaque bagi client.

Isi cursor internal bisa berupa:

{
  "tenantId": "...",
  "sort": "createdAt:desc,id:desc",
  "lastCreatedAt": "2026-07-11T10:00:00Z",
  "lastId": "...",
  "filtersHash": "..."
}

Lalu di-encode:

base64url(json + signature)

Cursor harus mempertahankan:

• sort fields
• last seen values
• filter identity/hash
• direction
• tenant context jika perlu validasi
• expiry jika relevan

Security concern:

• jangan expose raw sensitive filter
• jangan percaya cursor tanpa validation/signature
• jangan izinkan cursor tenant A dipakai tenant B
• jangan biarkan client mengubah sort/filter via cursor tampering

6. Filtering Semantics

Filtering harus explicit.

Contoh filter:

GET /quotes?status=SUBMITTED&customerId=...&createdFrom=2026-01-01&createdTo=2026-02-01

SQL:

SELECT id, quote_number, status, customer_id, created_at
FROM quote
WHERE tenant_id = :tenantId
  AND status = :status
  AND customer_id = :customerId
  AND created_at >= :createdFrom
  AND created_at < :createdTo
ORDER BY created_at DESC, id DESC
LIMIT :limit OFFSET :offset;

Filtering review questions:

• Apakah field filter valid dan whitelisted?
• Apakah filter nullable punya semantics jelas?
• Apakah empty string sama dengan null?
• Apakah date range inclusive/exclusive?
• Apakah timezone jelas?
• Apakah filter tenant/soft-delete/effective-date selalu ada?
• Apakah index mendukung filter utama?
• Apakah kombinasi filter menyebabkan query plan buruk?

Date range best practice:

created_at >= :fromInclusive
AND created_at < :toExclusive

Bukan:

created_at BETWEEN :from AND :to

Karena BETWEEN inclusive di kedua sisi dan sering menciptakan bug boundary timestamp.

7. Dynamic Sorting dan SQL Injection Risk

Parameter binding tidak bisa dipakai untuk identifier seperti column name.

Ini salah:

ORDER BY #{sortColumn}

Ini bisa menjadi string literal, bukan column.

Ini berbahaya:

ORDER BY ${sortColumn}

Karena ${} melakukan string substitution.

Jika sortColumn berasal dari request, ini membuka SQL injection.

Solusi: whitelist mapping.

Map<String, String> SORT_COLUMNS = Map.of(
    "createdAt", "q.created_at",
    "updatedAt", "q.updated_at",
    "quoteNumber", "q.quote_number",
    "status", "q.status"
);

String sortColumn = SORT_COLUMNS.get(request.sortBy());
if (sortColumn == null) {
    throw new BadRequestException("Unsupported sort field");
}

Direction juga whitelist:

String direction = switch (request.direction()) {
    case ASC -> "ASC";
    case DESC -> "DESC";
};

Final SQL fragment hanya dari server-controlled values:

ORDER BY q.created_at DESC, q.id DESC

Rule:

Dynamic ORDER BY boleh memakai string substitution hanya jika semua fragment berasal dari whitelist server-side, bukan raw client input.

8. MyBatis Pagination

Contoh MyBatis mapper untuk offset pagination:

<select id="searchQuotes" resultMap="QuoteSummaryResultMap">
  SELECT
      q.id,
      q.quote_number,
      q.status,
      q.created_at,
      q.updated_at
  FROM quote q
  WHERE q.tenant_id = #{tenantId}
    AND q.deleted_at IS NULL
    <if test="status != null">
      AND q.status = #{status}
    </if>
    <if test="customerId != null">
      AND q.customer_id = #{customerId}
    </if>
    <if test="createdFrom != null">
      AND q.created_at &gt;= #{createdFrom}
    </if>
    <if test="createdTo != null">
      AND q.created_at &lt; #{createdTo}
    </if>
  ORDER BY ${orderByClause}
  LIMIT #{limit}
  OFFSET #{offset}
</select>

orderByClause harus hasil whitelist server-side.

Contoh parameter object:

record QuoteSearchQuery(
    UUID tenantId,
    QuoteStatus status,
    UUID customerId,
    Instant createdFrom,
    Instant createdTo,
    int limit,
    int offset,
    String orderByClause
) {}

Lebih aman jika mapper tidak menerima raw request DTO.

Bad:

quoteMapper.search(requestDto);

Better:

QuoteSearchQuery query = quoteSearchQueryFactory.from(request, tenantScope);
quoteMapper.searchQuotes(query);

Factory bertugas:

• validate page size
• validate sort field
• build order by clause
• normalize date range
• attach tenant scope
• enforce deleted/effective filters

9. MyBatis Keyset Pagination

MyBatis keyset query:

<select id="findNextQuotes" resultMap="QuoteSummaryResultMap">
  SELECT
      q.id,
      q.quote_number,
      q.status,
      q.created_at
  FROM quote q
  WHERE q.tenant_id = #{tenantId}
    AND q.deleted_at IS NULL
    <if test="status != null">
      AND q.status = #{status}
    </if>
    <if test="lastCreatedAt != null and lastId != null">
      AND (
        q.created_at &lt; #{lastCreatedAt}
        OR (q.created_at = #{lastCreatedAt} AND q.id &lt; #{lastId})
      )
    </if>
  ORDER BY q.created_at DESC, q.id DESC
  LIMIT #{limit}
</select>

Important:

• cursor predicate harus cocok dengan ORDER BY
• direction harus konsisten
• tie-breaker harus ikut cursor
• index harus mendukung predicate/order

Index:

CREATE INDEX idx_quote_tenant_created_id
ON quote (tenant_id, created_at DESC, id DESC)
WHERE deleted_at IS NULL;

10. JPA Pagination

JPA pagination umum:

TypedQuery<QuoteEntity> query = entityManager.createQuery("""
    select q
    from QuoteEntity q
    where q.tenantId = :tenantId
      and q.deletedAt is null
      and (:status is null or q.status = :status)
    order by q.createdAt desc, q.id desc
""", QuoteEntity.class);

query.setParameter("tenantId", tenantId);
query.setParameter("status", status);
query.setFirstResult(offset);
query.setMaxResults(limit);

List<QuoteEntity> result = query.getResultList();

Risiko JPQL pattern:

(:status is null or q.status = :status)

Pattern ini convenient, tetapi kadang membuat query planner sulit memilih plan optimal dibanding dynamic query yang hanya memasukkan predicate saat ada filter.

Untuk search kompleks, pertimbangkan:

• Criteria API
• query builder internal
• MyBatis untuk SQL eksplisit
• native query untuk PostgreSQL-specific search
• dedicated read model

JPA pagination juga harus hati-hati dengan fetch join collection.

Bad smell:

select q
from QuoteEntity q
join fetch q.items
order by q.createdAt desc

Dengan pagination, collection fetch join dapat menyebabkan duplicate row dan pagination yang salah.

Lebih aman:

1. page ids dulu
2. fetch detail by ids
3. preserve ordering

11. Criteria API untuk Dynamic Filtering

Criteria API berguna saat filter banyak dan optional.

Pseudo-code:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<QuoteEntity> cq = cb.createQuery(QuoteEntity.class);
Root<QuoteEntity> q = cq.from(QuoteEntity.class);

List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(q.get("tenantId"), tenantId));
predicates.add(cb.isNull(q.get("deletedAt")));

if (status != null) {
    predicates.add(cb.equal(q.get("status"), status));
}

if (createdFrom != null) {
    predicates.add(cb.greaterThanOrEqualTo(q.get("createdAt"), createdFrom));
}

cq.where(predicates.toArray(Predicate[]::new));
cq.orderBy(cb.desc(q.get("createdAt")), cb.desc(q.get("id")));

Keunggulan:

• type-ish safe dibanding string JPQL
• dynamic predicate lebih terstruktur
• sort whitelist bisa dikontrol

Risiko:

• verbose
• generated SQL perlu dicek
• mudah menjadi abstraction terlalu kompleks
• tidak selalu cocok untuk PostgreSQL-specific query

12. Count Query

Offset pagination sering butuh total count.

SELECT count(*)
FROM quote q
WHERE q.tenant_id = :tenantId
  AND q.deleted_at IS NULL
  AND q.status = :status;

Count query bisa lebih mahal daripada data query.

Masalah umum:

• count dengan join kompleks
• count dengan dynamic filter tidak terindeks
• count pada table besar
• count untuk setiap request padahal UI tidak butuh exact total
• count query JPA berbeda filter dengan data query

Alternatif:

• return hasMore saja
• approximate count
• count async/cached
• cap count sampai batas tertentu
• only count on first page
• require narrower filter untuk count

Pattern limit + 1 untuk hasMore:

SELECT id, quote_number, status, created_at
FROM quote
WHERE tenant_id = :tenantId
ORDER BY created_at DESC, id DESC
LIMIT :limitPlusOne;

Jika result size > requested limit, berarti ada next page.

13. Filtering dan Index Design

Query:

SELECT id, quote_number, status, created_at
FROM quote
WHERE tenant_id = :tenantId
  AND status = :status
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index:

CREATE INDEX idx_quote_tenant_status_created_id_active
ON quote (tenant_id, status, created_at DESC, id DESC)
WHERE deleted_at IS NULL;

Query:

SELECT id, quote_number, status, updated_at
FROM quote
WHERE tenant_id = :tenantId
  AND customer_id = :customerId
ORDER BY updated_at DESC, id DESC
LIMIT 50;

Index:

CREATE INDEX idx_quote_tenant_customer_updated_id
ON quote (tenant_id, customer_id, updated_at DESC, id DESC);

Index design harus mempertimbangkan:

• equality predicates dulu
• range predicates
• order by fields
• partial index untuk soft delete/status tertentu
• tenant ID
• cardinality
• write overhead
• index bloat

Tidak semua kombinasi filter harus punya index sendiri. Prioritaskan access pattern utama dan observability evidence.

14. Filtering dengan JSONB

Jika field filter berada dalam JSONB:

SELECT id, quote_number, attributes
FROM quote
WHERE tenant_id = :tenantId
  AND attributes ->> 'channel' = :channel
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pertimbangkan expression index:

CREATE INDEX idx_quote_tenant_channel_created
ON quote (tenant_id, (attributes ->> 'channel'), created_at DESC, id DESC);

Atau GIN index untuk containment:

CREATE INDEX idx_quote_attributes_gin
ON quote USING gin (attributes jsonb_path_ops);

Risiko JSONB filtering:

• type ambiguity
• missing key semantics
• query plan tidak sesuai ekspektasi
• expression index tidak dipakai karena query expression berbeda
• sulit enforce constraint dibanding column normal

Review question:

Is this JSONB attribute now important enough to become a real column?

15. Sorting by Derived or Joined Fields

Sorting by joined field:

SELECT q.id, q.quote_number, c.name AS customer_name
FROM quote q
JOIN customer c
  ON c.tenant_id = q.tenant_id
 AND c.id = q.customer_id
WHERE q.tenant_id = :tenantId
ORDER BY c.name ASC, q.id ASC
LIMIT 50;

Risiko:

• join cost tinggi
• sort memory besar
• index lintas table tidak ada
• pagination tidak stabil jika customer name berubah
• count query makin mahal

Alternatif:

• denormalized read model
• materialized/search table
• precomputed sortable field
• restrict allowed sorts
• async search index jika justified

Jangan otomatis expose semua column sebagai sortable field.

16. Sorting by Nullable Field

Nullable sort harus eksplisit.

ORDER BY approved_at DESC NULLS LAST, id DESC

Tanpa NULLS FIRST/LAST, behavior bisa membingungkan dan berbeda antar DB.

API contract harus menjelaskan:

For approvedAt descending, unapproved quotes appear last.

Keyset pagination dengan nullable field lebih kompleks karena cursor predicate harus handle null.

Jika sort field nullable dan pagination harus scalable, pertimbangkan default sort non-null seperti created_at, id.

17. Page Size Limits

Jangan percaya size dari client.

Bad:

GET /quotes?size=100000

Service harus enforce:

int size = Math.min(requestedSize, MAX_PAGE_SIZE);

Atau reject:

if (requestedSize > MAX_PAGE_SIZE) {
    throw new BadRequestException("Page size too large");
}

Typical rules:

• default size: 20/50/100 tergantung use case
• max UI size: 100/200
• export endpoint terpisah
• streaming/chunking untuk data besar
• timeout-aware search

Review question:

Can a client turn this endpoint into a bulk export accidentally?

18. Pagination Consistency During Writes

Data berubah saat user membuka page.

Offset pagination problem:

Page 1 returns A, B, C
New row X inserted at top
Page 2 with OFFSET 3 returns C, D, E

Row C muncul dua kali.

Atau row bisa terlewat.

Mitigasi:

• keyset pagination
• stable sort + tie-breaker
• snapshot timestamp filter
• cursor carrying boundary
• UI acceptance that list is eventually changing
• search result materialization untuk workflow tertentu

Untuk business-critical review list, pertimbangkan query berdasarkan snapshot:

WHERE created_at <= :snapshotTime
ORDER BY created_at DESC, id DESC

Cursor menyimpan snapshotTime agar page berikutnya konsisten terhadap snapshot tersebut.

19. Pagination dan Transaction Boundary

List query biasanya read-only transaction pendek.

Jangan membuka transaction panjang hanya untuk pagination UI.

Risiko transaction panjang:

• connection held longer
• MVCC bloat pressure
• lock wait jika ada query locking
• timeout
• pool exhaustion

Untuk streaming/export, transaction bisa lebih panjang karena cursor butuh connection terbuka. Itu harus menjadi endpoint/job yang berbeda dari normal paginated list.

Rule:

Normal pagination should be short-lived and bounded. Large traversal should be chunked, cursor-based, and operationally controlled.

20. Pagination dan Microservices/Event-Driven Architecture

Dalam microservices, list API sebaiknya hanya query data milik service sendiri.

Bad:

Quote service list endpoint joins order service database table

Better:

• quote service punya read model sendiri
• event dari order service memperbarui projection
• eventual consistency dijelaskan di API/UI

Untuk event-driven read model, pagination harus mempertimbangkan:

• projection lag
• duplicate event handling
• idempotent updates
• reordering event
• rebuild projection
• cursor compatibility after rebuild

Search/list endpoint sering menjadi alasan orang melanggar service boundary. Jangan jadikan pagination sebagai pembenaran shared database anti-pattern.

21. Production Debugging: Slow Paginated Endpoint

Jika endpoint search/list lambat:

1. Ambil SQL aktual dari log/trace.
2. Pastikan tenant/soft-delete/effective filters ada.
3. Cek parameter page size dan offset.
4. Cek ORDER BY.
5. Cek count query terpisah.
6. Jalankan EXPLAIN/EXPLAIN ANALYZE di environment aman.
7. Cek index yang dipakai.
8. Cek sort method dan memory.
9. Cek row estimate vs actual.
10. Cek join/filter selectivity.
11. Cek JPA generated SQL atau MyBatis dynamic SQL branch.
12. Cek apakah client meminta page terlalu dalam.
13. Cek apakah endpoint dipakai untuk export.

Common fixes:

• add/adjust composite index
• reduce allowed sort fields
• cap page size/offset
• switch to keyset pagination
• remove expensive count
• split query into ids then detail
• create read model
• denormalize sort/filter field

22. PR Review Checklist

API Contract

• Apakah default sort jelas?
• Apakah sort fields di-whitelist?
• Apakah page size dibatasi?
• Apakah offset maksimal dibatasi?
• Apakah total count benar-benar dibutuhkan?
• Apakah cursor opaque dan tervalidasi?

Query Correctness

• Apakah query tenant-scoped?
• Apakah soft-deleted/expired data difilter?
• Apakah ORDER BY deterministic?
• Apakah tie-breaker unique ada?
• Apakah date range memakai inclusive/exclusive boundary yang jelas?

Performance

• Apakah index mendukung WHERE + ORDER BY?
• Apakah count query mahal?
• Apakah large offset mungkin terjadi?
• Apakah sorting by joined/derived field justified?
• Apakah query plan sudah dicek untuk filter utama?

MyBatis

• Apakah dynamic SQL readable?
• Apakah ${} hanya dipakai untuk whitelist fragment?
• Apakah mapper menerima query object, bukan raw request DTO?
• Apakah dynamic filter tidak menghilangkan mandatory predicate?

JPA/Hibernate

• Apakah pagination memakai fetch join collection?
• Apakah generated SQL dicek?
• Apakah query projection lebih tepat daripada entity loading?
• Apakah Criteria API tidak menyembunyikan plan buruk?

Security/Privacy

• Apakah sort/filter raw input tidak masuk SQL?
• Apakah logs meredact sensitive parameters?
• Apakah admin/export query punya permission dan audit?

Testing

• Apakah test mencakup stable ordering?
• Apakah test mencakup duplicate sort value?
• Apakah test mencakup invalid sort field?
• Apakah test mencakup max page size?
• Apakah test mencakup SQL injection attempt di sort/filter?

23. Internal Verification Checklist

Gunakan checklist ini di codebase/team:

• Apa pagination convention internal: page/size, offset/limit, cursor, atau campuran?
• Apakah ada standard max page size?
• Apakah ada allowed sort field registry?
• Apakah API list/search wajib punya default sort?
• Apakah query pagination wajib tenant-scoped?
• Apakah soft delete/effective date filter wajib?
• Apakah count query otomatis atau optional?
• Apakah MyBatis mapper memakai ${} untuk ORDER BY?
• Apakah order fragment dibangun dari whitelist?
• Apakah JPA query pagination pernah memakai fetch join collection?
• Apakah ada endpoint list yang dipakai sebagai export?
• Apakah ada slow query dari endpoint pagination?
• Apakah index mendukung filter/sort utama?
• Apakah EXPLAIN plan disimpan di PR/ADR untuk query kritikal?
• Apakah cursor format disign/encrypted/validated?
• Apakah pagination read model lintas service melanggar ownership database?

24. Senior Engineer Mental Model

Pagination yang baik memenuhi empat constraint sekaligus:

Correctness:
  deterministic order, no missing tenant/soft-delete filter, clear time semantics

Performance:
  bounded result, index-supported, no unbounded offset/count surprise

Security:
  whitelist sort/filter, no raw SQL injection, no data leakage

Operability:
  observable SQL, debuggable plan, clear API contract, safe failure mode

Jangan review pagination hanya dari sisi “apakah endpoint jalan”.

Review sebagai production access pattern:

• seberapa besar table ini dalam 6 bulan?
• apa query plan pada tenant besar?
• apa yang terjadi saat user membuka page 5000?
• apakah sort field bisa dimanipulasi?
• apakah count query lebih mahal dari data query?
• apakah MyBatis/JPA menyembunyikan SQL yang buruk?

Core closing principle:

List endpoint adalah query product. Treat it like a production workload, not a UI convenience.