Offset Limit

title: "Offset-Limit Pagination — The Most Common API Pagination Strategy" description: "Offset-limit pagination uses page/offset and limit/row count parameters to navigate result sets, offering simplicity but with consistency issues for changing data." date: 2026-06-28 lastmod: 2026-06-28 weight: 12 tags: [apis, pagination] }

Offset-limit pagination divides results using offset (how many to skip) and limit (how many to return), providing simple page navigation with SQL OFFSET/LIMIT semantics.

What You'll Learn

• How offset and limit parameters work
• Page-based vs offset-based variants
• When offset pagination is appropriate

Why It Matters

Offset-limit is the most widely supported pagination strategy, but it has well-known performance and consistency limitations.

Code Examples

# Offset-limit pagination
@app.route('/users')
def list_users():
    page = request.args.get('page', 1, type=int)
    limit = request.args.get('limit', 20, type=int)
    offset = (page - 1) * limit

    # SQL: SELECT * FROM users ORDER BY id LIMIT %s OFFSET %s
    users = db.execute("SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?",
                      [limit, offset])

    total = db.execute("SELECT COUNT(*) FROM users")[0][0]

    return jsonify({
        "data": [u.to_dict() for u in users],
        "pagination": {
            "page": page,
            "limit": limit,
            "total": total,
            "pages": (total + limit - 1) // limit,
            "has_next": page * limit < total,
            "has_prev": page > 1
        }
    })

// Express offset-limit pagination
app.get('/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, [{ count }]] = await Promise.all([
    db.query('SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2', [limit, offset]),
    db.query('SELECT COUNT(*) as count FROM users')
  ]);

  res.json({
    data: users.rows,
    pagination: {
      page,
      limit,
      total: parseInt(count),
      hasNext: page * limit < count
    }
  });
});

Common Mistakes

1. Missing Total Count

Without the total, clients don't know how many pages remain.

2. No Maximum Limit

Without a cap, clients request 1 million records per page.

3. Inconsistent Ordering

Offset pagination requires stable ordering. Missing ORDER BY causes duplicates.

4. Performance with Large Offsets

OFFSET 100000 scans and discards rows. Cursor pagination is faster for deep pages.

5. Page Number Zero

Page numbers should start at 1, not 0, for clarity.

Practice Questions

1. How is offset calculated from page and limit?
2. What is the performance problem with large offsets?
3. Why does offset pagination need consistent ordering?
4. What happens if new data is inserted while browsing pages?
5. What is a safe maximum limit value?

Answers:

1. offset = (page - 1) * limit.
2. The database still scans all skipped rows before returning results.
3. Without ORDER BY, the same row can appear on multiple pages.
4. Items can shift pages (phantom reads), causing duplicates or missed items.
5. 100-1000 depending on row size and server capacity.

Challenge: Implement offset-limit pagination with total count and page navigation. Test what happens when items are inserted between page requests.

FAQ