Pagination: Offset, Cursor, and Keyset Strategies
Implement offset, cursor, and keyset pagination for a PHP/SQL API, understand their stable-ordering guarantees, and know why deep OFFSET pagination collapses in performance on large tables.
No API returns an entire table in one response — a collection endpoint has to page through potentially millions of rows in manageable chunks, and the strategy you pick for 'give me the next page' has real consequences for both correctness (does a row ever get skipped or duplicated if data changes between page requests?) and performance (does fetching page 5,000 take the same time as page 1?). This lesson covers the three pagination strategies you'll meet in real APIs: offset pagination (simple, but slow and occasionally inconsistent at scale), cursor pagination (opaque tokens that encode 'where I left off'), and keyset pagination (the technique that actually solves the performance problem, using indexed comparisons instead of counting-and-skipping rows).
Offset pagination: simple, and simply slow at scale
Offset pagination is the one most people reach for first: LIMIT 20 OFFSET 40 for 'page 3, 20 per page'. It's simple to implement and simple for a client to reason about ('give me page N'), and it supports jumping directly to an arbitrary page number, which cursor/keyset pagination cannot do.
The problem is that OFFSET doesn't skip rows for free — the database still has to scan and discard every row before the offset to find where the requested page starts. OFFSET 100000 LIMIT 20 means scanning (or index-walking) 100,020 rows to return 20 of them. On a small table this is invisible; on a table with tens of millions of rows, deep pages become dramatically slower than early ones, and that cost grows roughly linearly with the offset — this is exactly the performance cliff that makes offset pagination unsuitable for 'infinite scroll' style APIs on large datasets.
Offset pagination also has a subtler correctness issue: if rows are inserted or deleted between two page requests, the 'window' shifts — a row can be skipped entirely (never shown to the client) or shown twice, because 'page 3' is defined purely by position in a result set that isn't frozen between requests.
Cursor pagination: an opaque 'continue from here' token
Cursor pagination replaces 'give me page N' with 'give me the next batch after this token', where the token (cursor) encodes enough information for the server to resume exactly where the last page left off — often the last row's sort key, base64-encoded and possibly encrypted/signed so the client treats it as opaque. The client never constructs or interprets the cursor itself; it just passes back whatever the server gave it in the previous response's next_cursor field.
Because a cursor is anchored to an actual row's values rather than a numeric position, it doesn't suffer offset's 'the window shifted' correctness problem — resuming after 'the row with id 8532' still means the same thing even if rows were inserted or deleted elsewhere in the table. The tradeoff is that cursors don't support jumping to an arbitrary page ('show me page 50') — you can only go forward (or backward) relative to a known cursor, which is exactly the access pattern most real UIs (infinite scroll, 'load more') actually need.
Keyset pagination: cursors, implemented with indexed comparisons
Keyset pagination (sometimes called 'seek method' pagination) is the concrete SQL technique that usually sits underneath a well-implemented cursor: instead of OFFSET, you filter with a WHERE clause on the sort column(s) — WHERE id > :last_seen_id ORDER BY id LIMIT 20 — so the database can jump straight to the right starting point using an index, in roughly constant time regardless of how deep into the table you are, instead of scanning everything before it.
The key requirement for keyset pagination is a stable, unique sort order — a single non-unique column like created_at can have ties (two rows with the identical timestamp), which makes WHERE created_at > :last ambiguous about which of the tied rows come next and can silently skip or duplicate rows. The standard fix is a compound sort key that's guaranteed unique, typically (created_at, id) — sort by the natural column, then break ties by the primary key — and compare with a tuple/compound condition: WHERE (created_at, id) > (:last_created_at, :last_id).
• Offset: WHERE unrestricted, LIMIT :n OFFSET :skip — simple, supports arbitrary page jumps, gets slow and can skip/duplicate rows as offset grows. • Cursor: an opaque token round-tripped by the client, encoding where the last page ended — no arbitrary page jump, but stable under concurrent writes. • Keyset: the SQL technique behind a good cursor — WHERE (sort_col, id) > (:last_val, :last_id) ORDER BY sort_col, id LIMIT :n — uses an index, roughly constant time at any depth. • Always pick a unique compound sort key (e.g. (created_at, id)) for keyset pagination — a non-unique sort column alone can skip or duplicate rows across pages.
Limits and defending against abuse
Whichever strategy you use, the page size (limit/per_page) must be capped server-side, never trusted as-is from the client — an unbounded ?limit=1000000 request can single-handedly degrade the database for every other user. A sensible pattern is a default page size (e.g. 20) when the client doesn't specify one, and a hard maximum (e.g. 100) that the server clamps to even if the client asks for more, returning the clamped value in the response so the client isn't confused about why it got fewer rows than requested.
For genuinely large or export-style listings (pulling 'all rows' for a report, a data migration, or a bulk download) keyset pagination is close to mandatory — offset pagination on a multi-million-row table, walked page by page to the end, degrades to a near-quadratic total cost across the full walk, while keyset pagination stays close to linear because every page fetch is an indexed lookup regardless of depth.
Three pagination queries side by side
The compound WHERE (created_at, id) < (...) tuple comparison requires the tie-breaker (id) to make the sort order unique. A composite index on (created_at, id) is what makes the keyset WHERE clause fast — without it, the database still has to scan.
-- OFFSET (simple, slow at depth; page 3 of 20-per-page)
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;
-- KEYSET / cursor-backed (fast at any depth; resumes after a known row)
SELECT id, title, created_at FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id) -- DESC order: strictly "older than"
ORDER BY created_at DESC, id DESC
LIMIT 20;Code Examples
This is the classic offset implementation: it clamps the client-supplied per_page to a sane maximum, computes OFFSET from the page number, and additionally runs a COUNT(*) so the response can report total pages — which is itself an extra full-table scan cost on very large tables. It's simple and lets a client jump straight to page=50, but both the OFFSET walk and the COUNT(*) get more expensive as the table grows. Expected result: {"data":[...20 rows...],"page":3,"per_page":20,"total":48213,"total_pages":2411} $offset = ($page - 1) * $perPage;
Instead of skipping rows by position, this fetches strictly-after the last row the client has already seen, using a compound (created_at, id) comparison so ties on created_at are broken deterministically by id. Fetching limit + 1 rows is a common trick to detect 'is there a next page' without a separate COUNT(*) query. The response never exposes the raw cursor values to the client directly — it's base64-encoded JSON the client treats as opaque and just echoes back on the next request. Expected result: {"data":[...20 rows...],"next_cursor":"eyJjcmVhdGVkX2F0IjoiMjAyNC0wNi0wMSIsImlkIjo4NTMyfQ=="}
WHERE (created_at, id) < (:last_created_at,:last_id) Common mistake: Using only created_at (without the id tie-breaker) as the seek column breaks the moment two rows share the same timestamp — rows can be silently skipped or repeated across pages.
<?php
declare(strict_types=1);
function listArticlesOffset(PDO $pdo, int $page, int $perPage = 20): array {
$perPage = min(max($perPage, 1), 100); // clamp: never trust the client's limit as-is
$page = max($page, 1);
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare(
'SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT :limit OFFSET :offset'
);
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$total = (int) $pdo->query('SELECT COUNT(*) FROM articles')->fetchColumn();
return [
'data' => $rows,
'page' => $page,
'per_page' => $perPage,
'total' => $total,
'total_pages' => (int) ceil($total / $perPage),
];
// Fine for page=1..a few dozen on modest tables; OFFSET 500000 on a
// 10M-row table forces the DB to walk half a million rows to discard them.
}<?php
declare(strict_types=1);
function listArticlesKeyset(PDO $pdo, ?string $cursor, int $limit = 20): array {
$limit = min(max($limit, 1), 100);
// Cursor is an opaque, base64-encoded JSON pair: the sort key + tie-breaker
// of the last row the client already saw. The client never builds this
// itself -- it just echoes back whatever we handed it last time.
[$lastCreatedAt, $lastId] = $cursor !== null
? json_decode(base64_decode($cursor), true)
: [null, null];
if ($cursor === null) {
$stmt = $pdo->prepare(
'SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT :limit'
);
$stmt->bindValue(':limit', $limit + 1, PDO::PARAM_INT); // fetch one extra to know if there's a next page
$stmt->execute();
} else {
// Compound "seek" comparison uses the (created_at, id) index directly --
// roughly constant-time regardless of how deep this page is.
$stmt = $pdo->prepare(
'SELECT id, title, created_at FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DE
…Why OFFSET gets slower with depth, keyset doesn't
OFFSET forces the database to walk and discard every prior row; keyset jumps straight there via an index.
OFFSET pagination cost vs. page depth:
page 1 (OFFSET 0) ▓ fast — scan ~20 rows
page 100 (OFFSET 1980) ▓▓▓ slower — DB walks ~2000 rows, keeps 20
page 5000 (OFFSET 99980) ▓▓▓▓▓▓▓▓▓▓ much slower — DB walks ~100000 rows
cost grows roughly with OFFSET itself
KEYSET pagination cost vs. page depth:
page 1 (no cursor) ▓ fast — index seek to the start
page 100 (cursor #1980) ▓ fast — index seek directly to (created_at, id)
page 5000 (cursor #99980) ▓ fast — same index seek, same cost as page 1
cost stays ~constant, independent of depthImportant Rules
Always clamp the client-requested page size server-side (a default and a hard maximum) — never trust an unbounded limit/per_page value.
Prefer keyset (seek-method) pagination over deep OFFSET for large or fast-growing tables — its cost stays roughly constant with depth.
Use a unique, stable compound sort key for keyset pagination (e.g. (created_at, id)) — a non-unique sort column alone can skip or duplicate rows.
Treat cursors as opaque tokens from the client's point of view — the client only ever echoes back what the server gave it, never constructs one.
Reserve offset pagination for small tables or UIs that genuinely need 'jump to page N' — it's the only strategy that supports that directly.
Index the exact columns used in the keyset WHERE/ORDER BY clause — the performance win depends entirely on that composite index existing.
Why this matters at scale
GitHub's REST API historically supported offset-style page/per_page parameters but pushes large consumers toward its GraphQL API's cursor-based Relay-style connections (first, after) for exactly this reason — offset pagination on a repository with millions of commits or issues becomes prohibitively slow deep in the list. Stripe's list APIs use cursor pagination (starting_after/ending_before) exclusively for the same reason: at their scale, OFFSET-style paging simply isn't viable.
SELECT * FROM articles ORDER BY created_at DESC LIMIT 20 OFFSET 500000 -- on a 10M-row table, every page request
SELECT * FROM articles WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20
Deep OFFSET forces the database to scan and discard half a million rows on every single request for that page — the cost grows with depth. The keyset version seeks directly to the right position via an index, at roughly constant cost no matter how deep the page is.
WHERE created_at < :last_created_at ORDER BY created_at DESC LIMIT 20 -- sort key alone, no tie-breaker
WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20
If multiple rows share the exact same created_at timestamp, comparing on created_at alone can't determine which of the tied rows belong on which page — rows can be silently skipped or duplicated. Adding a unique tie-breaker (the primary key) makes the sort order — and the pagination — deterministic.
Why does deep OFFSET pagination get slower as the offset grows? A. OFFSET queries always require a full table lock B. The database must scan and discard every row before the offset to find where the requested page starts C. JSON serialization gets slower with larger offsets D. OFFSET pagination doesn't actually get slower
B. The database must scan and discard every row before the offset to find where the requested page starts — OFFSET doesn't skip rows for free — the database walks through (and discards) every row before the offset on every request, so cost grows with how deep the requested page is.
Why does keyset pagination require a unique, stable compound sort key like (created_at, id) instead of just created_at? A. PHP requires at least two ORDER BY columns B. A non-unique sort column alone can have ties, making it ambiguous which tied rows belong on which page — causing skipped or duplicated rows C. It makes the SQL query shorter D. id must always be the first sort column
B. A non-unique sort column alone can have ties, making it ambiguous which tied rows belong on which page — causing skipped or duplicated rows — If multiple rows share the same value in the primary sort column, comparing on that column alone can't determine a deterministic boundary between pages; adding a unique tie-breaker (like the primary key) fixes this.
Which pagination strategy allows a client to jump directly to an arbitrary page number (e.g. page 50)? A. Cursor pagination B. Keyset pagination C. Offset pagination D. None of them support this
C. Offset pagination — Only offset pagination supports jumping directly to an arbitrary page by position; cursor and keyset pagination only support moving forward/backward relative to a known position (a cursor).
A products table has 50 million rows. An API needs a 'load more' style products listing sorted by price ascending, then by id to break ties. Design the keyset query for the first page and for a subsequent page given the last-seen row's price and id, and explain why COUNT(*) should not run on every request.
First page: SELECT id, name, price FROM products ORDER BY price ASC, id ASC LIMIT 20. Next page: SELECT id, name, price FROM products WHERE (price, id) > (:last_price, :last_id) ORDER BY price ASC, id ASC LIMIT 20, with a composite index on (price, id). COUNT(*) on 50M rows costs roughly as much as scanning the whole table and should be avoided per-request — omit total counts from cursor-paginated responses, or maintain a separate cached/approximate count instead.
Ascending sort means the seek condition uses '>' instead of '<'. COUNT(*) on 50M rows is itself an expensive scan on every request — only compute it if a total is genuinely required, ideally cached or approximate.