LIMIT and OFFSET

What LIMIT / OFFSET really cost

LIMIT n caps the rows returned; OFFSET m skips the first m. The syntax is trivial — the performance is not. The database cannot skip rows it hasn't produced: to honour OFFSET 100000 it computes the query in sort order, walks past the first 100,000 result rows, and discards them, returning only the next n. So the deeper you page, the more wasted work:

-- page 1:    cheap  — read 20 rows
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 0;
-- page 5001: slow   — read 100020 rows, throw away 100000
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000;

OFFSET pagination is O(offset). It feels fine in testing (page 1–3) and quietly melts in production when a crawler or a power user reaches page 5,000. This is the classic deep-pagination problem.

Keyset (seek) pagination — pay for the page, not the offset

Instead of "skip 100,000 rows", remember the sort key of the last row you showed and ask for rows after it. With an index on the sort columns, the engine seeks straight to that position:

-- first page
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- next page: carry the last row's (created_at, id) as a cursor
SELECT * FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)   -- row-value comparison
ORDER BY created_at DESC, id DESC
LIMIT 20;

Now every page costs the same — an index seek plus reading 20 rows — no matter how deep you go. The tie-break on a unique column (id) is mandatory: created_at alone isn't unique, so without id two rows sharing a timestamp could land on a page boundary and be skipped or repeated.

The trade-off, stated honestly

• OFFSET: can jump to any page number ("go to page 47"), but slow at depth and unstable if rows are inserted/deleted between requests (the window shifts → you see duplicates or miss rows).
• Keyset: O(log n) at any depth and stable under inserts, but only supports next/prev relative to a cursor — no "jump to page 47". This is why infinite-scroll feeds (Twitter, Slack) use keyset, while admin tables with numbered pages often keep OFFSET.

Takeaways

• OFFSET m is O(m): the rows are produced then discarded — deep pages get linearly slower.
• Keyset pagination (WHERE (k) < (cursor) + matching index) makes every page equally cheap.
• Always order by something deterministic (append a unique tie-breaker); both schemes corrupt page boundaries without it.

---

Deepened for this guide (the prior version covered only syntax). Seek-method pagination per Markus Winand's "Use The Index, Luke" and the PostgreSQL row-value-comparison docs. See also: Indexing & Storage — B+tree internals.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — LIMIT and OFFSET

Why this exists / the decision it encodes

LIMIT/OFFSET exist for paging UX, but OFFSET is not free: the engine must produce and discard the skipped rows. Deep pagination becomes O(offset). Keyset (seek) pagination exists to pay O(log n + page) via an index seek past a cursor — trading away arbitrary page-number jumps.

Worked example with numbers or traced SQL/FD

-- OFFSET page 5001 of 20: reads ~100020 ordered rows, discards 100000
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000;
-- Keyset: same page cost at any depth
SELECT * FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Must include unique id: created_at alone → skipped/duplicated boundary rows
Mutating table: OFFSET windows shift → duplicates/gaps; keyset is stable under inserts.

When NOT / named alternative

Use OFFSET only for shallow admin tables that need "go to page 47" and small depth. Use keyset for infinite scroll, APIs, and any feed that can go deep. Do not paginate without deterministic ORDER BY including a unique tie-breaker.

Failure mode / ops fingerprint / interview trap

Ops fingerprint: p99 latency climbs linearly with page number; crawlers hit page 5000. Interview trap: keyset without unique column; or OFFSET "optimized" by larger LIMIT only.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: pagination is an access-path problem; keyset requires a composite index matching the ORDER BY. Stability under concurrent inserts is a correctness property of the page model.

Hostile-panel drills (with model answers)

Q1. Complexity of OFFSET m LIMIT n vs keyset LIMIT n?
Model answer: OFFSET is O(m+n) work to produce-then-discard; keyset is O(log N + n) with a matching index on the sort keys.

Q2. Why ORDER BY created_at DESC, id DESC not just created_at?
Model answer: Timestamps collide; without a unique tie-breaker, rows on page boundaries can be skipped or repeated between requests.

Q3. When is OFFSET still acceptable?
Model answer: Small tables, UI that must jump to arbitrary page numbers, and proven shallow max depth (e.g. admin last 10 pages only).

I just read a lesson on **LIMIT and OFFSET** (Databases) and want to truly understand it. Explain LIMIT and OFFSET from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **LIMIT and OFFSET** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **LIMIT and OFFSET** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **LIMIT and OFFSET** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.