Overview
Offset pagination is simple and works well for small result sets. As the offset grows, the database may still need to scan and discard many preceding rows. Concurrent inserts and deletes can also cause records to shift between pages.
Start with the workload and the invariant
First ensure the ordering is deterministic. Ordering only by a non-unique timestamp is ambiguous when several rows share the same value. Add a unique tie-breaker such as the primary key. Then create an index that supports the filter and order used by the page.
Practical example
SELECT id, title, created_at
FROM articles
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 30 OFFSET 3000;
Keyset pagination remembers the last row from the previous page and asks for rows after that position in the sort order. For descending (created_at, id), the next-page predicate uses a lexicographic comparison. The cursor should contain both values, be validated, and ideally be opaque or signed if clients must not tamper with it.
SELECT id, title, created_at
FROM articles
WHERE status = 'published'
AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 30;
Trade-offs and failure modes
Keyset pagination is excellent for next/previous traversal and exports, but it does not naturally support jumping to an arbitrary page number. It also requires care if the sort key can change during traversal. Prefer immutable ordering fields for cursor-based feeds.
Verification and rollout
Benchmark with realistic table sizes and inspect EXPLAIN. Test duplicate timestamps, records inserted during paging, deleted rows, invalid cursors, and transitions at page boundaries. Keep page size bounded to protect memory and response time.
Engineering checklist
Choose pagination based on user behavior: offset for small admin tables with direct page navigation, keyset for feeds and large sequential datasets. The goal is stable, bounded work per page, not adopting a technique without considering the interface.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.