Lesson 10

Stable pagination

Use deterministic ordering and cursor predicates for pages that stay stable as data changes.

pagination
ordering
cursors

Good Code

review-feed.sql

SELECT id, title, submitted_at
FROM code_reviews
WHERE status = 'submitted'
  AND (submitted_at, id) < ($1, $2)
ORDER BY submitted_at DESC, id DESC
LIMIT 20;

Bad Code

review-feed.sql

SELECT id, title, submitted_at
FROM code_reviews
WHERE status = 'submitted'
ORDER BY submitted_at DESC
LIMIT 20 OFFSET $1;

Review Notes

What to review

Good Code

The good version uses a cursor made from submitted_at and id, giving the database a deterministic place to continue.

Bad Code

The bad version uses offset pagination with a non-unique order. New rows can shift earlier pages, causing duplicates or missing items.

Takeaways

Pagination needs a stable order, not just a limit.