Skip to content
AgileLogics
Web Development

API Pagination Explained: Offset vs. Cursor-Based Pagination

· By Alex Rivera · 15 min read
Illustration showing database records being divided into pages using offset and cursor navigation.

Imagine opening an online store and asking its API to return every product in the catalogue. If the store has 100,000 products, sending all of them in one response would waste server resources, increase response times, and make the page difficult to use.

A better approach is to return a small group of products at a time.

This is called API pagination.

Pagination is used in product listings, customer dashboards, order histories, search results, activity feeds, and almost every application that works with growing collections of data.

However, choosing the wrong pagination strategy can create performance problems and even cause users to miss or see duplicate records.

In this guide, we will explain how API pagination works, compare offset-based and cursor-based pagination, and look at practical SQL and REST API examples.

1. What is API pagination?

API pagination is the process of dividing a large collection of records into smaller groups and returning one group per request.

Instead of requesting 10,000 products at once, a client might request 20 products, then request the next 20 when the user clicks Next or scrolls down.

For example:

GET /api/products?page=1&limit=20

The API returns the first 20 products.

The next request might be:

GET /api/products?page=2&limit=20

This approach reduces the amount of data transferred in each response.

A typical paginated API response could look like this:

{
  "data": [
    {
      "id": 101,
      "name": "Wireless Keyboard",
      "price": 49.99
    },
    {
      "id": 102,
      "name": "USB-C Hub",
      "price": 29.99
    }
  ],
  "pagination": {
    "current_page": 1,
    "per_page": 20,
    "total": 250,
    "last_page": 13
  }
}

The data property contains the records, while pagination provides information that the frontend can use to build navigation controls.

The exact response structure is a design choice. What matters is that the API documents its pagination behavior and provides a reliable way to retrieve the next set of records.

2. Why does pagination matter?

Pagination is not just a frontend convenience. It helps protect the application as its data grows.

Consider an order management system with 500,000 orders. Returning every order in one API response can create several problems:

  • Slow responses: The database and application server have more work to do.

  • High memory usage: Large result sets consume more server memory.

  • Network overhead: Clients download data they may never display.

  • Poor user experience: Users wait longer before seeing useful results.

  • Database pressure: Large queries can affect other operations running on the same database.

Pagination makes it possible to retrieve only the records needed for the current view.

It is especially important for APIs that return collections, such as:

  • Product catalogues

  • Customer records

  • Order histories

  • Blog posts

  • Transaction lists

  • Audit logs

  • Search results

3. Offset-based pagination

Offset pagination is one of the easiest methods to understand and implement.

It uses two common parameters:

  • limit: Maximum number of records to return.

  • offset: Number of records to skip before returning results.

For example:

GET /api/products?limit=20&offset=0

Returns the first 20 records.

GET /api/products?limit=20&offset=20

Skips the first 20 records and returns the next 20.

GET /api/products?limit=20&offset=40

Skips the first 40 records and returns the following 20.

SQL example

Suppose you have a products table.

You can retrieve the first 20 products with:

SELECT id, name, price, created_at
FROM products
ORDER BY id ASC
LIMIT 20 OFFSET 0;

To retrieve the next 20:

SELECT id, name, price, created_at
FROM products
ORDER BY id ASC
LIMIT 20 OFFSET 20;

The ORDER BY clause is important. Without a deterministic order, the database is not required to return records in a predictable sequence.

For reliable pagination, use a unique ordering, such as an ID or a combination of a timestamp and ID.

Advantages of offset pagination

  • Easy to understand and implement.

  • Supports numbered page navigation.

  • Makes it straightforward to jump to a particular page.

  • Works well for small and moderately sized datasets.

  • Supported by most relational databases.

Disadvantages of offset pagination

The main drawback is that large offsets can become expensive.

For example:

SELECT id, name
FROM products
ORDER BY id ASC
LIMIT 20 OFFSET 100000;

The database still has to process the rows it skips. The exact cost depends on the database, indexes, query plan, and data distribution, but large offsets can make requests slower.

There is also a consistency problem when records are added or deleted while someone is moving between pages.

Imagine the first request returns:

Page 1: Product 1, Product 2, Product 3

Before the user requests page 2, a new product is inserted at the beginning.

The next request uses OFFSET 3, so the results may become:

Page 2: Product 3, Product 4, Product 5

Product 3 appears twice.

This is one reason offset pagination can be less suitable for frequently changing datasets.

4. Cursor-based pagination

Cursor-based pagination takes a different approach.

Instead of telling the database how many records to skip, the client provides a reference to the last record it received.

That reference is called a cursor.

For example, imagine an API returns these products:

{
  "data": [
    { "id": 101, "name": "Keyboard" },
    { "id": 102, "name": "Mouse" },
    { "id": 103, "name": "Monitor" }
  ],
  "next_cursor": "eyJpZCI6MTAzfQ"
}

The client uses the returned cursor to request the next group:

GET /api/products?limit=3&cursor=eyJpZCI6MTAzfQ

The server uses that cursor to identify where to continue.

The cursor format is an implementation detail. A production API should generally treat it as an opaque token rather than requiring clients to understand its contents.

SQL example

For a simple dataset ordered by an increasing ID, the next page can be retrieved using:

SELECT id, name, price
FROM products
WHERE id > 103
ORDER BY id ASC
LIMIT 3;

This retrieves records after ID 103.

However, real applications often sort by creation date, price, or another field. In those cases, the cursor must contain enough information to continue the same ordering reliably.

For example, suppose products are sorted by newest first:

SELECT id, name, created_at
FROM products
WHERE
    (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3;

Here:

  • $1 is the timestamp of the last record from the previous page.

  • $2 is its ID.

  • $3 is the requested page size.

Why include the ID?

Because multiple records can have the same timestamp. Adding a unique ID as a tie-breaker creates a deterministic order and avoids ambiguity around the cursor position.

The database should also have an appropriate index for the filtering and sorting columns. For this example, an index on (created_at DESC, id DESC) is a useful starting point. Always verify the actual query plan with your database's query analysis tools.

Advantages of cursor pagination

  • Avoids scanning and skipping a large number of earlier rows in the same way offset pagination does.

  • Works well for large datasets.

  • Is suitable for continuously changing feeds and activity lists.

  • Provides a reliable way to continue from a known position when the ordering is properly designed.

Disadvantages of cursor pagination

  • More complicated to implement.

  • Does not naturally support jumping directly to page 50.

  • Requires careful handling of sorting and filtering.

  • May require token signing, encryption, or server-side token storage.

  • Does not automatically provide a fixed snapshot of changing data.

Cursor pagination is not a guarantee that every request will be fast or that records can never change between requests. Query design, indexing, data updates, and consistency requirements still matter.

5. Offset vs. cursor pagination: What is the difference?

Both approaches solve the same basic problem, but they are designed for different navigation patterns.

Comparison

Offset pagination

Cursor pagination

Navigation

Numbered pages

Next and previous positions

Implementation

Simpler

More complex

Large datasets

Can become expensive

Often more efficient

New records during navigation

Can shift page boundaries

Usually more stable

Jump to a specific page

Easy

Not naturally supported

Best suited for

Small or moderately sized lists

Large or frequently changing lists

Typical parameters

page, limit, offset

cursor, limit

A practical rule: Use offset pagination when users need numbered pages and the dataset is manageable. Consider cursor pagination when the dataset is large, frequently updated, or primarily navigated using Next and Previous controls.

6. Building cursor pagination in a Node.js API

Let's look at a practical example using Node.js, Express, and PostgreSQL.

Assume your products table contains:

id
name
price
created_at

We will return the newest products first and use both created_at and id to identify the next position.

The following example uses a simple Base64URL-encoded cursor for demonstration. It is not an opaque or secure token by itself. A production implementation should use a signed token or server-side cursor storage, as discussed below.

Step 1: Create the pagination endpoint

const express = require("express");
const { Pool } = require("pg");

const app = express();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

app.get("/api/products", async (req, res) => {
  try {
    const requestedLimit = Number.parseInt(req.query.limit, 10);
    const limit = Number.isInteger(requestedLimit)
      ? Math.min(Math.max(requestedLimit, 1), 100)
      : 20;

    let cursor = null;

    if (req.query.cursor) {
      try {
        cursor = JSON.parse(
          Buffer.from(req.query.cursor, "base64url").toString("utf8")
        );
      } catch {
        return res.status(400).json({
          message: "Invalid pagination cursor."
        });
      }

      if (
        !cursor ||
        typeof cursor.createdAt !== "string" ||
        !Number.isInteger(cursor.id)
      ) {
        return res.status(400).json({
          message: "Invalid pagination cursor."
        });
      }
    }

    const values = [limit + 1];
    let whereClause = "";

    if (cursor) {
      values.push(cursor.createdAt, cursor.id);
      whereClause = "WHERE (created_at, id) < ($2, $3)";
    }

    const result = await pool.query(
      `
        SELECT id, name, price, created_at
        FROM products
        ${whereClause}
        ORDER BY created_at DESC, id DESC
        LIMIT $1
      `,
      values
    );

    const hasMore = result.rows.length > limit;
    const products = result.rows.slice(0, limit);
    const lastProduct = products[products.length - 1];

    const nextCursor =
      hasMore && lastProduct
        ? Buffer.from(
            JSON.stringify({
              createdAt: lastProduct.created_at.toISOString(),
              id: lastProduct.id
            })
          ).toString("base64url")
        : null;

    res.json({
      data: products,
      pagination: {
        limit,
        has_more: hasMore,
        next_cursor: nextCursor
      }
    });
  } catch (error) {
    console.error("Product pagination failed:", error);

    res.status(500).json({
      message: "Unable to retrieve products."
    });
  }
});

This example demonstrates several important practices:

  1. Limit validation: The requested page size is restricted to a maximum of 100.

  2. Parameterized SQL: User input is passed as query parameters rather than inserted directly into SQL.

  3. Stable ordering: Both created_at and id are used for sorting.

  4. Fetch one extra record: The query requests limit + 1 records to determine whether another page exists.

  5. Next cursor: The cursor is generated from the last record actually returned to the client.

The extra record is only used to determine whether more results exist. It should not be included in the current response.

Step 2: Understand the API response

A successful request might return:

{
  "data": [
    {
      "id": 105,
      "name": "Wireless Headphones",
      "price": 79.99,
      "created_at": "2026-11-10T12:30:00.000Z"
    },
    {
      "id": 104,
      "name": "Laptop Stand",
      "price": 35.00,
      "created_at": "2026-11-09T10:15:00.000Z"
    }
  ],
  "pagination": {
    "limit": 2,
    "has_more": true,
    "next_cursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTExLTA5VDEwOjE1OjAwLjAwMFoiLCJpZCI6MTA0fQ"
  }
}

The frontend does not need to understand the cursor. It only needs to send it back in the next request.

Step 3: Request the next page

GET /api/products?limit=2&cursor=eyJjcmVhdGVkQXQiOiIyMDI2LTExLTA5VDEwOjE1OjAwLjAwMFoiLCJpZCI6MTA0fQ

The API decodes the cursor, applies the matching SQL condition, and returns the next records.

Important production considerations

The example is intended to demonstrate the pagination flow, not a complete production cursor-token implementation.

For a production API:

  • Validate cursor fields strictly, including timestamp format and valid ID ranges.

  • Sign cursor payloads with an HMAC or store cursor state on the server so clients cannot modify the position undetected.

  • Encrypt or use opaque server-side tokens if cursor contents contain sensitive information. Base64URL encoding is not encryption.

  • Never treat a cursor as an authorization credential. Authenticate and authorize every request normally.

  • Bind the cursor to the relevant sort order and filters, or reject it when those parameters change.

  • Consider token expiry and versioning when the cursor format may evolve.

  • Use database indexes that support the cursor filter and sort order.

  • Define how the API handles records that are inserted, updated, or deleted while a client is paging.

If users need a consistent snapshot across several requests, cursor pagination alone is not enough. You may need a snapshot or transaction strategy appropriate to your database and workload.

7. Which pagination method should you choose?

The best method depends on what users need to do and how frequently the underlying data changes.

Use offset pagination when:

  • Your application displays numbered pages.

  • Users need to jump directly to a particular page.

  • Your dataset is relatively small or moderate in size.

  • Your list does not change frequently during navigation.

  • Simplicity is more important than deep-page performance.

Examples include a small admin configuration list or a basic content management table.

Use cursor pagination when:

  • Your application contains millions of records.

  • Your data changes frequently.

  • Users scroll through a continuous feed.

  • You need to process large collections in batches.

  • Your API should avoid large offset scans.

Examples include transaction histories, activity feeds, large product catalogues, and event logs.

Can you support both?

Yes.

Some applications expose offset pagination for admin interfaces and cursor pagination for public APIs or infinite-scroll experiences.

The important point is to choose the behavior deliberately and document it. Avoid exposing multiple pagination styles without a clear use case.

8. Common API pagination mistakes

Even a correctly implemented pagination method can create problems when important details are overlooked.

1. Not defining a default page size

Always provide a documented default limit. Do not allow a request to return an unlimited number of records accidentally.

2. Not enforcing a maximum limit

A client should not be able to request hundreds of thousands of records in a single call. Apply a reasonable server-side maximum.

3. Missing an ORDER BY clause

Without a deterministic order, records can appear in inconsistent positions between requests.

4. Sorting by a non-unique column

If several records share the same timestamp, sorting only by timestamp can cause ambiguous cursor boundaries. Add a unique tie-breaker such as the record ID.

5. Ignoring filters and sorting changes

A cursor generated for one filter or sort order should not be reused blindly with another. Validate the request context.

6. Assuming cursor pagination provides a snapshot

A cursor identifies where to continue. It does not freeze the dataset. If a fixed view is required, implement an appropriate consistency strategy.

7. Exposing sensitive cursor information

Never put secrets or authorization data in a cursor. Base64 encoding does not hide its contents.

8. Forgetting the frontend experience

The frontend should know whether more records exist, prevent duplicate requests, handle loading states, and display useful errors when a cursor is invalid or expired.

9. Frequently asked questions

What is the difference between pagination and infinite scrolling?

Pagination divides records into pages and usually provides navigation controls. Infinite scrolling loads additional records as the user moves down the page. Infinite scrolling commonly uses cursor-based pagination behind the scenes.

Is cursor pagination always faster than offset pagination?

No. Cursor pagination can avoid the cost of skipping large numbers of records, but its performance depends on the query, indexes, sorting columns, and database execution plan. For small datasets, offset pagination may be perfectly adequate.

What is the best page size for an API?

There is no universal value. A limit between 20 and 50 is a reasonable starting point for many interactive lists, but the correct value depends on record size, query cost, network conditions, and user experience. Measure your actual workload.

Should an API return the total record count?

It depends on the interface. Numbered page navigation often benefits from a total count. Infinite scrolling may only need a has_more flag and a next cursor.

Calculating an exact count can add database work, especially for large or complex filtered datasets. If you return a total, document whether it is exact or estimated.

Can cursor pagination support previous-page navigation?

Yes, but it requires additional logic. One approach is to query in the reverse sort direction using the first record of the current page as the cursor, then reverse the returned results before sending them to the client.

Should cursor values be encrypted?

Not always. If the cursor contains only non-sensitive ordering information, a signed token may be sufficient to prevent tampering. If its contents must be hidden, use encryption or an opaque server-side token. In every case, authorization must be checked separately.

Conclusion

API pagination is a small design decision that can have a significant impact on application performance and usability.

Offset pagination is simple and works well when users need numbered pages or direct page navigation. Cursor pagination is often a better fit for large, frequently changing datasets and continuous feeds.

Before choosing a method, consider the size of your dataset, how often records change, the database query cost, and how users navigate the results.

Most importantly, use deterministic sorting, validate page sizes, handle changing data carefully, and test pagination with realistic datasets.

A well-designed pagination strategy helps your API remain predictable and responsive as your application grows.


Explore more developer tools

If you are working with APIs and backend development, these Agilelogics tools may also be useful:

Written by

Alex Rivera

AI/ML writer covering AI assistants, coding tools, and generative AI platforms — pricing, capabilities, and how they actually compare.

Share