Pagination in Depth — REST APIs in Depth

Every collection must be paginated with a capped page size. Offset pagination (?page=3&per_page=50) supports jumping to pages but slows on deep pages and shifts when data changes. Cursor/keyset pagination (?cursor=…) is fast and stable for feeds, timelines and large tables. Return either a next_cursor in the body or RFC 8288 Link headers. Totals are expensive on big tables — offer them optionally (?include_total=true) or approximate them.

Pagination strategies

Criterion	Offset/limit	Cursor (opaque)	Keyset (explicit)
Deep page performance	Degrades (scans skipped rows)	Constant	Constant
Stable under inserts	No (duplicates/skips)	Yes	Yes
Jump to page N	Yes	No	No
Arbitrary sort	Easy	Needs sort key in cursor	Needs index per sort
Client complexity	Low	Low (opaque token)	Medium (passes last values)
Best for	Small admin tables	Feeds, public APIs	Internal/export jobs

@app.get("/orders")
async def list_orders(limit: int = Query(20, ge=1, le=100), cursor: str | None = None, db=Depends(get_db)):
    stmt = select(Order).order_by(Order.created_at.desc(), Order.id.desc()).limit(limit + 1)
    if cursor:
        ts, oid = decode_cursor(cursor)
        stmt = stmt.where(tuple_(Order.created_at, Order.id) < (ts, oid))
    rows = list(await db.scalars(stmt))
    nxt = encode_cursor(rows[limit - 1].created_at, rows[limit - 1].id) if len(rows) > limit else None
    return {"items": rows[:limit], "next_cursor": nxt}

app.get("/orders", async (req, res) => {
  const limit = Math.min(Number(req.query.limit) || 20, 100);
  const params: unknown[] = [limit + 1];
  let where = "";
  if (req.query.cursor) {
    const { t, id } = JSON.parse(Buffer.from(String(req.query.cursor), "base64url").toString());
    params.push(t, id);
    where = "WHERE (created_at, id) < ($2, $3)";
  }
  const { rows } = await pool.query(`SELECT * FROM orders ${where} ORDER BY created_at DESC, id DESC LIMIT $1`, params);
  const page = rows.slice(0, limit);
  const last = page.at(-1);
  res.json({ items: page, next_cursor: rows.length > limit && last
    ? Buffer.from(JSON.stringify({ t: last.created_at, id: last.id })).toString("base64url") : null });
});

@GetMapping("/orders")
Map<String, Object> list(@RequestParam(defaultValue = "20") @Max(100) int limit,
                         @RequestParam(required = false) String cursor) {
    ScrollPosition position = cursor == null ? ScrollPosition.keyset() : Cursors.decode(cursor);
    Window<Order> window = orders.findFirst20ByOrderByCreatedAtDescIdDesc(position);   // keyset scrolling
    String next = window.hasNext() ? Cursors.encode(window.positionAt(window.size() - 1)) : null;
    return Map.of("items", window.getContent(), "next_cursor", next);
}

Common pitfalls

Unbounded page sizes.
Sorting by non-unique columns without a tiebreaker.
COUNT(*) on every request for million-row tables.

Key points

Always paginate with a max size
Cursors for large or changing data
Totals optional or approximate

Pagination strategies

Common pitfalls

Key points

Practice questions