?page=2 or ?limit=20 seems like plenty. But the moment you try to design it as a real API, awkward questions pile up in no time.
In what order do you fix the results? What represents the boundary of the next page? Can it be shared and revisited as a URL? Is it acceptable for duplicates or omissions to occur while data is being updated?
The DB has its say, too: there's the question of how it executes that query. And you have to make peace with frontend caching and the browser back button. In other words, pagination is a design topic that squats right on the border between UI, API, DB, and operations.
What follows sorts out pagination query design by splitting it into four:page-based, offset-based, cursor-based, and time-based. If this were a book, the difference would be between opening to a page number, counting sheets from the front, slipping in a bookmark, or looking things up by date, diary-style.
The First Question to Ask
Personally, I think there is one question to ask first: is the experience of jumping to a page number really needed for this list? If it is, consider thepage / offset family; if not, consider cursor first. And if "from when to when" matters more than "which page," bring time-based to the front.
Roughly speaking, the breakdown is as follows.
| Approach | Typical query | Suited for | Strengths | Weaknesses |
|---|---|---|---|---|
| page-based | ?page=3&perPage=20 | Search results, admin screens, SEO-conscious lists | Easy for humans to understand. Easy to share as a URL | Tends to get slow on deep pages. Weak against changing data |
| offset-based | ?offset=40&limit=20 | Small to medium lists, simple paging | Simple to implement. Easy to move to any position | Large offsets are slow. Prone to duplicates and omissions |
| cursor-based | ?cursor=opaque&limit=20 | Timelines, notifications, chat, sync APIs, infinite scroll | Performance tends to stay stable on deep pages. Relatively robust against changing data | Jumping to an arbitrary page is hard. Requires cursor design |
| time-based | ?since=...&until=...&limit=50 | Logs, events, audit history, message history | Fits requirements for searching by time range | Boundaries tend to be unstable with timestamps alone |
page without thinking and it can come back to bite you. Then again, if you have an experience like an admin screen where someone wants to jump to page 10, cursor alone is awkward. Pagination isn't about choosing the one technically superior approach. It's about choosing according to how the list is used.
The Clarity of Page Numbers
Page-based pagination acceptspage and perPage as its external interface.
GET /articles?page=3&perPage=20
From the user's point of view, this is the easiest to understand. It fits well with experiences like "open page 3," "share the URL," and "go back with the browser back button." For search results, category lists, and admin tables, it's still a perfectly natural choice.
In implementation, however, it's usually converted to an offset internally.
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;
So even if it's page-based as an API, it often behaves as offset-based toward the DB. That's why, when deep pages get dug through endlessly or when data is frequently added and deleted, the weaknesses of offset surface just as they are.
Page-based suits cases where you want to move directly to a page number. The same goes for sharing?page=n as a URL or building an SEO-conscious list page. As long as the data volume isn't that large and nobody is crawling through lots of deep pages, it lacks nothing. Conversely, for something like a timeline or notification list where "read the next one" is enough, you'll rarely miss page numbers.
The Weaknesses of Offset
Offset-based pagination specifiesoffset and limit directly.
GET /articles?offset=40&limit=20
It maps straightforwardly to SQL.
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;
It's simple to implement and easy to reason about. For small lists or internal admin screens, this is often enough.
The trouble is that offset tends to get slower the deeper it goes.LIMIT 20 OFFSET 100000 is not "light because it only returns 20 rows." The DB has to find the position of row 100000 and only then return the next 20. Of course, if an index is used it will be faster to some degree, but the property remains: the more rows you skip, the more work there is.
The other problem is that it's weak against changing data. Suppose you view the five items A through E on page 1. Before you open page 2, a new article X is added to the top. The order is now X A B C D E, so fetching the next page with offset=5 puts E, which you've already seen, at the front.
That's how E can appear twice. Conversely, if data in the middle is deleted, you skip one item you haven't seen yet. Offset is based on "the Nth position in the current order," so when the order itself changes, the boundary shifts with it.
Offset-based suits lists where the data volume is small, deep pages are rarely opened, and updates are infrequent. It's also an option when you want to prioritize implementation simplicity or need page-number jumps. For full synchronization of large datasets or frequently updated timelines, however, you'll want to steer clear.
The Order List at Page 5000
Let's walk through a case that comes up often in practice. Suppose the order list API receives a request like this.
GET /api/orders?page=5000&size=100
page starts at 1, the offset is (page - 1) × size, which here comes to (5000 - 1) × 100 = 499900. As SQL, it looks roughly like this.
SELECT
id,
status,
created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 100
OFFSET 499900;
Only 100 rows are returned, yet before reaching them the DB has to walk past 499900 rows from the start. In book terms, it's like thumbing through and counting nearly half a million sheets before you can start reading.
With the samesize=100, the work balloons the deeper the page goes. page=1 means OFFSET 0, page=100 means OFFSET 9900, page=1000 means OFFSET 99900, and page=5000 means OFFSET 499900. It happens precisely because, although the API says page, it runs as offset-based on the DB.
That said, running into this doesn't automatically mean you should switch to cursor-based. The first thing I'd check is whether a user is actually opening page 5000. It may just be a batch job or crawler traversing every page in order.
There are other things worth checking too. Is moving directly by page number truly necessary? Can search conditions or date range filters be added to the list? Is there an index matching theORDER BY, and is an exact totalCount really needed?
If a human is diving to page 5000 to find an order, for instance, the problem lies in the search UI rather than the pagination method. Paging through 5000 pages to find one order is less a job than a spiritual discipline. Letting people filter by order number, customer name, status, creation date, and so on is far kinder to both users and the DB.
On the other hand, if a batch job is scanning all orders, a page-number jump feature isn't needed at all. In that case, cursor-based pagination fits the requirements better.
To switch to cursor-based, first fix the order of the order list like this.
ORDER BY created_at DESC, id DESC
Suppose the last order fetched previously had these values.
{
"createdAt": "2026-07-01T10:00:00Z",
"id": 12345
}
For the next page, fetch orders that come after this value.
SELECT
id,
status,
created_at
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 100;
In the API, that boundary value is passed as a cursor.
GET /api/orders?limit=100&cursor=opaqueCursor
You don't specify "how many rows to skip from the start" as with offset. You specify "which order to continue from." Prepare an index matching the sort order as well.
CREATE INDEX idx_orders_created_at_id
ON orders (created_at DESC, id DESC);
Now, even after reading through 5000 pages' worth, you no longer have to skip 499900 rows each time.
With cursor-based pagination, though, an operation like "jump straight to page 5000" becomes difficult. So for screens where page-number jumps are truly needed, keeping page-based is a valid choice.
In that case, there are a few measures you can take. Narrow the target count first with search conditions or a date range. Limit the maximum reachable page, or split old data by year and month. It also helps to measure access to deep pages, check thatORDER BY and the index match, and carve full retrieval out into an asynchronous export.
For example, you can keep page-based pagination for the admin screen and use cursor-based pagination for batch and external integration APIs. That kind of split works fine.
# For the admin screen
GET /api/orders?page=10&size=100
# For batch and sync processing
GET /api/order-exports?cursor=opaqueCursor&limit=1000
Even with the same data, it's perfectly fine to use different pagination approaches depending on the purpose.
Also, when deep pages are slow, don't rely on guesswork; check the execution plan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT
id,
status,
created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 100
OFFSET 499900;
What to look at isn't just the 100 rows finally returned. Check how many rows were scanned, which index was used, whether a Sort occurred, and how much Buffers grew. It's also worth watching how execution time changes as the offset deepens. Compare shallow and deep pages and you should see that, although the number of returned rows is the same, deeper offsets process more rows.
In the end,page=5000 being slow isn't necessarily something a little SQL tweaking will fix. There are three things to consider. One is how the DB handles deep offsets; another is whether users need to move to arbitrary pages at all. And finally, whether the process scanning the whole list can be split into a separate API.
A pagination approach should be chosen not only for query performance but with the UI, batch processing, and how data is searched all in view.
The Cursor: A Boundary Expressed as a Value
Cursor-based pagination passes "where we read up to last time" instead of a page number. In book terms, it's the bookmark.
GET /articles?limit=20
GET /articles?cursor=opaqueCursor&limit=20
The response returns a cursor for the next page.
{
"items": [
{ "id": "a1", "title": "Designing Pagination Queries" }
],
"pageInfo": {
"nextCursor": "opaque-next",
"hasNextPage": true
}
}
id increases monotonically and the list can be ordered by id DESC, you can write:
SELECT id, title, created_at
FROM articles
WHERE id < $1
ORDER BY id DESC
LIMIT 20;
Unlike offset, the condition is "after which value." So performance tends to stay relatively stable even on deep pages.
In real lists, however,id DESC alone isn't always enough. A common case is a list ordered by created_at DESC. There, using only created_at as the cursor is risky. Multiple articles can share the same created_at.
ORDER BY created_at DESC
created_at, their order among themselves isn't stable. If a page boundary lands right inside such a group of equal values, you get duplicates or omissions.
So in practice, a unique order is often built with a composite key such as created_at + id.
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
In this case, match the index to the sort order as well.
CREATE INDEX idx_articles_created_at_id
ON articles (created_at DESC, id DESC);
If there are filter conditions, design them in too.
CREATE INDEX idx_articles_tenant_created_at_id
ON articles (tenant_id, created_at DESC, id DESC);
In short, the crux of cursor-based pagination is to treat the sort order, the cursor, and the index as a set of three. Dressing up only the API in cursor clothing means little if there's nothing behind it. If the order is unstable or the index doesn't match, the promised benefits are slow to materialize.
A Cursor That Hides Its Contents
A cursor is easiest to work with when it's an opaque string from the client's point of view. That is, return it as a token whose contents the client doesn't interpret.
{
"nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA3LTA5VDAwOjAwOjAwWiIsImlkIjoiMTIzIn0="
}
Internally, it might hold information like this.
{
"createdAt": "2026-07-09T00:00:00Z",
"id": "123"
}
id will later need to become created_at + id.
If clients depend on the cursor's contents at that point, the change quickly gets hard. With an opaque cursor, the client simply passes the string it received into the next request. What's scribbled on the back of the bookmark is none of the reader's business.
While we're at it, in APIs where filters or sorts can be changed, which conditions a cursor belongs to also matters.
GET /articles?tag=api&sort=createdAtDesc&cursor=...
tag=api and sort=createdAtDesc. Use an old cursor after tag or sort has changed and its meaning breaks. It's like tucking one book's bookmark into a different book. So you'll want either to include filter/sort information in the cursor and validate it, or to design things so the cursor is invalidated when the conditions change.
Time-Based: Cutting by Time
Time-based pagination is less about paging and more about cutting by time range.
GET /events?since=2026-07-01T00:00:00Z&until=2026-07-31T23:59:59Z&limit=50
For logs, events, audit history, message history, time-series metrics, and the like, "from when to when" matters more than "which page." Here, time-based is the natural choice.
SELECT id, event_type, created_at
FROM events
WHERE created_at >= $1
AND created_at < $2
ORDER BY created_at DESC, id DESC
LIMIT 50;
If you try to do full paging on time alone, however, rows sharing the same timestamp at the boundary become a problem. So in practice, time-based and cursor-based are often combined.
GET /events?since=2026-07-01T00:00:00Z&until=2026-07-31T23:59:59Z&cursor=opaque&limit=50
In SQL, you narrow by time range while watching the page boundary with a composite cursor.
SELECT id, event_type, created_at
FROM events
WHERE created_at >= $1
AND created_at < $2
AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 50;
With time-based, things become much clearer once you separate time as a search condition from the cursor as a page boundary. Even a diary you open by date still needs a bookmark.
items and pageInfo
For a REST API, I personally find a shape that separatesitems and pageInfo easiest to work with. For a request like this,
GET /articles?limit=20&cursor=opaque
return a response like this.
{
"items": [
{
"id": "a1",
"title": "Designing Pagination Queries",
"createdAt": "2026-07-09T00:00:00Z"
}
],
"pageInfo": {
"nextCursor": "opaque-next",
"prevCursor": "opaque-prev",
"hasNextPage": true,
"hasPreviousPage": false
}
}
items is the actual data, and pageInfo is the information needed to control paging. Keeping the two apart makes life considerably easier on the frontend.
hasNextPage is easy to implement by fetching limit + 1 rows. With limit=20, for example, fetch 21 rows from the DB.
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;
hasNextPage: true. If 20 or fewer, it's hasNextPage: false. This way you can tell whether there's a next page without running a separate COUNT(*) every time.
totalCount, on the other hand, is convenient but tends to be heavy on huge lists. Especially when search conditions are complex or rows are numerous, returning an exact count every time incurs a cost that is no laughing matter. So consider options like returning totalCount only on screens that need it, making it approximate, or returning it asynchronously.
Connections in GraphQL
In GraphQL, the Relay Connection format is commonly used.
query {
articles(first: 20, after: "opaque-cursor") {
edges {
node {
id
title
}
cursor
}
pageInfo {
hasNextPage
hasPreviousPage
startCursor
endCursor
}
}
}
items + pageInfo, it has a rather ceremonious air. The idea, though, is similar. node is the actual data, cursor is the position of that element, and pageInfo tells you whether there's a next or previous page. In GraphQL, forward and backward paging are also easy to express as part of the spec.
articles(first: 20, after: "cursor")
articles(last: 20, before: "cursor")
starting_after / ending_before.
GET /articles?limit=20&startingAfter=...
GET /articles?limit=20&endingBefore=...
created_at DESC, id DESC, then to fetch the previous page you go upward with a > condition.
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 20;
DESC display order.
URL and Cache
In frontend implementation, how you hold state changes with the pagination approach. With page-based, it's easy to push state into the URL, as in/articles?page=3&tag=api&sort=createdAtDesc. That form works well with sharing, revisiting, and the browser back button, and it's quite comfortable for search results and category lists.
Infinite scroll, on the other hand, pairs well with cursor-based.
useInfiniteQuery({
queryKey: ["articles", filters, sort, pageSize],
queryFn: ({ pageParam }) =>
fetchArticles({
cursor: pageParam,
limit: pageSize,
filters,
sort,
}),
initialPageParam: null,
getNextPageParam: (lastPage) =>
lastPage.pageInfo.nextCursor ?? undefined,
});
queryKey.
["articles", filters, sort, pageSize]
filters or sort change, that's a different list altogether. Otherwise, you end up using an old cursor against results for different conditions.
If you want the browser back button to feel natural too, you also have to decide how much to keep in the URL. With page-based, putting ?page=3 in the URL is enough. With cursor-based infinite scroll, you need to decide whether to put the last cursor in the URL, keep the sequence of loaded pages in history state, or refetch when the user comes back. Which one you pick depends on what you expect of the UI.
Verifying on PostgreSQL
Rather than stopping at theory, trying it on PostgreSQL makes it sink in far better. For example, prepare a table to hold around a million rows for testing.
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 1,
title TEXT NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_articles_created_at_id
ON articles (created_at DESC, id DESC);
CREATE INDEX idx_articles_tenant_created_at_id
ON articles (tenant_id, created_at DESC, id DESC);
generate_series and refresh the statistics.
INSERT INTO articles (tenant_id, title, body, created_at)
SELECT
(random() * 9)::int + 1,
'title-' || gs,
repeat('x', 200),
now() - ((gs % 100000) || ' seconds')::interval
FROM generate_series(1, 1000000) AS gs;
ANALYZE articles;
For offset-based, compare shallow and deep offsets.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 900000;
For cursor-based, use a composite cursor.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Buffers, the scan type, whether there's a sort, and the rows at upstream nodes. Whether the index is used as expected deserves a check, too. With a deep offset, you should see that even though only 20 rows are returned, a great many rows are processed on the way to that position.
Cursor-based, by contrast, can narrow the start position with an index condition. That's why its behavior tends to stay close to constant even at deep positions.
Deciding by How the List Is Read
For admin screens and search results, page-based is still a natural choice. But for APIs that traverse large amounts of data in order, timelines, notifications, chat, and sync processing, I'd consider cursor-based first. If the time range itself carries meaning, as with logs and audit history, combining time-based and cursor-based is the easiest to work with.
That's my take on designing pagination queries.page=2 is easy, but it isn't necessarily enough. There's far more to ask than meets the eye.
Whether you need to jump to page numbers, whether the list order is uniquely fixed, how much duplication or omission during updates you can tolerate. How the DB handles deep pages, whether the API response tells the client its next move, and whether it all meshes with the frontend's URL, cache, and browser back behavior. None of that can be read off the surface of page=2.
Pagination isn't a story about limit and offset. How the list is read, in what order it's fixed, and what to return next so the client never gets lost: only once you've decided all of that can you say you've designed a list. It's just turning pages, and yet it's never just turning pages.