Add a public PostgreSQL tuning MCP server

[sebastianwebber]

Public PostgreSQL Tuning MCP Server

Problem Statement

pgconfig already calculates PostgreSQL configuration recommendations through its
REST API and CLI, but an AI agent using an MCP-compatible CLI cannot discover or
invoke that capability through the Model Context Protocol. Users must manually
translate their environment into a different interface, interpret configuration
output without calculation provenance, and supply it to the agent themselves.

The existing tuning pipeline also does not expose a single rich domain boundary
that can serve MCP now and a future REST v2 later. Adding MCP directly around the
legacy handlers would duplicate validation and defaults, preserve ambiguous input
behavior, and make the MCP contract dependent on REST v1 compatibility choices.

Solution

Expose a public, anonymous, stateless Streamable HTTP MCP endpoint at /mcp in
the existing pgconfig API deployment. The endpoint will initially publish one
tool, recommend_postgres_configuration, which turns a validated Tuning Request
into deterministic PostgreSQL Tuning Recommendations.

Introduce a shared tuning domain module that owns request normalization,
validation, defaults, Tuning Assumptions, warnings, calculations, and concise
deterministic reasons. The MCP adapter will be thin and protocol-specific. REST
v1 and the CLI will be migrated internally to the same tuning boundary without
changing their public output, and a future REST v2 can expose the richer result.

User Stories

1. As an MCP-compatible CLI user, I want to discover pgconfig as a remote MCP server, so that I can use PostgreSQL tuning without installing the pgconfig CLI.
2. As an AI agent, I want to discover a single clearly named tuning tool, so that I can choose the correct capability without ambiguity.
3. As a PostgreSQL operator, I want to provide my server resources and workload through the tool, so that the recommendations reflect my environment.
4. As a PostgreSQL operator, I want total RAM to require an explicit unit, so that a bare number is never silently interpreted at the wrong scale.
5. As a PostgreSQL operator, I want RAM units to be accepted case-insensitively, so that common variants of B, KB, MB, GB, and TB work consistently.
6. As an AI agent, I want an invalid or missing RAM value to produce an actionable tool error, so that I can correct the request or ask the user for the missing fact.
7. As a PostgreSQL operator, I want total CPU to mean logical CPUs, so that the input matches values reported by common operating-system tools.
8. As an AI agent, I want total CPU to require a positive integer, so that invalid resource counts cannot produce misleading recommendations.
9. As a PostgreSQL operator, I want to provide a complete PostgreSQL Version such as 9.6.24 or 18.4, so that the request accurately describes the target release.
10. As an AI agent, I want PostgreSQL Version to remain a string, so that versions such as 9.6 and 17.10 are not corrupted by numeric parsing.
11. As a PostgreSQL operator, I want PostgreSQL Version syntax validated, so that malformed values such as v18.4, 18.x, or 9..6 fail clearly.
12. As a PostgreSQL operator, I want pgconfig to derive the PostgreSQL Major Version according to PostgreSQL's versioning policy, so that parameter compatibility is evaluated correctly before and after PostgreSQL 10.
13. As an AI agent, I want unsupported PostgreSQL Major Versions to produce an actionable tool error, so that I do not present recommendations for an unsupported release series.
14. As a PostgreSQL operator, I want the complete PostgreSQL Version preserved in the normalized request, so that I can verify exactly what the agent supplied.
15. As a user who omits a workload profile, I want the WEB profile applied explicitly, so that the tool remains useful with the established pgconfig default.
16. As a user who supplies a workload profile, I want WEB, OLTP, DW, MIXED, and DESKTOP accepted case-insensitively and normalized, so that casing does not change behavior.
17. As an AI agent, I want unknown workload profiles rejected, so that a typo never silently changes tuning behavior.
18. As a user who omits disk type, I want SSD applied explicitly, so that the tool follows the established pgconfig default.
19. As a user who supplies disk type, I want SSD, HDD, and SAN accepted case-insensitively and normalized, so that casing does not change behavior.
20. As an AI agent, I want unknown disk types rejected, so that contradictory fallback behavior cannot hide an input mistake.
21. As a user who omits operating system, I want Linux applied explicitly, so that operating system is not required for a first recommendation.
22. As a user who supplies operating system, I want linux, windows, unix, and darwin accepted case-insensitively and normalized, so that Windows-specific tuning is not skipped because of casing.
23. As a user who omits architecture, I want amd64 applied explicitly, so that architecture is not required for a first recommendation.
24. As a user who supplies architecture, I want common aliases normalized to 386, amd64, arm, or arm64, so that equivalent architecture names behave consistently.
25. As an AI agent, I want unsupported operating systems and architectures rejected, so that invalid context cannot silently select an unrelated tuning path.
26. As a user who omits maximum connections, I want 100 applied explicitly, so that the tool follows the established pgconfig default.
27. As a user who supplies maximum connections, I want a positive integer accepted without an arbitrary upper limit, so that unusual but intentional deployments remain representable.
28. As a user with an unusually high maximum-connections value, I want a non-fatal warning when appropriate, so that I can review a suspicious input without losing the result.
29. As a user receiving a result with defaults, I want every applied default represented as a Tuning Assumption, so that no fallback is silent.
30. As an AI agent, I want non-fatal concerns represented separately as warnings, so that I can distinguish usable results from tool errors.
31. As a user, I want the result to echo the normalized Tuning Request, so that I can verify units, casing, aliases, defaults, and the supplied PostgreSQL Version.
32. As a user, I want recommendations keyed by PostgreSQL parameter name, so that each result is easy for an agent to locate and discuss.
33. As a PostgreSQL operator, I want each Tuning Recommendation to include a PostgreSQL-formatted value, so that it is understandable without additional conversion.
34. As a PostgreSQL operator, I want each Tuning Recommendation to include a concise deterministic reason in English, so that I can understand why the value was selected.
35. As a PostgreSQL operator, I want reasons to mention relevant caps or later adjustments, so that the explanation matches the final value rather than only the initial calculation.
36. As an AI agent, I want the response to use MCP structured content with a declared output schema, so that I can consume the result reliably.
37. As an older MCP client, I want the same result serialized as JSON text fallback, so that I can still consume the tool response.
38. As an AI agent, I want missing or invalid required facts reported as MCP tool execution errors, so that I can self-correct without treating the MCP transport as broken.
39. As a pgconfig maintainer, I want recommendation behavior to remain deterministic, so that identical Tuning Requests always produce identical results for the same server version.
40. As a pgconfig maintainer, I want the MCP adapter separated from tuning logic, so that protocol concerns do not become a second calculation implementation.
41. As a pgconfig maintainer, I want REST v1 and the CLI to reuse the shared tuning boundary without changing their public contracts, so that MCP does not introduce regressions for existing users.
42. As a pgconfig maintainer, I want a future REST v2 to reuse the rich Tuning Result, so that values, assumptions, warnings, and reasons have one source of truth.
43. As an MCP client, I want the endpoint to be stateless, so that calls do not depend on sticky sessions or server-side conversation state.
44. As an MCP client, I want JSON responses over Streamable HTTP, so that the remote server works through the existing HTTP deployment.
45. As a public user, I want to invoke the first version without authentication, so that any compatible CLI can use pgconfig immediately.
46. As a service operator, I want native clients without an Origin header accepted, so that normal CLI usage works.
47. As a service operator, I want browser requests restricted to official pgconfig origins, so that unrelated websites cannot freely invoke the public endpoint from a user's browser.
48. As a service operator, I want each tool call limited to five seconds, so that a stuck calculation does not consume server resources indefinitely.
49. As a service operator, I want structured call logs without complete arguments, so that I can observe usage and errors without unnecessarily recording environment details.
50. As a service operator, I want success logs to include tool, status, duration, assumption count, warning count, and server version, so that basic operational analysis is possible from logs.
51. As a service operator, I want error logs to include a stable error code and missing-field names where relevant, so that common failures can be diagnosed without logging complete requests.
52. As a pgconfig maintainer, I want the MCP server to share the existing application identity and release version, so that it does not require an independent versioning lifecycle.
53. As a pgconfig maintainer, I want MCP documentation to be the source of truth for the public contract and deferred work, so that implementation and future decisions remain discoverable.
54. As a prospective user, I want the README to link to the MCP documentation, so that I can find setup and usage instructions from the project entry point.
55. As a pgconfig maintainer, I want official MCP conformance checked in a separate pinned CI job, so that protocol compatibility is verified without adding Node tooling to the normal Go test command.
56. As a pgconfig maintainer, I want the stable public endpoint submitted to the official MCP Registry after launch, so that users can discover it through the ecosystem.

Implementation Decisions

• The MCP server is public, anonymous, stateless, deterministic, and read-only in its first release.
• The MCP server runs inside the existing Fiber API application, on the existing listener and deployment. It is not a separate service, process, port, or release artifact.
• The public Streamable HTTP endpoint is /mcp. It is intentionally outside REST namespaces such as /v1 and /v2 because MCP is a separate protocol rather than a REST API version.
• The official Model Context Protocol Go SDK will provide the server and Streamable HTTP handler. Fiber will mount the standard HTTP handler through its adapter.
• Streamable HTTP will use stateless mode and JSON responses.
• The server identity is pgconfig, and it uses the existing application release version rather than a separate MCP semantic version.
• The first release exposes only recommend_postgres_configuration. Additional capabilities require a distinct use case rather than expanding the first tool into unrelated behavior.
• The tool is marked as read-only, deterministic, and idempotent through the applicable MCP annotations.
• A shared tuning domain module will expose one conceptual operation: accept a Tuning Request and return a Tuning Result or a domain error.
• The tuning module owns validation, strict normalization, default application, Tuning Assumptions, warnings, recommendation calculation, and deterministic reasons.
• The MCP server adapter owns protocol schemas, tool registration, MCP error mapping, and conversion between MCP content and the tuning domain. It does not own tuning rules.
• The API entry point only composes dependencies and mounts /mcp.
• The existing rule and category logic will be deepened or migrated behind the tuning boundary instead of creating a duplicate MCP calculation path.
• REST v1 and the CLI will be migrated internally to the shared tuning operation while preserving their current inputs, output formats, and compatibility behavior.
• A future REST v2 may expose the rich Tuning Result directly. No REST v2 endpoint is introduced by this work.
• Required tool inputs are total_ram, total_cpu, and postgres_version.
• total_ram is a string containing a positive integer and a mandatory case-insensitive unit from B, KB, MB, GB, or TB. Decimals, missing units, unknown units, zero, and negative values are errors. Users can express fractional larger units through a smaller integer unit, such as 1536MB.
• Strict RAM parsing is part of the shared tuning normalization rather than the MCP adapter. Existing permissive CLI parsing remains compatible until its public contract is intentionally changed.
• total_cpu is a positive integer representing logical CPUs, including hyperthreads where reported by the operating system.
• postgres_version is a dotted numeric string. The complete supplied version is preserved, while PostgreSQL Major Version is derived using two number groups for releases before 10 and one number group from 10 onward.
• Supported PostgreSQL Major Versions are 9.1 through 9.6 and 10 through 18. The tool validates syntax and supported major series but does not maintain a catalog of valid minor releases; for example, a syntactically valid minor number is not checked against quarterly release history.
• Optional profile values are WEB, OLTP, DW, MIXED, and DESKTOP. Values are case-insensitive and normalize to uppercase. The default is WEB.
• Optional disk_type values are SSD, HDD, and SAN. Values are case-insensitive and normalize to uppercase. The default is SSD.
• Optional max_connections is a positive integer. The default is 100. No arbitrary hard maximum is introduced, although suspiciously high values may produce a warning.
• Optional os values are linux, windows, unix, and darwin. Values are case-insensitive and normalize to lowercase. The default is linux.
• Optional arch accepts 386 and i686 as 386; amd64 and x86-64 as amd64; arm as arm; and arm64 as arm64. The default is amd64.
• Invalid optional values are errors. They never silently fall back to a default.
• Every omitted optional value that receives a default is visible in the result as a Tuning Assumption. Non-fatal quality or risk signals are returned as warnings.
• The service never detects RAM, CPU, operating system, or architecture from the remote server runtime, because those facts would describe the pgconfig deployment rather than the user's PostgreSQL environment.
• Missing or invalid required inputs return an MCP tool execution error with an actionable English message. They do not become protocol-level transport errors.
• The successful structured result contains the normalized Tuning Request, applied Tuning Assumptions, warnings, and a parameter map.
• Each parameter entry contains a PostgreSQL-formatted value and a short deterministic English reason. Reasons describe the calculation and any relevant later cap or adjustment that affected the final value.
• The tool declares an MCP output schema and returns structured content. It also mirrors the same JSON as text content for clients that do not yet consume structured results.
• The first MCP result omits listen_addresses = '*'. That setting is connectivity-related and security-sensitive rather than a performance recommendation. REST v1 and CLI behavior remain unchanged for compatibility, and the omission will be reviewed later.
• The first version does not return rendered postgresql.conf, ALTER SYSTEM, or StackGres output and has no output-format input.
• The existing Fiber body-size default remains unchanged. A stricter MCP-specific request limit is not part of this release.
• Each tool execution has a five-second timeout.
• Native CLI requests without an Origin header are accepted. Browser requests are accepted only from official pgconfig origins; unrelated browser origins are rejected.
• No application-level authentication, custom caching, or custom rate limiting is introduced. Existing Cloudflare protections remain the deployment baseline.
• Structured logs are the initial observability mechanism. Complete tool arguments are not logged.
• Successful call logs include tool, status, duration in milliseconds, assumption count, warning count, and server version. Error logs include status, stable error code, and missing-field names when applicable.
• Public MCP documentation will describe connection details, input and output contracts, examples, defaults, errors, operational constraints, and deferred work. The README will link to this document rather than duplicate it.
• The public production URL is https://api.pgconfig.org/mcp.
• Publication to the official MCP Registry happens after the endpoint and documentation are stable and does not block the initial deployment.

Testing Decisions

• Tests assert externally observable behavior and domain outcomes, not the internal order or private shape of individual rules. A good test supplies a Tuning Request or MCP call and verifies normalized inputs, errors, assumptions, warnings, recommendation values, reasons, or protocol responses.
• The principal and highest-value test seam is the shared tuning operation. Table-driven Go tests cover required fields, strict RAM units, positive integer constraints, PostgreSQL Version parsing and support, enum normalization, aliases, defaults, Tuning Assumptions, warnings, deterministic values, reasons, caps, and omission of the MCP-only unsafe recommendation.
• Existing rule calculation tests remain useful prior art for expected values. Their behavioral cases should migrate toward the shared tuning seam where practical rather than duplicating assertions across every private rule.
• A small set of end-to-end handler tests exercises /mcp through Fiber with an official MCP client. These tests cover initialization, tool discovery, a successful call, structured content, text fallback, actionable tool errors, Origin policy, and timeout behavior.
• REST v1 regression tests verify that its public inputs and outputs remain unchanged after internal migration to the tuning boundary.
• CLI regression tests verify existing defaults, resource detection, formats, and output remain unchanged after internal migration.
• Determinism is tested by repeating equivalent normalized requests and comparing complete Tuning Results.
• Structured logging is tested at the call boundary to confirm required fields are emitted and complete arguments are not recorded.
• The standard Go suite continues to run through make test, including race detection and coverage according to the existing project workflow.
• Official MCP conformance runs in a separate CI job with the conformance package version pinned. This avoids adding Node dependencies to make test while preventing unreviewed conformance changes from breaking CI.

Out of Scope

• A separate MCP deployment, process, binary, listener, or port.
• REST v2 endpoints or public changes to REST v1 and CLI contracts.
• Authentication, API keys, user accounts, persisted state, or per-user capabilities.
• Application-level caching or custom rate limiting.
• Prometheus metrics or a /metrics endpoint.
• More MCP tools in the first release.
• Rendered postgresql.conf, ALTER SYSTEM, or StackGres output from MCP.
• A selectable response format.
• A complete structured calculation trace or symbolic formula model beyond concise deterministic reasons.
• Exact validation against PostgreSQL's catalog of released minor versions.
• Decimal RAM quantities.
• pgBadger integration or log_format recommendations.
• Returning parameter abstracts, documentation, or recommendation links with every tool response.
• Recommending listen_addresses = '*' through MCP.
• MCP Registry publication as a prerequisite for deploying the endpoint.

Further Notes

• Deferred work must be recorded in the MCP documentation with both its rationale and a trigger for reconsideration.
• Reconsider custom caching if calculation cost or latency becomes operationally significant. Deterministic requests make cache keys feasible, including at Cloudflare, but cache correctness and invalidation should follow measurement.
• Reconsider custom rate limiting if traffic or abuse exceeds the protection provided by Cloudflare.
• Reconsider Prometheus metrics when pgconfig has a metrics collector and monitoring infrastructure; structured logs are sufficient for the initial release.
• Reconsider authentication if the MCP server gains persisted state, private data, user-specific behavior, or mutating capabilities.
• Reconsider additional tools only when a separate user intent cannot be expressed cleanly through recommend_postgres_configuration.
• Reconsider a detailed calculation trace if consumers need machine-readable provenance beyond the concise reason.
• Reconsider pgBadger and log_format only as a distinct observability-driven tuning capability.
• Review the omission of listen_addresses after operational experience, while treating secure connectivity guidance separately from performance tuning.
• Review decimal RAM support only if real clients cannot reliably express equivalent integer quantities in smaller units.
• The domain vocabulary should consistently use Tuning Request, Tuning Recommendation, Tuning Assumption, PostgreSQL Version, and PostgreSQL Major Version.