Designing pagination is not just about deciding how many items to return at a time.

At first, something like ?page=2 or ?limit=20 seems like enough.

But once you actually try to design it as an API, various problems quickly appear.

  • How do you fix the order of 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?
  • How does the DB execute that query?
  • How does it fit with frontend caching and the browser back button?

In other words, I think pagination is a design topic that sits at the boundary of UI, API, DB, and operations.

In this article, I'll sort out pagination query design by splitting it into page-based, offset-based, cursor-based, and time-based.

Conclusion First

Personally, I think the first question to ask is this:

Is the experience of jumping to a page number really needed for that list?

If it is, consider page / offset approaches. 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.

ApproachTypical querySuited forStrengthsWeaknesses
page-based?page=3&perPage=20Search results, admin screens, SEO-conscious listsEasy for humans to understand. Easy to share as a URLTends to get slow on deep pages. Weak against changing data
offset-based?offset=40&limit=20Small to medium lists, simple pagingSimple to implement. Easy to move to any positionLarge offsets are slow. Prone to duplicates and omissions
cursor-based?cursor=opaque&limit=20Timelines, notifications, chat, sync APIs, infinite scrollPerformance tends to stay stable on deep pages. Relatively robust against changing dataJumping to an arbitrary page is hard. Requires cursor design
time-based?since=...&until=...&limit=50Logs, events, audit history, message historyFits requirements for searching by time rangeBoundaries tend to be unstable with timestamps alone
If you pick page without thinking, it can become painful later.

Conversely, if you have an experience like an admin screen where "I want to jump to page 10," cursor alone is hard to work with.

Pagination is less about choosing one technically superior approach and more about choosing according to how the list is used.

page-based pagination

Page-based pagination accepts page and perPage as its external interface. 
GET /articles?page=3&perPage=20

It's the easiest to understand from the user's point of view.

It also 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 quite a natural choice.

However, in implementation 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;

In other words, even if it's page-based as an API, it often behaves as offset-based toward the DB.

For this reason, the weaknesses of offset surface when many deep pages are dug through, or in lists where data is frequently added and deleted.

Page-based suits cases like the following.

  • You want to move directly to a page number
  • You want to share ?page=n as a URL
  • You want an SEO-conscious list page
  • The data volume isn't that large
  • Usage doesn't involve crawling lots of deep pages

Conversely, for something like a timeline or notification list where "read the next" is enough, you usually don't need page numbers.

offset-based pagination

Offset-based pagination specifies offset 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.

However, 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 then return the next 20.

Of course, if an index is used it will be faster to some degree, but the property remains that the more rows you skip, the more work there is.

Another problem is that it's weak against changing data.

For example, suppose that after viewing page 1, a new article is added to the top before you view page 2.

First page 1:
  A B C D E

New article X is added to the top:
  X A B C D E

Fetching next with offset=5:
  E ...
In this case, E may appear twice.

Conversely, if data in the middle is deleted, you may skip one item you haven't seen yet.

Because offset is based on "the Nth position in the current order," the boundary shifts when the order itself changes.

Offset-based suits cases like the following.

  • Data volume is small
  • Deep pages are rarely opened
  • Update frequency is low
  • You want to prioritize implementation simplicity
  • You need page-number jumps

Conversely, you'll want to avoid it for full synchronization of large datasets or frequently updated timelines.

A Practical Case: The Order List at Page 5000 Is Slow

Suppose the order list API receives a request like this.

GET /api/orders?page=5000&size=100

At first glance, it looks like an ordinary request that just fetches 100 orders.

But page-based pagination is often converted to an offset internally.

If page starts at 1, offset is computed like this. 
offset = (page - 1) × size

In this case:

(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;

The data returned is 100 rows, but before fetching those 100 rows, the DB has to walk past 499900 rows from the start.

So even with the same size=100, the amount of work grows as the page gets deeper. 
page=1     → OFFSET 0
page=100   → OFFSET 9900
page=1000  → OFFSET 99900
page=5000  → OFFSET 499900
This happens because, even though the API uses page, it runs as offset-based pagination on the DB.

What to Check First

When you run into this problem, it's not always right to immediately switch to cursor-based pagination.

The things I'd check first are:

  • Is a user actually opening page 5000?
  • Is a batch job or crawler traversing every page in order?
  • Is it really necessary to move directly by page number?
  • Can search conditions or date range filters be added to the list?
  • Does an index matching the ORDER BY exist?
  • Is an exact totalCount really needed?

For example, if a human is navigating to page 5000 to find an order, the problem may lie in the search UI rather than the pagination method.

Letting them filter by order number, customer name, status, creation date, and so on is easier for 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.

In that case, cursor-based pagination fits the requirements better.

Switching to cursor-based pagination

Suppose the order list is fetched in this order.

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, the boundary value is passed as a cursor.

GET /api/orders?limit=100&cursor=opaqueCursor

Instead of specifying "how many rows to skip from the start" as with offset, you specify "which order to continue from."

Prepare a corresponding index matching the sort order as well.

CREATE INDEX idx_orders_created_at_id
ON orders (created_at DESC, id DESC);

This way, even after reading through 5000 pages' worth, you no longer have to skip 499900 rows each time.

When Page-Number Jumps Are Needed

With cursor-based pagination, an operation like "jump directly to page 5000" becomes difficult.

So for screens where page-number jumps are truly needed, keeping page-based pagination is also an option.

In that case, consider measures like the following.

  • Narrow down the target count first with search conditions or date range
  • Limit the maximum page number that can be reached
  • Split old data by year and month
  • Measure access to deep pages
  • Check whether ORDER BY and the index match
  • Separate full retrieval 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.

# 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 when handling the same data, it's fine to use different pagination approaches depending on the purpose of use.

Verify with the Execution Plan

When deep pages are slow, don't rely on guesses; 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.

  • How many rows were scanned
  • Which index was used
  • Whether a Sort occurred
  • How much Buffers increased
  • How execution time changes as the offset gets deeper

If you compare shallow and deep pages, you'll see that even though the number of returned rows is the same, deeper offsets process more rows.

What This Case Tells Us

The problem of page=5000 being slow won't necessarily be solved by tweaking the SQL a bit.

There are three things to consider:

  • How the DB handles deep offsets
  • Whether users need to move to arbitrary pages
  • Whether the process that scans the entire list can be split into a separate API

The pagination approach must be chosen not just for query performance but also with the UI, batch processing, and how data is searched in mind.

cursor-based pagination

Cursor-based pagination passes "where we read up to last time" instead of a page number.

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
  }
}

The essence of a cursor is a value that uniquely determines the boundary of the result set.

For example, if id increases monotonically and the list's order can be 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.

However, in real lists id DESC alone isn't always sufficient. A common case is a list ordered by created_at DESC. In that case, using only created_at as the cursor is dangerous. That's because multiple articles can share the same created_at. 
ORDER BY created_at DESC

This order looks natural to a human.

But if multiple articles have the same created_at, their order among themselves isn't stable.

If a page boundary falls within such a group of equal values, it causes 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, include them in the design too.

CREATE INDEX idx_articles_tenant_created_at_id
ON articles (tenant_id, created_at DESC, id DESC);

What's important in cursor-based pagination is to think of the sort order, the cursor, and the index as a set.

Even if you only make the API shape a cursor, if the order is unstable or the index doesn't match, the expected benefits are unlikely to appear.

Make the Cursor Opaque

It's easier to work with a cursor if 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"
}

However, rather than exposing this directly in the URL, encode it with something like base64.

That makes it easier to change the cursor's contents later.

For example, you may want to change a cursor that started as just id into created_at + id later.

If clients depend on the cursor's contents, changing it becomes difficult.

With an opaque cursor, the client only needs to pass the string it received into the next request.

Also, in APIs where filters or sorts can be changed, which conditions the cursor belongs to also matters.

GET /articles?tag=api&sort=createdAtDesc&cursor=...
This cursor is for the result set of tag=api and sort=createdAtDesc. If you use an old cursor after tag or sort has changed, its meaning breaks.

So you'll want either to include filter/sort information in the cursor and validate it, or to design it so the cursor is invalidated when the conditions change.

time-based pagination

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 so on, "from when to when" matters more than "which page."

In this case, time-based is quite natural.

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;

However, if you try to do full paging on time alone, 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

As SQL, you narrow by time range while looking at 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, it's easier to sort out if you think of time as the search condition and the cursor as the page boundary, separately.

Separate items and pageInfo in the API Response

For a REST API, I personally find it easier to work with a shape that separates items and pageInfo. 
GET /articles?limit=20&cursor=opaque

{
  "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. pageInfo is the information needed for controlling paging.

Separating the two makes things easier on the frontend.

hasNextPage is easy to implement by fetching limit + 1 rows. For example, with limit=20, 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;
If 21 rows come back, return only 20 and set hasNextPage: true. If 20 or fewer, set hasNextPage: false. This way you can tell whether there's a next page without running a separate COUNT(*) each time. totalCount is convenient, but tends to be heavy on huge lists.

Especially when search conditions are complex or there are many rows, just returning an exact count every time becomes costly.

So consider options like returning totalCount only on screens that need it, making it approximate, or returning it asynchronously.

With GraphQL, Use Connections

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
    }
  }
}
It looks a bit more complex than REST's items + pageInfo, but the idea is similar.
  • node: the actual data
  • cursor: the position of that element
  • pageInfo: 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")
If you do the same in REST, it's also clear to use separate names such as starting_after / ending_before as Stripe does. 
GET /articles?limit=20&startingAfter=...
GET /articles?limit=20&endingBefore=...

Backward paging is often implemented in SQL by fetching in reverse order and then reversing on the application side.

If the current display order is 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;
Reverse the fetched array on the application side to restore the DESC display order.

In the Frontend, Think of URL and Cache Separately

In frontend implementation, how you hold state changes depending on the pagination approach.

With page-based, it's easy to put state in the URL.

/articles?page=3&tag=api&sort=createdAtDesc

This form works well with sharing, revisiting, and the browser back button.

It's quite easy to handle for search results and category lists.

On the other hand, infinite scroll 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,
});
What matters here is to put all the conditions that define the list into the queryKey. 
["articles", filters, sort, pageSize]

Treat the cursor as continuation state within that queryKey.

If filters or sort change, treat it as a different list.

Otherwise, you'd end up using an old cursor against results for different conditions.

If you want the browser back button to feel natural too, you also need to think about how much to keep in the URL.

With page-based, just put ?page=3 in the URL.

With cursor-based infinite scroll, you have 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.

This depends on what the UI is expected to do, I think.

If You Try It on PostgreSQL

Rather than stopping at theory, trying it on PostgreSQL makes it much easier to understand.

For example, create around a million rows of data 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);

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;

Besides execution time, also look at:

  • Buffers
  • scan type
  • whether there's a sort
  • rows at upstream nodes
  • whether the index is used as expected

You should see that with a deep offset, even though only 20 rows are returned, many rows are processed before reaching that position.

On the other hand, cursor-based can narrow the start position with an index condition, so its behavior tends to stay closer to constant even at deep positions.

Summary

Pagination has a bigger design impact than it looks.

page=2 is easy, but it isn't necessarily enough by itself.

The main things to consider are:

  • Whether you need to jump to page numbers
  • Whether the list order is uniquely fixed
  • How much duplication or omission during data updates you can tolerate
  • How the DB handles deep pages
  • Whether the API response tells the client what to do next
  • Whether it fits the frontend's URL, cache, and browser back behavior

For admin screens and search results, page-based is still a natural choice.

However, 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 easy to work with.

Pagination isn't just about limit and offset.

How is the list read, in what order is it fixed, and what should be returned next so the client isn't lost?

If you design with all of that in mind, both the API and the frontend become much easier to implement.