Any API that returns lists eventually needs pagination. The two most common approaches are offset pagination, which asks for "page 5", and cursor pagination, which asks for "the items after this one". Both are useful, but they behave very differently as data grows and changes.
How offset pagination works
Offset pagination uses LIMIT and OFFSET in SQL. Page 5 with 20 items per page skips the first 80 rows and returns the next 20.
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 80;It is easy to understand, supports jumping to any page and makes it simple to show a total count. For admin screens and modest datasets, it is often perfectly fine.
Where offset pagination struggles
- Deep pages get slow. The database still has to walk past every skipped row, so page 5,000 costs far more than page 1.
- Changing data causes duplicates and gaps. If new rows are inserted while a client pages through results, items shift and the client may see the same row twice or miss one entirely.
- Counting can be expensive. A total count on a large, filtered table may need a full scan.
How cursor pagination works
Cursor pagination remembers the position of the last item seen, usually its sort key, and asks for rows beyond it.
SELECT * FROM orders WHERE id < 98123 ORDER BY id DESC LIMIT 20;With an index on the sort column, the database jumps straight to the right place, so every page costs roughly the same. New inserts do not shift the window, so clients do not see duplicates.
Trade-offs of cursors
Cursors cannot jump to an arbitrary page, and they make total counts less natural. They also require a stable, unique sort order. If you sort by created_at, add the primary key as a tie-breaker so that rows with identical timestamps are not skipped.
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESCEncoding the cursor
Do not expose raw column values as your public contract. Encode the cursor as an opaque string, often Base64-encoded JSON, so you can change its internals later without breaking clients. Treat it as untrusted input and validate it when decoding.
Using cursor pagination in Laravel
Laravel has built-in support, which takes care of encoding and building the next and previous links.
$orders = Order::orderByDesc('id')->cursorPaginate(20);
return OrderResource::collection($orders);The response includes next_cursor and prev_cursor values that clients pass back as a query parameter.
Designing the response
Whichever approach you choose, keep the response shape consistent across endpoints. A typical structure includes a data array, a next link or cursor that is null on the last page, and optionally a has_more flag. Document a maximum page size and enforce it on the server, so a client cannot request a million rows at once.
Which should you choose?
- Use offset for small or slowly changing datasets, admin tables and interfaces that need numbered pages.
- Use cursors for feeds, activity logs, infinite scroll, mobile apps, data sync and any large table that grows quickly.
- Consider both when different consumers have different needs, but keep each endpoint consistent.
Filtering and sorting with cursors
Cursor pagination works well with filters, as long as the filter stays the same across pages and the sort order includes a unique tie-breaker. If clients can change the sort column, the cursor must encode that column's value too, and you need an index that matches each supported sort. Reject a cursor if it was created with different filters, rather than returning confusing results.
Wrapping up
Offset pagination is a sensible default for simple cases, but it degrades on large and busy tables. Cursor pagination offers steady performance and consistent results at the cost of random page access. Pick deliberately, add the right indexes and keep your cursors opaque, and your list endpoints will scale gracefully.
