Offset paging and keyset cursors

An agent that reads more rows than a single result can hold reads them across several calls, and the tool has to describe where the next call should resume. The two ways to do that are an offset, a count of rows to discard before returning any, and a keyset cursor, a position expressed as the sort-key values of the last row that was actually read. They behave identically on a table nobody is writing to, which is why the difference between them is usually discovered in production rather than in a test.

What separates an agent's paging from a script's is the gap between pages. Each page goes back to the model, gets read, and comes back as another tool call three to five seconds later, so a seven-page traversal spends half a minute against a live table. On an incidents table that is exactly the window in which on-call engineers comment on incidents, change severities and mark things resolved — and every one of those writes bumps updated_at, which is the column the tool is sorting on.

Three to five seconds is not overhead that better engineering removes, and knowing where it comes from is what makes the window predictable rather than anecdotal. The next call cannot exist until the previous page has been read, because the offset or cursor it carries is generated from that page. Producing it means reading the whole conversation again and then emitting tokens one at a time, so what sits between two pages is a full model generation rather than a network hop. Each page is read and summarized into whatever the agent is accumulating, and only then is there a next call. Multiply that by the number of pages and the traversal occupies a window of tens of seconds — long enough for engineers commenting on incidents, changing severities and marking things resolved to land ordinary work in the middle of a read that assumes nothing is moving.

What an offset counts is positions in a list that is being reshuffled#

The query almost everybody writes first looks like this:

SELECT id, service, severity, title, opened_at, updated_at
FROM incidents
WHERE opened_at >= :week_start
ORDER BY updated_at DESC
LIMIT 50 OFFSET :offset;

OFFSET 50 does not mean "resume after the last row I saw". It means "run this query fresh, discard the first 50 rows of whatever it produces now, and give me the next 50". The result set is recomputed from scratch at each call, so the offset is only a valid resume point if the ordering has not changed since the previous call.

Consider incident 8612, sitting at position 214 in the ordering when the agent starts, which is on page five. During the pause after page one, an engineer adds a comment to it. Its updated_at becomes the most recent in the table, so under updated_at DESC it moves to position 0 — into the region the agent has already read and will never read again. When page five arrives, the rows that were at positions 200 through 249 have all shifted down by one, so the agent gets what used to be positions 199 through 248, and 8612 is not among them. The same shift is what produces a duplicate: the row that had been the last entry of page four is now at position 200, so it is returned again as the first entry of page five.

The positions in that sequence are worth working out rather than accepting, because they are the whole mechanism. Pages are 50 rows, so page one is OFFSET 0 and covers positions 0 through 49; counting on in fifties, page four ends at position 199 and page five is OFFSET 200, covering 200 through 249. Incident 8612 at position 214 is inside page five's range, which is why the agent expects to meet it there. When the comment lands, 8612 jumps to position 0, and every row that had been above it is pushed down exactly one place — there is now one more row in front of each of them than there was. So the row that used to sit at 199 sits at 200, and the fifty positions the query returns for page five hold what used to be 199 through 248.

Two consequences fall out of that single shift and neither needs any further argument. Old position 199 was the last row of page four, and it is now the first row of page five, so the agent reads it twice. Old position 214 has left the region page five covers, and it moved upward into pages the agent has already passed, so the agent never reads it at all. Each write that lifts an unread row into the already-read region costs one row and gains one repeat, the two counts match, the page totals look plausible, and the only visible symptom is the duplicate. The miss is invisible by construction.

An agent reading a single page never sees any of this, which is why the bug survives testing. It requires a multi-page read, a gap between pages long enough for writes to land, and a sort key that writes actually modify. All three are ordinary conditions for an agent and unusual conditions for a test suite.

The belief that keeps the bug alive afterwards is a reasonable one: that a traversal whose totals reconcile is a traversal that saw everything. It is tempting because every quantity available to check does reconcile. The offsets advance by exactly the page size, each call returns a full page, and the rows received sum to the number the tool reports. What that misses is that all three quantities are computed from the pages themselves, so they can only ever be consistent with one another. None of them confirms that the pages together cover the table, because covering the table is a claim about rows the traversal never saw, and no count taken from what you received can speak to what you did not. Every self-consistent number in such a trace is equally consistent with nine incidents having gone missing, which is how nine go missing with nobody noticing.

A cursor resumes from a position in the data#

The alternative is to describe the resume point in terms of the last row you actually read, so that the database can locate it again regardless of what else has moved:

SELECT id, service, severity, title, opened_at, updated_at
FROM incidents
WHERE opened_at >= :week_start
  AND (updated_at, id) < (:last_updated_at, :last_id)
ORDER BY updated_at DESC, id DESC
LIMIT 50;

Three things about that predicate have to be right or it fails in subtler ways than the offset version did. The tuple comparison must include a tiebreaker column, because updated_at is not unique — a bulk resolve sets forty rows to the same microsecond, and a cursor holding only a timestamp either skips all forty or returns them forever. The tiebreaker has to be unique and stable, so a primary key rather than, say, a service name. The ORDER BY must match the tuple exactly, including direction on both columns, or the comparison is scanning a different ordering than the one it is expressed in.

Both halves of that either-or come from one choice a timestamp-only cursor cannot avoid, which is whether its comparison is strict. Write updated_at < :last_updated_at and the next page starts strictly below the timestamp it was handed, so all forty rows sharing that microsecond are treated alike: the one that was actually read and the thirty-nine that were not are indistinguishable by timestamp, and every one of them is excluded. Write updated_at <= :last_updated_at instead and the page starts at that timestamp again, so all forty come back including the ones just read, and the cursor issued from the last of them carries the same value once more — the traversal never advances. Putting id on both sides is what dissolves the choice, because inside one microsecond the ids are still totally ordered, so "strictly after the row I read" becomes expressible where "strictly after the moment I read" was not.

The requirement that the ORDER BY match the tuple, direction included, is the same point approached from the other end. The predicate (updated_at, id) < (:last_updated_at, :last_id) means an earlier timestamp, or the same timestamp and a smaller id. That is a description of a position, and a position exists only relative to an ordering. Under updated_at DESC, id DESC the predicate names exactly the rows the sort places after the last one read, which is what the traversal needs. Change the second column of the sort to id ASC and the sort now places some of those rows before the last one read, while the predicate still excludes them — a skip with no write involved, no duplicate to notice it by, and nothing in the query plan that looks wrong.

The cursor the model receives is an encoding of that resume point, and it should carry more than the position:

import base64, json, hmac, hashlib

def encode_cursor(last_row, order_by: str, filters: dict) -> str:
    body = json.dumps({
        "u": last_row.updated_at.isoformat(),
        "i": last_row.id,
        "o": order_by,
        "f": filters,
    }, separators=(",", ":")).encode()
    sig = hmac.new(CURSOR_KEY, body, hashlib.sha256).digest()[:8]
    return base64.urlsafe_b64encode(body + sig).decode()

The ordering and the filters go inside because a cursor is only meaningful against the query that produced it. A model that receives a cursor from severity DESC and passes it to a call it has re-issued with opened_at DESC is asking for a position in an ordering that never existed, and without the ordering recorded, the tool will happily answer with garbage; with it recorded, the tool can reject the call and say why. The signature is there because the cursor is a string the model can modify, and a filter smuggled inside an unauthenticated cursor is an access-control decision being taken by a language model. In practice this comes to about 70 tokens of opaque base64 per page, which is worth it and is also a reason not to return a cursor on results that were not capped.

The offset version has a second cost that shows up under load. OFFSET 5000 requires the database to produce and discard five thousand rows before it can return anything, so page latency grows linearly with depth: in this table, page one returns in 3 ms and page one hundred in 180 ms, on the same index. Page one hundred is ninety-nine pages of fifty into the traversal, so the database walks four thousand nine hundred and fifty index entries it will immediately throw away before it starts collecting the fifty it was asked for. The discarded work is proportional to the offset and the offset is proportional to the page number, which is what makes the curve a straight line rather than a cliff — no page is where it breaks, each is a little dearer than the last, and the cost is easy to miss in a test that reads two pages. The keyset predicate does not walk any of them: (updated_at, id) < (…) is a value the index can be positioned at directly, so the database seeks to that point and reads fifty entries forward. The seek costs the same wherever the point sits, which is why page one hundred costs what page one costs.

A stable position is not a snapshot#

The strongest thing that can be said for the cursor is that each page resumes exactly where the previous one ended in the ordering. That removes duplicates, and it removes skips caused by rows shifting relative to a counted position. It does not remove the fact that the agent is reading a table other processes are writing to, and it is worth being exact about what remains.

An incident the agent read on page one and that is updated afterwards is never revisited, so the agent's copy is stale and the agent has no way to know. An incident inserted or updated into a position the cursor has already passed — under updated_at DESC, that is any row updated at all during the scan — is never seen. The seven pages therefore describe a set of rows that never existed simultaneously in the table; they are seven consistent slices of seven different moments, stitched together.

The two rows that give a true snapshot both cost something an agent is bad at paying. Holding a repeatable-read transaction open across an agent's pages — repeatable read being the isolation level under which every statement in the transaction sees the database exactly as it stood when the transaction began — means holding it for the model's thinking time, and thirty seconds is common while several minutes is not unusual. That pins vacuum, the background process that reclaims dead row versions, and bloats the table for everyone else. A temporal read at a fixed timestamp is the clean answer where the store supports it, and most do not, or support it only within a retention window measured in minutes. Keying on an immutable column is the cheap approximation: ordering by (created_at, id) gives you a stable enumeration of everything that existed when you started, at the price of never seeing updates, which is right for an audit export and wrong for an on-call digest.

For most agents the honest position is that a multi-page read is approximate and that this is acceptable, because the question being asked — summarize the week, find the unpaid invoices — tolerates a few seconds of skew. What is not acceptable is an agent asserting an exact count from a multi-page traversal, and the way to prevent that is to compute the count with a COUNT(*) in the same request rather than by adding up page lengths.

The ordering is semantics; the cursor is mechanism#

The incompleteness contract of the previous chapter put order_by in the response on the grounds that a model holding a sample needs to know which sample it holds. Paging sharpens the reason. An agent asked for the three most severe incidents this week, reading a result ordered by severity DESC, opened_at DESC, can stop after the first page and be right. The same agent reading the same rows ordered by updated_at DESC and stopping after the first page has answered a different question and will not know it. The ordering is the difference between a sample that supports early stopping and one that does not, which makes it part of the tool's meaning rather than part of its plumbing.

The reason early stopping is safe under one ordering and not the other comes out of what a sort key promises. Sorting by severity puts every row the question is about at the front by construction, so a first page of fifty contains the three most severe incidents whatever the other rows turn out to be, and the pages after it cannot contain a more severe one — that is what the ordering asserts. Sorting by updated_at promises only recency, which has no relation to severity at all, so the most severe incident of the week may sit anywhere in the traversal and the first page tells you nothing about where. Same rows, same page size, same tool: what changed is whether the question's answer is guaranteed to be at the front, and only the ordering carries that guarantee.

The cursor's contents are the opposite. The model gets an opaque string, passes it back unchanged, and should be told in the tool description that it is opaque, because a model that can see {"u":"2026-03-13T16:22:00Z","i":8821} will eventually try to construct one. Exposing the ordering while hiding the position is the split that holds: the model reasons about what it is looking at, and the tool retains control over where it resumes.

An offset is a promise about a list that no longer exists by the time the next call arrives, and on a table under active write it is wrong by one row for every write that lands behind the agent. A cursor keyed on the sort column and a unique tiebreaker, signed and carrying the ordering it was issued for, replaces that promise with a position the database can find again — at flat cost however deep the traversal goes, and with the residual honesty that stale rows and rows updated behind you are still invisible.

Paging solves the case where the model genuinely needs the rows and can afford to walk them. It is a poor answer when the rows are not what the model needs at all — when the question is about a distribution, a total, or a handful of outliers buried in forty thousand records, and walking the pages would cost more tokens than the answer is worth.