An AI agent rarely consumes a large PostgreSQL result in one call.
It reads a page, reasons, calls another tool, and comes back.
Meanwhile, rows change.
LIMIT/OFFSET describes a position in a changing list. Inserts can duplicate rows across pages. Deletes can skip them.
For MCP tools, use a deterministic keyset such as:
ORDER BY created_at DESC, id DESC
Then return an opaque cursor bound to:
- the last ordering values
- tenant and environment
- normalized filter hash
- sort and query version
- page-size ceiling
- snapshot rule
- expiry
The server should reject a cursor when any of that scope changes.
Also document consistency. Does the next page reflect live committed data, a captured high-water mark, or a materialized short-lived result?
Keyset pagination prevents position drift. It does not magically create a snapshot across separate tool calls.
Every page should carry has_more, returned count, filters, freshness or snapshot time, truncation state, and a trace ID. Otherwise the model may present page one as the complete answer.
Finally, retry the same cursor after a timeout. It should produce the same logical page under the contract—not silently advance or restart.
Full implementation guide: MCP server for Postgres: make pagination a stable data contract
Top comments (1)
This is the part I enjoy most about these threads: the same problem resurfaces whatever altitude you work at. A version-bound, scope-rejecting cursor is, at the read layer, the exact instinct I keep reaching for on the write side — bind the operation to a schema/policy version and refuse it the moment the ground shifts underneath. Different altitude, same contract-thinking.
Framing a pagination cursor as a security boundary rather than a convenience is spot on — and "otherwise the model may present page one as the complete answer" is painfully real with agents. Really like the approach; saved the full write-up.