Keyset pagination: why OFFSET gets slow and what to use instead

Keyset pagination fetches the next page by remembering where the last page ended, for example "rows older than this timestamp and id", instead of telling the database how many rows to skip. OFFSET pagination gets slower with every page because the database still reads and throws away every skipped row. Keyset pagination costs about the same on page 1 and page 50,000, and it doesn't skip or repeat rows when new data arrives.

Most APIs start with LIMIT ... OFFSET ... because it is easy. It works until tables grow and someone scrolls deep, exports everything, or runs a backfill job that walks the whole table page by page.

Why does OFFSET pagination get slow?

OFFSET n means "produce the first n rows of this ordered result, then discard them." A database can't jump to row 500,000 of a sorted result without counting its way there. Even with an index on the sort column, it walks 500,000 index entries, and if the query needs columns that aren't in the index, it may fetch the table row for each one as well.

So the cost of a page is proportional to how deep it is, not how big it is. Twenty rows from page 1 and twenty rows from page 25,000 return the same amount of data, but the second request does tens of thousands of times more work.

A quick check in SQLite on a 1,000,000 row table, with an index on (created_at DESC, id DESC) and 20 rows per page:

import sqlite3, random, time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE posts (id INTEGER PRIMARY KEY, created_at INTEGER NOT NULL, title TEXT)")
rnd = random.Random(1)
db.executemany("INSERT INTO posts (created_at, title) VALUES (?, ?)",
               [(rnd.randint(0, 10**9), f"post {i}") for i in range(1_000_000)])
db.execute("CREATE INDEX posts_created_id ON posts (created_at DESC, id DESC)")

offset_sql = """SELECT id, created_at, title FROM posts
                ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET ?"""
keyset_sql = """SELECT id, created_at, title FROM posts
                WHERE (created_at, id) < (?, ?)
                ORDER BY created_at DESC, id DESC LIMIT 20"""

Timing both queries at increasing depths gave roughly this:

The absolute numbers are small because this is an in-memory database on one machine. The shape is what matters: OFFSET grows linearly with depth, keyset stays flat. On a real server with disk reads, larger rows, joins and many concurrent users, a linear cost per request is what turns a deep crawl into a slow query log full of the same statement.

What is keyset pagination?

Keyset pagination, also called cursor pagination or the seek method, replaces "skip n rows" with a condition on the sort key. A cursor is the value of the sort key for the last row the client received. The next request asks for rows that come after that value in sort order.

-- first page
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: pass in created_at and id of the last row you got
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on the same columns in the same order, the database seeks straight to the cursor position and reads 20 entries. In SQLite, EXPLAIN QUERY PLAN shows this as a SEARCH on the index, while the OFFSET version shows a SCAN. PostgreSQL similarly turns the row comparison into an index range condition when a matching b-tree index exists.

Why do you need a tiebreaker column?

The sort key must be unique, or pages can lose rows. If you sort only by created_at and 30 posts share the same timestamp, a page boundary can fall in the middle of them. WHERE created_at < :last then skips the rest of that group, and <= repeats the ones already sent.

Adding a unique column, usually the primary key, fixes it. (created_at, id) is unique even when timestamps collide, so every row has exactly one position.

The (a, b) < (x, y) syntax is a row value comparison: it compares the first columns, and only looks at the second when the first are equal. SQLite and PostgreSQL support it. If your database doesn't, or doesn't use an index for it, write the expanded form:

WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)

The expanded form also handles mixed directions, such as ORDER BY price ASC, id DESC, where a single row comparison doesn't apply.

How does keyset pagination handle new and deleted rows?

OFFSET pages are defined by position, so any insert or delete above the current position shifts everything. Picture a feed sorted newest first with 3 items per page. The client loads page 1 and gets posts 10, 9 and 8. A new post 11 is created. Page 2 with OFFSET 3 now returns 8, 7 and 6, so post 8 shows up twice. A delete does the opposite and silently skips a row.

Running exactly that sequence in SQLite:

offset  page 1: [10, 9, 8]  page 2: [8, 7, 6]
keyset  page 1: [10, 9, 8]  page 2: [7, 6, 5]

The keyset version asked for rows older than post 8, which is still a correct question after the insert. This matters most for infinite scroll, sync jobs and data exports, where a duplicated or missing row is a real bug rather than a cosmetic one.

When should you use OFFSET instead?

Keyset pagination has trade-offs, and they are worth stating plainly.

• No jumping to page N. You can only move to the next or previous page from a known cursor. A UI with numbered page links needs OFFSET, or a different design.
• Total counts are a separate problem. "Page 3 of 812" needs a COUNT(*), which is a full scan on large tables either way. Many feeds drop it or show an estimate.
• The cursor is tied to the sort. Change the sort order or filters and old cursors become meaningless.
• Indexes must match. The index needs the same columns in the same order as ORDER BY, plus any equality filters first, such as (tenant_id, created_at, id).

A rule of thumb:

For APIs, return the cursor as an opaque string, such as base64-encoded JSON of the last row's sort values, rather than raw column values. Clients pass it back unchanged, and you can change its contents later without breaking them.

const encode = (row) =>
  Buffer.from(JSON.stringify([row.created_at, row.id])).toString("base64url");
const decode = (cursor) =>
  JSON.parse(Buffer.from(cursor, "base64url").toString());

// response shape
// { items: [...], next_cursor: items.length === limit ? encode(items.at(-1)) : null }

Validate decoded cursors like any other input, since clients can send anything.

Key takeaways

• OFFSET cost grows with page depth because skipped rows are still read.
• Keyset pagination seeks to the last seen sort key, so every page costs about the same.
• Always add a unique tiebreaker, usually the primary key, to the sort.
• Back the query with an index whose columns match the ORDER BY exactly.
• Use keyset for feeds, APIs and batch jobs; keep OFFSET for small tables with numbered pages.

FAQ

Is keyset pagination the same as cursor pagination?

Mostly, yes. "Cursor pagination" usually describes the API shape, an opaque token for the next page, and keyset pagination is the most common way to implement it in SQL.

Can I go backwards with keyset pagination?

Yes. Flip the comparison and the sort direction to fetch the rows before the first row on the current page, then reverse the result before returning it.

Does keyset pagination work with filters?

Yes, as long as the filters stay the same between pages. Put equality filters at the start of the index, then the sort columns, so the database can still seek directly to the cursor.