Offset and Limit Pagination: Implementing Scalable REST API Requests

Previously in this course, we explored the need for pagination as a mechanism to protect server memory and network bandwidth. Now that we understand the "why," this lesson focuses on the "how": implementing offset and limit pagination to slice your collections into manageable chunks.

The Mechanics of Offset and Limit

Offset and limit pagination relies on two simple query parameters to navigate through a collection:

• limit: Defines the maximum number of resources to return in a single response.
• offset: Specifies the number of resources to skip before starting to return the result set.

If you have 100 tasks, a request for /v1/tasks?limit=10&offset=20 tells the server: "Skip the first 20 tasks, then give me the next 10." This creates a "window" into your data that remains constant in size regardless of the total record count.

Implementing Pagination in the Task Manager

To integrate this into our project, we need to modify our collection-fetching logic. When a client requests GET /v1/tasks, our backend must parse these parameters and pass them to our data layer.

Worked Example: Database Integration

Assuming we are using a standard SQL-based data access pattern, the implementation looks like this:

JAVASCRIPT
// Example controller logic
const getTasks = async (req, res) => {
  // 1. Parse and validate parameters
  const limit = parseInt(req.query.limit) || 10; // Default to 10
  const offset = parseInt(req.query.offset) || 0; // Default to 0

  // 2. Fetch data and total count
  // We need the total to let the client know how many pages exist
  const tasks = await db.tasks.findMany({
    take: limit,
    skip: offset,
    orderBy: { createdAt: CE9178">'desc' }
  });

  const total = await db.tasks.count();

  // 3. Return a standardized response
  res.status(200).json({
    data: tasks,
    meta: {
      total,
      limit,
      offset
    }
  });
};

By including the total count in the meta object, the client can calculate how many "pages" of data exist (e.g., Math.ceil(total / limit)), allowing them to build UI elements like pagination buttons.

Hands-on Exercise

Update your GET /v1/tasks route to support limit and offset.

1. Add default values so that a request without parameters still returns a sensible page.
2. Ensure your total count query does not include the limit or offset filters, as you need the count of the entire collection, not just the current slice.
3. Test your endpoint by requesting /v1/tasks?limit=5&offset=5 and verifying that the returned array length is 5 and the meta.total reflects the actual database size.

Common Pitfalls

• The "Deep Paging" Performance Hit: As the offset grows (e.g., offset=10000), the database must scan and skip thousands of rows before returning data. This makes offset-based pagination slow for very large datasets, which is why REST API Pagination: Choosing Between Offset and Cursor-Based is a vital conversation for high-scale systems.
• Missing Total Counts: Always include a total count unless performance constraints strictly forbid it. Without this metadata, clients cannot implement "Jump to last page" or "Total pages" indicators.
• Unbounded Limits: Always enforce a maximum limit (e.g., max 100). Without this, a malicious or accidental request with limit=1000000 could crash your service or exhaust your database connection.

FAQ

Q: Should I use page numbers instead of offset? A: page and limit are often easier for humans to understand (e.g., page=2 vs offset=10). Internally, you simply calculate offset = (page - 1) * limit.

Q: What happens if I delete an item while the user is paging? A: With offset pagination, items may shift "up" into the previous page, causing a user to see the same item twice or miss an item entirely. This is a known limitation of this strategy.

Q: Should I return the total count in the header or body? A: While some older APIs use custom headers like X-Total-Count, modern REST design favors putting this metadata inside the JSON response body for better visibility and easier parsing.

Recap

We’ve successfully implemented the standard offset-and-limit pattern. We've ensured our API remains responsive by enforcing limits, providing necessary metadata for client-side navigation, and safeguarding our database queries.

Up next: We'll explore cursor-based pagination to solve the performance issues inherent in deep offset-based paging.