Thiết kế một API list có filter + sort + phân trang cho bảng hàng triệu dòng, bạn dùng offset hay cursor? Tránh những cạm bẫy gì?

Với bảng hàng triệu dòng, chốt cursor (keyset) cho API chính; offset chỉ giữ cho màn admin cần nhảy tới trang số N trên dữ liệu vừa phải.

Truy vấn: mốc là giá trị dòng cuối trang trước, so sánh theo bộ (created_at, id). Thiếu tie-breaker id, các dòng trùng created_at sẽ lặp hoặc sót giữa hai trang. Lấy dư một dòng để biết còn trang sau mà không cần COUNT(*).

SELECT * FROM posts
WHERE status = :status
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;  -- 20 + 1: row thừa chỉ dùng để set has_next

Index phải khớp cả filter lẫn sort: (status, created_at DESC, id DESC) — cột lọc bằng = đứng trước, cột sort theo sau. Sort trên cột ngoài index là full scan kèm sort tạm.

Cursor là token opaque: encode (created_at, id) của dòng cuối thành base64, trả trong next_cursor. Client không tự ghép điều kiện nên sau này đổi cột sort không phá API; decode xong vẫn phải validate, cursor hỏng/giả mạo thì trả 400.

GET /posts?status=active&limit=20&cursor=eyJjIjoiMjAy...
→ { "items": [...], "next_cursor": "eyJjIjoiMjAy..." | null }

Giới hạn tổ hợp filter + sort: mỗi tổ hợp cần một index tương ứng, nên whitelist cột được phép sort — mở sort tự do vừa sinh full scan vừa là đường vào SQL injection nếu ghép chuỗi.

Lỗi hay gặp: trả total bằng COUNT(*) mỗi request (UI cần tổng thì dùng count ước lượng hoặc cache); đổi filter nhưng giữ cursor cũ — mốc thuộc tập kết quả khác nên phải reset cursor về đầu; nạp quan hệ con theo từng dòng gây N+1 query, hãy JOIN/batch một lần cho cả trang. Dòng ở vị trí cursor bị xoá thì không sao: keyset chỉ so sánh giá trị, không cần dòng đó tồn tại.