🔥PostgreSQL: Cẩn thận! Phân trang sai lầm có thể khiến bạn mất dữ liệu

Hôm trước mình có chia sẻ về cách phân trang với PostgreSQL bằng LIMIT ... OFFSET vs OFFSET ... FETCH NEXT. Tuy nhiên, đồng nghiệp của mình đã góp ý một vấn đề quan trọng mà mọi người cần lưu ý khi sắp xếp theo cột ngày (ORDER BY date_column). Nhờ thế mà mình có cơ hội tìm hiểu sau hơn nữa và chia sẻ lại.

👉 Vấn đề 1: Khi ORDER BY date_column, nếu có nhiều bản ghi có cùng giá trị ngày, PostgreSQL sẽ không có quy tắc sắp xếp rõ ràng cho những bản ghi này.

Điều này dẫn đến việc thứ tự có thể thay đổi mỗi lần thực thi truy vấn, gây ra:

✅ Bản ghi bị bỏ sót hoặc trùng lặp giữa các trang. Khi bạn phân trang, một bản ghi có thể xuất hiện ở trang trước, sau đó lại nhảy sang trang sau hoặc biến mất hoàn toàn nếu PostgreSQL thay đổi cách sắp xếp những bản ghi có cùng ngày. Hãy xem demo sau:

Trang 1:

Trang 2:

💡 Cách khắc phục: Để đảm bảo thứ tự phân trang luôn cố định, bạn cần bổ sung một tiêu chí sắp xếp phụ, thường là một cột có giá trị duy nhất như "id". Ví dụ:

SELECT * FROM orders
ORDER BY id DESC, order_date DESC
LIMIT 10 OFFSET 20;

SELECT * FROM orders
ORDER BY id DESC, order_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

📌 PostgreSQL không đề cập cụ thể về việc sắp xếp ổn định (stable sort) trong mệnh đề ORDER BY. Vì vậy, để đảm bảo thứ tự cố định giữa các lần phân trang, việc thêm tiêu chí sắp xếp phụ, như cột "id", là cần thiết.

⚡ Lợi ích của cách làm này:

• Giữ thứ tự cố định giữa các lần phân trang.
• Tránh mất hoặc trùng lặp dữ liệu khi di chuyển giữa các trang.
• Giúp hệ thống hoạt động ổn định hơn.

👉 Vấn đề 2: Lỗi hiệu xuất khi OFFSET lớn.

Dù sử dụng LIMIT ... OFFSET hay OFFSET ... FETCH NEXT, khi OFFSET có giá trị lớn (ví dụ: 100000), PostgreSQL vẫn phải đọc và bỏ qua tất cả các dòng trước đó. Điều này có thể gây ra hiệu suất kém:

SELECT * FROM orders
ORDER BY id DESC, order_date DESC
OFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY;

⟶ PostgreSQL vẫn đọc và loại bỏ 100000 dòng đầu tiên, làm chậm truy vấn.

💡 Giải pháp: Phân tích nghiệp vụ xem có dùng keyset pagination để cải thiện hiệu suất được không, giả sử được nghiệp vụ chỗ này dùng được:

Nguyên tắc:

• Khi phân trang KHÔNG cần nhảy trang ngẫu nhiên.
• Không dùng OFFSET nữa.
• Lọc dữ liệu dựa trên giá trị của trang trước đó thay vì duyệt từ đầu.

Trang 1:

SELECT * FROM orders
ORDER BY id DESC, order_date DESC
LIMIT 10;

Trang 2:

SELECT * FROM orders
WHERE id < @p_id
ORDER BY id DESC, order_date DESC
LIMIT 10;

📌 Khi nào nên dùng Keyset Pagination?

• Khi dữ liệu lớn và bạn cần tốc độ cao.
• Khi phân trang không cần nhảy trang ngẫu nhiên (Keyset Pagination không hỗ trợ điều này như OFFSET).
• Khi dữ liệu có cột thời gian hoặc ID tuần tự để dễ dàng lọc tiếp.

💬 Bạn đã từng gặp lỗi này chưa? Chia sẻ giải pháp của bạn nhé! 🚀