The problem of OFFSET pagination on large tables and how to solve it (cursor-based pagination)

The problem with OFFSET pagination on large tables is that, with large OFFSET values, the database has to scan and skip a large number of rows, which significantly reduces performance.

For example, the query:

SELECT * FROM table ORDER BY id LIMIT 10 OFFSET 1000000;

will be slow because the DBMS has to scan a million rows to return the desired 10.

Solution — cursor-based pagination:

Instead of offsetting by the number of rows, use the value of the last retrieved item (e.g., id), and start the next query from there.

Example:

SELECT * FROM table WHERE id > last_seen_id ORDER BY id LIMIT 10;

Advantages:

• Fast retrieval, as it uses an index on id.
• No need to skip a large number of rows.
• More stable pagination when data changes (e.g., when adding or deleting records).

Thus, cursor-based pagination significantly improves performance and user experience when working with large tables.