How to implement pagination in an API? The OFFSET problem in PostgreSQL and alternatives (cursor-based pagination)?

Pagination in API is needed to limit the amount of data returned per request. OFFSET is often used in SQL, for example:

SELECT * FROM items ORDER BY id LIMIT 10 OFFSET 100;

But OFFSET has issues:

• For large OFFSET values, queries become slower because the database has to skip many rows.
• When data changes between requests, skips or duplicates may occur.

An alternative — cursor-based pagination:

Instead of offset, the value of the last retrieved item (e.g., id or timestamp) is passed. The query looks like:

SELECT * FROM items WHERE id > :last_id ORDER BY id LIMIT 10;

Advantages:

• Faster with large data volumes.
• More stable when data changes (no skips or duplicates).

The disadvantage — more complex to implement and maintain, especially if sorting is complex.

In Java API, cursor passing can be implemented as a request parameter, for example ?after=12345&limit=10.