How does cursor-based pagination work? What are the advantages and disadvantages compared to LIMIT/OFFSET?

Cursor pagination is a method of paginating data where a unique identifier (cursor) of the last record on the current page is used to fetch the next page. Instead of specifying a page number or offset, the client sends the cursor, and the server returns records following that cursor.

Advantages of cursor pagination over LIMIT/OFFSET:

• More efficient on large datasets. OFFSET requires the database to skip a specified number of rows, which becomes costly as OFFSET grows.
• Avoids issues with data duplication or skipping when data changes between requests. OFFSET can lead to data shifts if inserts or deletes occur in the table.
• Maintains a stable order and consistent data viewing.

Disadvantages:

• Requires a unique and ordered field (e.g., ID or timestamp).
• More complex to implement and understand than simple OFFSET pagination.
• Harder to jump to an arbitrary page (e.g., directly to page 10), as you need to know the previous page's cursor.

Example of cursor pagination in SQL:

-- Get the first 10 records
SELECT * FROM items ORDER BY id ASC LIMIT 10;

-- Suppose the last id on the first page is 100
-- Get the next 10 records after id=100
SELECT * FROM items WHERE id > 100 ORDER BY id ASC LIMIT 10;