Reducing Large Query Results in Cloudflare D1 With Pagination

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

1. Always define a stable ordering for paginated queries.
2. Set a server-side maximum page size.
3. Select only the columns required by the current screen or API operation.
4. Use offset pagination for simple page-based navigation and moderate datasets.
5. Consider cursor pagination for large or frequently changing datasets.
6. Keep cursor values opaque to API consumers.
7. Validate page and pageSize parameters.
8. Use appropriate indexes for filtering and ordering.
9. Avoid returning large text or JSON fields in list endpoints unless required.
10. Test pagination with realistic data volumes rather than a small development dataset.

Advantages and Disadvantages

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.