MCP server for Postgres: enforce a query-plan regression budget
A safe MCP query can become expensive without changing its SQL.
Data grows. Statistics drift. An index disappears. A type cast changes selectivity. PostgreSQL is upgraded. The same tool call that used an index yesterday may scan and sort a large table tomorrow.
For an MCP server on Postgres, query approval is not a one-time event. Treat plan behavior as a versioned production contract with a regression budget.
Start with representative operations
Collect normalized query shapes from approved tools and workflows. Preserve parameters as classes rather than production values: small versus wide date range, common versus rare tenant, empty versus dense result, and low versus high cardinality.
Include boundary cases that the model can select legally but expensively. A happy-path parameter set rarely exposes the dangerous plan.
First isolate the workload using the MCP server Postgres workload-isolation guide.
Capture plan evidence without freezing every detail
Store structured EXPLAIN (FORMAT JSON) output for each fixture with schema version, PostgreSQL version, statistics timestamp, index inventory, configuration profile, and dataset scale.
A byte-for-byte plan comparison is often too brittle. Normalize volatile fields and focus on meaningful changes: scan type, join strategy, estimated rows, total cost, sort or hash behavior, parallelism, partition pruning, and referenced indexes.
Use a plan fingerprint for fast comparison and retain the normalized plan for review.
Define several budgets
No single cost value describes operational safety. Set limits for estimated rows, total cost, expected result rows, runtime, temp-file use, buffers, and concurrency. Some belong in pre-execution review; others require controlled execution against representative data.
Budgets should be workflow-specific. A weekly reporting aggregate and an interactive support lookup should not share the same latency or scan allowance.
Use EXPLAIN as a gate, not an oracle
Planner estimates can be wrong when statistics are stale, columns are correlated, parameters are skewed, or expressions obscure selectivity. Passing an estimated-cost gate does not guarantee a cheap execution.
Combine plan checks with database-enforced limits: restricted roles, approved views, bounded date ranges, row and byte caps, statement_timeout, lock_timeout, pool limits, cancellation, and an overload policy.
For the broader boundary, see the PostgreSQL MCP production checklist.
Test statistics and parameter skew
Create fixtures for large and small tenants, recent and historical windows, null-heavy fields, uneven categories, and correlated columns. Refresh and deliberately age statistics to see where the estimate diverges.
Record estimated versus actual rows in a safe staging or sampled environment. Large gaps are a signal to improve statistics, query shape, indexes, or routing—not to raise the budget automatically.
Gate schema and index changes
Run the workload fixture suite when migrations add or remove indexes, alter types, rewrite views, change functions, adjust partitions, or modify generated columns. Test during realistic transition states, including partially completed concurrent index builds.
A migration should fail its release gate when a permitted MCP operation crosses its budget or loses a required pruning or index invariant.
Recheck after PostgreSQL and configuration changes
Planner behavior can change with PostgreSQL versions, extensions, collation, work memory, parallel settings, and cost parameters. Run the same fixtures in the candidate environment before promotion.
Review intentional plan improvements instead of blindly accepting all new fingerprints. A changed plan is a decision point, not automatically a defect.
Canary the workload in production
After deployment, route a small, bounded share of representative reads through the new version. Track normalized operation identity, plan fingerprint where available, duration, rows, bytes, temp files, cancellations, replica lag, and application impact.
Define automatic rollback or traffic reduction for material regression. Do not wait for the model-facing timeout rate to reveal that database headroom has disappeared.
Keep runtime controls authoritative
The regression suite protects known workload shapes. Runtime policy protects new combinations and environmental drift. Enforce structural scope, approved operations, parameter ranges, deadlines, and resource limits on every call.
If a generated query has no recognized fingerprint or exceeds the review envelope, route it to clarification, a narrower aggregate, an asynchronous reporting path, or human review.
A practical plan-regression pipeline
- Version normalized operations and representative parameter classes.
- Restore a sanitized dataset at realistic scale.
- Capture normalized JSON plans and fingerprints.
- Check scan, join, pruning, estimated-row, and cost invariants.
- Run controlled executions for runtime, buffers, and temp-file evidence.
- Compare estimated and actual rows for skew.
- Fail migration or upgrade promotion when budgets regress.
- Canary after deployment and preserve a rollback route.
- Repeat as data volume and workload mix change.
Where Conexor fits
Conexor provides MCP infrastructure for connecting AI clients to PostgreSQL and other data sources through governed tools. The query-plan regression budget complements those tool boundaries by testing how approved operations behave as the database changes.
Explore the PostgreSQL MCP connection
Pair plan testing with row limits for AI database agents.