Offset-Limit Pagination — The Standard Approach

In this tutorial, you will learn about Offset. We cover key concepts, practical examples, and best practices to help you master this topic.

Offset-limit pagination uses page and limit parameters to skip a number of records and return a limited set, with metadata about total pages and navigation.

What You'll Learn

You will learn offset-limit pagination implementation, parameter conventions, response format, and when this approach is appropriate.

Request Format

GET /api/users?page=2&limit=20

Server Implementation

app.get("/api/users", async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const offset = (page - 1) * limit;

  const [users, total] = await Promise.all([
    db.query("SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2",
      [limit, offset]),
    db.query("SELECT COUNT(*) FROM users")
  ]);

  res.json({
    data: users.rows,
    pagination: {
      page,
      limit,
      total: parseInt(total.rows[0].count),
      pages: Math.ceil(parseInt(total.rows[0].count) / limit),
      hasNext: page * limit < parseInt(total.rows[0].count),
      hasPrev: page > 1
    }
  });
});

Response Format

{
  "data": [],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 95,
    "pages": 5,
    "hasNext": true,
    "hasPrev": true
  }
}

Trade-offs

Pros: Simple, works with any database, easy to jump pages. Cons: Inefficient at large offsets, results shift if data changes, duplicate records on page boundaries.

Common Mistakes

1. Page starting at 0 — Inconsistent indexing. Pick 0-indexed or 1-indexed and be consistent.
2. No limit cap — Allowing unlimited page size.
3. COUNT query on every request — COUNT is expensive on large tables. Consider Caching.

Practice Questions

1. How is offset calculated from page and limit?
2. When does offset pagination perform poorly?
3. How do you calculate total pages?
4. Why might records appear on multiple pages?

Challenge

Implement offset pagination for a product catalog API with 50,000 products. Include page, limit, total, and page navigation metadata.

FAQ

What's Next

In the next lesson, you will learn page-based pagination.