Offset vs cursor pagination in SQL

Pagination means splitting a long list into smaller pages, like showing 10 posts at a time instead of every row in the table. It's a favorite backend interview topic, and the question usually sounds like this: offset or cursor, and why?

Every example here is plain SQL for PostgreSQL 18, the current version. You can run it in psql, the command-line tool that comes with Postgres, or in any SQL editor. You'll fill a table with 100,000 posts, watch offset pagination slow down and repeat rows, then fix both problems with a cursor. It takes about 10 minutes to read and try.

Offset pagination says "skip 20 rows and give me the next 10," while cursor pagination says "give me the 10 rows after this one."

1. Make a table with 100,000 posts

Start with one posts table and plenty of sample rows, so the slow queries actually show up.

CREATE TABLE posts (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO posts (title, created_at)
SELECT 'Post ' || n, timestamptz '2026-01-01 00:00+00' + n * interval '1 minute'
FROM generate_series(1, 100000) AS n;

GENERATED ALWAYS AS IDENTITY makes Postgres number each new row, so a bigger id always means a newer post, and timestamptz is a date and time with a time zone. A primary key is the column that identifies each row, and Postgres builds an index for it automatically: a sorted copy of the ids it can search quickly, like the index at the back of a book. generate_series(1, 100000) produces the numbers 1 to 100,000, so the INSERT adds one post per number, each a minute after the last.

2. Offset pagination

Offset pagination is the version most tutorials show. The app takes a page number, works out how many rows to skip, and puts that number in the query.

-- page 3, 10 posts per page: skip (3 - 1) * 10 = 20 rows
SELECT id, title
FROM posts
ORDER BY id DESC
LIMIT 10 OFFSET 20;

ORDER BY id DESC puts the newest posts first, LIMIT caps how many rows come back, and OFFSET says how many to skip, so this returns posts 99980 down to 99971. Always include ORDER BY when you paginate, because SQL doesn't promise any row order without it and two pages could share rows.

3. Problem one: deep pages get slow

Interviewers usually expect you to name two problems with offset. The first is speed, and EXPLAIN ANALYZE shows why: it runs the query and prints the plan, the list of steps the database took, with how many rows each step handled.

EXPLAIN ANALYZE
SELECT id, title
FROM posts
ORDER BY id DESC
LIMIT 10 OFFSET 90000;

These are the two lines that matter, with the timing details cut:

Limit  (...) (actual ... rows=10.00 ...)
  ->  Index Scan Backward using posts_pkey on posts  (...) (actual ... rows=90010.00 ...)

Read the plan from the bottom up: the index scan walked through 90,010 rows, and the Limit step threw away the first 90,000 and kept 10. Page 9,001 does all that work every time someone opens it, and it gets worse as the table grows. PostgreSQL 18 prints row counts with two decimals, so older versions show rows=10 instead.

4. Problem two: new rows shift the pages

The second problem appears when the data changes between two requests. Run these three statements in order, as if a user loads page 1, someone publishes a post, and then the user loads page 2.

SELECT id FROM posts ORDER BY id DESC LIMIT 2 OFFSET 0;  -- 100000, 99999

INSERT INTO posts (title) VALUES ('Brand new post');     -- gets id 100001

SELECT id FROM posts ORDER BY id DESC LIMIT 2 OFFSET 2;  -- 99999, 99998

Post 99999 shows up on both pages, because the new row pushed every post down one spot. A deleted row does the opposite and makes the user skip one, and since neither case throws an error, these bugs are easy to miss.

5. Cursor pagination

A cursor is a small value that marks the last row the user saw, like a bookmark. The next query asks only for rows older than the bookmark.

-- the last post the user saw was 99991, so get the 10 after it
SELECT id, title
FROM posts
WHERE id < 99991
ORDER BY id DESC
LIMIT 10;

This returns posts 99990 down to 99981. New posts get bigger ids and land above the bookmark, so they can't push anything onto the next page.

6. Why the cursor stays fast

To compare with section 3, ask for the same deep page, posts 10000 down to 9991, with a cursor.

EXPLAIN ANALYZE
SELECT id, title
FROM posts
WHERE id < 10001
ORDER BY id DESC
LIMIT 10;

Limit  (...) (actual ... rows=10.00 ...)
  ->  Index Scan Backward using posts_pkey on posts  (...) (actual ... rows=10.00 ...)
        Index Cond: (id < 10001)

Index Cond means Postgres used the index to jump straight to id 10000, so it handled 10 rows instead of 90,010. The cursor query does the same small amount of work on page 2 or page 9,001.

7. Know when to stop

The app also needs to know whether another page exists. Ask for one row more than you show, so 11 rows for a page of 10.

SELECT id, title
FROM posts
WHERE id < 11
ORDER BY id DESC
LIMIT 11;

Only 10 rows come back (posts 10 down to 1), so this is the last page. When the 11th row does come back, the app shows the first 10 and sends the 10th row's id as the next cursor, usually in JSON like { "items": [...], "nextCursor": 9991 }. The client sends that cursor with its next request, and it becomes the number in WHERE id < ....

8. When the sort column can repeat

Sorting by id works because ids never repeat. Many apps sort by created_at instead, where two posts can share the same time, and a plain WHERE created_at < ... would skip any post with exactly the same time as the bookmark. The fix is a tie-breaker, a second sort column that is unique, like id, plus an index on both columns.

CREATE INDEX posts_created_at_id_idx ON posts (created_at, id);

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-03-11 10:31:00+00', 99991)
ORDER BY created_at DESC, id DESC
LIMIT 10;

(created_at, id) < (x, y) compares pairs the way a dictionary compares words: time first, then id only when the times match. The cursor now holds two values, the last row's time and id, and because the index has the same columns in the same order as the ORDER BY, Postgres can still jump straight to the bookmark.

That's it

Offset pagination counts rows to skip, cursor pagination continues from a bookmark, and that bookmark keeps pages fast and free of repeats when the table changes.

Two practical tips:

1. Run EXPLAIN ANALYZE on your own pagination queries and compare the actual rows with the rows you return, since a big gap means wasted work.
2. In an interview, lead with the short answer: offset fits admin tables where people jump to page 7, and cursor fits feeds, infinite scroll, and tables that change while people read them.

Which one does your current project use, and have you ever caught the duplicate-post bug in the wild?