Gowtham Potureddi

Posted on Jul 11

SQL Pagination Done Right: OFFSET / FETCH / LIMIT / Keyset Cursors

#python #sql #dataengineering #interview

sql pagination is the single most-shipped, least-benchmarked primitive in every REST endpoint, GraphQL resolver, admin dashboard, and infinite-scroll feed a data engineer builds — and the single largest silent source of tail-latency incidents once the underlying table crosses 10 million rows. Every engineer types LIMIT 20 OFFSET 40 on their first week, ships it to production, and only discovers that sql limit offset performance collapses at page 100,000 when the on-call graph turns red at 3 a.m. This guide is the honest, dialect-aware tour of what actually happens inside the planner when you ask for "page 47" of a 100-million-row table, why the answer looks fine at page 1 and terrifying at page 1,000,000, and what to reach for instead.

The tour walks the four primitives you have to keep straight in 2026 — the classic sql offset fetch family (Postgres / MySQL LIMIT n OFFSET k, ANSI OFFSET k ROWS FETCH FIRST n ROWS ONLY, SQL Server 2012+ OFFSET k ROWS FETCH NEXT n ROWS ONLY, Oracle 12c+ FETCH FIRST n ROWS ONLY, BigQuery and Snowflake LIMIT n OFFSET k), the honest performance story of why OFFSET is O(k + n) per page and why that curve goes vertical past a few hundred thousand rows, the keyset pagination (a.k.a. seek method pagination) pattern that replaces "skip k rows" with "give me everything after this cursor tuple" and produces the O(log n + K) deep-page path that every modern feed on the internet actually uses, and full sql cursor pagination for sql infinite scroll feeds — opaque base64 tokens, HMAC signing, the GraphQL Relay connections spec, and the six-engine dialect matrix that tells you which keyword lands where. Every section ships a teaching block followed by a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works — so you leave with the two-line skeleton and the reason it wins.

When you want hands-on reps immediately after reading, drill the SQL pagination practice library →, rehearse on order-by-limit problems →, and sharpen the perf axis with the SQL indexing drill room →.

On this page

Why pagination matters in 2026
OFFSET / FETCH / LIMIT — dialect matrix
Why OFFSET is slow at page 1M
Keyset (seek method) pagination
Cursor pagination + dialect matrix
Cheat sheet — pagination recipe list
Frequently asked questions
Practice on PipeCode

1. Why pagination matters in 2026

The three shapes of `sql pagination` — infinite scroll, page-numbered UIs, and CSV-style exports — and why the OFFSET default fails all three at scale

The one-sentence invariant: sql pagination is the primitive that lets a client request "the next N rows of this ordered result set without re-fetching the previous rows," and the choice between OFFSET-based and keyset-based pagination is the single decision that determines whether your endpoint tail latency stays flat as the underlying table grows from 10K rows to 10B rows. Every backend eventually ships pagination; only a subset of them survive contact with a 10M-row table.

Where pagination actually shows up in production.

Infinite scroll feeds. Twitter / X home timeline, Instagram feed, TikTok For You page, GitHub notifications, Slack conversation history. The client asks "give me 20 more" every time the user scrolls, forever. Random-access-to-page-N is not a requirement; strict ordering and stability under new inserts are.
Page-numbered admin dashboards. "Show orders 1–50, page 2 is 51–100, ..." with a « ‹ 1 2 3 ... 47 › » pager at the bottom. Random-access-to-page-47 is a hard requirement; total row count usually is too. This is the shape where OFFSET is genuinely hard to replace.
Bulk CSV / Parquet exports. "Give me every row that matches this filter, split into 10K-row batches." Effectively a stream, not a paginator; users don't skip pages, they read from page 1 to the end.
API list endpoints. REST GET /orders?limit=20&cursor=... or GraphQL orders(first: 20, after: $endCursor). Almost always keyset-based in 2026 — the Relay spec bakes cursors into the wire protocol.
Data-warehouse dashboards. Superset / Metabase / Looker "next page" buttons on aggregate tables. Aggregates are usually small enough that OFFSET works; row-level fact-table pagination in warehouses needs keyset with a clustering key.

The OFFSET trap in one sentence.

LIMIT 20 OFFSET 1000000 asks the planner to read the first 1,000,020 rows in order and then discard the first 1,000,000. There is no "skip ahead" — the engine walks the ordered scan row by row until it has counted past the offset, then emits the next 20. Cost is O(k + n) per page. Across a full pagination from page 1 to page P, total cost is O(P × (k + n)) = O(P² × n) on a fixed page size — quadratic in the number of pages fetched.

The "consistent read across pages" problem.

Between page 1 and page 2 of an OFFSET paginator, other transactions may have INSERTed rows above the current window or DELETEd rows within it. The offset counter is positional, not keyed, so the same row can be seen twice or skipped entirely.
Example — a feed sorted by created_at DESC. Page 1 returns rows with timestamps [10:05, 10:04, 10:03, ..., 09:46] (20 rows). Between the two calls a new row lands at 10:06. Page 2 with OFFSET 20 LIMIT 20 now starts at the new row 21, which is the old row 20 (09:46) — the user sees 09:46 twice.
The dual — a row on page 1 gets deleted. Page 2 now starts one row earlier, skipping what was previously row 21. The user never sees that row.
Both problems are latent in every OFFSET-based paginator against a mutating source. Keyset pagination cures them because it navigates by key (the last row's tuple), not by position.

Why interviewers love pagination as a probe.

It's a fluency check on plan reading. Type EXPLAIN SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 1000000, then describe the plan out loud. A senior candidate says "Seq Scan or Index Scan of the first 1,000,020 rows plus a Limit that emits the last 20" in one breath.
It's a fluency check on index design. "What index does this need?" — a candidate who says "(created_at DESC, id DESC) for both OFFSET and keyset, but keyset is the one that actually uses it as a seek" signals senior instincts.
It's a fluency check on system design. "Design the feed for a Twitter-scale product." Every good answer includes keyset cursors as the default and OFFSET as an anti-pattern.
It's a fluency check on API design. "Design a REST endpoint that returns paginated results." Every good answer includes an opaque cursor token, hasNext / hasPrev, and no page=N query parameter.
It's a fluency check on failure modes. "What happens if two users insert between your page 1 and page 2 calls?" A senior candidate walks through the double-read / skipped-row failure and shows how keyset avoids it.

Six-engine keyword matrix — one-line summary.

Postgres 8.0+ / MySQL 4.0+ / Snowflake / BigQuery. LIMIT n OFFSET k. The de-facto default. Postgres also accepts the ANSI spelling.
ANSI SQL:2008. OFFSET k ROWS FETCH FIRST n ROWS ONLY. Verbose, deterministic, standard. Postgres, Oracle 12c+, SQL Server 2012+, and Db2 all support it.
SQL Server 2012+. OFFSET k ROWS FETCH NEXT n ROWS ONLY. The NEXT is a synonym for FIRST. Requires an ORDER BY clause — the parser refuses.
Oracle 12c+. OFFSET k ROWS FETCH FIRST n ROWS ONLY. Legacy Oracle (pre-12c) used the ROWNUM pseudocolumn pattern.
All engines. No dialect will save you from the O(k + n) cost. OFFSET is a plan shape, not a keyword.

What senior interviewers actually probe.

Do you know the OFFSET cost model? O(k + n) per page — the planner scans and discards.
Do you know when OFFSET is fine? For k ≤ 1,000 on a modestly wide table with a covering index — the scan is fast enough. Past k ≥ 100,000, latency climbs into the "someone will file a ticket" range.
Do you know the keyset skeleton? WHERE (order_col, id) < (:cursor_col, :cursor_id) ORDER BY order_col DESC, id DESC LIMIT n. One-shot tuple compare, one composite index seek.
Do you know the tie-breaker rule? Every paginated ORDER BY needs a deterministic tie-breaker on a unique column (usually id), or two rows with identical created_at will alternate between pages.
Do you know the NULLs rule? ORDER BY created_at DESC sorts NULLs first on some engines, last on others. Always spell it out — ORDER BY created_at DESC NULLS LAST.
Do you know the API-shape rule? Cursors are opaque, base64-encoded, and HMAC-signed. Never expose the raw tuple to clients — it's a leaky abstraction and a tampering vector.

Worked example — the OFFSET query that ships to production on day one

Detailed explanation. The archetype: given events(id, user_id, created_at, payload), return the first page of 20 most recent events. This is the query every backend intern types in week one. It is fine — for page 1. It becomes a tail-latency incident somewhere past page 10,000.

Question. Write a Postgres query that returns the N-th page of 20 most recent events, given a page number P (0-indexed). Then note the cost model.

Input.

id	user_id	created_at	payload
1	42	2026-07-01 09:00	login
2	42	2026-07-01 09:05	click
3	17	2026-07-01 09:10	login
...	...	...	...
1000000	91	2026-07-10 08:59	logout

Code.

-- Page P of size 20, 0-indexed
SELECT id, user_id, created_at, payload
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET (:page * 20);

Step-by-step explanation.

ORDER BY created_at DESC, id DESC — the compound sort key. The id DESC tie-breaker ensures deterministic ordering when two events share a millisecond.
LIMIT 20 — the page size. Clients typically pass page_size as a query parameter capped at 100 or 500.
OFFSET (:page * 20) — skip this many rows. On page 0, the offset is 0 and the query is fast. On page 50,000, the offset is 1,000,000 and the planner walks a million rows before emitting anything.
The planner picks either a Seq Scan (if no covering index) or an Index Scan on (created_at DESC, id DESC). Either way, it walks the ordered stream one row at a time until it has counted past the offset, then emits the next 20.
Cost model — O(k + n) where k is the offset and n is the limit. Wall-clock latency grows linearly with k.

Output (page 0, most recent 20 first).

id	user_id	created_at	payload
1000000	91	2026-07-10 08:59	logout
999999	42	2026-07-10 08:58	click
...	...	...	...

Rule of thumb. OFFSET is fine up to k ≈ 1,000 on a table with a covering index. Past that, latency becomes user-visible; past k ≈ 100,000, latency crosses SLA. If your product needs a « ‹ 1 2 ... 47 › » pager over a 10M+ row table, either cap the offset (Facebook's "5,000 pages" cap) or switch to keyset for the deep pages.

Worked example — the OFFSET failure mode across concurrent writes

Detailed explanation. A common source of pagination bugs — between page 1 and page 2, other transactions mutate the underlying table and the OFFSET counter drifts. The interviewer wants to hear "OFFSET is positional; keyset is keyed."

Question. Given the same events table, describe what happens if a user reads page 1 (offset 0, limit 20), then between calls someone inserts a new event with a created_at newer than every existing row, then the user reads page 2 (offset 20, limit 20). Which rows does the user see?

Input.

Pre-insert — 100 events with created_at from 10:00:00 (id=1) to 10:01:39 (id=100). Sorted DESC, row 1 is 10:01:39.
Between calls — one new row lands with created_at = 10:02:00, id=101.

Code.

-- Page 1 (returned to the user first)
SELECT id, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
-- Returns id=100 down to id=81

-- Concurrent insert lands: id=101, created_at=10:02:00

-- Page 2 (returned to the user second)
SELECT id, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20;
-- Returns id=81 down to id=62 — id=81 IS SEEN TWICE

Step-by-step explanation.

Page 1 returns rows 100 → 81 (the twenty most recent). The user reads them.
Between calls, one new row lands (id=101). The ordered stream is now 101, 100, 99, ..., 1.
Page 2 asks for offset 20 limit 20 from the new ordered stream. Offset 20 is now id=81; offset 39 is id=62.
The user sees id=81 on both page 1 and page 2 — the duplicate — because the positional offset drifted by one when id=101 was inserted.
Symmetrically, if id=90 had been deleted between calls, page 2 would start at what was previously id=80 — the row at previous position 21 (id=80) would be skipped.

Output. Page 1 shows ids 100 → 81. Page 2 shows ids 81 → 62. id=81 appears in both.

Rule of thumb. OFFSET is positional and drifts under concurrent inserts / deletes. Keyset navigates by key (the last row's tuple), so a new row appearing above the cursor doesn't affect subsequent pages, and a deleted row inside a previous page doesn't cause a skip. Every social feed in the world uses keyset for this reason.

Worked example — engine dialect quickstart

Detailed explanation. A quick sanity check — the six most common warehouses in 2026 and the pagination keyword each supports. Knowing this cold is a senior signal in system-design interviews.

Question. Given a list of engines, mark each with the OFFSET / LIMIT / FETCH keyword combination it supports as of 2026.

Input. Postgres 16, MySQL 8, SQL Server 2022, Oracle 19c, Snowflake, BigQuery.

Code. (No code — a dialect matrix answer.)

Engine          | Preferred syntax                          | Notes
Postgres 9+     | LIMIT n OFFSET k (ANSI also supported)    | ORDER BY strongly recommended
MySQL 8         | LIMIT n OFFSET k or LIMIT k, n            | ORDER BY strongly recommended
SQL Server 2012+| OFFSET k ROWS FETCH NEXT n ROWS ONLY      | ORDER BY REQUIRED
Oracle 12c+     | OFFSET k ROWS FETCH FIRST n ROWS ONLY     | pre-12c uses ROWNUM
Snowflake       | LIMIT n OFFSET k                          | avoid OFFSET past 1M
BigQuery        | LIMIT n OFFSET k                          | OFFSET slow; consider partition prune

Step-by-step explanation.

Postgres has supported the LIMIT n OFFSET k spelling since 8.0 and the ANSI OFFSET k FETCH FIRST n ROWS ONLY since 8.4. Both compile to identical plans.
MySQL supports LIMIT n OFFSET k and the shorthand LIMIT k, n (note the reversed operand order). Both compile to the same plan.
SQL Server 2012 introduced OFFSET / FETCH and requires an ORDER BY clause — the parser refuses without one, unlike Postgres and MySQL which allow (but discourage) unordered pagination.
Oracle 12c (2013) added ANSI OFFSET / FETCH. Older Oracle used a ROWNUM filter — WHERE ROWNUM <= 100 MINUS WHERE ROWNUM <= 80 — messy but the only pre-12c option.
Snowflake and BigQuery both accept LIMIT n OFFSET k but neither warehouse is optimised for deep OFFSET — micro-partitions on Snowflake or partition-and-cluster pruning on BigQuery mean the scan-and-discard cost is amplified by columnar-read overhead.

Output. The matrix above.

Rule of thumb. Memorise the six-row list. In an interview, if asked "how do you paginate on X," the first sentence should be the keyword combination and the second should be "with ORDER BY plus a deterministic tie-breaker on id."

Senior interview question on when to use OFFSET vs keyset

A senior interviewer often opens with: "You're designing the paginated /api/v1/orders endpoint for a two-billion-row orders table. The product wants both an infinite-scroll consumer app and a page-numbered admin dashboard with « ‹ 1 2 ... 100000 › ». Which pagination strategy do you pick for each, and why?"

Solution Using dual-endpoint design — keyset for infinite-scroll, capped OFFSET for admin

# Consumer app — infinite scroll, keyset-only, opaque cursors
# GET /api/v1/orders?first=20&after=<cursor>
def list_orders_keyset(first: int, after_cursor: str | None):
    # Cap first at 100 to protect the DB
    first = min(first, 100)

    if after_cursor:
        ts, oid = decode_and_verify_hmac(after_cursor)
        rows = db.query(
            """
            SELECT id, created_at, user_id, total
            FROM orders
            WHERE (created_at, id) < (%s, %s)
            ORDER BY created_at DESC, id DESC
            LIMIT %s + 1
            """,
            (ts, oid, first),
        )
    else:
        rows = db.query(
            """
            SELECT id, created_at, user_id, total
            FROM orders
            ORDER BY created_at DESC, id DESC
            LIMIT %s + 1
            """,
            (first,),
        )

    has_next = len(rows) > first
    rows = rows[:first]
    end_cursor = (
        encode_hmac(rows[-1].created_at, rows[-1].id) if rows else None
    )
    return {
        "edges": [{"node": r, "cursor": encode_hmac(r.created_at, r.id)} for r in rows],
        "pageInfo": {"hasNext": has_next, "endCursor": end_cursor},
    }


# Admin dashboard — page-numbered, capped OFFSET, total_count
# GET /admin/orders?page=<int>&page_size=<int>
def list_orders_admin(page: int, page_size: int):
    # Cap page at 5000, page_size at 200
    page = min(max(page, 0), 5_000)
    page_size = min(page_size, 200)

    total_count = db.query_scalar("SELECT COUNT(*) FROM orders")
    rows = db.query(
        """
        SELECT id, created_at, user_id, total
        FROM orders
        ORDER BY created_at DESC, id DESC
        LIMIT %s OFFSET %s
        """,
        (page_size, page * page_size),
    )
    return {
        "rows": rows,
        "page": page,
        "page_size": page_size,
        "total_count": total_count,
        "total_pages": (total_count + page_size - 1) // page_size,
    }

Step-by-step trace.

Step	Consumer app (keyset)	Admin dashboard (OFFSET)
1	client calls with `after=null`, `first=20`	client calls with `page=0`, `page_size=50`
2	first=capped at 100; no cursor	page capped at 5000; page_size at 200
3	SELECT with LIMIT 21 (first+1)	SELECT with LIMIT 50 OFFSET 0
4	21 rows returned; hasNext=true	50 rows returned
5	rows[:20] emitted; endCursor from row 20	rows emitted with total_count and total_pages
6	next call passes endCursor as after=	next call passes page=1
7	O(log n + 20) per page, flat forever	O(k + 50), fine for page ≤ 5000

The consumer app uses keyset because the infinite-scroll UX has no page-number requirement and needs constant-time deep pagination. The admin dashboard uses OFFSET because "jump to page 47" is a real product requirement, but caps the maximum page at 5,000 (like Facebook's search-result cap) so no admin can accidentally trigger a 100-million-row scan.

Output:

Endpoint	Cost model	Deep-page behaviour	Stability under writes
Consumer (keyset)	O(log n + K)	flat 5–10 ms at any depth	perfect — no drift
Admin (capped OFFSET)	O(k + n), k ≤ 5000×200=1M	slow-but-bounded past 100K rows	positional — mild drift

Why this works — concept by concept:

Dual-endpoint design matches the two consumer shapes — the consumer feed never needs "page 47"; it needs "the next 20 after this cursor forever." Keyset gives that with O(log n + K) deep pages. The admin dashboard does need "page 47"; capped OFFSET gives that with a bounded worst case.
LIMIT first + 1 is the peek-ahead trick — the endpoint fetches 21 rows but returns 20; the 21st row's existence tells the client whether hasNext is true. Zero extra queries, exact answer.
HMAC-signed opaque cursors — the cursor after parameter is a base64-encoded (created_at, id) tuple with an HMAC signature. Clients can't tamper with the tuple to leak rows they shouldn't see; servers can validate the signature in constant time before decoding.
Capped OFFSET protects the DB — a page-number cap of 5,000 with a page-size cap of 200 means the absolute worst case is OFFSET 999,800 LIMIT 200 — a ~1M-row scan, painful but bounded. Without the cap, an admin could drop page=1000000 and hang the DB.
Cost — Consumer endpoint is O(log n + K) per page where n is orders.rowcount and K = first. Admin endpoint is O(k + n) per page where k is page × page_size and n = page_size. On a 2B-row table with a covering (created_at DESC, id DESC) index, the consumer endpoint runs at ~8 ms per page at any depth; the admin endpoint runs at ~5 ms at page 0, ~50 ms at page 100, ~500 ms at page 10K, and ~5 s at page 100K (which the cap prevents from exceeding).

SQL
Topic — pagination
SQL pagination problems

Practice →

SQL Topic — order by / limit ORDER BY + LIMIT drills

Practice →

2. OFFSET / FETCH / LIMIT — dialect matrix

`sql offset fetch` and `LIMIT` across Postgres, MySQL, SQL Server, Oracle, Snowflake, and BigQuery — same primitive, six spellings, one shared perf model

The mental model in one line: every engine implements pagination with two knobs — a skip count (OFFSET k or LIMIT k, n's first operand) and a take count (LIMIT n or FETCH FIRST n ROWS ONLY) — and the choice of keyword changes nothing about the underlying plan shape, which is always "scan the ordered stream, discard k, emit n". Once you memorise the six spellings, translating a pagination query across dialects is mechanical.

Slot 1 — Postgres and MySQL (the LIMIT n OFFSET k family).

Postgres — SELECT ... ORDER BY ... LIMIT n OFFSET k. Also accepts the ANSI OFFSET k ROWS FETCH FIRST n ROWS ONLY since 8.4.
MySQL 8 — SELECT ... ORDER BY ... LIMIT n OFFSET k or the shorthand LIMIT k, n (note the reversed operand order — offset then limit). Both compile identically.
Snowflake — LIMIT n OFFSET k. Micro-partition pruning helps at page 1 but not at deep offsets.
BigQuery — LIMIT n OFFSET k. Documented as slow on deep offsets; the Google docs explicitly recommend keyset pagination past a few thousand rows.
Redshift, ClickHouse, DuckDB, SQLite — all support LIMIT n OFFSET k with the same semantics.

Slot 2 — the ANSI SQL:2008 spelling.

OFFSET k ROWS FETCH FIRST n ROWS ONLY — verbose, standard, deterministic. Every "ANSI-compliant" dialect accepts it.
OFFSET k ROWS FETCH NEXT n ROWS ONLY — the SQL-Server-idiomatic spelling; NEXT is a synonym for FIRST.
FETCH FIRST n ROWS ONLY — without OFFSET, equivalent to LIMIT n. Common on Oracle where LIMIT is not a keyword.
FETCH FIRST n ROWS WITH TIES — a variant that returns extra rows past the limit if they tie on the ORDER BY key. Rarely used in pagination (breaks page-size invariants) but the interviewer might ask about it.

Slot 3 — SQL Server-specific rules.

OFFSET k ROWS FETCH NEXT n ROWS ONLY — the SQL Server 2012+ syntax. Requires an ORDER BY clause — the parser refuses without one, unlike Postgres and MySQL.
Pre-2012 SQL Server — used ROW_NUMBER() OVER (ORDER BY ...) AS rn in a CTE and WHERE rn BETWEEN k+1 AND k+n to paginate. Ugly, but the only option.
SQL Server also supports TOP n for the first-page-only case (SELECT TOP 20 ...), which is not pagination — it just limits the result set without offset support.

Slot 4 — Oracle-specific rules.

Oracle 12c+ — OFFSET k ROWS FETCH FIRST n ROWS ONLY. Fully ANSI-compliant, same semantics as SQL Server 2012+.
Pre-12c Oracle (11g and earlier) — used the ROWNUM pseudocolumn: SELECT * FROM (SELECT rownum AS rn, t.* FROM t ORDER BY ... ) WHERE rn BETWEEN k+1 AND k+n. Legacy but still shows up in enterprise codebases.
Oracle 21c added FETCH FIRST n PERCENT ROWS ONLY — for percent-based limits — but this isn't a pagination primitive per se.

Slot 5 — BigQuery and Snowflake specifics.

BigQuery — LIMIT n OFFSET k, but OFFSET is documented as an anti-pattern past a few thousand rows. The recommended replacement is keyset. BigQuery also has LIMIT n with an implicit OFFSET 0 — same as everywhere.
Snowflake — LIMIT n OFFSET k. Snowflake's micro-partition metadata helps prune the initial scan but still has to walk the ordered result set past the offset. Deep OFFSET on a 1B-row Snowflake table is measurably slow.
Databricks SQL — LIMIT n OFFSET k since 2022. Same behaviour as Snowflake.

Slot 6 — the ORDER BY requirement.

Without ORDER BY, pagination is nondeterministic. Two calls with the same LIMIT / OFFSET may return different rows because the underlying scan order is not guaranteed stable.
SQL Server requires ORDER BY for OFFSET / FETCH — the parser rejects the query.
Postgres, MySQL, Snowflake, BigQuery allow omitting ORDER BY but will return unstable results. Every senior review comment says "add ORDER BY plus a tie-breaker on id."
Composite ORDER BY — the sort key must be a superset of the columns you want to compare in a cursor. For a "most recent" feed, the sort is ORDER BY created_at DESC, id DESC — id as tie-breaker.

Slot 7 — ties and tie-breakers.

If two rows share the same created_at (millisecond precision or worse), plain ORDER BY created_at DESC LIMIT 20 OFFSET 20 may put one on page 1 and the other on page 2 — or vice versa, on the next call. The results are unstable.
Fix — always include a unique tie-breaker: ORDER BY created_at DESC, id DESC. Every pagination code review comment worth reading says this.
FETCH FIRST n ROWS WITH TIES — the ANSI variant that returns all rows that tie on the sort key at the limit boundary. Rarely appropriate for pagination because it violates the "exactly n rows per page" invariant.

Slot 8 — NULLs ordering.

ORDER BY created_at DESC — where do NULLs go? Postgres and Oracle put NULLs first; MySQL and SQL Server put NULLs last; BigQuery puts NULLs first.
Fix — spell it out: ORDER BY created_at DESC NULLS LAST. Supported on Postgres, Oracle, and BigQuery. MySQL 8 supports the syntax but the behaviour matches the engine default anyway.
SQL Server does not support NULLS FIRST / LAST syntax — the workaround is ORDER BY (CASE WHEN created_at IS NULL THEN 1 ELSE 0 END), created_at DESC.

Common newbie mistakes.

Forgetting ORDER BY — the query "works" on page 1 but returns different rows on subsequent pages because the plan changed.
Omitting a unique tie-breaker — two rows with the same created_at can appear on both page 1 and page 2 or on neither.
Using SELECT * — projecting more columns than needed forces a heap fetch that OFFSET amplifies. Always project the minimum column set.
Assuming OFFSET is O(1) — it's O(k + n). On page 1M, that's a million rows scanned before the first byte is returned.
Assuming pagination is stable under writes — OFFSET is positional and drifts. Every social feed uses keyset.

How OFFSET interacts with EXPLAIN.

EXPLAIN SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 1000000 on Postgres — the plan shows Limit → Index Scan Backward using idx_events_created_at. The Limit step has actual rows=20 but actual loops = 1,000,020 — the engine walks a million rows before hitting the limit.
The same query with keyset — WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 20 — shows Limit → Index Scan Backward with actual rows=20 actual loops=20. One seek, twenty reads, done.
The Buffers: shared read=100000 line in EXPLAIN (ANALYZE, BUFFERS) is the smoking gun — OFFSET buffers grow linearly with k; keyset buffers stay constant.

Interview probes on syntax fluency.

"Which dialect requires ORDER BY for OFFSET/FETCH?" — SQL Server 2012+. Postgres, MySQL, Oracle, Snowflake, BigQuery all allow (but discourage) omitting it.
"What is the shorthand LIMIT 40, 20 in MySQL?" — offset 40, limit 20. Note the reversed operand order (offset first, then limit) — the most confused MySQL trivia question in interviews.
"How do you paginate on Oracle 11g?" — the legacy ROWNUM filter inside a subquery. Not the ANSI FETCH FIRST because that came in 12c.
"How do you handle NULLs on SQL Server's ORDER BY?" — the CASE WHEN … IS NULL trick, because SQL Server doesn't support NULLS FIRST / LAST.

Worked example — Postgres, MySQL, and SQL Server pagination side by side

Detailed explanation. The clearest way to see the dialect mechanics: write the same "20 most recent orders, page 47" query in three dialects. Once you can round-trip the syntax, the mental model locks in.

Question. Given a table orders(id, user_id, created_at, total), write the page-47-of-20 query in Postgres, MySQL 8, and SQL Server 2022. Note any syntactic requirements each engine imposes.

Input.

id	user_id	created_at	total
1	100	2026-07-10 09:00	25.00
2	101	2026-07-10 09:01	40.00
...	...	...	...

Code.

-- Postgres
SELECT id, user_id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 940;

-- MySQL 8 (identical to Postgres; also accepts LIMIT 940, 20 shorthand)
SELECT id, user_id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 940;

-- SQL Server 2022 — ORDER BY is REQUIRED
SELECT id, user_id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 940 ROWS
FETCH NEXT 20 ROWS ONLY;

Step-by-step explanation.

Postgres uses LIMIT 20 OFFSET 940. The order is "take 20, skip 940." Postgres also supports the ANSI spelling — OFFSET 940 ROWS FETCH FIRST 20 ROWS ONLY — and treats it identically.
MySQL 8 supports the identical LIMIT 20 OFFSET 940 plus the classic shorthand LIMIT 940, 20 (note the operand order — offset first). The shorthand is the one 90% of MySQL codebases still use; the explicit form is more readable.
SQL Server 2022 uses OFFSET 940 ROWS FETCH NEXT 20 ROWS ONLY. The NEXT is a synonym for FIRST. The ROWS keyword is required. ORDER BY is required — the parser refuses without one, giving a clear compile-time error.
All three plans compile to the same shape — walk the sorted stream past 940 rows, emit the next 20. Actual latency is nearly identical on all three engines for the same page depth.
Migrating pagination between the three is mechanical — swap LIMIT n OFFSET k for OFFSET k ROWS FETCH NEXT n ROWS ONLY (and vice versa).

Output (page 47 = rows 941–960).

id	user_id	created_at	total
999059	...	2026-07-10 08:15	30.00
...	...	...	...

Rule of thumb. Learn both spellings. Every migration from a MySQL app to a SQL Server backend or from Postgres to Oracle rewrites pagination. The mechanical swap is a five-second edit; forgetting it is a compile error.

Worked example — Oracle 11g's `ROWNUM` pattern vs Oracle 12c+'s `FETCH FIRST`

Detailed explanation. Legacy Oracle codebases still use the ROWNUM pseudocolumn for pagination. Interviewers love this — it's a fluency check on both "you know the old syntax" and "you know the new one."

Question. Write the "page 47 of 20 orders" query on Oracle 11g (legacy) and Oracle 19c (modern). Explain why the legacy pattern needs a subquery.

Input. (Same as above.)

Code.

-- Oracle 11g and earlier — ROWNUM inside a subquery
SELECT * FROM (
  SELECT rownum AS rn, o.*
  FROM (
    SELECT id, user_id, created_at, total
    FROM orders
    ORDER BY created_at DESC, id DESC
  ) o
  WHERE rownum <= 960
)
WHERE rn > 940;

-- Oracle 12c+ — ANSI FETCH FIRST
SELECT id, user_id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 940 ROWS
FETCH FIRST 20 ROWS ONLY;

Step-by-step explanation.

Oracle's ROWNUM is assigned after the WHERE clause but before the ORDER BY. The naive SELECT ROWNUM, ... FROM t ORDER BY x returns numbered-then-sorted rows, not sorted-then-numbered — the numbering is on the wrong data.
The workaround — a nested subquery. The inner SELECT orders the rows; the middle SELECT numbers them with ROWNUM; the outer SELECT filters by the numbered range.
The WHERE rownum <= 960 on the middle query is a common perf trick — Oracle can short-circuit once it has assigned 960 numbers, avoiding a full sort of the underlying table.
Oracle 12c (2013) shipped ANSI OFFSET / FETCH, so modern code should always prefer the second form. It's more readable, ships with the same plan shape, and is portable across engines.
If you inherit legacy Oracle code, recognise the three-level nested pattern instantly. It is one of the top-three most-migrated SQL constructs in Oracle-to-Postgres modernisation projects.

Output. Both queries return the same page 47.

Rule of thumb. On Oracle 12c+, always use OFFSET k ROWS FETCH FIRST n ROWS ONLY. On Oracle 11g and earlier, use the three-level ROWNUM subquery. Migrating from 11g to 12c is a mechanical rewrite; interview candidates who can spot both patterns and translate between them signal senior instincts.

Worked example — BigQuery's OFFSET warning and the recommended keyset alternative

Detailed explanation. BigQuery documents OFFSET as an anti-pattern past a few thousand rows. The recommended replacement is keyset — and the interviewer might ask why BigQuery is more sensitive to deep OFFSET than a row-store database.

Question. Given a 10B-row BigQuery events table partitioned by DATE(created_at), why is LIMIT 20 OFFSET 1000000 an anti-pattern? What's the recommended alternative?

Input. (Same events table as before, at 10B rows.)

Code.

-- BigQuery — anti-pattern past ~10K OFFSET
SELECT id, user_id, created_at, payload
FROM `project.dataset.events`
WHERE DATE(created_at) BETWEEN '2026-07-01' AND '2026-07-10'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 1000000;

-- BigQuery — recommended keyset
SELECT id, user_id, created_at, payload
FROM `project.dataset.events`
WHERE DATE(created_at) BETWEEN '2026-07-01' AND '2026-07-10'
  AND (created_at, id) < (@cursor_ts, @cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Step-by-step explanation.

BigQuery is a columnar warehouse — each column is stored separately and reads are page-oriented. Skipping a million rows means reading a million-row-worth of column data across every projected column, then discarding it.
Even with partition pruning on the DATE(created_at) filter, the OFFSET step happens after the scan — the pruning helps but does not eliminate the scan-and-discard cost.
The keyset alternative uses WHERE (created_at, id) < (@cursor_ts, @cursor_id) — a range predicate that BigQuery can push into partition and cluster pruning. If the table is clustered on created_at, the query touches only the blocks that contain the target page.
On a 10B-row events table with DATE partitioning and created_at clustering, OFFSET 1000000 runs in ~30 seconds; the keyset variant runs in ~200 ms — a 150× win.
Every BigQuery pagination guide since 2019 has recommended keyset for anything past a few thousand rows. The Snowflake guidance is identical (micro-partitions instead of BigQuery partitions, but the same shape).

Output.

Query	Rows scanned	Slot time	Wall-clock
OFFSET 1000000	1,000,020	30 s slot-seconds	~30 s
Keyset	20	0.02 s slot-seconds	~200 ms

Rule of thumb. In a columnar warehouse (BigQuery, Snowflake, Redshift), OFFSET's cost is amplified by the columnar-read overhead — projecting fewer columns helps a little, but the fundamental scan-and-discard cost stays. Keyset is the only stable long-form pagination on warehouses.

Senior interview question on cross-warehouse pagination portability

A senior interviewer might ask: "Design a paginated orders endpoint that runs identically on Postgres (production OLTP), Snowflake (analytics), and BigQuery (data-warehouse). Where can you use OFFSET, where must you use keyset, and how do you keep the API contract stable across all three?"

Solution Using dialect-dispatched dbt macro with a shared cursor contract

-- macros/paginate_orders.sql (dbt)
{% macro paginate_orders(cursor_ts=None, cursor_id=None, page_size=20) %}
  {%- if target.type == 'postgres' -%}
    -- OLTP: keyset with covering index (created_at DESC, id DESC)
    SELECT id, user_id, created_at, total
    FROM orders
    WHERE
      {% if cursor_ts is not none %}
        (created_at, id) < ({{ cursor_ts }}, {{ cursor_id }})
      {% else %}
        true
      {% endif %}
    ORDER BY created_at DESC, id DESC
    LIMIT {{ page_size }} + 1
  {%- elif target.type == 'snowflake' -%}
    -- Analytics: keyset with clustering key on created_at
    SELECT id, user_id, created_at, total
    FROM orders
    WHERE
      {% if cursor_ts is not none %}
        (created_at, id) < ({{ cursor_ts }}, {{ cursor_id }})
      {% else %}
        TRUE
      {% endif %}
    ORDER BY created_at DESC, id DESC
    LIMIT {{ page_size }} + 1
  {%- elif target.type == 'bigquery' -%}
    -- DW: keyset + partition pruning on DATE(created_at)
    SELECT id, user_id, created_at, total
    FROM `{{ project }}.orders`
    WHERE
      DATE(created_at) BETWEEN
        DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
      {% if cursor_ts is not none %}
        AND (created_at, id) < ({{ cursor_ts }}, {{ cursor_id }})
      {% endif %}
    ORDER BY created_at DESC, id DESC
    LIMIT {{ page_size }} + 1
  {%- endif -%}
{% endmacro %}

Step-by-step trace.

Dispatch branch	What runs	Why
Postgres	keyset with `(created_at, id)` covering btree	OLTP; index seek is O(log n)
Snowflake	keyset with `created_at` clustering key	analytics; micro-partition prune
BigQuery	keyset + partition prune on DATE(created_at)	warehouse; partition + cluster prune
All	LIMIT page_size + 1 for hasNext peek-ahead	uniform API contract
All	ORDER BY created_at DESC, id DESC	deterministic ordering

The macro dispatches on target.type. Postgres, Snowflake, and BigQuery each get a keyset variant optimised for that engine's storage model (btree, clustering, partitioning). The output schema and cursor contract are identical — every caller sees (id, user_id, created_at, total) plus a peek-ahead row to compute hasNext.

Output:

target	page cost	deep-page cost	cursor stability
Postgres	~5 ms	~5 ms at any depth	perfect
Snowflake	~200 ms	~200 ms at any depth	perfect
BigQuery	~150 ms	~150 ms at any depth	perfect

Why this works — concept by concept:

One cursor contract, three plan shapes — the API contract is stable ({cursor_ts, cursor_id, page_size}), the underlying plan adapts per engine. Callers never learn which warehouse is behind the endpoint.
Compile-time dispatch, zero runtime overhead — dbt evaluates the Jinja block at compile time. The emitted SQL is one of three variants; no runtime branching, no reflection, no penalty.
Postgres gets the covering btree — the composite index (created_at DESC, id DESC) is index-aligned with the ORDER BY, so the plan is index scan + LIMIT with no sort step.
Snowflake gets the clustering key — CLUSTER BY (created_at) on the orders table plus the tuple predicate prunes to a small set of micro-partitions per page.
BigQuery gets partition + cluster pruning — partition the table by DATE(created_at) and cluster by created_at. The 90-day window on the outer WHERE prunes at partition level; the tuple predicate then prunes within clusters.
Cost — Postgres is O(log n + K) per page, Snowflake and BigQuery are O(P + K) where P is the number of relevant partitions/clusters (typically 1–3 per page). All three are effectively flat across page depth — the whole point of keyset.

SQL
Topic — SQL
SQL problem library — 450+ DE-focused questions

Practice →

SQL Topic — order by / limit ORDER BY + LIMIT drills

Practice →

3. Why OFFSET is slow at page 1M

`sql limit offset performance` — the O(k + n) cost model, the wide-row memory story, and the "consistent read across pages" failure mode

The mental model in one line: sql limit offset performance is O(k + n) per page because the planner must walk the ordered scan past k rows and discard them before emitting the next n, so a page at depth k=1,000,000 costs 50,000× more than a page at depth k=20 — a curve that stays flat until it isn't and then climbs a cliff. Everything you say about OFFSET at scale is a corollary of this cost model.

Slot 1 — the planner has to walk the scan.

OFFSET is not a "skip pointer." No engine in 2026 (row-store or columnar) has an O(1) skip-to-position primitive on an ordered scan.
The plan for SELECT ... ORDER BY x LIMIT n OFFSET k on Postgres is Limit (n rows) → Index Scan Backward using idx_x. The Limit step reads k + n rows from the index scan; the first k are counted-then-discarded; the last n are emitted.
Cost is O(k + n) per page. For a fixed n = 20, cost is effectively O(k).
Total cost across pages 1 through P is Σ (k_i + n) = Σ ((i-1) × n + n) = n × P × (P+1) / 2 = O(P² × n). Quadratic in the number of pages fetched.

Slot 2 — wide rows amplify the pain.

Every scanned-and-discarded row still incurs I/O for the columns projected in the SELECT list. SELECT * FROM t OFFSET 1000000 on a 1KB-per-row table reads ~1GB before emitting the first byte.
Even with SELECT id, created_at (a covering-index-only query), the index leaf pages must be read — smaller than the heap but non-zero.
Wide rows also amplify sort memory. If the ORDER BY isn't index-aligned, the query needs an explicit sort — a sort of k + n rows fitting in sort_mem (Postgres work_mem) or spilling to disk. Deep OFFSET on a non-indexed ORDER BY is catastrophic.

Slot 3 — the benchmark curve.

On a 10M-row Postgres table with covering btree (created_at DESC, id DESC):
- Page 1 (OFFSET 0 LIMIT 20) — ~3 ms.
- Page 100 (OFFSET 1,980) — ~4 ms.
- Page 1,000 (OFFSET 19,980) — ~15 ms.
- Page 10,000 (OFFSET 199,980) — ~140 ms.
- Page 100,000 (OFFSET 1,999,980) — ~1.4 s.
- Page 500,000 (OFFSET 9,999,980) — ~7 s (hits shared-buffer pressure).
The curve is roughly linear in k until buffer pressure kicks in, then super-linear.
On columnar warehouses (BigQuery, Snowflake), the same curve is shifted upward by 5–10× because of per-column read overhead.

Slot 4 — the "consistent read across pages" problem.

OFFSET is positional — it counts rows by their ordinal in the ORDER BY. If a row is inserted before the current position, the ordinal of every subsequent row shifts by one.
Duplicate read — a new row lands above the last cursor position between pages. The old row 21 now becomes new row 22; OFFSET 20 LIMIT 20 returns the old row 20 as its first row on page 2. The user sees it twice.
Skipped read — a row is deleted between pages. The old row 21 disappears; new row 21 is what was old row 22. OFFSET 20 LIMIT 20 returns starting from new row 21 (= old row 22), skipping what the user should have seen.
Both failure modes are silent — no error is thrown, no log line is emitted. The client just sees inconsistent data.

Slot 5 — mitigations that don't fix the fundamental problem.

Serialisable transactions. Wrapping the paginator in BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE gives a stable snapshot for all reads. Works, but holds locks and pins MVCC snapshots across HTTP calls — hostile to any web-scale system.
Snapshot cursors. Server-side declared cursors (Postgres DECLARE CURSOR ... FOR ..., SQL Server cursor handles). Same problem — stateful on the DB side, needs sticky sessions, doesn't survive connection pooling.
Row versioning / audit tables. Add deleted_at and updated_at columns; filter out rows deleted after the initial page. Half a fix — new inserts still shift positions.
Cap the max page. Facebook / LinkedIn "5,000 pages max" hard cap. Doesn't fix drift, just bounds the pain.
Keyset pagination. The real fix. Navigate by key, not by position. Immune to inserts, deletes, and updates by design.

Slot 6 — when OFFSET is actually fine.

Small tables (< 1M rows). The whole table fits in RAM; even deep offsets are cheap.
Aggregate tables in warehouses. Superset / Looker dashboards on 10K-row summary tables. Fine.
Admin dashboards with page caps. If your admin UI caps the pager at page 500, deep OFFSET stays bounded.
Batched exports. The exporter reads pages 1 → P sequentially; total cost is quadratic, but if P is small the constant hides it.
Prototyping. Ship OFFSET, get the UI working, replace with keyset before scaling. This is the standard trajectory.

Slot 7 — reading the EXPLAIN plan.

Postgres EXPLAIN (ANALYZE, BUFFERS) — look for Buffers: shared read=N on the Limit step. N grows linearly with k for OFFSET; stays flat for keyset.
MySQL 8 EXPLAIN FORMAT=TREE — look for rows examined on the scan. Same story.
SQL Server SET STATISTICS IO ON — the logical reads count grows with k.
Snowflake — the query profile shows Rows Scanned on the source table. For OFFSET 1M LIMIT 20, Rows Scanned is ~1M; for keyset, it's ~20 (post-prune).
BigQuery — the "Bytes billed" tab shows OFFSET reads the full pre-limit column data. Keyset reads dramatically less.

Common newbie mistakes.

Assuming OFFSET is O(1). It's O(k + n). Every deep-page complaint traces back to this misconception.
Not adding a tie-breaker. Rows with duplicate ORDER BY keys drift across pages.
Not adding NULLS LAST. NULLs appear or disappear across pages depending on engine defaults.
Trusting OFFSET under concurrent writes. It drifts — silently.
Assuming the covering index makes OFFSET fast. It makes it faster than a sort-and-discard, but still O(k). The keyset path is O(log n + K) — dramatically different.

Worked example — measuring the OFFSET latency curve with EXPLAIN ANALYZE

Detailed explanation. The clearest demonstration — run EXPLAIN (ANALYZE, BUFFERS) at pages 1, 100, 1K, 10K, 100K, and 1M on the same query, then read the numbers. Every senior engineer has done this at least once; the interview question is often "walk me through what changes."

Question. Given a Postgres events(id, user_id, created_at, payload) table with 10M rows and a covering btree on (created_at DESC, id DESC), run EXPLAIN (ANALYZE, BUFFERS) for LIMIT 20 OFFSET k at k = 0, 20,000, 200,000, and 2,000,000. Report the latency curve.

Input. (10M-row events table, indexed on (created_at DESC, id DESC).)

Code.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
-- Limit  (cost=0.43..1.28 rows=20)
--   Buffers: shared hit=6
--   -> Index Scan Backward using idx_events (cost=0.43..425000.00 rows=10000000)
-- Planning Time: 0.1 ms
-- Execution Time: 3 ms

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20000;
-- Limit  (cost=849.30..850.15 rows=20)
--   Buffers: shared hit=1200 read=800
--   Execution Time: 45 ms

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;
-- Limit  (cost=8493.00..8493.85 rows=20)
--   Buffers: shared hit=8000 read=12000
--   Execution Time: 380 ms

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 2000000;
-- Limit  (cost=84930.00..84930.85 rows=20)
--   Buffers: shared hit=50000 read=150000
--   Execution Time: 3800 ms

Step-by-step explanation.

Page 1 (OFFSET 0) — 6 buffer hits, 3 ms. The plan reads 20 index leaves and emits 20 rows. Best case.
Page 1,001 (OFFSET 20,000) — 2,000 buffer accesses, 45 ms. Postgres walks 20,000 index leaves before emitting. Latency grows roughly linearly with k.
Page 10,001 (OFFSET 200,000) — 20,000 buffer accesses, 380 ms. shared read (buffer misses) start climbing because the working set exceeds shared_buffers.
Page 100,001 (OFFSET 2,000,000) — 200,000 buffer accesses, 3.8 s. Almost all reads are shared read — buffer pool saturated. This is the "someone will page the on-call" latency.
The Buffers: shared read count is the smoking gun — for OFFSET, it grows linearly with k; for keyset, it stays flat at 6–10 per page regardless of depth.

Output — latency curve.

Page (OFFSET)	Buffers	Latency
1 (0)	6	3 ms
1,001 (20,000)	2,000	45 ms
10,001 (200,000)	20,000	380 ms
100,001 (2,000,000)	200,000	3,800 ms

Rule of thumb. Deep OFFSET's latency curve is linear-then-super-linear. Everything under k ≤ 10,000 on a covering-indexed table is fine; everything past k ≥ 100,000 is a support ticket waiting to happen. Add a hard cap or migrate to keyset — the choice is not "if" but "when."

Worked example — the concurrent-write drift bug in action

Detailed explanation. The most infuriating pagination bug: pages that "shift" as users read them. This example builds a minimal reproduction using two sessions and shows exactly which row is missed and which row is duplicated.

Question. Simulate two sessions — session A reads page 1 of an OFFSET paginator, session B inserts a new row, session A reads page 2. Show which row is duplicated. Then show the same scenario with a delete instead of an insert.

Input. Rows 1–40 in events, sorted DESC by created_at, all with a unique created_at.

Code.

-- Session A: page 1
SELECT id FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Returns: 40, 39, 38, ..., 21

-- Session B: insert a new row with created_at newer than any existing
INSERT INTO events (id, created_at) VALUES (41, '2026-07-11 00:00');

-- Session A: page 2 (OFFSET drifted)
SELECT id FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 20;
-- Returns: 21, 20, 19, ..., 2
-- id=21 IS SEEN TWICE (once at end of page 1, once at start of page 2)


-- Reset. Repeat with delete.
-- Session A: page 1
SELECT id FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Returns: 40, 39, ..., 21

-- Session B: delete row 30 (which was on page 1)
DELETE FROM events WHERE id = 30;

-- Session A: page 2
SELECT id FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 20;
-- Returns: 20, 19, ..., 1
-- id=21 is SKIPPED (it should have been on page 2 but the offset now starts at 20)

Step-by-step explanation.

Pre-insert state — 40 rows sorted DESC. Page 1 (offset 0, limit 20) returns rows 40 → 21.
Session B inserts id=41 with a newer timestamp. The ordered stream is now 41, 40, 39, ..., 1 — 41 rows total.
Session A calls page 2 (offset 20, limit 20). Offset 20 in the new stream is row 21 (41, 40, ..., 22 is the first 20). Page 2 returns 21, 20, ..., 2 — the user sees id=21 twice.
In the delete scenario, session B deletes id=30 from the middle of page 1. The stream becomes 40, 39, ..., 31, 29, 28, ..., 1 — 39 rows.
Session A calls page 2. Offset 20 in the shrunken stream is row 21 in the original stream, which is now... row 20 in the new stream (since one was deleted). The paginator emits 20, 19, ..., 1 — id=21 is skipped entirely.

Output. Both scenarios produce inconsistent data — one duplicates, one skips.

Rule of thumb. Any OFFSET paginator against a mutable source has this drift. Every social feed, every "list of my orders" endpoint, every "list of my repos" page uses keyset instead. Keyset navigates by key (the last row's tuple), so inserts and deletes elsewhere in the ordered stream have no effect on subsequent pages.

Worked example — bounded-OFFSET as a safety net

Detailed explanation. If you can't migrate to keyset today (legacy admin dashboard, page-numbered pager), the cheap fix is a hard cap. Facebook, LinkedIn, and Google search results all do this — they refuse to serve past page N.

Question. Design a bounded-OFFSET endpoint that caps at 5,000 pages of 100 rows each. What are the trade-offs?

Input. A 100M-row orders table.

Code.

def list_orders_capped(page: int, page_size: int):
    # Hard caps
    MAX_PAGE = 5_000
    MAX_PAGE_SIZE = 200

    if page > MAX_PAGE:
        raise HttpError(400, "Page number exceeds maximum. Use search or filters.")

    page_size = min(page_size, MAX_PAGE_SIZE)
    offset = page * page_size

    # Worst case: page=5000, page_size=200 => OFFSET 1,000,000 LIMIT 200
    # Bounded ~600ms on a covering-indexed table
    rows = db.query(
        """
        SELECT id, created_at, total
        FROM orders
        ORDER BY created_at DESC, id DESC
        LIMIT %s OFFSET %s
        """,
        (page_size, offset),
    )
    return rows

Step-by-step explanation.

The MAX_PAGE = 5000 cap means the largest OFFSET is 5000 × 200 = 1,000,000. Bounded worst case.
The MAX_PAGE_SIZE = 200 cap means no client can ask for a 10K-row page and force a LIMIT 10000 OFFSET 100000 monster query.
If the client asks for page 5,001, the endpoint returns a 400 with a hint to use search filters (or infinite scroll via a keyset endpoint).
Deep-page latency at the cap is bounded — ~600 ms on a covering-indexed 100M-row table. Acceptable for an admin UI; unacceptable for consumer feeds (that's why consumer uses keyset).
This is the Facebook-search pattern — "your search returned too many results, please refine your query" is not a UX failure, it is a deliberate cost boundary.

Output.

Client on page ≤ 5,000 — normal response, bounded latency.
Client on page > 5,000 — 400 error with guidance.

Rule of thumb. Bounded OFFSET is the "we can't migrate today" fix. It doesn't cure the drift problem (concurrent writes still cause duplicates / skips), but it caps the perf blast radius. Every legacy admin dashboard in production ships this pattern.

Senior interview question on OFFSET perf diagnosis

A senior interviewer might ask: "You get a Slack ping — the orders list page is 8 seconds at page 500. The engineer who wrote it says 'we have an index.' Diagnose the failure in under two minutes."

Solution Using EXPLAIN ANALYZE + covering-index verification + keyset migration path

-- Step 1: EXPLAIN the failing query
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

-- Look for:
--   1. Is the scan using the index? (Index Scan Backward using idx_orders_created_at)
--   2. Is the sort happening in the executor? (Sort node → covering index missing / mis-aligned)
--   3. Buffers: shared read count (growing with OFFSET = OFFSET trap)
--   4. Any Filter step after the index scan (missing covering columns)

-- Step 2: Verify the index actually covers the ORDER BY
\d+ orders
-- If the existing index is (created_at) but the ORDER BY is (created_at DESC, id DESC),
-- Postgres 15+ can still walk backward but old versions do a sort.

-- Step 3: Add the covering composite if missing
CREATE INDEX CONCURRENTLY idx_orders_paginate
  ON orders (created_at DESC, id DESC);
-- Now the Limit → Index Scan Backward path is optimal for OFFSET too.
-- But OFFSET remains O(k) — indexing does not fix the trap.

-- Step 4: Migrate to keyset for the consumer endpoint
SELECT id, user_id, created_at, total
FROM orders
WHERE (created_at, id) < (:cursor_ts, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Now O(log n + K) at any depth.

Step-by-step trace.

Step	What you check	What it tells you
1	EXPLAIN plan node type	Sort node = missing / mis-aligned index; Index Scan Backward = good
2	Buffers: shared read	Growing with OFFSET = classic OFFSET trap
3	\d+ orders index list	Composite `(created_at DESC, id DESC)` = optimal; single-col = suboptimal
4	Actual rows vs actual loops	20 rows but 10,020 loops = classic OFFSET scan-and-discard
5	Fix path A (short-term)	CREATE INDEX CONCURRENTLY (covering)
6	Fix path B (long-term)	Migrate to keyset for consumer endpoints

The senior diagnosis is: identify the OFFSET trap in EXPLAIN, verify the covering index is in place (adding it if missing), and set the migration to keyset for any endpoint expected to serve deep pages. The short-term win is a 2–3× improvement from index alignment; the long-term win is O(log n) from keyset — a 100× improvement at page 100K.

Output:

Fix	Page 500 latency	Page 100K latency	Sustainable?
Baseline (missing composite index)	8 s	Timeout	No
Composite index added	300 ms	4 s	For admin UI with page cap
Migrated to keyset	8 ms	8 ms	Yes — flat forever

Why this works — concept by concept:

EXPLAIN ANALYZE reveals the plan shape — the actual rows vs actual loops line is diagnostic. A LIMIT of 20 that runs 10,000 loops is the classic OFFSET signature. Every senior engineer reads this line first.
Composite index alignment — the ORDER BY (created_at DESC, id DESC) matches a composite index (created_at DESC, id DESC); Postgres walks the index leaves backward without a Sort node. Reducing the sort step is the cheapest short-term win.
OFFSET is O(k) even with covering index — indexing does not fix the trap; it just makes the scan-and-discard cheaper per row. At k = 100,000 on a covering-indexed table, latency is still seconds.
Keyset is O(log n + K) — the tuple predicate uses the same composite index as a seek. The plan is Index Scan Backward with actual loops = 20, regardless of depth.
Cost — Baseline (no composite) is O(k × log n) with sort; composite-only is O(k); keyset is O(log n + K). On a 100M-row table with K=20 and k=100,000, the numbers are ~40 s / ~4 s / ~10 ms — a 400× spread across the three fixes.

SQL
Topic — indexing
SQL indexing drills

Practice →

SQL Topic — optimization SQL optimization problems

Practice →

4. Keyset (seek method) pagination

`keyset pagination` and `seek method pagination` — WHERE (created_at, id) < (:cursor) ORDER BY DESC LIMIT n, composite index alignment, and the O(log n) deep-page path

The mental model in one line: keyset pagination (a.k.a. seek method pagination) replaces "skip k rows" with "give me the rows strictly after this cursor tuple," where the cursor is the last row of the previous page and the comparison is a lexicographic tuple compare — turning the deep-page cost from O(k + n) (scan + discard) into O(log n + K) (one index seek + K sequential reads). Every modern feed, every GraphQL Relay endpoint, and every "next 20" API on the internet is built on this primitive.

Slot 1 — the keyset skeleton.

Two-slot template: Slot A — the WHERE tuple comparison: WHERE (order_col, id) < (:cursor_col, :cursor_id) for DESC pagination, > for ASC. Slot B — the ORDER BY with matching direction and tie-breaker: ORDER BY order_col DESC, id DESC LIMIT n.
Every keyset query fills the two slots. The cursor is (last_row.order_col, last_row.id) from the previous page — no OFFSET, no page number.
The tuple comparison (a, b) < (:a, :b) is lexicographic — first a < :a, or a = :a AND b < :b. This is not the same as a < :a AND b < :b — that comparison would drop rows where a = :a AND b < :b.

Slot 2 — tuple comparison is one-shot, not per-column.

Postgres, MySQL 8, Oracle 12c+, Snowflake, and BigQuery all support WHERE (a, b) < (:a, :b) as native row-value comparison. This is the ANSI SQL:1999 spelling.
SQL Server and older MySQL don't support row-value comparison. The equivalent is WHERE a < :a OR (a = :a AND b < :b) — verbose but semantically identical.
The one-shot form is not just a syntactic nicety — it lets the planner recognise the comparison as a range scan on the composite index (a, b) and use a single index seek. The expanded per-column form may or may not compile to the same plan depending on planner cleverness.

Slot 3 — composite index alignment.

The composite index (order_col, id) in the same direction as the ORDER BY (DESC or ASC) enables an index-only backward or forward scan.
Postgres 15+ can walk any btree in either direction, so CREATE INDEX ... ON t (created_at DESC, id DESC) and CREATE INDEX ... ON t (created_at ASC, id ASC) work equivalently for backward scans.
MySQL 8.0.13+ supports descending indexes (CREATE INDEX ... ON t (created_at DESC, id DESC)). Older MySQL always stored indexes ascending — the planner walked them backward for DESC scans, which was slightly less efficient.
SQL Server has supported descending index columns since forever. Oracle since 8i.
Snowflake has no btree — the equivalent is CLUSTER BY (created_at), which reorders micro-partitions to co-locate rows with similar created_at values.
BigQuery uses table clustering — CLUSTER BY created_at — which does the same physical co-location.

Slot 4 — the O(log n + K) deep-page path.

The index seek on (created_at, id) < (:ts, :id) positions the scan at exactly the right leaf page in O(log n) — the depth of the btree.
From that leaf, the engine reads K sequential rows (where K is the LIMIT). Each read is O(1). Total forward-scan cost is O(K).
Overall: O(log n + K) per page — flat in the depth of the pagination. Page 1 and page 1,000,000 both cost the same.
Compared to OFFSET's O(k + n) — at page 100K with n = 20, keyset is 5,000× faster.

Slot 5 — DESC pagination vs ASC pagination.

DESC (newest-first feed) — WHERE (created_at, id) < (:cursor_ts, :cursor_id) ORDER BY created_at DESC, id DESC LIMIT n. Cursor is the last (oldest) row emitted.
ASC (oldest-first log) — WHERE (created_at, id) > (:cursor_ts, :cursor_id) ORDER BY created_at ASC, id ASC LIMIT n. Cursor is the last (newest) row emitted.
The < / > operator flips with the direction; the ORDER BY direction flips too; the tie-breaker direction must match.
Mixing directions — ORDER BY created_at DESC, id ASC — is possible but requires the tuple comparison and the ORDER BY to match. The composite index must be (created_at DESC, id ASC) or Postgres can't do an index-only scan.

Slot 6 — bidirectional pagination.

Forward — WHERE (a, b) < (:a, :b) ORDER BY a DESC, b DESC LIMIT n. Returns the next N older rows.
Backward — WHERE (a, b) > (:a, :b) ORDER BY a ASC, b ASC LIMIT n, then reverse the result set in application code. Returns the previous N newer rows.
The Relay spec's first / after / last / before maps directly to this — first + after is forward, last + before is backward.
Some engines support the ORDER BY a ASC, b ASC LIMIT n variant with the result reversed — Postgres does this via a subquery: SELECT * FROM (... ORDER BY a ASC LIMIT n) t ORDER BY a DESC.

Slot 7 — trade-offs and honest limitations.

No random access. You can't ask for "page 47" — keyset only lets you paginate forward or backward from a known cursor. The trade-off is: no page numbers, no total-count-of-pages banner.
Total row count is expensive. Keyset gives you hasNext cheaply (via LIMIT n+1 peek-ahead) but no total. Total count on a mutable table is O(n) anyway — the OFFSET pager pays that cost only because the total-count query is a separate SELECT COUNT(*).
Skipping a page is impossible. Client-side "jump to page 100" doesn't map to keyset. The only workaround is a hybrid — keyset for infinite scroll, bounded OFFSET for admin dashboards.
Cursor must include all ORDER BY columns. If the ORDER BY is (created_at, id), the cursor is (created_at, id). Adding a third sort column adds a third cursor component.

Slot 8 — handling NULLs correctly.

If created_at can be NULL, the tuple comparison behaves unexpectedly. On most engines, NULL < anything and NULL > anything both return NULL — the row is not returned.
Fix — use ORDER BY created_at DESC NULLS LAST and, in the WHERE, use a COALESCE(created_at, '-infinity') cast to give NULLs a defined position in the order.
Better fix — never allow NULLs on the paginated sort column. Make it NOT NULL and populate on insert.
On BigQuery, the default for ORDER BY x DESC is NULLs first. Use NULLS LAST explicitly.

Slot 9 — handling duplicate ORDER BY keys (ties).

Two rows with the same created_at — the tie-breaker on id disambiguates. WHERE (created_at, id) < (:ts, :id) correctly excludes only the exact cursor row and continues from there.
Without the id tie-breaker, WHERE created_at < :ts would skip all rows with created_at = :ts — you might miss valid rows.
The tie-breaker column must be unique across the table (usually the primary key id) — otherwise the composite comparison can still tie and cause drift.

Slot 10 — cursor stability under mutations.

New insert with created_at > cursor — the cursor's tuple stays the same; the new row appears on future backward pages (relative to a newer cursor), never on already-paginated results. Perfect.
New insert with created_at < cursor — same story; the new row is in front of the cursor and thus in future forward pages.
Delete a row already paginated — its removal doesn't affect anything; the cursor points to a row that's still in the ordered sequence (or, if the cursor itself is deleted, the next call skips ahead to the next valid row).
Update the paginated sort column (e.g. UPDATE events SET created_at = ...) — the updated row can move in the sequence, but since keyset uses the cursor's remembered tuple, not the current row's tuple, subsequent pages remain consistent.

Common newbie mistakes.

Using per-column WHERE instead of tuple comparison — WHERE created_at < :ts AND id < :id drops rows where created_at < :ts AND id ≥ :id. Wrong semantically.
Forgetting the tie-breaker — two rows with the same created_at alternate between pages.
Wrong ORDER BY direction — ORDER BY created_at DESC, id ASC with a < comparison drops the wrong rows.
Missing composite index — the query works but plans as a Filter after a full sort. Latency stays O(n).
Encoding the cursor as page=N — this is not keyset. Cursors are opaque tuples, not page numbers.

Interview probes on syntax fluency.

"Why is tuple comparison better than per-column AND/OR?" — one-shot lexicographic comparison, single index-range scan, no chance of dropping edge rows.
"What index does keyset need?" — composite on the ORDER BY columns in the ORDER BY direction, matching direction to the DESC/ASC.
"What if two rows share a millisecond?" — the unique tie-breaker (id) disambiguates. Every keyset paginator ends the ORDER BY with , id or another unique column.
"What if the sort column is NULL for some rows?" — either the column is NOT NULL, or you use COALESCE(sort_col, sentinel) in both the WHERE and ORDER BY. The NULL problem is one of the two most common keyset bugs.
"How do you paginate backward?" — swap < for >, flip the ORDER BY direction, reverse the result in application code. Or use first/last + after/before if you're implementing the Relay spec.

Worked example — the "next 20 events" keyset query on Postgres

Detailed explanation. The canonical keyset query — DESC pagination on (created_at, id) with a composite index. Every consumer feed on the internet uses this shape.

Question. Given events(id BIGINT PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL, user_id BIGINT, payload TEXT) with an index on (created_at DESC, id DESC), write the Postgres query that returns the next 20 events after a cursor (cursor_ts, cursor_id).

Input.

10M rows, sorted DESC by (created_at, id).
Cursor is (2026-07-05 12:00, 5000000) — the last row of the previous page.

Code.

SELECT id, user_id, created_at, payload
FROM events
WHERE (created_at, id) < (:cursor_ts, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Step-by-step explanation.

WHERE (created_at, id) < (:cursor_ts, :cursor_id) — tuple comparison. Lexicographic — first created_at < :cursor_ts, or created_at = :cursor_ts AND id < :cursor_id.
This maps to a range predicate on the composite index (created_at DESC, id DESC). Postgres does one index seek to position at the cursor, then scans backward.
ORDER BY created_at DESC, id DESC — matches the index order. No sort step.
LIMIT 20 — bounds the scan. The engine reads exactly 20 rows past the cursor position and stops.
Total plan cost — Index Scan Backward using idx_events_paginate (cost=0.43..1.28) with actual rows=20 actual loops=1. Flat regardless of pagination depth.

Output (20 rows just older than the cursor).

id	user_id	created_at	payload
4999999	42	2026-07-05 11:59:59	click
4999998	91	2026-07-05 11:59:58	login
...	...	...	...
4999980	17	2026-07-05 11:59:00	logout

Rule of thumb. The two-line skeleton — tuple compare + ORDER BY DESC — is the entire keyset primitive. Memorise both slots; every future keyset query is a variation on this template.

Worked example — expanded per-column form for SQL Server (no row-value comparison)

Detailed explanation. SQL Server (and older MySQL) doesn't support the ANSI row-value comparison WHERE (a, b) < (:a, :b). The expanded form is verbose but semantically identical.

Question. Rewrite the previous keyset query using per-column WHERE clauses for SQL Server compatibility. Verify the planner still uses a range scan.

Input. (Same events table, on SQL Server.)

Code.

-- SQL Server — expanded per-column form
SELECT TOP 20 id, user_id, created_at, payload
FROM events
WHERE
     created_at < @cursor_ts
  OR (created_at = @cursor_ts AND id < @cursor_id)
ORDER BY created_at DESC, id DESC;

Step-by-step explanation.

Row-value comparison (a, b) < (:a, :b) is not SQL Server-native. The expanded form is a < :a OR (a = :a AND b < :b).
The OR decomposition is critical — a < :a AND b < :b is wrong — it drops rows where a < :a AND b >= :b.
SQL Server's optimiser recognises the OR (equal AND less) shape and typically compiles it to a range seek on the composite index. Verify with the execution plan.
SELECT TOP 20 replaces LIMIT 20 — SQL Server's spelling. Semantically identical.
If the planner does not recognise the pattern (older SQL Server versions, or complex predicates), it may fall back to a scan + filter — always verify with the actual plan.

Output. Same 20 rows as the tuple-form query.

Rule of thumb. On SQL Server and older MySQL, use the expanded OR (equal AND less) form. It's more verbose but semantically identical. Every keyset paginator against SQL Server ships this pattern.

Worked example — backward pagination (previous page)

Detailed explanation. The Relay spec's last + before — "give me the 20 rows immediately newer than this cursor." Implementable with a flipped tuple compare and a reversed result set.

Question. Write a Postgres query that returns the 20 events immediately newer than a cursor (cursor_ts, cursor_id), in DESC order.

Input. (Same events table; cursor (2026-07-05 12:00, 5000000).)

Code.

-- Backward keyset — get the 20 rows immediately newer than the cursor
SELECT * FROM (
  SELECT id, user_id, created_at, payload
  FROM events
  WHERE (created_at, id) > (:cursor_ts, :cursor_id)
  ORDER BY created_at ASC, id ASC
  LIMIT 20
) t
ORDER BY created_at DESC, id DESC;

Step-by-step explanation.

Inner query — flip < to > (newer, not older) and flip ORDER BY to ASC (so the closest rows to the cursor are read first). LIMIT 20.
This produces 20 rows in ASC order — the "reading direction" for backward pagination but the wrong final order for the UI.
Outer query — re-sort DESC to match the standard feed ordering. Rows are the same; the presentation flips.
The two-step (inner-ASC, outer-DESC) is unavoidable — you need the closest-first read direction internally but the newest-first presentation externally.
On engines with FETCH FIRST 20 ROWS ONLY inside a subquery restriction (Oracle 12c+), the same pattern works with the ANSI spelling instead of LIMIT.

Output (20 rows just newer than the cursor, presented newest-first).

id	user_id	created_at	payload
5000020	...	2026-07-05 12:00:20	...
...	...	...	...
5000001	...	2026-07-05 12:00:01	...

Rule of thumb. Backward pagination flips the operator, flips the direction, and reverses the result. The composite index on (created_at DESC, id DESC) works for both directions on Postgres 15+.

Worked example — keyset over composite ORDER BY with mixed directions

Detailed explanation. A subtle case — the ORDER BY has one DESC column and one ASC tie-breaker (ORDER BY score DESC, id ASC for leaderboards). The tuple comparison needs to handle the mixed direction.

Question. Write a Postgres keyset query for a leaderboard sorted by score DESC, id ASC, given a cursor (cursor_score, cursor_id).

Input. leaderboard(id INT PRIMARY KEY, score INT NOT NULL). Composite index (score DESC, id ASC).

Code.

-- Leaderboard: score DESC, id ASC as tie-breaker
SELECT id, score
FROM leaderboard
WHERE
     score < :cursor_score
  OR (score = :cursor_score AND id > :cursor_id)
ORDER BY score DESC, id ASC
LIMIT 20;

Step-by-step explanation.

The ORDER BY has mixed directions — score DESC, id ASC. Row-value comparison doesn't fit — (score, id) < (:cursor_score, :cursor_id) would compare both columns in the same direction.
Use the expanded form — score < :cursor_score (strictly worse score) OR (score = :cursor_score AND id > :cursor_id) (same score, larger id — the tie-breaker direction).
The composite index must match — (score DESC, id ASC). Postgres supports mixed-direction indexes since 8.3.
The OR (score = :cursor_score AND id > :cursor_id) — note >, not < — because the tie-breaker is ASC.
Interviewers love this — a candidate who reflexively types (score, id) < (:cs, :ci) shows they haven't thought through the mixed-direction case.

Output (20 rows just past the cursor in the leaderboard).

id	score
101	4800
102	4800
55	4795
...	...

Rule of thumb. Mixed-direction ORDER BY needs the expanded OR (equal AND direction-specific) form. Row-value comparison only works when all columns share the same direction. Every leaderboard paginator ships this pattern.

Senior interview question on keyset design

A senior interviewer might ask: "Design the composite index and the SQL for a keyset-paginated chat_messages endpoint that returns the last 30 messages of a specific channel_id. Explain how the index alignment guarantees O(log n) at any depth."

Solution Using channel-scoped composite index + keyset tuple compare

-- Table
CREATE TABLE chat_messages (
  id           BIGINT PRIMARY KEY,
  channel_id   BIGINT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_id      BIGINT NOT NULL,
  body         TEXT
);

-- Channel-scoped composite index
CREATE INDEX idx_chat_channel_paginate
  ON chat_messages (channel_id, created_at DESC, id DESC);

-- Page 1: last 30 messages of channel 42
SELECT id, user_id, created_at, body
FROM chat_messages
WHERE channel_id = :channel_id
ORDER BY created_at DESC, id DESC
LIMIT 30;

-- Page 2: next 30 older messages after the cursor (last row of page 1)
SELECT id, user_id, created_at, body
FROM chat_messages
WHERE channel_id = :channel_id
  AND (created_at, id) < (:cursor_ts, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 30;

Step-by-step trace.

Step	What happens	Why
1	Composite index `(channel_id, created_at DESC, id DESC)`	Channel as prefix; sort keys after
2	Page 1 query hits index at `channel_id = 42`	O(log n) seek to channel prefix
3	Reads backward from first leaf	O(30) sequential reads
4	Page 2 adds tuple predicate	Positions at cursor within channel prefix
5	Reads next 30 rows backward	O(30) more sequential reads
6	Cursor is opaque `base64((created_at, id))`	Server issues; client passes back

The composite index has channel_id as its leading column so every message in a given channel lives in a contiguous range of the index. Within that range, rows are ordered (created_at DESC, id DESC), so both the initial page and every subsequent keyset page hit the same tight index range. Plan is Index Scan Backward with actual rows = 30 actual loops = 1 for both pages.

Output:

Metric	Page 1	Page 100	Page 10,000
Plan	Index Scan Backward	Index Scan Backward	Index Scan Backward
Rows scanned	30	30	30
Latency (10M msgs, 10K channels)	4 ms	4 ms	4 ms
Cursor size (base64)	~48 B	~48 B	~48 B

Why this works — concept by concept:

Composite index with channel_id as prefix — the leading column matches the WHERE filter (channel_id = :cid), so the index seek positions at the start of the channel's rows in O(log n). Everything after that is a sequential index-leaf scan.
ORDER BY matches the composite index — (created_at DESC, id DESC) inside the channel prefix is stored contiguously; Postgres reads backward with no sort node.
Tuple compare uses the same index range — AND (created_at, id) < (:cursor_ts, :cursor_id) is a range predicate on the composite; the planner tightens the initial seek by one more equality-then-less-than step.
Flat latency at any pagination depth — every page hits the exact same plan shape. Latency at page 1 = latency at page 10,000 = latency at page 100,000.
Cursor size is bounded — a (TIMESTAMPTZ, BIGINT) tuple base64-encodes to ~48 bytes. Fits in a URL query parameter without percent-encoding overhead. HMAC-signed it grows to ~80 bytes — still fine.
Cost — O(log n + K) per page where n is the total row count and K is the LIMIT. For n = 10M and K = 30, that's ~24 index-leaf reads (log₂(10M) ≈ 24) plus 30 sequential reads — well under 5 ms on any modern hardware.

SQL
Topic — indexing
Composite indexing drills

Practice →

SQL Topic — top-N per group Top-N per group problems

Practice →

5. Cursor pagination + dialect matrix

`sql cursor pagination` and `sql infinite scroll` — opaque base64 tokens, HMAC signing, the GraphQL Relay spec, and the six-engine dialect matrix that ties it all together

The mental model in one line: sql cursor pagination is the API-shape layer on top of keyset — the client sends an opaque after token, the server decodes it into (cursor_col, cursor_id), runs the keyset SQL, and returns the next page plus an endCursor for the client to send on the next call, and the whole family of sql infinite scroll endpoints (REST, GraphQL Relay, gRPC streaming) reduces to this one wire contract with slightly different naming. Once you nail the opaque-token discipline and the Relay edges/pageInfo shape, every consumer feed is a variation on the same theme.

Slot 1 — the opaque cursor token.

Cursors are opaque to clients — a base64-encoded string, not a page number, not a raw tuple. Clients treat cursors as black boxes; the server owns the encoding.
Payload — usually a JSON blob {"ts": "2026-07-05T12:00:00Z", "id": 5000000, "dir": "desc"} or a msgpack / protobuf equivalent. JSON is simplest; msgpack shrinks the on-the-wire size.
HMAC-signed. Every cursor is signed with a server-side secret so a client can't tamper with it to skip rows or leak data. cursor = base64(json_payload).hmac(secret). Verification is O(1) on the way in.
Encoding — base64url (no +/ padding characters) is URL-safe and drop-in for query parameters.
Rotation — the HMAC secret rotates every N months; cursors older than the rotation window return a 400 with a hint to re-fetch page 1.

Slot 2 — the Relay Connections spec.

GraphQL's Relay spec (2015, still the standard) defines cursor pagination as first / after / last / before with edges and pageInfo envelope.
Query — orders(first: 20, after: "eyJ0cyI6..."). edges { node { id, ...fields }, cursor } pageInfo { hasNextPage, endCursor }.
Every edge has its own cursor (usable as the after for a "page start here" request). The endCursor is a shorthand for the last edge's cursor.
hasNextPage — cheap via the LIMIT n+1 peek-ahead trick. Fetch 21 rows, return 20, hasNext = (fetched > 20).
hasPreviousPage — same trick backward, or hasPrev = (after cursor is set). Not always cheap to compute exactly.

Slot 3 — REST cursor endpoints.

The naming varies — Twitter uses cursor, GitHub uses page+per_page with Link headers, Stripe uses starting_after / ending_before. All are cursor-based under the hood.
Response envelope — {"data": [...], "next_cursor": "eyJ0cyI6...", "prev_cursor": null}. The client passes next_cursor on the next call.
Link header (GitHub-style) — RFC 5988 links: <https://api.example.com/orders?cursor=eyJ...>; rel="next", <...>; rel="prev". Elegant; clients can parse without knowing the JSON envelope.
Idempotency — the same cursor + limit returns the same rows (assuming no schema changes). Clients can retry safely.

Slot 4 — cursor lifecycle and invalidation.

Cursors survive inserts — they point to a specific row, so new rows in the ordered stream don't affect the cursor's target.
Cursors survive deletes — if the cursor's target row is deleted, the next call reads from just past the (now missing) target position. Effectively the cursor still works.
Cursors survive schema changes — as long as the sort columns are unchanged. If the sort column changes semantics (e.g. renaming created_at to posted_at), old cursors become invalid and the server should reject them with a clear error.
Cursors survive secret rotation — with graceful degradation. Rotate secrets; grandfather the previous secret for one rotation window; reject cursors older than that.
Cursors are short-lived in practice — clients drop them on tab close. But an idle browser tab holding a cursor for a week should still work if the schema hasn't changed.

Slot 5 — the six-engine dialect matrix for keyset.

Postgres — full support. (a, b) < (:a, :b) tuple compare + composite btree in matching direction. Best case.
MySQL 8 — full support since 8.0.16 for row-value comparison; also supports descending indexes since 8.0.13. Perfect for keyset.
SQL Server — no row-value comparison; use expanded OR (equal AND less) form. Composite index seek works via query planner recognition; verify with the plan.
Oracle 12c+ — supports row-value comparison and composite descending indexes. Same story as Postgres.
Snowflake — supports row-value comparison. No btree indexes; the equivalent is CLUSTER BY (sort_col) for micro-partition co-location. Deep-page latency depends on cluster health.
BigQuery — supports row-value comparison in WHERE. No btree; the equivalent is table CLUSTER BY sort_col plus partition pruning if the sort_col has a date component. Best practice: partition by DATE(sort_col), cluster by sort_col, then keyset.

Slot 6 — dialect gotchas.

NULL ordering. Postgres and Oracle default to NULLs first for DESC; MySQL and SQL Server default to NULLs last; BigQuery defaults to NULLs first. Always spell out NULLS LAST explicitly.
Millisecond precision. Different engines store TIMESTAMP at different resolutions — Postgres microseconds, MySQL up to microseconds, SQL Server 100-ns, BigQuery microseconds. Two "identical" timestamps in one engine may differ in another — always include the id tie-breaker.
Case sensitivity in text sort columns. created_at is numeric so no problem; a name-based sort has to consider collation. ORDER BY name COLLATE "C" or the equivalent, plus include the collation in the cursor to avoid inconsistency across mixed locale servers.
Composite index size. A composite (a, b, c) index is 30–50% larger than a single-column (a) index. Keyset requires the composite; the storage cost is the price of admission. Usually worth it.
Row-value comparison support. SQL Server, older MySQL, and some BigQuery legacy modes don't recognise (a, b) < (:a, :b) as a row-value comparison — use the expanded form.

Slot 7 — the peek-ahead trick for hasNextPage.

Client asks for first: 20. Server queries with LIMIT 21 (one extra). If the query returns 21 rows, hasNextPage = true and the server returns only the first 20; if it returns ≤ 20, hasNextPage = false and the server returns all of them.
Zero-cost — the 21st row is a single extra index leaf read. No separate SELECT COUNT(*) query.
The 21st row's cursor is the endCursor for the next page's after parameter — perfect handoff.
Every Relay implementation ships this trick. If your endpoint doesn't do it, hasNextPage requires a separate query that's expensive on large tables.

Slot 8 — HMAC signing and secret management.

Server has a secret S. Cursor payload is P = json({"ts": ..., "id": ...}). Signed cursor is base64url(P) + "." + hmac_sha256(S, base64url(P)).
On the way in — verify signature first. If invalid, return 400 with "invalid cursor." No SQL runs.
Secrets live in a KMS / secret manager. Rotate every 90 days. On rotation, keep the previous secret valid for a grace period equal to the maximum expected client idle time (usually 24–72 hours).
Alternative — encrypt the payload symmetrically with the secret. Adds tamper-proofing plus payload privacy. Not strictly needed for pagination (the payload is not confidential), but common in enterprise deployments.

Slot 9 — total count and its trade-offs.

Keyset gives you hasNextPage cheaply; it does not give you totalCount.
Exact total on a large mutable table is O(n) regardless — SELECT COUNT(*) scans the whole thing or the index.
Approximations — pg_class.reltuples on Postgres, information_schema.tables.table_rows on MySQL, SYSTEM_SAMPLING on Snowflake. Fast but stale.
Best practice — don't return total count on infinite-scroll feeds. Show a scroll bar that grows dynamically instead. On admin dashboards, cache the total count and refresh every few minutes.

Slot 10 — the interview litmus test.

"Design a paginated timeline endpoint." Every good answer starts with cursor pagination, keyset SQL, opaque tokens, and the Relay spec. If the candidate reaches for page=1&limit=20, they're a junior.
"How do you handle 'jump to page 47'?" — you don't, on infinite scroll. On admin dashboards, use capped OFFSET; on data exports, iterate keyset from page 1.
"Explain hasNextPage without an extra query." — the LIMIT n+1 peek-ahead. Instant senior signal.
"Explain why cursors are opaque." — tamper resistance, forward compatibility (server can change the payload schema without breaking clients), and abstraction (clients shouldn't reason about database internals).

Common newbie mistakes.

Exposing raw tuples in cursors. ?cursor=2026-07-05T12:00:00Z_5000000 is a leaky abstraction and a tampering vector.
Skipping HMAC. Anyone can craft a cursor to read rows they shouldn't see. Sign every cursor.
Assuming keyset gives you totalCount. It doesn't; that's a separate expensive query.
Not planning for schema changes. When the sort column changes semantics, old cursors must be rejected cleanly.
Using page numbers instead of cursors on a "list this user's posts" endpoint. If two viewers see slightly different data (privacy filters), page numbers don't align across viewers.

Worked example — encoding and decoding an opaque cursor with HMAC

Detailed explanation. The full round-trip — encode a (created_at, id) tuple into a base64 HMAC-signed cursor, decode it on the way in with signature verification. Every consumer feed does this exactly once.

Question. Write Python that encodes a cursor from (created_at, id) with HMAC-SHA256 signing, and decodes it back on the way in with signature verification. Show the wire format.

Input. created_at = datetime(2026, 7, 5, 12, 0), id = 5000000, secret = b"pagination-secret-2026".

Code.

import base64
import hashlib
import hmac
import json
from datetime import datetime, timezone


SECRET = b"pagination-secret-2026"


def encode_cursor(created_at: datetime, row_id: int) -> str:
    payload = {
        "ts": created_at.astimezone(timezone.utc).isoformat(),
        "id": row_id,
        "dir": "desc",
    }
    payload_bytes = json.dumps(payload, separators=(",", ":"), sort_keys=True).encode()
    payload_b64 = base64.urlsafe_b64encode(payload_bytes).rstrip(b"=").decode()
    signature = hmac.new(SECRET, payload_b64.encode(), hashlib.sha256).digest()
    sig_b64 = base64.urlsafe_b64encode(signature).rstrip(b"=").decode()
    return f"{payload_b64}.{sig_b64}"


def decode_cursor(cursor: str) -> tuple[datetime, int]:
    try:
        payload_b64, sig_b64 = cursor.split(".", 1)
    except ValueError:
        raise ValueError("malformed cursor")
    # Verify signature
    expected_sig = hmac.new(SECRET, payload_b64.encode(), hashlib.sha256).digest()
    expected_sig_b64 = base64.urlsafe_b64encode(expected_sig).rstrip(b"=").decode()
    if not hmac.compare_digest(sig_b64, expected_sig_b64):
        raise ValueError("cursor signature invalid")
    # Decode payload
    padding = b"=" * (-len(payload_b64) % 4)
    payload_bytes = base64.urlsafe_b64decode(payload_b64.encode() + padding)
    payload = json.loads(payload_bytes)
    return datetime.fromisoformat(payload["ts"]), payload["id"]


# Round-trip
ts = datetime(2026, 7, 5, 12, 0, tzinfo=timezone.utc)
cursor = encode_cursor(ts, 5_000_000)
print(cursor)
# eyJkaXIiOiJkZXNjIiwiaWQiOjUwMDAwMDAsInRzIjoiMjAyNi0wNy0wNVQxMjowMDowMCswMDowMCJ9.CzRK9pKu1EWvKQrKn2H8kMXvpQXwZR7Tu-y1O4iF1Ho

ts2, id2 = decode_cursor(cursor)
assert (ts2, id2) == (ts, 5_000_000)

Step-by-step explanation.

Payload — a JSON dict with ts, id, and dir. Sort keys for determinism (so re-encoding produces the same bytes).
base64.urlsafe_b64encode — URL-safe base64 (uses -_ instead of +/), strips = padding so the cursor is drop-in for URL query parameters.
Signature — HMAC-SHA256 over the base64 payload (not the raw JSON, so the signature covers the exact wire form). Base64-encode the signature too.
Wire format — payload_b64.signature_b64. Two base64url strings separated by a dot, JWT-like but without a header.
Decode — split on the dot, verify signature with hmac.compare_digest (constant-time compare to prevent timing attacks), then decode the payload.

Output.

Cursor eyJkaXIiOiJkZXNjIiwiaWQiOjUwMDAwMDAsInRzIjoiMjAyNi0wNy0wNVQxMjowMDowMCswMDowMCJ9.CzRK9pKu1EWvKQrKn2H8kMXvpQXwZR7Tu-y1O4iF1Ho (or similar; signature varies with the secret).
Decode returns (datetime(2026,7,5,12,0,tzinfo=UTC), 5_000_000).

Rule of thumb. JWT-like signed cursors are the industry standard. Every consumer API in 2026 uses this exact shape (with minor variations in payload schema and signing algorithm). Roll it yourself in 30 lines of code or use a library like itsdangerous.

Worked example — the Relay Connections envelope

Detailed explanation. The GraphQL Relay spec, in one worked query. This is what every graphql-ruby, graphene, strawberry, and apollo-server codebase writes verbatim.

Question. Given the events table and the keyset SQL from before, write the resolver for a Relay-style events(first: 20, after: "...") connection query.

Input. events(id, user_id, created_at, payload) table.

Code.

from typing import Optional

def resolve_events_connection(first: int, after: Optional[str]) -> dict:
    # Clamp first to prevent unbounded page sizes
    first = min(max(first, 1), 100)

    # Decode the after cursor (if any)
    if after:
        try:
            cursor_ts, cursor_id = decode_cursor(after)
        except ValueError:
            raise GraphQLError("invalid cursor")
        rows = db.query(
            """
            SELECT id, user_id, created_at, payload
            FROM events
            WHERE (created_at, id) < (%s, %s)
            ORDER BY created_at DESC, id DESC
            LIMIT %s
            """,
            (cursor_ts, cursor_id, first + 1),
        )
    else:
        rows = db.query(
            """
            SELECT id, user_id, created_at, payload
            FROM events
            ORDER BY created_at DESC, id DESC
            LIMIT %s
            """,
            (first + 1,),
        )

    has_next = len(rows) > first
    rows = rows[:first]
    edges = [
        {
            "node": {
                "id": r["id"],
                "user_id": r["user_id"],
                "created_at": r["created_at"],
                "payload": r["payload"],
            },
            "cursor": encode_cursor(r["created_at"], r["id"]),
        }
        for r in rows
    ]

    return {
        "edges": edges,
        "pageInfo": {
            "hasNextPage": has_next,
            "hasPreviousPage": after is not None,
            "startCursor": edges[0]["cursor"] if edges else None,
            "endCursor": edges[-1]["cursor"] if edges else None,
        },
    }

Step-by-step explanation.

Clamp first to a safe range (1–100). Prevents a client from asking for a million-row page.
If after is provided, decode it (with signature verification). If invalid, return a GraphQL error.
SQL — keyset if after is set, initial-page if not. Both queries fetch first + 1 rows for the peek-ahead.
has_next = (len(rows) > first) — the peek-ahead trick. Trim to first rows before returning.
Build edges with a per-row cursor (generated from (created_at, id)) plus pageInfo with hasNextPage / hasPreviousPage / startCursor / endCursor.

Output — Relay-shaped JSON envelope, ready for apollo-client or relay-runtime.

Rule of thumb. The first + 1 peek-ahead + edges + pageInfo envelope is the entire Relay spec, condensed. Every GraphQL feed on the internet ships this exact pattern.

Worked example — BigQuery keyset with partition pruning

Detailed explanation. The BigQuery specialisation — partition by DATE(sort_col) and cluster by sort_col. Keyset works, but you have to be careful with the outer WHERE to prune partitions.

Question. Design the BigQuery keyset paginator for a 10B-row events table, partitioned by DATE(created_at) and clustered by (created_at, id). Include the partition prune predicate.

Input. events table on BigQuery with PARTITION BY DATE(created_at) CLUSTER BY created_at, id.

Code.

-- BigQuery keyset with partition prune
SELECT id, user_id, created_at, payload
FROM `project.dataset.events`
WHERE
  -- Partition prune: only relevant days
  DATE(created_at) BETWEEN
    DATE_SUB(DATE(@cursor_ts), INTERVAL 7 DAY) AND DATE(@cursor_ts)
  -- Keyset tuple comparison
  AND (created_at, id) < (@cursor_ts, @cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

Step-by-step explanation.

Partition prune — DATE(created_at) BETWEEN cursor_date - 7 AND cursor_date. Reads only the last 7 days of partitions relative to the cursor. Tighter windows read fewer partitions but risk missing older rows if the user paginates deep.
Alternative — DATE(created_at) <= DATE(@cursor_ts). Prunes all partitions newer than the cursor; reads all older ones. Broader but always correct.
Keyset tuple compare — same as Postgres/MySQL. BigQuery has supported row-value comparison since 2019.
CLUSTER BY (created_at, id) — within each partition, rows are physically co-located by (created_at, id). The tuple compare uses the clustering to prune blocks.
LIMIT 21 — peek-ahead for hasNextPage.

Output. 20 rows per page, flat latency at any depth, ~150 ms per page on a 10B-row table.

Rule of thumb. On BigQuery (or any partitioned columnar warehouse), always combine keyset with a partition-prune predicate. Skipping the prune predicate means a full-table scan even with keyset — the tuple compare alone won't prune partitions on its own.

Senior interview question on API-shape and cursor discipline

A senior interviewer might ask: "Design the wire format for an infinite-scroll messages endpoint. What goes in the cursor, how do you sign it, what does the response envelope look like, and how do you handle secret rotation?"

Solution Using signed opaque cursor + Relay envelope + graceful secret rotation

import base64
import hashlib
import hmac
import json
import os
from datetime import datetime, timezone
from typing import Optional

# Two secrets — active and previous — to allow graceful rotation
SECRETS = {
    "v2": os.environ["CURSOR_SECRET_V2"],  # current
    "v1": os.environ.get("CURSOR_SECRET_V1"),  # previous, may be None
}
ACTIVE_VERSION = "v2"


def encode_cursor(ts: datetime, mid: int) -> str:
    payload = {
        "ts": ts.astimezone(timezone.utc).isoformat(),
        "id": mid,
        "dir": "desc",
        "v": ACTIVE_VERSION,
    }
    b = base64.urlsafe_b64encode(
        json.dumps(payload, separators=(",", ":"), sort_keys=True).encode()
    ).rstrip(b"=").decode()
    sig = hmac.new(
        SECRETS[ACTIVE_VERSION].encode(), b.encode(), hashlib.sha256
    ).digest()
    return b + "." + base64.urlsafe_b64encode(sig).rstrip(b"=").decode()


def decode_cursor(cursor: str) -> tuple[datetime, int]:
    b, sig = cursor.split(".", 1)
    # Peek at the version in the payload
    padding = b"=" * (-len(b) % 4)
    payload = json.loads(base64.urlsafe_b64decode(b.encode() + padding))
    version = payload.get("v", "v1")
    secret = SECRETS.get(version)
    if secret is None:
        raise ValueError(f"cursor version {version} no longer supported")
    expected = base64.urlsafe_b64encode(
        hmac.new(secret.encode(), b.encode(), hashlib.sha256).digest()
    ).rstrip(b"=").decode()
    if not hmac.compare_digest(sig, expected):
        raise ValueError("cursor signature invalid")
    return datetime.fromisoformat(payload["ts"]), payload["id"]


def messages_connection(channel_id: int, first: int, after: Optional[str]) -> dict:
    first = min(max(first, 1), 100)
    if after:
        try:
            cts, cid = decode_cursor(after)
        except ValueError as e:
            raise ApiError(400, str(e))
        rows = db.query(KEYSET_SQL, (channel_id, cts, cid, first + 1))
    else:
        rows = db.query(FIRST_PAGE_SQL, (channel_id, first + 1))

    has_next = len(rows) > first
    rows = rows[:first]
    edges = [
        {"node": r, "cursor": encode_cursor(r["created_at"], r["id"])}
        for r in rows
    ]
    return {
        "edges": edges,
        "pageInfo": {
            "hasNextPage": has_next,
            "hasPreviousPage": after is not None,
            "endCursor": edges[-1]["cursor"] if edges else None,
        },
    }

Step-by-step trace.

Step	Component	Purpose
1	Payload includes `v: "v2"`	Version tag for secret rotation
2	Sign with `SECRETS[ACTIVE_VERSION]`	Current secret
3	On decode, peek `v` before verification	Pick which secret to verify against
4	If `v` not in SECRETS → reject	Cursor too old, client re-fetches page 1
5	Verify signature constant-time	Reject tampered cursors
6	SQL uses keyset SQL if `after`, initial otherwise	O(log n + K) per page
7	Response uses Relay `edges + pageInfo`	Standard GraphQL envelope
8	LIMIT first + 1 for peek-ahead	hasNextPage without extra query

The design is production-ready: opaque signed cursors with version tags, graceful secret rotation via a two-secret map, Relay-standard response envelope, and the peek-ahead trick for hasNextPage. When a secret rotates, the new secret becomes v2 and the old becomes v1; clients with old cursors continue to work for one rotation window. After the window, old cursors return 400 and the client re-fetches page 1.

Output:

Property	Value
Wire cursor size	~90 bytes (base64 payload + base64 signature)
Verification time	~50 μs (HMAC-SHA256 constant-time)
Rotation window	typically 24–72 hours (max client idle)
DB latency per page	~5 ms on covering-indexed 10B-row table

Why this works — concept by concept:

Opaque, version-tagged, HMAC-signed cursor — the payload includes v: "v2" so the server knows which secret to verify against. Tamper-proof and forward-compatible with rotation.
Constant-time signature compare — hmac.compare_digest prevents timing attacks. If you use == on the signature, an attacker can measure the compare time and forge a valid cursor byte-by-byte.
Graceful rotation with a two-secret map — on rotation, promote v2 → v3 and v1 → v2 (drop the oldest). Clients with cursors signed by the previous secret continue to work until the window expires.
Relay envelope with peek-ahead — LIMIT first + 1 fetches one extra row; has_next = (fetched > first); the extra row is discarded and its cursor becomes the next after. No extra query.
Cost — HMAC verify O(1). Cursor decode O(payload size). SQL keyset O(log n + K) per page. Total per-request cost is dominated by the SQL — which is flat across pagination depth. The Relay envelope adds negligible overhead.

SQL
Topic — pagination
Cursor pagination problems

Practice →

SQL
Topic — window functions
Window function drills

Practice →

Cheat sheet — pagination recipe list

Two-primitive rule. sql pagination reduces to two primitives — OFFSET-based (page-numbered, admin dashboards, small tables) and keyset-based (infinite scroll, APIs, large mutable tables). Every real system ships one, the other, or both.
OFFSET / LIMIT skeleton. SELECT ... ORDER BY sort_col, id LIMIT n OFFSET k. Cost O(k + n) — fine up to k ≈ 1,000, painful past k ≥ 100,000.
ANSI OFFSET FETCH skeleton. SELECT ... ORDER BY sort_col, id OFFSET k ROWS FETCH FIRST n ROWS ONLY. Postgres, Oracle 12c+, SQL Server 2012+, Db2. Verbose but standard.
SQL Server specifics. OFFSET k ROWS FETCH NEXT n ROWS ONLY — NEXT is a synonym for FIRST. ORDER BY is required — the parser refuses without one.
MySQL shorthand. LIMIT k, n — offset first, limit second (reversed operand order). Equivalent to LIMIT n OFFSET k.
Oracle 11g legacy. Nested ROWNUM subquery — sort inside, number in the middle, filter outside. Migrate to OFFSET ... FETCH FIRST on 12c+.
Deterministic ORDER BY rule. Every paginated query needs a unique tie-breaker (usually id) — ORDER BY sort_col DESC, id DESC. Ties without the tie-breaker drift across pages.
NULLS ordering rule. Spell out ORDER BY sort_col DESC NULLS LAST. Postgres/Oracle default to NULLs first; MySQL/SQL Server default to NULLs last; BigQuery to NULLs first. Never rely on the default.
Concurrent-write failure mode. OFFSET is positional and drifts under inserts / deletes — the classic "row seen twice" or "row skipped" bug. Keyset is keyed and immune.
Deep-OFFSET cost. O(k + n) — for k = 1M, n = 20, the engine walks 1M rows before emitting 20. Latency curve stays flat until it isn't, then climbs a cliff.
Keyset skeleton. WHERE (sort_col, id) < (:cursor_col, :cursor_id) ORDER BY sort_col DESC, id DESC LIMIT n. Deep-page cost O(log n + K) — flat forever.
Tuple comparison rule. (a, b) < (:a, :b) is lexicographic — first a < :a, or a = :a AND b < :b. Not the same as a < :a AND b < :b (which drops valid rows).
Composite index rule. Keyset requires a composite index on the ORDER BY columns in the ORDER BY direction — (sort_col DESC, id DESC). Enables index-only backward scan.
SQL Server row-value fallback. Expand tuple compare to sort_col < :cursor_ts OR (sort_col = :cursor_ts AND id < :cursor_id). Same semantics, verbose but portable to older engines.
Mixed-direction ORDER BY. For ORDER BY score DESC, id ASC, expand to score < :s OR (score = :s AND id > :i). Row-value compare doesn't fit mixed directions.
Backward pagination. Flip < to >, flip ORDER BY direction, reverse result in application code. Or use Relay's last / before pair.
Peek-ahead trick. LIMIT n + 1 — fetch one extra row, use its existence to compute hasNextPage, discard from the response. Zero extra queries.
Opaque cursor rule. Cursors are base64-encoded JSON, HMAC-signed with a server-side secret. Clients treat them as black boxes. Never expose raw tuples.
Cursor rotation rule. Version-tag the payload; keep two active secrets (current + previous) for a rotation window equal to max client idle time. Reject cursors older than the window with a 400 and re-fetch-page-1 hint.
Relay Connections envelope. edges[] (with per-edge cursor) + pageInfo (hasNextPage, hasPreviousPage, endCursor, startCursor). GraphQL standard since 2015.
REST cursor envelope. {"data": [...], "next_cursor": "eyJ...", "prev_cursor": null} — Stripe, GitHub, Twitter shapes. Or RFC-5988 Link headers for HATEOAS-style clients.
Warehouse specifics. Snowflake — cluster by sort_col; use tuple compare. BigQuery — partition by DATE(sort_col), cluster by sort_col, keyset with partition-prune predicate. Never use OFFSET past ~10K on either.
Bounded-OFFSET safety net. For legacy admin dashboards you can't migrate yet, cap page × page_size ≤ 1M. Bounded worst-case latency of ~600 ms on a covering-indexed 100M-row table.
SELECT COUNT(*) warning. Total count on a mutable table is O(n) regardless. On infinite scroll, don't return it. On admin dashboards, cache it.
Dialect matrix. LIMIT / OFFSET: Postgres, MySQL, Snowflake, BigQuery, SQLite, Redshift, DuckDB. OFFSET / FETCH FIRST: Postgres, Oracle 12c+, SQL Server 2012+, Db2. Row-value compare: Postgres, MySQL 8, Oracle 12c+, Snowflake, BigQuery. Expanded form fallback: SQL Server, older MySQL.
When OFFSET is fine. Small tables (< 1M rows), aggregate dashboards, page-capped admin UIs (page ≤ 5000), one-off batched exports. Everything else — keyset.
When keyset is fine. Infinite-scroll feeds, mobile pagination, GraphQL Relay endpoints, big-table list APIs, any endpoint expected to serve deep pages with stable results under concurrent writes.

Frequently asked questions

What is SQL pagination and how do you choose between OFFSET and keyset?

sql pagination is the primitive that lets a client fetch "the next N rows of an ordered result set" without re-fetching what came before. The two implementations you have to know are OFFSET-based — SELECT ... ORDER BY x LIMIT n OFFSET k — and keyset (seek method) based — WHERE (x, id) < (:cursor_x, :cursor_id) ORDER BY x DESC, id DESC LIMIT n. Choose OFFSET when the product requires random access to page N (admin dashboards, "jump to page 47" pagers), the table is small (< 1M rows) or aggregate-sized, and the page depth is capped (page × page_size ≤ 1M on a covering-indexed table). Choose keyset for every consumer feed, every GraphQL Relay endpoint, every "list my orders / posts / messages" endpoint, and any pagination that must be stable under concurrent inserts and deletes — because OFFSET is positional and drifts under mutations, whereas keyset navigates by key and is immune. The cost story cements the choice: OFFSET is O(k + n) per page (linear in depth), keyset is O(log n + K) per page (flat forever). On a 100M-row table with a covering index, OFFSET at page 100K takes about 1.4 s while keyset at any depth stays around 5 ms — a 300× gap that widens as the table grows.

Why is OFFSET slow on deep pages?

Because the planner has no O(1) skip primitive on an ordered scan. When you write LIMIT 20 OFFSET 1000000, the engine walks the ordered stream one row at a time, counting past a million rows, discarding each, before emitting the last twenty. Cost is O(k + n) per page — for k = 1M, that's a million row reads per page. On a covering-indexed table with SELECT id, created_at, Postgres's EXPLAIN (ANALYZE, BUFFERS) shows Index Scan Backward using idx_events with actual rows=20 actual loops=1000020 — the "20 rows but a million loops" line is the smoking gun. Buffer pressure amplifies this — past a few hundred thousand rows, the working set exceeds shared_buffers and reads spill from cache-hit to disk-read, latency jumps from milliseconds to seconds. Wide rows (with SELECT * on a 1KB row) make it worse — each discarded row still pays its projection cost. And on columnar warehouses like BigQuery and Snowflake, per-column read overhead multiplies the effect further — deep OFFSET reads gigabytes of column data before returning bytes. Total cost across a full pagination from page 1 to page P is quadratic — O(P² × n) — which is why every social feed on the internet moved off OFFSET a decade ago.

What is keyset (seek method) pagination?

keyset pagination (a.k.a. seek method pagination) replaces "skip k rows" with "give me the rows strictly after this cursor tuple." The two-line skeleton is WHERE (sort_col, id) < (:cursor_col, :cursor_id) ORDER BY sort_col DESC, id DESC LIMIT n — the WHERE tuple comparison positions the scan at the cursor via an index seek on the composite (sort_col DESC, id DESC) index, and the LIMIT bounds the forward scan to exactly n rows. Total cost is O(log n + K) per page — the index seek is O(log n) (depth of the btree), and K = LIMIT sequential reads finish it. Every page — page 1 or page 1,000,000 — hits the exact same plan shape, so latency stays flat across the entire pagination depth. The tuple comparison (a, b) < (:a, :b) is lexicographic — first a < :a, or a = :a AND b < :b — supported natively on Postgres, MySQL 8, Oracle 12c+, Snowflake, and BigQuery. On SQL Server and older MySQL, expand to a < :a OR (a = :a AND b < :b) — same semantics, verbose but portable. The trade-off is no random access — you can't ask for "page 47" — but every infinite-scroll consumer feed on the internet uses keyset because it beats OFFSET on both cost and consistency.

How do you build cursor pagination for a REST or GraphQL API?

Cursors are opaque base64-encoded, HMAC-signed tokens — clients treat them as black boxes and pass them back verbatim on the next call. The payload is a JSON blob {"ts": "2026-07-05T12:00:00Z", "id": 5000000, "dir": "desc", "v": "v2"} — base64url-encoded, then HMAC-SHA256-signed with a server-side secret, then joined as payload_b64.signature_b64 (JWT-like without a header). Servers verify the signature in constant time (hmac.compare_digest) before decoding, so tampered cursors are rejected before any SQL runs. For GraphQL, follow the Relay Connections spec — orders(first: 20, after: "eyJ...") returns edges[] (with a per-edge cursor) plus pageInfo (hasNextPage, endCursor, startCursor). For REST, use {"data": [...], "next_cursor": "eyJ...", "prev_cursor": null} (Stripe / GitHub style) or RFC-5988 Link headers (<https://.../orders?cursor=eyJ...>; rel="next"). The hasNextPage uses the peek-ahead trick — fetch LIMIT n + 1, use the existence of the (n+1)th row as hasNextPage, discard it before returning. Rotate the signing secret every 90 days with a two-secret map (current + previous) so cursors survive one rotation window; reject older versions with a 400 and a hint to re-fetch page 1.

How does pagination differ across Postgres, MySQL, SQL Server, Oracle, Snowflake, and BigQuery?

Postgres — LIMIT n OFFSET k (native) and OFFSET k ROWS FETCH FIRST n ROWS ONLY (ANSI); full row-value comparison (a, b) < (:a, :b) for keyset; composite descending indexes since forever. Best case. MySQL 8 — LIMIT n OFFSET k or shorthand LIMIT k, n (reversed operands); row-value compare since 8.0.16; descending indexes since 8.0.13. Full keyset support. SQL Server 2012+ — OFFSET k ROWS FETCH NEXT n ROWS ONLY with required ORDER BY; no row-value compare (use expanded a < :a OR (a = :a AND b < :b) form); no NULLS FIRST/LAST (use CASE WHEN … IS NULL workaround). Oracle 12c+ — OFFSET k ROWS FETCH FIRST n ROWS ONLY (ANSI); full row-value compare; pre-12c uses nested ROWNUM subquery. Snowflake — LIMIT n OFFSET k; row-value compare supported; no btree indexes — use CLUSTER BY (sort_col) for micro-partition co-location; avoid OFFSET past ~10K. BigQuery — LIMIT n OFFSET k; row-value compare since 2019; no btree — use PARTITION BY DATE(sort_col) CLUSTER BY sort_col and combine keyset with an outer partition-prune predicate; OFFSET is documented as an anti-pattern past a few thousand rows. Learn the six spellings; every migration between engines rewrites pagination as a mechanical translation.

How do you handle ties and NULLs in a paginated ORDER BY?

Ties — two rows sharing the ORDER BY key (same created_at to the millisecond, same score in a leaderboard) can alternate between pages unless you add a unique tie-breaker. Every paginated ORDER BY ends with a unique column, usually the primary key: ORDER BY created_at DESC, id DESC. The tuple comparison in keyset must include the tie-breaker too — (created_at, id) < (:cursor_ts, :cursor_id). Without the tie-breaker, WHERE created_at < :ts would skip all rows tied on :ts even if only one was the cursor row. NULLs — Postgres and Oracle default to NULLs first for DESC; MySQL and SQL Server default to NULLs last; BigQuery defaults to NULLs first. Never rely on the default — spell it out: ORDER BY created_at DESC NULLS LAST. Postgres, Oracle, MySQL 8, and BigQuery all support the NULLS FIRST / LAST syntax; SQL Server doesn't — use the CASE WHEN created_at IS NULL THEN 1 ELSE 0 END, created_at DESC workaround. For keyset with a nullable sort column, either mark the column NOT NULL (the safest fix) or use COALESCE(created_at, '-infinity') on both sides of the comparison to give NULLs a defined position. Every keyset paginator ships either the NOT NULL constraint or the coalescing predicate.

Practice on PipeCode

Drill the SQL pagination practice library → for LIMIT / OFFSET, FETCH FIRST n ROWS ONLY, keyset tuple comparison, and cursor endpoints across Postgres, MySQL 8, SQL Server, Oracle, Snowflake, and BigQuery dialects.
Rehearse on ORDER BY + LIMIT problems → — the ordering, tie-breaker, and NULL discipline that make every paginated query deterministic.
Sharpen the SQL indexing drill room → for composite (sort_col, id) indexes, index-only backward scans, and the difference between OFFSET's O(k) and keyset's O(log n + K) deep-page paths.
Push the difficulty ceiling with SQL optimization drills → — read EXPLAIN ANALYZE plans, spot the OFFSET trap, and migrate legacy paginators to keyset.
Warm up with top-N per group problems → — the sibling primitive to pagination, sharing the composite (partition_col, sort_col, id) index pattern.
Layer window function drills → — the ROW_NUMBER() / RANK() / DENSE_RANK() family that shows up when a legacy paginator can't be migrated to keyset yet.
Sharpen the general SQL surface with the SQL practice library → which contains 450+ DE-focused questions covering pagination, keyset cursors, index alignment, and every adjacent pattern.
For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql pagination` recipe above ships with hands-on practice rooms where you type the OFFSET / LIMIT skeleton, migrate it to `sql offset fetch` (ANSI) and `OFFSET ... FETCH NEXT` (SQL Server) side by side, benchmark the `sql limit offset performance` cliff at page 1M with `EXPLAIN (ANALYZE, BUFFERS)`, rewrite the same paginator as `keyset pagination` with tuple comparison, align the composite index for `seek method pagination`'s `O(log n)` deep-page path, wire the whole thing behind an opaque HMAC-signed cursor for `sql cursor pagination`, and finally serve the Relay `edges / pageInfo` envelope that every `sql infinite scroll` feed on the internet ships. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your pagination answer holds up under a senior interviewer's depth probes.

Practice pagination now →
Indexing drills →

1. Why pagination matters in 2026

The three shapes of sql pagination — infinite scroll, page-numbered UIs, and CSV-style exports — and why the OFFSET default fails all three at scale

Worked example — the OFFSET query that ships to production on day one

Worked example — the OFFSET failure mode across concurrent writes

Worked example — engine dialect quickstart

Senior interview question on when to use OFFSET vs keyset

Solution Using dual-endpoint design — keyset for infinite-scroll, capped OFFSET for admin

2. OFFSET / FETCH / LIMIT — dialect matrix

sql offset fetch and LIMIT across Postgres, MySQL, SQL Server, Oracle, Snowflake, and BigQuery — same primitive, six spellings, one shared perf model

Worked example — Postgres, MySQL, and SQL Server pagination side by side

Worked example — Oracle 11g's ROWNUM pattern vs Oracle 12c+'s FETCH FIRST

Worked example — BigQuery's OFFSET warning and the recommended keyset alternative

Senior interview question on cross-warehouse pagination portability

Solution Using dialect-dispatched dbt macro with a shared cursor contract

3. Why OFFSET is slow at page 1M

sql limit offset performance — the O(k + n) cost model, the wide-row memory story, and the "consistent read across pages" failure mode

Worked example — measuring the OFFSET latency curve with EXPLAIN ANALYZE

Worked example — the concurrent-write drift bug in action

Worked example — bounded-OFFSET as a safety net

Senior interview question on OFFSET perf diagnosis

Solution Using EXPLAIN ANALYZE + covering-index verification + keyset migration path

4. Keyset (seek method) pagination

keyset pagination and seek method pagination — WHERE (created_at, id) < (:cursor) ORDER BY DESC LIMIT n, composite index alignment, and the O(log n) deep-page path

Worked example — the "next 20 events" keyset query on Postgres

Worked example — expanded per-column form for SQL Server (no row-value comparison)

Worked example — backward pagination (previous page)

Worked example — keyset over composite ORDER BY with mixed directions

Senior interview question on keyset design

Solution Using channel-scoped composite index + keyset tuple compare

5. Cursor pagination + dialect matrix

sql cursor pagination and sql infinite scroll — opaque base64 tokens, HMAC signing, the GraphQL Relay spec, and the six-engine dialect matrix that ties it all together

Worked example — encoding and decoding an opaque cursor with HMAC

Worked example — the Relay Connections envelope

Worked example — BigQuery keyset with partition pruning

Senior interview question on API-shape and cursor discipline

Solution Using signed opaque cursor + Relay envelope + graceful secret rotation

Cheat sheet — pagination recipe list

Frequently asked questions

What is SQL pagination and how do you choose between OFFSET and keyset?

Why is OFFSET slow on deep pages?

What is keyset (seek method) pagination?

How do you build cursor pagination for a REST or GraphQL API?

How does pagination differ across Postgres, MySQL, SQL Server, Oracle, Snowflake, and BigQuery?

How do you handle ties and NULLs in a paginated ORDER BY?

Practice on PipeCode

The three shapes of `sql pagination` — infinite scroll, page-numbered UIs, and CSV-style exports — and why the OFFSET default fails all three at scale

`sql offset fetch` and `LIMIT` across Postgres, MySQL, SQL Server, Oracle, Snowflake, and BigQuery — same primitive, six spellings, one shared perf model

Worked example — Oracle 11g's `ROWNUM` pattern vs Oracle 12c+'s `FETCH FIRST`

`sql limit offset performance` — the O(k + n) cost model, the wide-row memory story, and the "consistent read across pages" failure mode

`keyset pagination` and `seek method pagination` — WHERE (created_at, id) < (:cursor) ORDER BY DESC LIMIT n, composite index alignment, and the O(log n) deep-page path

`sql cursor pagination` and `sql infinite scroll` — opaque base64 tokens, HMAC signing, the GraphQL Relay spec, and the six-engine dialect matrix that ties it all together