If you're building an API that returns lists of data, you'll quickly run into a problem: what happens when that list has thousands or millions of items? You can't return everything at once—it would be slow, memory-intensive, and probably crash someone's browser.
That's where pagination comes in. But pagination isn't as simple as it sounds. There are multiple strategies, each with different trade-offs around performance, consistency, and ease of implementation.
In this guide, we'll explore the three main pagination approaches—offset-based, cursor-based, and keyset pagination—and show you how to implement each one effectively.
Before we dive into the different approaches, let's talk about why pagination is essential:
Performance: Returning 10,000 records in a single response is slow. The database query takes longer, serialization takes longer, network transfer takes longer, and the client has to process more data.
Memory: Large responses consume memory on both the server and client. This can lead to out-of-memory errors, especially on mobile devices or in resource-constrained environments.
User Experience: Users don't want to wait 30 seconds for a response. They want fast, incremental loading that feels responsive.
Cost: Transferring large amounts of data costs money, especially if you're paying for bandwidth or API calls.
Good pagination solves all these problems by breaking large datasets into manageable chunks.
This is the simplest and most common pagination strategy. You specify how many items to skip (offset) and how many to return (limit):
GET /api/pets?limit=20&offset=0 # First page
GET /api/pets?limit=20&offset=20 # Second page
GET /api/pets?limit=20&offset=40 # Third page
Here's a basic implementation in Node.js with PostgreSQL:
app.get('/pets', async (req, res) => {
const limit = parseInt(req.query.limit) || 20;
const offset = parseInt(req.query.offset) || 0;
// Validate inputs
if (limit > 100) {
return res.status(400).json({
error: 'Limit cannot exceed 100'
});
}
try {
// Get total count
const countResult = await db.query(
'SELECT COUNT(*) FROM pets'
);
const total = parseInt(countResult.rows[0].count);
// Get paginated results
const result = await db.query(
'SELECT * FROM pets ORDER BY id LIMIT $1 OFFSET $2',
[limit, offset]
);
res.json({
data: result.rows,
pagination: {
limit,
offset,
total,
hasMore: offset + limit < total
}
});
} catch (error) {
res.status(500).json({ error: 'Database error' });
}
});
Here's an example of the consistency problem:
// User requests page 1
GET /pets?limit=10&offset=0
// Returns pets 1-10
// Someone adds a new pet at position 1
// User requests page 2
GET /pets?limit=10&offset=10
// Returns pets 11-20, but pet #10 is now at position 11
// User sees pet #10 twice!
Cursor-based pagination solves the consistency problem by using a pointer (cursor) to track position instead of an offset. The cursor is typically an encoded identifier that points to a specific item.
GET /api/pets?limit=20
GET /api/pets?limit=20&cursor=eyJpZCI6MTAwfQ==
Here's an implementation:
app.get('/pets', async (req, res) => {
const limit = parseInt(req.query.limit) || 20;
let cursor = req.query.cursor;
// Decode cursor
let lastId = 0;
if (cursor) {
try {
const decoded = Buffer.from(cursor, 'base64').toString();
lastId = JSON.parse(decoded).id;
} catch (e) {
return res.status(400).json({ error: 'Invalid cursor' });
}
}
try {
// Query items after the cursor
const result = await db.query(
'SELECT * FROM pets WHERE id > $1 ORDER BY id LIMIT $2',
[lastId, limit + 1] // Fetch one extra to check if there's more
);
const hasMore = result.rows.length > limit;
const items = hasMore ? result.rows.slice(0, limit) : result.rows;
// Create next cursor
let nextCursor = null;
if (hasMore) {
const lastItem = items[items.length - 1];
nextCursor = Buffer.from(
JSON.stringify({ id: lastItem.id })
).toString('base64');
}
res.json({
data: items,
pagination: {
nextCursor,
hasMore
}
});
} catch (error) {
res.status(500).json({ error: 'Database error' });
}
});
For more complex sorting (like by creation date), you need a compound cursor:
app.get('/pets', async (req, res) => {
const limit = parseInt(req.query.limit) || 20;
let cursor = req.query.cursor;
let lastCreatedAt = null;
let lastId = 0;
if (cursor) {
try {
const decoded = Buffer.from(cursor, 'base64').toString();
const parsed = JSON.parse(decoded);
lastCreatedAt = parsed.createdAt;
lastId = parsed.id;
} catch (e) {
return res.status(400).json({ error: 'Invalid cursor' });
}
}
try {
let query, params;
if (lastCreatedAt) {
// Use compound condition for consistent ordering
query = `
SELECT * FROM pets
WHERE (created_at, id) > $2)
ORDER BY created_at, id
LIMIT $3
`;
params = [lastCreatedAt, lastId, limit + 1];
} else {
query = 'SELECT * FROM pets ORDER BY created_at, id LIMIT $1';
params = [limit + 1];
}
const result = await db.query(query, params);
const hasMore = result.rows.length > limit;
const items = hasMore ? result.rows.slice(0, limit) : result.rows;
let nextCursor = null;
if (hasMore) {
const lastItem = items[items.length - 1];
nextCursor = Buffer.from(JSON.stringify({
createdAt: lastItem.created_at,
id: lastItem.id
})).toString('base64');
}
res.json({
data: items,
pagination: {
nextCursor,
hasMore,
limit
}
});
} catch (error) {
res.status(500).json({ error: 'Database error' });
}
});
Keyset pagination (also called "seek method") is similar to cursor-based pagination but uses actual values instead of encoded cursors. It's more transparent and RESTful:
GET /api/pets?limit=20
GET /api/pets?limit=20&after_id=100
Implementation:
app.get('/pets', async (req, re
const limit = parseInt(rquery.limit) || 20;
const afterId = parseInt(req.query.after_id) || 0;
try {
const result = await db.query(
'SELECT * FROM pets WHERE id > $1 ORDER BY id LIMIT $2',
[afterId, limit + 1]
);
const hasMore = result.rows.length > limit;
const items = hasMore ? result.rows.slice(0, limit) : result.rows;
const response = {
data: items,
pagination: {
limit,
hasMore
}
};
if (hasMore) {
const lastItem = items[items.length - 1];
response.pagination.afterId = lastItem.id;
}
res.json(response);
} catch (error) {
res.status(500).json({ error: 'Database error' });
}
});
How you structure your pagination metadata matters. Here are common patterns:
{
"data": [...],
"pagination": {
"limit": 20,
"offset": 40,
"total": 1000,
"hasMore": true
}
}
Link: <https://api.example.com/pets?page=3>; rel="next",
<https://api.example.com/pets?page=50>; rel="last",
<https://api.example.com/pets?page=1>; rel="first",
<https://api.example.com/pets?page=1>; rel="prev"
Implementation:
function buildLinkHeader(baseUrl, page, limit, total) {
const lastPage = Math.celimit);
const links = [];
if (page < lastPage) {
links.push(`<${baseUrl}?page=${page + 1}&limit=${limit}>; rel="next"`);
links.push(`<${baseUrl}?page=${lastPage}&limit=${limit}>; rel="last"`);
}
if (page > 1) {
links.push(`<${baseUrl}?page=1&limit=${limit}>; rel="first"`);
links.push(`<${baseUrl}?page=${page - 1}&limit=${limit}>; rel="prev"`);
}
return links.join(', ');
}
app.get('/pets', async (req, res) => {
// ... pagination logic ...
const linkHeader = buildLinkHeader(
'https://api.example.com/pets',
page,
limit,
total
);
res.set('LinkkHeader);
res.json({ data: items });
});
{
"data": [...],
"pageInfo": {
"hasNextPage": true,
"hasPreviousPage": false,
"startCursor": "eyJpZCI6MX0=",
"endCursor": "eyJpZCI6MjB9"
}
}
Make sure your pagination fields are indexed:
CREATE INDEX idx_pets_id ON pets(id);
CREATE INDEX idx_pets_created_at_id ON pets(created_at, id);
Counting millions of rows is slow. Consider:
// Instead of exact count
const countResult = await db.query('SELECT COUNT(*) FROM pets');
// Use estimated count
const estimateResult = await db.query(`
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'pets'
`);
Or skip total count entirely:
{
"data": [...],
"pagination": {
"hasMore": true,
"nextCursor": "..."
}
}
For very large datasets, consider streaming:
app.get('/pets/export', async (req, res) => {
res.setHeader('Content-Type', 'application/json');
res.write('{"data":[');
const stream = db.query(
new QueryStream('SELECT * FROM pets ORDER BY id')
);
let first = true;
stream.on('data', (row) => {
if (!first) res.write(',');
res.write(JSON.stringify(row));
first = false;
});
stream.on('end', () => {
res.write(']}');
res.end();
});
stream.on('error', (err) => {
res.status(500).end();
});
});
Here's my recommendation based on use case:
Use offset pagination when:- You neepage numbers (like in a UI) - Dataset is small (< 10,000 items) - Random access is important - Simplicity is a priority
Use cursor pagination when:- Dataset is large (> 100,000 items) - Consistency is critical - You're building infinite scroll - Performance matters more than simplicity
Use keyset pagination when:- You want cursor benefits with transparency - Sorting is simple (single field or ID) - You want RESTful, cacheable URLs
For most modern APIs, I recommend cursor-based pagination. It scales better, provides consistent results, and works well with real-time data.
Sometimes you need to paginate both forward and backward:
app.get('/pets', async (req, res) => {
const limit = parseInt(req.query.limit) || 20;
const afterCursor = req.query.after;
const beforeCursor = req.query.before;
let query, params;
if (afterCursor) {
const { id } = decodeCursor(afterCursor);
query = 'SELECT * FROM pets WHERE id > $1 ORDER BY id LIMIT $2';
params = [id, limit + 1];
} else if (beforeCursor) {
const { id } = decodeCursor(beforeCursor);
query = `
SELECT * FROM (
SELECT * FROM pets WHERE id < $1 ORDER BY iLIMIT $2
) sub ORDER BY id
`;
params = [id, limit + 1];
} else {
query = 'SELECT * FROM pets ORDER BY id LIMIT $1';
params = [limit + 1];
}
const result = await db.query(query, params);
const hasMore = result.rows.length > limit;
const items = hasMore ? result.rows.slice(0, limit) : result.rows;
res.json({
data: items,
pageInfo: {
hasNextPage: afterCursor ? hasMore : false,
hasPreviousPage: beforeCursor ? hasMore : false,
startCursor: encodeCursor(items[0]),
endCursor: encodeCursor(items[items.length - 1])
}
});
});
Pagination is one of thosehings that seems simple until you actuaplement it. The right strategy depends on your specific needs:
Whatever you choose, make sure to: - Add proper indexes - Validate input parameters - Document your pagination clearly - Consider performance at scale - Test with realistic data volumes
Start with the simplest approach that meets your needs, and don't be afraid to switch strategies as your requirements evolve. Your users will appreciate fast, c pagination no matter which approach you choose.
