Your SQL Pagination Can Repeat Rows. A Cursor Fixes Only Part of It.

When you paginate an SQL result set using OFFSET and LIMIT, you may encounter an issue where rows are repeated or skipped. This occurs because OFFSET skips a certain number of rows from the beginning, not from where you left off. For example, if you have a feed sorted by newest first and you request the second page (OFFSET 2) with a page size of 2, you might see the same row twice if a new row is inserted between your first and second pages.

Deleting a row can also cause gaps in the pagination results. A cursor-based pagination can help fix this issue, but it still doesn't guarantee that you get a snapshot of the data as it existed at a specific point in time. To demonstrate both the problem and the solution, consider a simple SQLite database with six rows. When you request the first page (OFFSET 0), you get IDs 6 and 5.

If you add a new row with ID 7, the next page (OFFSET 2) will return ID 5 again because the order has changed. If you delete the row with ID 6, the second page will skip ID 4 entirely. To solve this, you can use keyset pagination, also known as cursor-based pagination. This method keeps track of the last row's sort values and uses them to fetch the next set of rows.

For instance, after fetching the first page, you would remember the timestamp and ID of the last row (400, 5). The next page would then be fetched using a WHERE clause that checks for rows with a created_at value less than 400 and an id value less than 5. This ensures that you get the correct set of rows, even if new rows are inserted or existing rows are deleted.

This solution works with SQLite, which supports row-value comparisons. However, it's important to note that this approach does not guarantee a snapshot of the data at a specific moment in time. It only ensures that you get the correct rows based on the current state of the database.