How do you paginate efficiently in SQL? | Interview Question

Q: How do you paginate efficiently in SQL?

**Target Audience:** Developers building list screens, feeds and APIs who have used `LIMIT`/`OFFSET` and want to know why deep pages get slow and rows repeat; candidates preparing for a pagination design question. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (plans and timings), both run for this page. `LIMIT`/`OFFSET` works on both; SQL Server uses `OFFSET ... FETCH NEXT ...`. > **How to read this doc:** Each idea is explained in plain words first, then tagged with `📌 Interview term:` so you also learn the vocabulary an interviewer expects. All outputs and timings were produced by actually running the queries; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You are reading a very long book and stop for the night. There are two ways to find your place the next day. **The page-count way:** "I was on page 412." Tomorrow you open the book at the start and count 412

The problem

Question presented to candidate: "Our article feed uses LIMIT 20 OFFSET n and the deep pages are getting slow; users also report seeing the same article twice when they click next. What is going on, and how would you paginate this efficiently?"

What a strong answer should cover:

Why OFFSET n is slow on deep pages: the database still produces and throws away the first n rows, so page cost grows with page depth even with an index
Why OFFSET pages drift: rows inserted or deleted between requests shift every later page, so rows repeat or are skipped
Keyset (seek) pagination: remember the sort key of the last row seen and ask for rows after it, WHERE (published_at, id) < (:last_date, :last_id) ORDER BY published_at DESC, id DESC LIMIT 20
A unique tiebreaker in both the ORDER BY and the cursor, and a composite index in exactly that order, so the next page is one index seek
Trade-offs: keyset cannot jump to page 517 and needs an opaque cursor in the API; total counts (COUNT(*)) are a separate, full-cost query; row-value syntax differs by engine

Clarifying questions expected:

Do users need to jump to an arbitrary page number, or only next / previous (infinite scroll)?
What is the sort order, and is it unique? If not, what is the tiebreaker?
Do we need an exact total count, or is "more results" enough?

Code / implementation expected: Yes — the OFFSET query, the keyset query with a tiebreaker, the index that supports it, and ideally a plan showing the difference.

Schema used by the examples:

pagination

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers building list screens, feeds and APIs who have used LIMIT/OFFSET and want to know why deep pages get slow and rows repeat; candidates preparing for a pagination design question. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (plans and timings), both run for this page. LIMIT/OFFSET works on both; SQL Server uses OFFSET ... FETCH NEXT ....

How to read this doc: Each idea is explained in plain words first, then tagged with 📌 Interview term: so you also learn the vocabulary an interviewer expects. All outputs and timings were produced by actually running the queries; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You are reading a very long book and stop for the night. There are two ways to find your place the next day.

The solution is hidden so you can try first

9 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLOFFSET pagination: page 2 of the feed, 4 per page

SQLA row inserted between requests: OFFSET repeats a row, keyset does not

PostgreSQL: OFFSET on the last page reads all 100,000 index entries

-- 100,000 generated posts, then ask for "page 5,001" (20 per page)
CREATE TABLE posts AS
SELECT g AS id, 'post ' || g AS title,
       TIMESTAMP '2024-01-01' + g * INTERVAL '1 minute' AS created_at
FROM generate_series(1, 100000) AS g;
CREATE INDEX posts_feed ON posts (created_at DESC, id DESC);
VACUUM ANALYZE posts;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000 - 20;

PostgreSQL: keyset on the same page reads 20

-- The same 100,000 posts; the client sends the last key it saw
CREATE TABLE posts AS
SELECT g AS id, 'post ' || g AS title,
       TIMESTAMP '2024-01-01' + g * INTERVAL '1 minute' AS created_at
FROM generate_series(1, 100000) AS g;
CREATE INDEX posts_feed ON posts (created_at DESC, id DESC);
VACUUM ANALYZE posts;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (TIMESTAMP '2024-01-01' + 21 * INTERVAL '1 minute', 21)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.