Pagination trong PostgreSQL: OFFSET và keyset Cơ bản

Vấn đề

Bảng event_demo, 100.000 dòng, id bigint PRIMARY KEY. Hai cách lấy “20 dòng bắt đầu từ vị trí 90.000”:

-- Cách 1: OFFSET
SELECT id, message FROM event_demo ORDER BY id LIMIT 20 OFFSET 90000;

-- Cách 2: keyset (nhớ id cuối trang trước)
SELECT id, message FROM event_demo WHERE id > 90000 ORDER BY id LIMIT 20;

Cùng trả về đúng 20 dòng, cùng vị trí logic trong bảng. Chạy thật trên PostgreSQL 17:

Cách	Thời gian	Buffers
OFFSET 90000	5.507 ms	821
id > 90000 (keyset)	0.019 ms	3

290 lần chậm hơn về thời gian, 273 lần nhiều buffer hơn — cho cùng một kết quả.

OFFSET đi bộ qua 90.000 entry, keyset nhảy thẳng tới điểm cần

Phóng to sơ đồ

OFFSET 90000 → Index Scanbỏ 90.000 dòng

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/4

OFFSET: planner không biết "dòng thứ 90.000" nằm đâu, nên phải đếm từ đầu index.

Chi phí OFFSET tăng tuyến tính theo độ sâu; keyset phẳng. Ví dụ events ở phần sau là bảng minh hoạ cursor nhiều cột, không phải event_demo.

Khái niệm cốt lõi

OFFSET n nói với PostgreSQL: “tính ra toàn bộ kết quả đã sắp xếp, bỏ qua n dòng đầu, rồi lấy tiếp.” Bỏ qua không có nghĩa miễn phí — PostgreSQL vẫn phải đi qua từng dòng trong số n dòng đó bằng index (hoặc đọc rồi loại).

WHERE id > last_id nói khác hẳn: “định vị trực tiếp điểm ngay sau last_id”. Với B-tree, đây là một phép so sánh — chính là việc B-tree được sinh ra để làm (xem B-tree index). Không có “phần bị bỏ qua” nào cần đi qua; PostgreSQL nhảy thẳng tới điểm bắt đầu.

Cách hoạt động

OFFSET: chi phí tăng theo độ sâu

Trang nông (OFFSET 20, tức trang 2):

Limit  (cost=0.94..1.59 rows=20 width=19) (actual time=0.009..0.010 rows=20 loops=1)
  Buffers: local hit=3
  ->  Index Scan using event_demo_pkey on event_demo  (actual time=0.007..0.008 rows=40 loops=1)
Execution Time: 0.019 ms

Trang sâu (OFFSET 90000, trang 4.501):

Limit  (cost=2919.89..2920.54 rows=20 width=19) (actual time=5.498..5.500 rows=20 loops=1)
  Buffers: local hit=821
  ->  Index Scan using event_demo_pkey on event_demo  (actual time=0.006..3.784 rows=90020 loops=1)
Execution Time: 5.507 ms

Ở cả hai, Index Scan phía dưới đều phải đi qua đúng OFFSET + LIMIT dòng rồi mới dừng — dòng rows=40 và rows=90020 trong actual chứng minh điều đó. Trang càng sâu, càng phải đi qua nhiều entry hơn trước khi chạm tới phần cần trả.

Đây không phải lỗi cài đặt — về mặt định nghĩa, “dòng thứ 90.000 đến 90.020 theo thứ tự” chỉ xác định được sau khi biết đủ 90.000 dòng đứng trước nó.

Keyset: chi phí không đổi theo độ sâu

Cùng vị trí (id > 90000):

Limit  (cost=0.29..1.00 rows=20 width=19) (actual time=0.014..0.015 rows=20 loops=1)
  Buffers: shared hit=3, local hit=3
  ->  Index Scan using event_demo_pkey on event_demo
        Index Cond: (id > 90000)
Execution Time: 0.019 ms

Sâu hơn nữa, gần cuối bảng (id > 99990):

Limit  (cost=0.29..8.47 rows=10 width=19) (actual time=0.003..0.005 rows=10 loops=1)
  Buffers: local hit=3
  ->  Index Scan using event_demo_pkey on event_demo
        Index Cond: (id > 99990)
Execution Time: 0.009 ms

3 buffer dữ liệu (local hit=3) ở cả hai vị trí — dù một cái ở giữa bảng, một cái gần cuối.

Dòng shared hit=3 ở lần đo đầu không thuộc bảng demo: bảng TEMP chỉ dùng local buffer, nên phần shared đó là page của hệ thống được đọc thêm ở lần chạy đầu.

Index Cond: (id > 90000) cho B-tree một điều kiện so sánh trực tiếp: nhảy từ root xuống leaf chứa giá trị đó, hoàn toàn không liên quan đến việc có bao nhiêu dòng đứng trước nó. Đây là lý do keyset không chậm dần khi đi sâu vào bảng, còn OFFSET thì có.

Điều kiện bắt buộc: thứ tự phải duy nhất

ORDER BY created_at DESC một mình không đủ nếu nhiều dòng trùng created_at — PostgreSQL không cam kết thứ tự giữa các dòng ngang hàng, nên ranh giới trang có thể cắt giữa một nhóm trùng giá trị, làm dòng bị lặp hoặc mất giữa hai lần gọi. Luôn thêm cột duy nhất (thường là khoá chính) làm tie-breaker:

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

-- Trang tiếp theo dùng CẢ HAI giá trị cuối trang trước
SELECT id, created_at, body FROM events
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

So sánh tuple (created_at, id) < (...) — không phải hai điều kiện AND tách rời — để giữ đúng ngữ nghĩa “nhỏ hơn theo đúng thứ tự sort”.

Bẫy khi triển khai keyset

Bẫy	Chuyện gì xảy ra	Cách xử lý
Sort hỗn hợp hướng (created_at DESC, id ASC)	So sánh tuple (a, b) < (x, y) luôn so cùng một chiều, không biểu diễn được	Viết điều kiện tường minh a < x OR (a = x AND b > y), hoặc đảo hướng cột sao cho cùng chiều. Điều kiện OR thường không tận dụng index gọn bằng so sánh tuple, nên phải kiểm EXPLAIN
Cột sort có NULL	NULL so sánh cho kết quả unknown, hàng NULL bị mất khỏi trang	Cột sort NOT NULL, hoặc COALESCE thành giá trị cố định trong cả ORDER BY lẫn index
Filter (WHERE tenant_id = 42)	Nếu filter không nằm đầu index, keyset phải lọc trong khi đi	Index (tenant_id, created_at DESC, id DESC) (thứ tự cột)
Tổng số trang (“trang 3 / 450”)	SELECT count(*) phải duyệt hết tập khớp, chi phí tỷ lệ với số dòng, không do keyset giải quyết	Bỏ tổng số, hiển thị “còn nữa”, hoặc dùng ước lượng (ví dụ reltuples)
Cursor lộ cột nội bộ	Client sửa cursor, dò dữ liệu	Mã hoá opaque (base64 của JSON), tốt hơn là ký HMAC; luôn validate lại
Lùi trang	Điều kiện đảo chiều	Chạy query với ORDER BY ngược và >, rồi đảo kết quả trong ứng dụng
Dòng bị xoá giữa hai lần gọi	Keyset vẫn đúng: tiếp tục từ vị trí cursor, không trượt như OFFSET	Không cần xử lý

Row comparison được định nghĩa là so sánh từ trái sang phải, dừng ở cặp đầu tiên khác nhau hoặc là NULL. Đó là lý do cả NULL lẫn hướng sort ảnh hưởng đến kết quả.

Khi nào OFFSET vẫn ổn

Tình huống	OFFSET	Keyset
Trang admin, bảng dưới khoảng 10.000 dòng	Ổn, đơn giản hơn	Thừa
Cần ô “đi tới trang N”	Cần	Không làm trực tiếp được
Người dùng hiếm khi qua trang vài	Ổn	Không cần
Feed vô hạn, API công khai, export lớn	Tránh	Nên dùng
Bảng hàng triệu dòng, người dùng lướt sâu	Chậm dần theo độ sâu	Chi phí phẳng

Ví dụ thực tế

Ví dụ thực tế

Feed hoạt động của một app có 5 triệu sự kiện, chỉ có nút “xem thêm”:

1. API trả nextCursor là base64 của {created_at, id} dòng cuối.
2. Lần gọi sau gửi lại cursor; server giải mã và chạy WHERE (created_at, id) < (...).
3. Chi phí mỗi trang gần như không đổi dù người dùng kéo bao sâu.

Trang admin có ô “đi tới trang 200” trên bảng nhỏ dùng OFFSET; với bảng lớn, giữ mốc theo ngày để nhảy gần vị trí rồi mới keyset từ đó.

Trade-offs

Ưu điểm

• OFFSET đơn giản, hiển thị được số trang, cho phép nhảy tới trang bất kỳ trực tiếp
• Keyset giữ chi phí gần như không đổi bất kể đi sâu bao nhiêu, miễn có index đúng thứ tự sort
• Keyset ổn định hơn khi có dòng mới được thêm vào trước vị trí đang đọc — không làm trang bị “trượt”

Nhược điểm

• OFFSET sâu tốn chi phí tăng dần theo độ sâu — 290 lần chậm hơn ở trang 4.501 so với trang 2 trong demo
• Keyset không nhảy trực tiếp tới “trang N” được nếu không biết trước ranh giới của trang đó
• Cả hai không tự đảm bảo snapshot nhất quán xuyên nhiều lần gọi API — dữ liệu có thể đổi giữa hai request

Sai lầm thường gặp

Sai lầm thường gặp

Dùng cursor chỉ chứa một cột sort không duy nhất. Nếu nhiều dòng cùng created_at, điều kiện created_at < $last có thể bỏ sót các dòng còn lại có cùng giá trị đó nằm “phía sau” trong cùng nhóm.

Luôn đưa đủ cột tie-breaker (thường là primary key) vào cả ORDER BY, index, và điều kiện so sánh dạng tuple như ví dụ trên.

🎮 Thử tay

• Query chậm dần, bước 4: OFFSET 100.000 so với keyset.

Checklist review một API phân trang keyset

1. ORDER BY kết thúc bằng cột duy nhất (thường là primary key), và cursor chứa đủ mọi cột sort.
2. Có index khớp đúng thứ tự sort; nếu có filter như tenant_id, cột đó đứng đầu index.
3. Điều kiện trang tiếp dùng so sánh tuple (created_at, id) < (...); sort hỗn hợp hướng thì viết điều kiện OR tường minh và kiểm EXPLAIN.
4. Cột sort NOT NULL, hoặc COALESCE nhất quán trong cả ORDER BY lẫn index.
5. Cursor opaque (base64, tốt hơn là ký HMAC) và luôn được validate lại ở server.
6. Không hiển thị tổng số trang bằng count(*) trên tập lớn; dùng “còn nữa” hoặc ước lượng.

Tóm tắt

Tóm tắt

• OFFSET phải đi qua đúng số dòng bị bỏ qua trước khi trả kết quả — chi phí tăng tuyến tính theo độ sâu trang
• Keyset dùng B-tree để định vị trực tiếp bằng so sánh, không phụ thuộc có bao nhiêu dòng đứng trước
• Trong demo: cùng vị trí (dòng 90.000), OFFSET mất 5.507 ms/821 buffer, keyset mất 0.019 ms/3 buffer
• Keyset cần: tie-breaker duy nhất, cùng hướng sort, cột sort không NULL, cursor opaque; “tổng số trang” vẫn là phép đếm tốn kém
• Bắt buộc ORDER BY duy nhất (thêm primary key làm tie-breaker) cho cả hai cách, nếu không muốn dòng bị lặp hoặc mất
• Chọn theo UX: “tải thêm” hợp keyset; “nhảy tới trang N bất kỳ” cần OFFSET hoặc thiết kế mốc riêng

Tham khảo

• PostgreSQL 17 — LIMIT and OFFSET
• PostgreSQL 17 — Indexes and ORDER BY