Pagination That Survives Real Data

Pagination looks like a solved problem. LIMIT and OFFSET, page numbers, done. It works perfectly in development with fifty rows, ships, and then a year later someone notices page 5000 takes eight seconds and occasionally shows the same post twice. Both of those are the same root cause, and both go away with a different approach that's only slightly more code.

How offset pagination breaks

The familiar version:

SELECT * FROM posts ORDER BY created_at DESC OFFSET 100000 LIMIT 20;

It has two problems, and they show up at different times.

It gets slow as you go deeper. OFFSET 100000 does not skip to row 100,000 cheaply. The database has to produce and then throw away the first 100,000 rows to reach yours. The cost grows with the offset, so early pages are fast and deep pages crawl. The work is proportional to how far in you are, not how many rows you asked for.

It shows duplicates and skips when data changes. Offset counts positions in a list that's shifting underneath you. If someone inserts a new post while you're paging, every later row shifts down by one, so the first row of your next page is one you already saw. Delete a row and you skip one. On a busy feed this is constant.

Keyset (cursor) pagination

Instead of counting positions, you remember the value where you stopped and ask for rows after it. "Give me 20 posts created before this timestamp" rather than "skip 100,000 rows."

SELECT * FROM posts
WHERE (created_at, id) < ('2026-05-26T10:00:00Z', 8842)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This fixes both problems at once. It's fast at any depth, because with an index on the sort key the database jumps straight to your position and reads 20 rows; the cost is the same whether you're on page 1 or page 50,000. And it's stable under inserts and deletes, because the cursor is a value, not a position. A row added before your cursor doesn't shift anything you'll see next; you simply continue from the value you remembered.

Here it is end to end: paging through a list with a cursor, surviving an insert between pages.

Building a stable cursor

The one subtlety: the sort key must be unique, or pagination breaks at ties. If you sort by created_at alone and three posts share a timestamp, a cursor at that timestamp can't tell which of the three you already saw. The fix is to make the sort key unique by appending a tiebreaker, almost always the primary key: sort by (created_at, id) and put both in the cursor.

What to return

A keyset-paginated response carries the rows plus how to get the next page:

{
  "data": [ /* 20 rows */ ],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIuLi4iLCJpZCI6ODg0Mn0",
  "hasMore": true
}

hasMore is cheap to compute: ask for LIMIT 21, and if you get 21 rows, there's a next page (return the first 20 and set hasMore: true). No count query needed. When there's no next page, nextCursor is null and the client stops.

The total-count problem

Clients often want "showing 20 of 4,328." That total is surprisingly expensive: an exact COUNT(*) with a filter can be as costly as the query itself, because the database must examine every matching row. Options, cheapest to most exact:

The rule: don't return a total count unless the client genuinely needs an exact one, because counting is often slower than fetching the page you're showing.