Offset vs Cursor Pagination: Why LIMIT/OFFSET Breaks at Scale (and What to Use Instead)

📺 Prefer to watch? 90-second YouTube Short · 💬 Telegram

Originally published on software-engineer-blog.com.

Everyone writes this on day one:

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

Page three. Twenty rows. It works. Every ORM in existence will generate it for you from ?page=3, and for a long time nothing goes wrong.

That is exactly the problem. Page two is the one page that hides both of offset pagination's defects. The table is small, the data is sitting still, and you meet neither failure until you are in production with real traffic and a real table.

---

What offset pagination actually does

An offset is a count of positions. OFFSET 40 means: produce the ordered result set, walk past the first 40 rows, then start handing rows back.

And it genuinely buys you things — this is not a straw man:

• Page numbers for free. Page 47 is just OFFSET 920. You can render 1 2 3 … 47 … 312.
• Jump-to-page. The user can scrub anywhere in the list instantly.
• A total count. COUNT(*) tells you there are 6,240 results, so you can show "312 pages".
• It is trivial. One line, no state, no encoding, no client cooperation.

On a small, mostly static admin table — a settings list, a 900-row product catalogue nobody is writing to — offset pagination is the right call. Don't let anyone talk you out of it there.

Then the table grows.

---

Defect 1: deep pages get slower, and the cost is linear in the offset

OFFSET 100000 does not jump to row 100,001. There is no jumping. The database still has to produce all 100,000 rows — read them through the index, sort them if the sort isn't index-ordered — and then throw them away to hand you 20.

-- page 1: cheap
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- page 5,000: the database builds 100,000 rows and discards 99,980 of them
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

The work is proportional to the offset, so page 5,000 costs roughly 5,000× page 1. You can watch it in the plan — Postgres will tell you outright how many rows it threw away:

Limit  (cost=... rows=20)
  ->  Index Scan Backward using orders_created_at_idx on orders
        (actual time=0.02..118.4 rows=100020 loops=1)

rows=100020 to return twenty. That is the whole story.

This is why the "our API gets slower the deeper you page" ticket is always about the last pages, and why it never reproduces on the developer's laptop, where the table has 200 rows.

Defect 2: an offset is a position in a list that keeps moving

This one is worse, because it is silent.

An offset only means something if the list stands still between requests. Real lists don't. Take a feed sorted newest-first while a user reads:

• The user loads page 1 — rows 1–20.
• Someone inserts a new row. Everything shifts down by one.
• The user asks for page 2 — OFFSET 20. But what used to be row 20 is now row 21, so they get it again. A duplicate.

And the mirror image:

• The user loads page 1.
• A row on page 1 is deleted. Everything shifts up by one.
• The user asks for page 2 — the row that was at position 21 has slid to position 20, which they already passed. It is gone, and they never saw it.

No error. No log line. No exception to catch. Just a quietly wrong list — a duplicate here, a missing record there — and a bug report that reads "sometimes an order doesn't show up" and never reproduces.

---

Cursor (keyset) pagination: point at a row, don't count positions

The fix is to change the question. Instead of "skip 100,000 positions," say "give me the 20 rows that come after **this specific row."

-- page 1
SELECT id, created_at, total FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: anchored to the last row of the previous page
SELECT id, created_at, total FROM orders
WHERE (created_at, id) < ('2026-08-01 09:14:22', 8412)
ORDER BY created_at DESC, id DESC
LIMIT 20;

That WHERE is a row-value comparison on an indexed, unique, sortable key. It is an ordinary index seek: the database descends the B-tree straight to ('2026-08-01 09:14:22', 8412) and reads 20 entries forward. It never materialises the rows before it, so the cost is constant at any depth — page 5,000 costs the same as page 1.

And because the anchor is a real record rather than a position, inserts and deletes elsewhere in the table cannot shift it. New rows land above the cursor and simply aren't in this page's range. Nothing duplicates. Nothing vanishes.

The "cursor" you hand the client is usually just those key values, encoded so nobody treats them as an API:

import base64, json

def encode_cursor(row) -> str:
    payload = {"created_at": row.created_at.isoformat(), "id": row.id}
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()

def decode_cursor(cursor: str) -> tuple:
    payload = json.loads(base64.urlsafe_b64decode(cursor))
    return payload["created_at"], payload["id"]

# the query, parameterised — never string-format a cursor into SQL
SQL = """
    SELECT id, created_at, total FROM orders
    WHERE (created_at, id) < (%s, %s)
    ORDER BY created_at DESC, id DESC
    LIMIT %s
"""

The index that makes this work has to match the sort exactly:

CREATE INDEX orders_created_at_id_desc_idx
    ON orders (created_at DESC, id DESC);

---

The honest cost of a cursor

Cursors are not strictly better. They buy stability by giving up random access, and you should know what you are trading:

• No page numbers, no jump-to-page. Only next and back. There is no cursor for "page 47" because page 47 isn't a thing any more.
• No total count — not for free, anyway. "6,240 results" needs a separate COUNT(*), which is the expensive scan you were trying to avoid.
• It needs a strict total order. This is the one that bites. If you paginate on created_at alone and 200 rows share the exact same timestamp, WHERE created_at < '…' skips all of the ties at once — rows go missing at every page boundary. That is why the key is (created_at, id): the unique id tie-breaks so the order is total and every row has exactly one place in it.
• Changing the sort invalidates every cursor already issued. A cursor encodes a position in one particular ordering. Re-sort by total DESC and every outstanding cursor is meaningless — you must detect that and restart from page 1.
• It's more code. Encode, decode, validate, handle a cursor that points at a row someone has since deleted (row-value comparison handles this fine — the values still order correctly even if the row is gone).

Which is precisely why infinite-scroll feeds all work this way and admin tables mostly don't. A feed only ever needs "more", and it is written to constantly. An admin table needs "page 47 of 312" and nobody is inserting while you look.

---

Side by side

	Offset / LIMIT-OFFSET	Cursor / keyset
The question it asks	"Where am I in the list?"	"Which row was I on?"
Cost at depth	Linear in the offset — page 5,000 ≈ 5,000× page 1	Constant — an index seek, any depth
Under concurrent writes	Rows silently duplicate (insert) or vanish (delete)	Stable — the anchor is a real record
Page numbers / jump-to-page	Yes, free	No — next and back only
Total count	Yes (a COUNT(*) away)	Not without a separate expensive scan
Ordering requirement	Any ORDER BY	Must be a strict total order (tie-break on a unique column)
Changing the sort	Harmless	Invalidates every issued cursor
Implementation	One line, every ORM writes it	Encode/decode a cursor, index must match the sort
Natural fit	Small, static, human-browsed tables	Large, actively written feeds and APIs

---

The same problem, wearing an AI hat

If you are building on top of LLMs, you have this exact decision — it just doesn't look like a ?page= parameter.

Paging a corpus into an embedding pipeline. You ingest documents into a vector store by walking the source table in batches. Use LIMIT 1000 OFFSET n and you get both defects at once: the ingest gets slower every batch (batch 400 is re-producing 400,000 rows to discard them), and because documents are being written while you ingest, an insert makes you embed the same chunk twice and a delete makes you skip one entirely. You end up with a silently incomplete index and duplicate chunks that crowd out real results at retrieval time. Keyset on (updated_at, id) fixes both, and it doubles as your incremental-sync cursor: store the last key, resume from it tomorrow.

Paging tool results back to an agent. When a tool returns 4,000 rows and the context window fits 50, the agent pages. An offset here is worse than usual, because the whole point is that the agent is taking actions between pages — and some of those actions write to the very table it is reading. Offset guarantees it will re-read rows it already processed and skip rows it never saw. A cursor makes "continue from where I was" mean what it says.

Streaming retrieval and re-ranking. Retrieve top-k, re-rank, and if the answer isn't grounded well enough, fetch the next slice. That "next slice" is a cursor over a score-ordered result — and it needs the same tie-break discipline, because embedding similarity scores collide far more often than timestamps do. Two chunks with identical cosine scores and no unique tie-break means one of them never reaches the model.

The pattern generalises cleanly: the moment something is being written while it is being read, a position stops being a valid way to remember where you were. That is true of an orders table, a document corpus, and an agent's scratchpad alike.

---

The verdict

Reach for offset when the dataset is small and bounded, writes are rare or the list is effectively static, and a human genuinely needs page numbers, a total count, or the ability to jump. Admin tables, back-office reports, settings lists, a paginated archive. It is simpler, and simpler is a real feature.

Reach for a cursor when the table is large or growing, it is written to while it is read, or it is a public API where you cannot control how deep a client pages. Feeds, timelines, activity logs, exports, any GET /v1/…?limit= you expose to someone else. The API contract is next_cursor and nothing else, and you never have to have the "why is page 5,000 timing out" conversation.

The tell: ask one question — does the list grow while it is being read? If yes, an offset is a position in something that keeps moving, and it will be wrong sooner or later. A cursor is a place.

The axis was never speed. It is stability — the performance win is a bonus that comes along for the ride.

---

Your turn: you switch to keyset pagination ordered by created_at alone, and 200 rows share the exact same timestamp. What happens at that page boundary — and what is the one-column fix?

Watch the reel:
▶ Offset vs Cursor Pagination in 106 seconds