Designing robust interfaces means planning for changing data and edge cases.
When you fetch a list of items from an API, you rarely want all 1,000,000 at once. You use Pagination.
A common approach is Offset Pagination (LIMIT 10 OFFSET 10). But if a new item is inserted at the top of the list while the user is clicking "Next Page", the whole list shifts down, causing the user to see duplicates! The robust alternative is Cursor Pagination: instead of saying "skip 10", you say "give me 10 items after item ID 45". No matter how the data shifts, the cursor remains stable.
# Bad: Offset Pagination
# Vulnerable to data shifting. Also gets very slow for high offsets
# because the DB still has to scan and discard the skipped rows.
SELECT * FROM posts ORDER BY id DESC LIMIT 10 OFFSET 20
# Good: Cursor Pagination
# Stable even if data is inserted/deleted. Fast because it can
# jump directly to the index using the WHERE clause.
SELECT * FROM posts WHERE id < :last_seen_id ORDER BY id DESC LIMIT 10