Which pagination approach is more stable when new records are added during pagination?

Which pagination approach is more stable when new records are added during pagination?

• A. Offset-based pagination
• B. Cursor-based pagination
• C. Page-based pagination
• D. Hash-based pagination

Answer: (B)

Cursor-based pagination is more stable when new records are added during pagination. The idea is to paginate using a stable, unique ordering value (typically an increasing id or timestamp) and remember the last value you saw. You fetch the next page by asking for records with values greater than that last seen value, in the same order, and limiting to the page size. For example, you start with ORDER BY id ASC LIMIT 10, then take note of the last id you returned (say 50). The next request asks for ORDER BY id ASC WHERE id > 50 LIMIT 10. Because you’re always moving forward from the last seen item, inserts that happen between requests don’t shift the items you’ve already retrieved, so you don’t get duplicates or misses. To ensure this works smoothly, the sort must be stable and unique. If the sort key isn’t unique, you add a secondary tie-breaker (like including the primary key in the ORDER BY) to avoid returning the same item twice or skipping others. Offset-based pagination, by contrast, relies on a fixed offset (like skip 20 records). When new records are inserted or deleted between requests, the items shift, causing duplicates or skipped records across pages. Page-based and hash-based approaches don’t inherently solve this stability issue in the same straightforward way. So, using a moving cursor with a stable, unique sort key provides predictable, stable pagination even as the underlying data changes.

Cursor-based pagination is more stable when new records are added during pagination.

The idea is to paginate using a stable, unique ordering value (typically an increasing id or timestamp) and remember the last value you saw. You fetch the next page by asking for records with values greater than that last seen value, in the same order, and limiting to the page size. For example, you start with ORDER BY id ASC LIMIT 10, then take note of the last id you returned (say 50). The next request asks for ORDER BY id ASC WHERE id > 50 LIMIT 10. Because you’re always moving forward from the last seen item, inserts that happen between requests don’t shift the items you’ve already retrieved, so you don’t get duplicates or misses.

To ensure this works smoothly, the sort must be stable and unique. If the sort key isn’t unique, you add a secondary tie-breaker (like including the primary key in the ORDER BY) to avoid returning the same item twice or skipping others.

Offset-based pagination, by contrast, relies on a fixed offset (like skip 20 records). When new records are inserted or deleted between requests, the items shift, causing duplicates or skipped records across pages. Page-based and hash-based approaches don’t inherently solve this stability issue in the same straightforward way.

So, using a moving cursor with a stable, unique sort key provides predictable, stable pagination even as the underlying data changes.