Is this the same as getting different 'top 10' each time?

Same family. A LIMIT without a unique ORDER BY is already nondeterministic. Pagination adds a second problem: even a correct ORDER BY by date can skip or repeat rows if new rows are inserted between page 1 and page 2.

What's a unique ORDER BY?

A sort that cannot tie. created_at DESC, id DESC is unique if id is the primary key. created_at DESC alone is not — two signups in the same second can swap places between runs.

So should I never use OFFSET?

For a one-off 'show me the next 20' in chat, OFFSET is fine if the ORDER BY is unique and the table isn't being written to mid-conversation. For anything you re-run, or a table that keeps getting inserts, keyset pagination (WHERE (created_at, id) < (...)) is more stable.

Why does a new insert skip a row on page 2?

Page 1 was rows 1–50 in sort order. A new row inserts at position 3. What used to be row 50 is now row 51 — OFFSET 50 starts after it, so that row never appears on page 1 or page 2.

Does the 500-row cap make this worse?

It makes people paginate more often. If you're asking for 'all cancellations' and hitting the row cap, don't OFFSET through pages in chat — ask for a COUNT or a grouped breakdown instead.

OFFSET Pagination Skips Rows | Unstable ORDER BY

Q: Why does a new insert skip a row on page 2?

Page 1 was rows 1–50 in sort order. A new row inserts at position 3. What used to be row 50 is now row 51 — OFFSET 50 starts after it, so that row never appears on page 1 or page 2.

Q: Does the 500-row cap make this worse?

It makes people paginate more often. If you're asking for 'all cancellations' and hitting the row cap, don't OFFSET through pages in chat — ask for a COUNT or a grouped breakdown instead.

You ask for the 50 most recent signups. Then: "next 50." A name from page 1 shows up again. Or someone you know signed up yesterday is on neither page. The AI didn't invent rows. OFFSET is not a bookmark.

Two different ways the window slips

Ties. ORDER BY created_at DESC LIMIT 50 OFFSET 50 does not pin down rows that share a timestamp. Bulk imports, seed data, and "created at the same second" all produce ties. Between two runs, the database is free to break those ties differently — the same reason the same question can return a different top 10. Page 1 and page 2 can overlap or leave a gap even if nobody wrote a new row.

Inserts between pages. Even a unique sort (created_at DESC, id DESC) is a snapshot of this moment. Page 1 ran. Three new signups arrived. The old row 50 is now row 53. OFFSET 50 starts at the new 51st row and the old 50th never appears on either page. Deletes do the reverse: a row can show up on two consecutive pages.

Neither of those throws an error. Both look like "the AI is being inconsistent."

What to ask for instead

For a chat answer, say the sort keys out loud and keep the window small:

50 most recent signups, newest first.
Order by created_at DESC, then id DESC so ties don't shuffle.
Show id and created_at so I can ask for the next page from the last row.

Then page 2 is not OFFSET 50. It's keyset pagination — "everything older than the last row I already saw":

SELECT id, email, created_at
FROM signups
WHERE (created_at, id) < ('2026-08-28 14:02:11', 501671)
ORDER BY created_at DESC, id DESC
LIMIT 50

That window doesn't slide when new rows arrive at the top. It continues from a specific row you already have.

If you didn't actually need pages — you needed a total or a breakdown — skip pagination entirely. A 500-row dump isn't the answer anyway; COUNT and GROUP BY usually are.

The prompt that heads this off

If I ask for "next page," don't use OFFSET unless the ORDER BY includes
a unique column (usually id) and I explicitly want offset. Prefer
keyset pagination from the last (created_at, id) I already have.
Say so if new inserts could have shifted an OFFSET window.

The short version

LIMIT 50 OFFSET 50 means "skip 50 rows in whatever order you pick right now." It is not "the next 50 after the ones you already showed me." If page 2 looks haunted, check the ORDER BY for a unique tiebreaker, then stop using OFFSET across a table that's still being written to.

Read-only MCP gateway: mcpserver.design.

Related reading: