MCP server for Postgres: make pagination a stable data contract

An AI agent rarely consumes a large PostgreSQL result in one tool call.

It asks for a page, reasons about it, calls another tool, and returns for more. Meanwhile, rows are inserted, updated, or deleted. A pagination method that looked harmless in a demo can now duplicate records, skip records, or mix scopes across a long conversation.

For an MCP server connected to Postgres, pagination is part of the data contract.

Why OFFSET is fragile

LIMIT 100 OFFSET 200 describes a position in a changing result set, not a stable boundary. If a row is inserted before the next page, an earlier row may appear again. If a row is deleted, another row may be skipped. Large offsets also force PostgreSQL to walk past increasingly more rows.

Offset pagination can be acceptable for small, static administration lists. It is a poor default for agent workflows that span multiple calls.

Choose a deterministic order

Keyset pagination continues after the last ordered value rather than counting from the start. The order must be total and deterministic. A timestamp alone is usually insufficient because many rows can share it.

ORDER BY created_at DESC, id DESC
LIMIT 101

The extra row tells the server whether another page exists. The next request uses the final returned pair:

WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 101

Match the comparison direction to the sort direction, and back it with an index such as (created_at DESC, id DESC).

Return an opaque cursor

Do not make the model assemble SQL predicates. The tool should return an opaque cursor that the client can pass back unchanged.

The cursor can encode:

• the last ordering values;
• sort direction and query version;
• tenant, environment, and approved filter hash;
• page-size ceiling;
• snapshot or freshness rule;
• issued-at and expiry time.

Sign or authenticate the cursor. A cursor is server state in portable form; it must not become a way to alter tenant, filter, or sort scope.

Bind the cursor to the original scope

A follow-up call should not accept a cursor from one tenant with a new tenant parameter, or a cursor from an approved view with a broader filter. Recompute the normalized scope hash and reject mismatches.

Identity and tenant scope should come from trusted authenticated context, not from the conversation. See row-level security for AI database agents.

Define the consistency promise

Keyset pagination prevents position drift, but it does not automatically produce a transaction-wide snapshot across separate tool calls.

Choose and document one promise:

• live continuation: each page reflects current committed data after the keyset boundary;
• bounded snapshot: every page is constrained by a captured high-water mark;
• materialized result: the server stores a short-lived result set and pages through it.

Live continuation is often enough for event feeds. Financial or audit workflows may need a high-water mark, an exported snapshot, or a repeatable job instead of a long-lived database transaction.

Put evidence in every page

A page should return records plus metadata: normalized filters, ordering, page size, returned count, has_more, snapshot rule, source freshness, truncation, query version, and trace ID.

That makes partial results visible to the model and reviewer. It also prevents the final answer from presenting page one as a complete dataset.

For the wider evidence model, see query provenance for AI database agents.

Handle retries explicitly

Repeating a read request with the same cursor should return the same logical page under the documented consistency promise. Log retries separately from logical operations, and never advance server-side state merely because a response was generated.

If a cursor expires, return a structured error with a safe restart instruction. Do not silently restart at page one and let the agent merge incompatible sequences.

A concrete tool contract

list_failed_jobs(
  start_time,
  end_time,
  status,
  page_size,
  cursor?
)

The server derives tenant and environment from identity, caps the time range and page size, queries an approved view, and returns:

{
  "items": [...],
  "next_cursor": "...",
  "has_more": true,
  "snapshot_at": "2026-07-29T01:00:00Z",
  "filters": {...},
  "trace_id": "..."
}

The model receives a narrow operation, not a generic instruction to invent pagination SQL.

Test the contract under change

1. Insert rows before and after the current key while paging.
2. Delete the boundary row and request the next page.
3. Create many rows with identical timestamps.
4. Replay the same cursor after a timeout.
5. Change tenant, filters, sort order, or page size with an old cursor.
6. Expire and tamper with the cursor.
7. Verify that partial and truncated results remain explicit.

Also load-test the supporting index and query plan. The query-plan regression budget is a useful companion control.

Where Conexor fits

Conexor provides MCP infrastructure for connecting AI clients to databases and APIs through governed tools. Stable pagination keeps long-running AI workflows bounded, reviewable, and consistent with their original identity and scope.

Explore MCP infrastructure for PostgreSQL