REST API Pagination Best Practices: Offset, Cursor & Keyset

Every API that returns lists needs pagination. Without it, a simple “get all users” endpoint will eventually return a 50 MB JSON blob that crashes mobile clients and pegs your database.

But not all pagination is created equal. The right choice depends on your data size, how often rows are inserted or deleted, and whether clients need to jump to arbitrary pages.

The three pagination strategies

1. Offset pagination

The classic approach. The client sends offset (or page) and limit (or page_size).

GET /api/articles?offset=20&limit=10

Your SQL query:

SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Response:

{
  "data": [
    { "id": 21, "title": "Article 21" },
    { "id": 22, "title": "Article 22" }
  ],
  "pagination": {
    "offset": 20,
    "limit": 10,
    "total": 1532
  }
}

Pros:

• Simple to implement and understand
• Clients can jump to any page (offset = page * limit)
• Easy to show “Page 3 of 154” in the UI

Cons:

• Performance degrades with large offsets. OFFSET 100000 forces the database to scan and discard 100,000 rows.
• Inconsistent results when data changes. If a row is inserted before the current offset while a client is paginating, they see a duplicate. If a row is deleted, they skip one.

Use when: Your dataset is small (< 100K rows), data changes infrequently, and users need page numbers.

2. Cursor pagination

Instead of a numeric offset, the server returns an opaque cursor — typically a base64-encoded pointer to the last item in the current page. The client passes it back to get the next page.

GET /api/articles?limit=10
GET /api/articles?cursor=eyJpZCI6MjB9&limit=10

The cursor encodes enough information to resume from where the last page ended:

// Encoding a cursor
function encodeCursor(article) {
  return Buffer.from(JSON.stringify({
    id: article.id,
    created_at: article.created_at,
  })).toString('base64url');
}

// Decoding a cursor
function decodeCursor(cursor) {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString());
}

SQL query using the decoded cursor:

SELECT * FROM articles
WHERE (created_at, id) < ('2026-07-01T10:00:00Z', 20)
ORDER BY created_at DESC, id DESC
LIMIT 10;

Response:

{
  "data": [
    { "id": 19, "title": "Article 19", "created_at": "2026-06-30T14:00:00Z" },
    { "id": 18, "title": "Article 18", "created_at": "2026-06-30T12:00:00Z" }
  ],
  "pagination": {
    "next_cursor": "eyJpZCI6MTAsImNyZWF0ZWRfYXQiOiIyMDI2LTA2LTI5VDA4OjAwOjAwWiJ9",
    "has_more": true
  }
}

Pros:

• Consistent performance regardless of page depth (uses an index seek, not a scan)
• No duplicate or skipped items when data changes

Cons:

• Cannot jump to arbitrary pages
• Cursor is opaque — clients cannot construct their own
• Slightly more complex to implement

Use when: Large datasets, real-time feeds, infinite scroll UIs, or any API where data changes frequently.

3. Keyset pagination

Keyset is the non-opaque version of cursor pagination. Instead of an encoded cursor, the client passes the actual column values:

GET /api/articles?limit=10&after_id=20&after_date=2026-07-01

The SQL is identical to cursor pagination:

SELECT * FROM articles
WHERE (created_at, id) < ('2026-07-01', 20)
ORDER BY created_at DESC, id DESC
LIMIT 10;

Pros:

• Same performance benefits as cursor pagination
• Transparent — clients can see and construct the parameters
• Easy to debug

Cons:

• Exposes sort columns, which limits flexibility to change sorting later
• Multi-column sorting requires multiple parameters

Use when: Internal APIs or when you want cursor-like performance without opaque tokens.

Performance comparison

Here is what happens as your dataset grows:

Dataset size	Offset (page 1000)	Cursor/Keyset (page 1000)
10K rows	~5 ms	~2 ms
100K rows	~50 ms	~2 ms
1M rows	~500 ms	~2 ms
10M rows	~5000 ms	~2 ms

Offset pagination is O(offset + limit). Cursor/keyset pagination is O(limit) — it uses an index seek directly to the right position.

Adding HATEOAS pagination links

Regardless of which strategy you use, include navigation links in your response. This follows REST’s HATEOAS principle and lets clients navigate without building URLs themselves.

{
  "data": [...],
  "pagination": {
    "next_cursor": "abc123",
    "has_more": true
  },
  "links": {
    "self": "https://api.example.com/articles?cursor=xyz789&limit=10",
    "next": "https://api.example.com/articles?cursor=abc123&limit=10",
    "first": "https://api.example.com/articles?limit=10"
  }
}

For offset pagination, you can also include prev and last:

{
  "links": {
    "self":  "https://api.example.com/articles?page=3&limit=10",
    "first": "https://api.example.com/articles?page=1&limit=10",
    "prev":  "https://api.example.com/articles?page=2&limit=10",
    "next":  "https://api.example.com/articles?page=4&limit=10",
    "last":  "https://api.example.com/articles?page=154&limit=10"
  }
}

Implementation: Express.js with cursor pagination

import express from 'express';
import db from './db.js';

const app = express();

app.get('/api/articles', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const cursor = req.query.cursor;

  let query = 'SELECT * FROM articles';
  const params = [];

  if (cursor) {
    const decoded = JSON.parse(
      Buffer.from(cursor, 'base64url').toString()
    );
    query += ' WHERE (created_at, id) < ($1, $2)';
    params.push(decoded.created_at, decoded.id);
  }

  query += ' ORDER BY created_at DESC, id DESC LIMIT $' + (params.length + 1);
  params.push(limit + 1); // fetch one extra to check has_more

  const rows = await db.query(query, params);
  const hasMore = rows.length > limit;
  const data = rows.slice(0, limit);

  const nextCursor = hasMore
    ? Buffer.from(JSON.stringify({
        id: data[data.length - 1].id,
        created_at: data[data.length - 1].created_at,
      })).toString('base64url')
    : null;

  const baseUrl = `${req.protocol}://${req.get('host')}${req.path}`;

  res.json({
    data,
    pagination: {
      limit,
      has_more: hasMore,
      next_cursor: nextCursor,
    },
    links: {
      self: `${baseUrl}?limit=${limit}${cursor ? `&cursor=${cursor}` : ''}`,
      ...(nextCursor && {
        next: `${baseUrl}?limit=${limit}&cursor=${nextCursor}`,
      }),
      first: `${baseUrl}?limit=${limit}`,
    },
  });
});

Key detail: we fetch limit + 1 rows, then slice. This avoids a separate COUNT(*) query just to determine if there is a next page.

Common mistakes

1. No maximum page size

Always cap limit. Without a cap, a client can request ?limit=1000000 and dump your entire table:

const limit = Math.min(parseInt(req.query.limit) || 20, 100);

2. Missing total count with offset pagination

If you use offset pagination, clients often need the total count for their UI. But COUNT(*) on large tables is expensive. Options:

• Cache the count and refresh it periodically
• Use an approximate count: SELECT reltuples FROM pg_class WHERE relname = 'articles'
• Switch to cursor pagination and drop total count entirely

3. Sorting by a non-unique column

If you sort by created_at alone and two rows share the same timestamp, cursor pagination breaks — it skips or duplicates rows. Always include a unique tiebreaker:

-- Wrong: non-unique sort
ORDER BY created_at DESC

-- Right: unique tiebreaker
ORDER BY created_at DESC, id DESC

4. Exposing internal IDs in cursors

Do not put raw database IDs in keyset parameters if they are sensitive. Use opaque cursors instead, or ensure your IDs are not sequential (use UUIDs).

Decision flowchart

1. Do users need page numbers? Yes -> Offset pagination (if data is small)
2. Is the dataset large (> 100K rows)? Yes -> Cursor or keyset
3. Does data change frequently (inserts/deletes)? Yes -> Cursor or keyset
4. Is this an internal API where transparency matters? Yes -> Keyset
5. Is this a public API where you want flexibility? Yes -> Cursor (opaque)

Summary

Offset pagination is fine for small, stable datasets where users want page numbers. For everything else, cursor pagination gives you consistent performance and stable results. Always cap page sizes, include HATEOAS navigation links, and use a unique tiebreaker column in your sort order. Your future self debugging a “missing item” report will thank you.