MCP server for PostgreSQL: make result limits and truncation explicit
Every production MCP server for PostgreSQL needs result limits.
The dangerous part is not the limit. It is returning the first 1,000 rows without making truncation impossible to miss. The SQL succeeded, the model received data, and a bounded sample becomes a confident statement about the full population.
Limits protect the database only when the result contract protects the answer.
Use more than a row limit
A row cap does not bound a single large JSON value, a slow aggregate, an expensive sort, or a query that scans millions of rows before returning ten. A production operation should define independent budgets for:
- rows returned;
- bytes returned;
- statement and end-to-end time;
- estimated or measured query cost;
- concurrent work per tenant or principal;
- pages and total expansion allowed per logical request.
Apply the budgets before model context. Prompt instructions such as “return no more than 100 rows” are not database controls.
Make truncation a typed state
Do not bury “showing the first 1,000 rows” in a text footnote. Return structured fields such as is_complete, truncation_reason, rows_returned, bytes_returned, limit, stable_order, and continuation_available.
The final answer should preserve that state. If the operation is incomplete, the model must not describe totals, maxima, rankings, or absence as if every row was examined.
This complements the broader row limits for AI database agents.
Choose semantics before LIMIT
LIMIT 100 without a stable, unique ordering does not define which 100 rows should appear. Concurrent changes can duplicate or skip records between pages.
Use a deterministic order that ends with a unique key. Prefer keyset pagination over large offsets. When the workflow needs a count or top-N ranking, create a separate approved aggregate operation instead of asking the model to infer it from a raw sample.
Bind continuation to the original authority
A continuation token is an authorization artifact. Bind it to the authenticated principal, tenant, environment, approved operation, normalized query digest, policy version, stable-order keys, source snapshot or watermark, and expiry.
Sign or store the token server-side. Reject it if identity, policy, query shape, or snapshot changes. A token created for one tenant must never continue under another.
Define timeout outcomes
A timeout can happen before execution, during database work, while streaming rows, or after the database completed but before the client received the result. These states need different handling.
Propagate cancellation to PostgreSQL, release the connection, bound retries, and record whether any usable result was produced. Do not retry expensive analytical queries blindly. For mutations, use separate idempotent tools and durable receipts rather than reusing a read-oriented query endpoint.
Concrete example: top customers
A user asks, “Which customers generated the most revenue last quarter?” The generic query tool returns 1,000 invoice rows ordered by creation time because the result cap is reached.
Those rows cannot support a top-customer claim. They are neither the full quarter nor an aggregate by customer.
The server should refuse the unsupported interpretation and route to an approved operation that aggregates the complete scoped population, applies the reporting cutoff, sorts by the computed metric, and returns a bounded top-N result with evidence.
Return a reviewable envelope
A bounded PostgreSQL result should include:
- approved operation and normalized input digest;
- tenant, environment, role, and policy version;
- source snapshot or transaction boundary;
- stable ordering and page position;
- row, byte, time, and cost budgets;
- observed counts and truncation reason;
- continuation state and expiry;
- trace ID for independent review.
Minimize the payload as well. See ChatGPT database query data minimization.
Test the boundary
- Exceed the row cap with narrow and wide rows.
- Exceed the byte cap with JSON and text values.
- Run a slow query that returns very few rows.
- Page through concurrent inserts and updates.
- Replay a continuation token under another tenant or after expiry.
- Cancel mid-stream and verify PostgreSQL work stops.
- Ask the model for a total from a truncated sample and verify refusal.
Audit the logical operation
Multiple pages belong to one logical operation. Keep the operation ID stable while recording each attempt and page separately. That makes retries, costs, partial delivery, and final completeness reconstructable.
Use an audit-ready MCP database workflow for the wider evidence model.
Where Conexor fits
Conexor provides MCP infrastructure for connecting AI clients to PostgreSQL and other databases through governed tools. Explicit budgets, typed truncation, scoped continuation, and audit evidence keep a protective limit from becoming a misleading answer.