Keyset Pagination vs Offset Pagination: tối ưu phân trang cho database lớn

Phân trang là phần nhìn có vẻ đơn giản trong API backend: thêm page, limit, hoặc offset là xong. Nhưng khi bảng tăng lên vài triệu đến vài trăm triệu dòng, cách phân trang có thể trở thành nguyên nhân làm API chậm, database đọc thừa quá nhiều row, user thấy dữ liệu nhảy trang và p95 latency tăng theo thời gian.

Bài này phân tích sâu keyset pagination vs offset pagination dưới góc nhìn database/backend production: khi nào offset đủ dùng, khi nào phải chuyển sang cursor/keyset, cần index thế nào, thiết kế API ra sao và checklist kiểm tra trước khi release.

Offset pagination là gì?

Offset pagination dùng công thức quen thuộc: bỏ qua N dòng rồi lấy M dòng tiếp theo. Ví dụ:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 20000;

API thường expose dạng ?page=1001&per_page=20 hoặc ?limit=20&offset=20000. Cách này dễ hiểu, dễ debug, hỗ trợ nhảy tới trang bất kỳ và đủ tốt với dataset nhỏ.

Vấn đề của offset khi dữ liệu lớn

1. Skip row không miễn phí

Với OFFSET 200000, database không teleport tới dòng 200001. Tùy execution plan, nó thường vẫn phải đọc/sắp xếp/đi qua rất nhiều entry trước khi bỏ chúng đi. Index có thể giúp ORDER BY, nhưng không làm chi phí skip biến mất hoàn toàn.

2. Latency tăng theo page number

Trang đầu nhanh, trang sâu chậm. Đây là pattern rất hay gặp ở admin dashboard, activity log, audit trail, transaction list hoặc search result có filter rộng. Nếu chỉ test với 10.000 row trên staging, team dễ bỏ sót vấn đề p95/p99 khi production có 50 triệu row.

3. Dữ liệu có thể bị nhảy hoặc trùng

Trong lúc user đang chuyển page, nếu có row mới được insert hoặc row cũ bị delete, offset có thể khiến item bị lặp hoặc bị bỏ qua. Với feed/log thay đổi liên tục, đây là bug UX thật chứ không chỉ là vấn đề hiệu năng.

Keyset pagination là gì?

Keyset pagination còn gọi là seek pagination hoặc cursor pagination. Thay vì nói “bỏ qua 20.000 dòng”, client nói “lấy tiếp các dòng sau item cuối cùng tôi đã thấy”. Ví dụ page đầu:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Giả sử item cuối page có created_at = '2026-05-27 09:00:00' và id = 12345, page tiếp theo dùng:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (created_at, id) < ('2026-05-27 09:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Điểm quan trọng là điều kiện WHERE bám theo sort key, giúp database seek vào vùng cần đọc thay vì skip một lượng row lớn.

Thiết kế cursor API cho keyset pagination

API không nên bắt client tự gửi created_at và id rời rạc nếu không cần. Cách phổ biến là trả về cursor opaque:

GET /api/posts?limit=20

{
  "data": [...],
  "page_info": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0yN1QwOTowMDowMFoiLCJpZCI6MTIzNDV9",
    "has_next_page": true
  }
}

Cursor có thể là JSON base64, nhưng trong production nên ký HMAC hoặc mã hóa để tránh client sửa điều kiện truy vấn tùy ý. Cursor nên chứa sort key, filter context quan trọng nếu cần, version và direction.

Index cần có cho keyset pagination

Keyset pagination không tự động nhanh nếu index sai. Với query ở trên, một index hợp lý trong PostgreSQL có thể là:

CREATE INDEX idx_posts_published_created_id
ON posts (status, created_at DESC, id DESC);

Nếu filter theo tenant, category hoặc user, index phải phản ánh pattern thật:

CREATE INDEX idx_events_tenant_created_id
ON events (tenant_id, created_at DESC, id DESC);

Quy tắc thực dụng: cột equality filter thường đứng trước, tiếp theo là sort key, và luôn có tie-breaker unique như id. Nếu muốn đào sâu thêm về index, xem bài Index trong PostgreSQL.

So sánh keyset pagination và offset pagination

Offset nên dùng khi nào?

• Dataset nhỏ hoặc đã giới hạn rất hẹp.
• User thật sự cần nhảy tới trang bất kỳ.
• Admin/report nội bộ, tần suất thấp, không phải path nóng.
• Sort/filter phức tạp thay đổi liên tục, chưa đáng tối ưu sâu.

Keyset nên dùng khi nào?

• Feed, inbox, notification, transaction, audit log, event log.
• Bảng lớn và page sâu vẫn phải nhanh.
• Dữ liệu thay đổi liên tục, cần giảm duplicate/missing item giữa các page.
• API public/mobile cần infinite scroll hoặc “load more”.
• Endpoint nằm trong critical path backend production.

Trade-off của keyset pagination

Keyset không phải silver bullet. Nó khó hỗ trợ “đi tới trang 57” theo nghĩa truyền thống; backward pagination cần thiết kế riêng; sort phải ổn định và thường bị giới hạn vào vài field đã index; cursor cần versioning khi schema thay đổi; filter dynamic quá nhiều có thể làm số index tăng nhanh. Vì vậy, quyết định đúng thường là: dùng offset cho trải nghiệm page nhỏ, dùng keyset cho danh sách lớn hoặc path nóng.

Consistency: vì sao phải có tie-breaker?

Nếu chỉ sort theo created_at DESC, nhiều row có thể cùng timestamp. Page tiếp theo dùng điều kiện created_at < last_created_at sẽ bỏ sót các row có cùng timestamp nhưng chưa xuất hiện. Vì vậy sort key nên có tie-breaker unique:

ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < (:last_created_at, :last_id)

Với UUID không tăng dần, vẫn có thể dùng created_at + id làm tie-breaker, nhưng cần index đúng và hiểu đặc tính sort.

Checklist triển khai pagination cho production API

• Xác định danh sách nào có khả năng vượt 1 triệu row.
• Đo p95/p99 cho page đầu, page 100, page 10.000 trên dữ liệu gần production.
• Luôn có ORDER BY deterministic, không dựa vào thứ tự mặc định của database.
• Dùng tie-breaker unique trong sort key.
• Tạo composite index khớp filter + sort.
• Chạy EXPLAIN ANALYZE trước khi release.
• Giới hạn limit tối đa, ví dụ 50 hoặc 100.
• Cursor nên opaque, có version và signature.
• Không expose raw SQL assumptions quá nhiều ra client.
• Monitor slow query, buffer read, rows scanned và latency theo page/cursor.

Ví dụ Node.js + PostgreSQL

app.get('/api/events', async (req, res) => {
  const limit = Math.min(Number(req.query.limit || 20), 100);
  const cursor = decodeCursor(req.query.cursor); // { created_at, id }

  const params = [req.user.tenant_id, limit];
  let where = 'tenant_id = $1';

  if (cursor) {
    params.splice(1, 0, cursor.created_at, cursor.id);
    where += ' AND (created_at, id) < ($2, $3)';
  }

  const sql = 'SELECT id, type, created_at, payload FROM events WHERE ' + where + ' ORDER BY created_at DESC, id DESC LIMIT $' + params.length;
  const rows = await db.query(sql, params);

  const last = rows.at(-1);
  res.json({ data: rows, next_cursor: last ? encodeCursor(last) : null });
});

Đoạn code trên vẫn cần production hardening: validate cursor, ký cursor, timeout query, phân quyền tenant, giới hạn payload và monitoring. Xem thêm REST API Design Checklist để chuẩn hóa contract API.

Liên hệ với system design và database performance

Pagination là một ví dụ nhỏ của tư duy system design: tối ưu không chỉ nằm ở code mà nằm ở contract API, index, query plan, UX và monitoring. Với endpoint chịu tải cao, pagination sai có thể kéo theo timeout, retry storm và overload. Nếu API đã nằm trong production path, nên đọc thêm Backpressure và Load Shedding để tránh khuếch đại lỗi khi database chậm.

Kết luận

Offset pagination dễ dùng và vẫn phù hợp cho dữ liệu nhỏ, trang nông hoặc admin tool. Nhưng với bảng lớn, feed, log, transaction list hoặc API mobile cần infinite scroll, keyset pagination thường là lựa chọn ổn định hơn: latency ít phụ thuộc page number, giảm row scan thừa và ít bị duplicate/missing item khi dữ liệu thay đổi.

Điều kiện để keyset pagination hiệu quả là sort key deterministic, tie-breaker unique, composite index đúng và cursor API được thiết kế cẩn thận. Nếu thiếu các yếu tố này, “cursor pagination” chỉ là đổi tên parameter chứ chưa giải quyết gốc rễ database performance.