Define what next means
A page of records needs an ordering before it needs a page number. PostgreSQL does not promise a particular row order without ORDER BY, and LIMIT should be paired with an order that uniquely identifies the sequence. Sorting only by created_at is ambiguous when several records share a timestamp. Adding a unique id gives the example a tie-breaker.
For a small administrative list, LIMIT and OFFSET can be sufficient. Large offsets can become inefficient because skipped rows still have to be processed. The appropriate choice depends on data size, navigation needs, and measured query behavior.
Use a cursor that matches the order
For a newest-first stream, a cursor can carry the last seen created_at and id. A subsequent request selects rows below that pair and uses the same descending order. This example assumes both columns are non-null, id is unique, and the cursor is interpreted using the database's actual column types.
SELECT id, created_at
FROM orders
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;Specify the behavior when data changes
A cursor does not create a historical snapshot by itself. Inserts, deletions, or changes to an ordering field can affect what appears in later requests. Decide whether the endpoint promises a live feed or a consistent export. Those are different products, and the second may require a snapshot or another explicit boundary.
Validate the cursor, bind it to the requested filters where appropriate, and enforce authorization independently on every request. Encoding a cursor is not an access-control mechanism. Avoid treating caller-supplied cursor values as raw SQL. Parameterized queries and server-side page-size limits remain necessary.
Test page boundaries
Build a fixture containing tied timestamps, an empty result set, and a final partial page. Walk the entire fixed fixture and check for missing or duplicated IDs. Then test the chosen behavior while records are inserted or deleted between requests.
Offset and cursor pagination have different navigation tradeoffs. A numbered page can be convenient for an internal report; a continuation cursor can suit a growing stream. Choose the approach that matches the user's task and document its consistency behavior rather than promising that every form of pagination behaves like a frozen list.