API Pagination Patterns: Offset, Cursor, and Keyset Explained

Pagination is not one-size-fits-all

Every API that returns a list eventually needs pagination. The naive approach of ?page=1&limit=20 works for small datasets, but as your data grows, you'll hit performance cliffs and consistency issues. Let's break down the three main patterns, when to use them, and the tradeoffs.

Offset pagination (the classic)

GET /items?offset=40&limit=20

Offset pagination uses offset (how many records to skip) and limit (how many to return). It's easy to implement, and most developers understand it immediately.

app.get('/items', (req, res) => {
  const offset = parseInt(req.query.offset) || 0;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const items = db.query('SELECT * FROM items ORDER BY id LIMIT ? OFFSET ?', [limit, offset]);
  res.json({ items, offset, limit });
});

Pros:

• Simple to implement and reason about.
• Works well for small to medium datasets.
• Easy to jump to any page directly.

Cons:

• Deep offsets are slow: the database scans and discards rows before the offset.
• Inconsistent under concurrent writes: if a new record is inserted, the same offset can return duplicates or miss records.
• Not ideal for real-time feeds or infinite scroll.

Cursor pagination (the modern standard)

GET /items?limit=20&cursor=eyJpZCI6MTAwfQ

Instead of counting rows, you pass an opaque cursor that encodes the position of the last item you saw. The server decodes it and queries WHERE id > cursor_id.

app.get('/items', (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const cursor = req.query.cursor ? Buffer.from(req.query.cursor, 'base64').toString() : null;
  const cursorId = cursor ? JSON.parse(cursor).id : 0;
  const items = db.query('SELECT * FROM items WHERE id > ? ORDER BY id ASC LIMIT ?', [cursorId, limit]);
  const nextCursor = items.length === limit ? Buffer.from(JSON.stringify({ id: items[items.length - 1].id })).toString('base64') : null;
  res.json({ items, nextCursor });
});

Pros:

• Fast regardless of page depth: the database uses the index on id to find the starting point.
• Consistent: new records inserted between requests don't cause duplicates or skips.
• Great for infinite scroll and real-time data.

Cons:

• Can't jump to a specific page number.
• Cursor encoding adds complexity (though libraries help).
• Requires a stable, unique sort key (usually id or a timestamp).

Keyset pagination (the efficient cousin)

Keyset pagination is similar to cursor but uses the actual column values instead of an opaque token. It's often used with composite keys.

GET /items?after_id=100&after_created_at=2023-01-01T00:00:00Z&limit=20

SELECT * FROM items 
WHERE (created_at, id) > (?, ?) 
ORDER BY created_at, id 
LIMIT ?

Pros:

• Very efficient, uses composite indexes.
• No encoding/decoding overhead.
• Transparent for debugging.

Cons:

• Requires the client to understand the sort columns.
• Exposes implementation details.
• More complex to implement correctly, especially with multiple sort fields.

Which one should you use?

• Offset: fine for admin panels, small datasets, or when you need page numbers. Avoid for public APIs with large data.
• Cursor: best default for most public APIs, especially for feeds or lists that change frequently.
• Keyset: good when you need maximum performance and control, and you're okay with exposing sort fields.

Practical tips

1. Always cap the limit to prevent abuse (e.g., max 100).
2. Include a next link in your response so clients don't have to construct URLs themselves.
3. For cursor pagination, encode the cursor as base64 or a signed token to prevent tampering.
4. If you need both page numbers and consistency, consider a hybrid: use cursor internally, but expose page numbers for navigation.
5. Document your pagination clearly: clients need to know what parameters to send and what to expect back.

A simple response envelope

{
  "data": [ ... ],
  "pagination": {
    "next": "/items?cursor=abc123",
    "prev": null,
    "total": null
  }
}

Note: total is often omitted with cursor pagination because counting all rows defeats the performance benefit. If you need totals, use a separate endpoint or a lightweight count query.

Final thoughts

Pagination is a small part of your API, but getting it wrong can cause production outages and frustrated clients. Start with cursor pagination for new endpoints. It's the safest choice for performance and consistency. If you're maintaining a legacy API with offset, consider migrating gradually, starting with the endpoints that have the most traffic.

Remember: the best pattern is the one that fits your data access patterns and client needs. There's no universal answer, but now you have the tools to make an informed decision.

Comments

[Dmitry Sergeev]

finally someone explained cursor pagination clearly, offset always kills my db performance once the table gets huge