When an application starts working with a large amount of data, returning every matching row in a single database query can become a problem.
This is especially important when using Cloudflare D1. A query may be valid from a SQL perspective but still return more data than an application actually needs at one time. Large result sets can increase response size, memory usage, processing time, and the amount of data transferred between the database and application.
Pagination solves this problem by dividing a large result set into smaller pages.
Instead of returning 100,000 records at once, an API might return 50 or 100 records and provide enough information for the client to request the next page.
This article explains how to design pagination for Cloudflare D1 applications, how offset pagination differs from cursor pagination, how to write the SQL queries, and which design decisions matter when the database contains a large amount of data.
Why Pagination Is Important in Cloudflare D1
Consider an application that stores customer orders.
A query such as this can become expensive when the table grows:
SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC;
The application might only need the latest 50 orders, but the query is designed to return every matching record.
A better approach is to divide the results into pages.
For example:
Page 1 → 50 records
Page 2 → 50 records
Page 3 → 50 records
Page 4 → 50 records
This gives the application several advantages:
- Smaller database responses
- Lower memory usage
- Faster API responses
- Less network traffic
- Better frontend performance
- Easier handling of large datasets
- More predictable API behavior
Pagination is not only a frontend feature. It should be designed as part of the database query and API architecture.
Basic Offset Pagination
The simplest pagination technique uses LIMIT and OFFSET.
For example:
SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 50 OFFSET 0;
The first page returns 50 records.
The next page can use:
SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 50 OFFSET 50;
The third page becomes:
SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 50 OFFSET 100;
The general calculation is:
offset = (pageNumber - 1) × pageSize
For example, if the page size is 50 and the requested page is 4:
offset = (4 - 1) × 50
= 150
The SQL query would therefore skip the first 150 rows and return the next 50.
Implementing Offset Pagination in an API
The API can accept page and pageSize parameters.
For example:
GET /api/orders?page=1&pageSize=50
The server should validate these values before constructing the query.
A simple JavaScript example for a Cloudflare Worker can look like this:
export default {
async fetch(request, env) {
const url = new URL(request.url);
const page = Math.max(
Number.parseInt(url.searchParams.get("page") || "1", 10),
1
);
const pageSize = Math.min(
Math.max(
Number.parseInt(url.searchParams.get("pageSize") || "50", 10),
1
),
100
);
const offset = (page - 1) * pageSize;
const result = await env.DB.prepare(`
SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, id DESC
LIMIT ? OFFSET ?
`)
.bind(pageSize, offset)
.all();
return Response.json({
page,
pageSize,
data: result.results
});
}
};
The maximum page size is intentionally limited.
Without a maximum, a client could request an extremely large page size and defeat the purpose of pagination.
Always Use a Stable Sort Order
Pagination depends on ordering.
This query is risky:
SELECT id, customer_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 50 OFFSET 50;
The problem is that multiple orders may have exactly the same order_date.
A better query adds a unique column as a secondary ordering field:
SELECT id, customer_id, order_date
FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 50 OFFSET 50;
The combination of order_date and id provides a more predictable ordering.
This becomes particularly important when records are inserted while users are moving between pages.
Offset Pagination vs Cursor Pagination
Offset pagination is easy to understand, but it is not always the best choice for large datasets.
Cursor pagination uses the last record from the current page to determine where the next page begins.
For example, suppose the current page ends with:
order_date = 2026-11-05 10:30:00
id = 1250
The next query can request records after that position.
A keyset-style query can look like this:
SELECT id, customer_id, order_date, total_amount
FROM orders
WHERE
order_date < ?
OR (order_date = ? AND id < ?)
ORDER BY order_date DESC, id DESC
LIMIT ?;
The application binds the last order date, last ID, and page size.
This avoids depending on a large numeric offset.
When Cursor Pagination Is Better
Cursor pagination is particularly useful for:
- Large tables
- Infinite scrolling
- Activity feeds
- Order histories
- Audit logs
- Message lists
- Frequently changing datasets
- APIs where users normally move forward one page at a time
For example, an API response can return:
{
"data": [
{
"id": 1250,
"order_date": "2026-11-05T10:30:00"
}
],
"nextCursor": "..."
}
The client then sends the cursor when requesting the next page.
The cursor should represent the position in the ordered result set rather than simply being a page number.
Designing a Good Pagination Response
A pagination API should return enough information for the client to continue navigation.
For offset pagination, a response might contain:
{
"page": 2,
"pageSize": 50,
"data": [
{
"id": 101,
"customer_id": 25,
"total_amount": 450.00
}
]
}
For cursor pagination:
{
"data": [
{
"id": 101,
"customer_id": 25,
"total_amount": 450.00
}
],
"nextCursor": "..."
}
A cursor-based response does not necessarily need to expose internal database values directly. The cursor can be encoded and protected by the application.
Avoid Selecting Unnecessary Columns
Pagination reduces the number of rows returned, but column selection is equally important.
Avoid:
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 50;
Prefer:
SELECT id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 50;
If a table contains large text fields, JSON documents, or other large values, returning them on every list request can increase the response size considerably.
A common pattern is to return lightweight summary data first and load detailed information only when the user opens a record.
Do You Need a Total Record Count?
Some applications want to display:
Page 2 of 240
This usually requires knowing how many matching records exist.
A separate count query might look like:
SELECT COUNT(*)
FROM orders
WHERE customer_id = ?;
However, counting a large filtered dataset on every request can add unnecessary database work.
If the user interface does not require an exact total, cursor pagination with a nextCursor value can be simpler.
For example:
{
"data": [],
"nextCursor": "..."
}
The API can indicate whether another page exists without calculating the complete result count.
Handling Data Changes During Pagination
Data can change while a user is browsing.
Suppose a user loads page 1 and then a new order is inserted before they request page 2.
With offset pagination, the new row can change the position of existing records. This can result in duplicated or skipped records across pages.
Cursor pagination is generally more stable for this type of continuously changing data because the next request starts from a known position in the ordered dataset.
This is one reason cursor pagination is useful for feeds, logs, and other data that changes frequently.
Choosing a Page Size
There is no universal page size that works for every application.
Common choices include:
20 records
25 records
50 records
100 records
The correct value depends on:
- Average row size
- Query complexity
- Network response size
- User interface requirements
- Expected traffic
- Database workload
Do not allow clients to choose unlimited values.
For example:
Requested pageSize = 5000
Allowed pageSize = 100
The server should enforce the maximum.
Indexes and Pagination
Pagination does not automatically make every query fast.
Filtering and ordering should also be considered when designing indexes.
For example, if the application frequently runs:
SELECT id, customer_id, order_date
FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC, id DESC
LIMIT 50;
The database may benefit from an index designed around the filtering and ordering pattern.
The important point is that pagination and indexing solve different problems.
Pagination controls how much data is returned.
Indexes help the database find the required data efficiently.
Both should be considered together.
Common Pagination Mistakes
Returning Everything Before Applying Pagination
Do not retrieve the entire dataset in application code and then select the required page.
Bad approach:
Database → 100,000 records → Application → 50 records
Better approach:
Database → 50 records → Application
The filtering and pagination should happen as close to the database as practical.
Allowing Unlimited Page Sizes
An API that accepts any pageSize value can still produce extremely large responses.
Always enforce a reasonable server-side maximum.
Using Pagination Without ORDER BY
Without an explicit ordering rule, the application should not assume that database rows will appear in a particular order.
Always define the ordering required by the API.
Using Only a Non-Unique Sort Column
Sorting only by a timestamp or status can produce unstable ordering when multiple records share the same value.
Add a unique secondary column when appropriate.
Running COUNT(*) for Every Request
An exact total is useful for some interfaces, but it is not always necessary.
Avoid calculating expensive totals when the client only needs to know whether another page exists.
Best Practices
- Always define a stable ordering for paginated queries.
- Set a server-side maximum page size.
- Select only the columns required by the current screen or API operation.
- Use offset pagination for simple page-based navigation and moderate datasets.
- Consider cursor pagination for large or frequently changing datasets.
- Keep cursor values opaque to API consumers.
- Validate page and pageSize parameters.
- Use appropriate indexes for filtering and ordering.
- Avoid returning large text or JSON fields in list endpoints unless required.
- Test pagination with realistic data volumes rather than a small development dataset.
Advantages and Disadvantages
| Pagination Approach | Advantages | Disadvantages |
|---|
| Offset pagination | Simple, easy to implement, supports direct page navigation | Can become less efficient with large offsets and changing data |
| Cursor pagination | Better for large and changing datasets, avoids large offsets | More complex API design and does not naturally support jumping to page 100 |
| Small page size | Smaller responses and faster UI rendering | Requires more API requests |
| Large page size | Fewer requests | Larger responses and more processing |
Troubleshooting Pagination Problems
If users see duplicate records between pages, check the ORDER BY clause and whether the underlying data changes while pagination is occurring.
If records are skipped, check whether OFFSET pagination is being used against frequently changing data.
If later pages become slower, investigate large OFFSET values and consider cursor pagination.
If responses are still too large, reduce the page size and review the columns returned by the query.
If pagination queries are slow even with a small page size, inspect the filtering and ordering strategy and review the relevant indexes.
Summary
Pagination is an important part of designing Cloudflare D1 applications that work with large datasets.
Offset pagination is simple and works well when users need traditional page-based navigation. Cursor pagination requires more design work but is a better fit for large datasets and data that changes frequently.
A good pagination implementation should use stable ordering, reasonable page-size limits, focused column selection, appropriate indexes, and a response format that gives the client enough information to request the next page.
The goal is not simply to divide a large result into pages. The goal is to make the entire data-access path predictable, efficient, and safe as the dataset continues to grow.
Join the conversation! Your thoughts help the community grow.