Pagination is also a consistency decision
Pagination limits how much data an API returns at once. It also determines what the next page means when records are added or removed. A numbered admin table and a continuously growing activity feed have different navigation needs, so they do not always need the same pagination strategy.
Begin with the user's interaction. Do they need to jump directly to page 20, or just load older entries? Is the underlying data mostly stable, or does it change while they browse? Answering those questions gives you a practical basis for choosing offset or cursor pagination.
OFFSET is straightforward for numbered pages
For a page size of 20, page three can use SELECT id, created_at FROM events ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40. The ordering includes a unique ID to break ties between rows with equal timestamps. PostgreSQL warns that LIMIT needs a predictable ordering and that skipped OFFSET rows still require work inside the server.
Suppose the first page showed records 100 through 81. Before the second request, a new record 101 arrives at the front. Skipping the first 20 rows now starts at 81, so the user can see a duplicate. This example illustrates how a position-based boundary shifts when the data changes.
A cursor follows the last row's ordering values
For a newest-first feed, a cursor can carry the last row's created_at and id. The next request then uses WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. PostgreSQL row comparisons evaluate values from left to right, which makes the ID a tie-breaker when timestamps match. This example assumes both fields are non-null.
Use parameterized values, preserve timestamp precision, and keep the filter and ordering unchanged across pages. If the user switches from one category to another, restart pagination rather than reusing the old cursor. The cursor identifies a position within a particular ordering and filter combination.
A cursor does not freeze the dataset
Cursor pagination avoids the specific shift caused by inserting a newer record ahead of the boundary. It does not create a snapshot of all data. A record can still disappear, and changing an ordering field can move it across the cursor. Prefer an immutable ordering key when the browsing experience depends on stable progress.
Fetching one more row than the page size can indicate whether another page exists. Return only the requested number and build the next cursor from the last returned row. Direct jumps to arbitrary page numbers need a different design, and an exact total count may involve a separate query.
Test the boundary before optimizing
Create rows with identical timestamps, browse multiple pages, and check for duplicates or omissions in an unchanged dataset. Then insert a newer row between requests and compare the behavior of each strategy. Also try deleting the row represented by the cursor; a value-based boundary should not require that row to remain present.
Measure actual query plans and timings with realistic filters and data volume. An appropriate index can help, but a cursor is not a universal speed guarantee. Choose numbered pages for the navigation that needs them, and consider cursor pagination for sequential feeds where a stable boundary is more useful.