Keyset Instead of Offset: Cursor Pagination, Unique Sort Keys and Rows Lost at Page Boundaries

Almost every list endpoint starts life as LIMIT 20 OFFSET 40. It works on a small table and it works in tests. The trouble shows up in two places: deep pages get slower as the table grows, and if rows are inserted while someone is paging, they see the same row twice or never see another one at all. This post explains why offset pagination behaves this way, how to build keyset (cursor) pagination correctly, and where it is most often built wrong.

Why offset gets slow

OFFSET 100000 tells the database to skip the first hundred thousand rows, but skipping is not free. The database still has to produce those rows in order, count them and throw them away. Even with an index, the first hundred thousand index entries are walked. Query time grows linearly with the page number. Humans rarely go that deep, but crawlers, export scripts and integrations written as "fetch everything" do exactly that, so deep page slowness usually shows up as load from automation rather than from people.

The second problem is consistency. If two rows are added to the top of the list after a client fetched page one, the first two rows of page two are the last two rows of page one. Deletions do the opposite and a row silently disappears. In a UI this is a minor annoyance. In a sync job it is data loss.

What keyset pagination does

Instead of carrying "which page am I on", keyset pagination carries "which row did I see last". The next page is the rows that sort after that row's key. The database satisfies this by seeking directly into the index, without walking the rows before it. The page after the millionth row is as fast as the first page, and inserts or deletes elsewhere do not shift the next page.

The ground rule: the sort key must be unique. If you sort only by created_at, one of two rows sharing a timestamp will fall through the crack at a page boundary. Add a unique column, usually the primary key, as a tiebreaker.

Doing it right in PostgreSQL

Take a newest-first list. Start with an index that matches the sort exactly:

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

First page:

SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 21;

Next page, using the values from the last row of the previous page:

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;

(created_at, id) < ($1, $2) is a row comparison: created_at is compared first, and id only breaks ties. PostgreSQL can use it as a range scan on the composite index. The expanded form created_at < $1 OR (created_at = $1 AND id < $2) means the same thing logically, but the planner does not always turn it into the same efficient plan. Prefer the row comparison in PostgreSQL and confirm with EXPLAIN that you get an index scan.

Asking for one more row than the page size (LIMIT 21 for a page of 20) answers "is there a next page" without a second query. If row 21 comes back, there is a next page; you just leave it out of the response.

Handing the cursor to the client

The cursor contains the sort values of the last row, but do not expose them as raw query parameters. Pack them into a structure and return an opaque base64 string:

{
  "items": [ ... ],
  "next_cursor": "eyJjIjoiMjAyNi0wMS0xNVQxMDozMDowMC4xMjM0NTZaIiwiaSI6OTgxMn0"
}

There are two reasons to keep it opaque. You can change the sort key later without breaking clients, and clients are discouraged from hand-crafting cursors that produce odd queries. Since cursor values go into the query as bound parameters there is no SQL injection risk, but still validate the decoded types; a malformed cursor should return 400, not 500.

Put the sort direction and the active filters inside the cursor too. A cursor obtained under one filter and replayed under another produces nonsense. If the filters in the cursor do not match the request, reject it.

Pitfalls

Timestamp precision. PostgreSQL timestamptz stores microseconds. If the cursor value passes through a JavaScript Date, it gets truncated to milliseconds. The next query then asks for created_at < 10:30:00.123, which either returns the last row again (it was really 10:30:00.123456) or skips rows within the same millisecond. Users see duplicates at page boundaries and the cause stays hidden for a long time. Carry the timestamp in the cursor as the full-precision text the database returned, or sort on a monotonically increasing integer id instead.

Nullable sort columns. In a row comparison, if either side is NULL the result is NULL and the row is filtered out. If a sort column allows NULL, those rows either never appear or appear only on the first page. Make sort columns NOT NULL; if you cannot, map them with COALESCE to a sentinel value in the query and build the index on the same expression.

Mixed sort directions. For ORDER BY priority ASC, created_at DESC, a row comparison does not work because a single < cannot express two directions. You have to write the expanded OR form: priority > $1 OR (priority = $1 AND created_at < $2) OR .... That gets complicated fast and is hard on the planner. Where you can, limit the product's sort options to single-direction combinations.

Mutable sort keys. In a list sorted by updated_at, a row updated while the user is paging jumps to the top. The user sees it twice or misses it. That is not a flaw of keyset pagination; it is what sorting on a changing key means. For sync jobs where loss is unacceptable, read changes through a separate, monotonically increasing version column.

Total counts. Showing "48,213 results, page 7 of 2411" means a COUNT(*) on every request, which on a large table costs more than the page itself. When moving to cursors, have the conversation about dropping exact totals from the UI. If you need something, show an estimate; for an unfiltered table in PostgreSQL, pg_class.reltuples is a decent one.

Going backwards. For the previous page, flip both the sort order and the comparison, query with the values of the first row, then reverse the result in the application. Forget the last step and the previous page renders upside down.

When to keep offset

Not every list needs cursors. Admin tables that never grow past a few thousand rows, interfaces where users really do say "go to page 14", and tables where users pick arbitrary sort columns are served better by offset. Keyset pagination cannot jump to an arbitrary page; if the product requires that, cursors are the wrong tool.

Make keyset the default for public API list endpoints, infinite scroll feeds, exports and sync jobs. Once such an endpoint ships with offset, changing it later is a breaking contract change for every client, so the first version is the cheapest moment to decide.