Dual-instance MCP server exposing database tools for EnterpriseDB Advanced Server 9.6 over HTTP, with HypoPG virtual index analysis, query optimization, and comprehensive security/authorization controls
Server provides 14 tools across dual PostgreSQL instances with moderately clear descriptions and explicit input schemas. Naming convention is descriptive (verb_noun) and parameter schemas are fully typed with descriptions. However, descriptions are terse (avg ~80 chars vs baseline ~194), parameters lack constraints (enums, ranges), output schemas are undocumented, and error handling guidance is absent. Tool names are overly verbose with instance prefixes (db_1_, db_2_) that create cognitive load rather than clarity. No mention of pagination limits enforced in schema or docs. Security posture unclear, no evidence of credentials management or permission gates. This is solid foundation-level work, but falls short of production-grade definition quality.
Tools (14)
db_1_pg96_blocking_sessionsread onlyauth50/100
Identify blocking sessions holding locks on tables or rows
db_1_pg96_exec_queryread onlyauth50/100
Execute arbitrary SELECT query with pagination support (cursor offset, max_rows limits)
Tool naming pattern db_N_pg96_* is verbose and violates clarity principle. Instance selector should be a parameter or context, not baked into every tool name. LLMs must reason about instance selection before calling, creating cognitive load. Consider: ping, exec_query, hypopg_create_virtual_indexes with instance_id parameter instead.
Descriptions are terse (avg 55 - 80 chars vs baseline 194 chars). e.g. 'Ping query returning instance identity (cluster_name, version, edb_compat_mode, ip_address, current_utc_time)' lists output fields but doesn't explain WHEN to call it, what it's for, or why an agent would use it. Descriptions should guide LLM selection and usage, not just summarize output.
Recommendations
Consolidate 14 tools into 7 (one set per instance) by moving instance_id to a required parameter. This reduces cognitive load and makes tool names match the 18-char production baseline (currently avg ~28 chars). Example: rename db_1_pg96_exec_query + db_2_pg96_exec_query → exec_query(instance_id: enum[primary, secondary], database_name, query_text, ...).
Expand descriptions to 120 - 200 chars, following the baseline. For each tool, add: (1) WHAT does it do? (2) WHEN should the LLM call it instead of alternatives? (3) Any prerequisites or side effects? Example: 'Execute a parameterized SELECT query. Use this to fetch data; use hypopg_explain_with_virtual to estimate performance first. Supports pagination via cursor_offset and max_rows limits. Queries are parameterized to prevent SQL injection.'
Document output schemas for all tools. Add a 'Returns' section to each description or source code docstring specifying the exact JSON structure. For exec_query, document: 'Returns {rows: [{column_name: value, ...}, ...], row_count: int, cursor_offset: int, has_more: bool, duration_ms: int}'. For hypopg tools, document the index plan structure and cost estimates.
Add constraint metadata to parameter descriptions. For max_rows, change from 'Maximum rows to return (capped at 10000)' to 'Maximum rows to return (integer, 1 - 10000, default 100). If omitted, returns up to 100 rows; specify 10000 for large result sets but be aware this increases response size and agent latency.'
Add error handling guidance to each tool description. For tools that execute queries, add: 'Errors: Query syntax errors return a 400 with the PostgreSQL error message, review the SQL. Connection timeouts return a 504, retry after 5s. Authentication failures (e.g., wrong password) return a 401, check credentials and reconfigure.' This enables LLM recovery without trial-and-error.
Spec posture evidence
Inferred effective spec: 2026-07-28+.
Relies on Logging (deprecated) - log to stderr or use OpenTelemetry
Score history
Overall score trend
↑ 1 points across a rubric change (v1 → v2)
51/100
Scored
Grade
Overall
Spec posture
Rubric
2026-09-22
D
51
2026-07-28+
v2
2026-03-09
D
50
-
v1
50/100
Retrieve current session counts by state and application
db_2_pg96_blocking_sessionsread onlyauth50/100
Identify blocking sessions holding locks on tables or rows on secondary instance
db_2_pg96_exec_queryread onlyauth50/100
Execute arbitrary SELECT query with pagination support (cursor offset, max_rows limits) on secondary instance
Find optimal virtual index combination for a query by orchestrating baseline capture, candidate creation, combination testing, and ranking on secondary instance
Output schemas undocumented. Tools like db_1_pg96_exec_query return result rows but schema format is not specified in docstring or metadata. LLMs cannot plan downstream tool chains without knowing the structure. Document: 'Returns: {rows: [array of column_name: value dicts], row_count: int, cursor_offset: int, has_more: bool}'.
Parameter max_rows and cursor_offset lack constraint documentation. Schema shows capped at 10000 and 1000000 respectively, but LLM-facing description does not state these limits.
Error handling not documented. No guidance on what errors tools may return, whether they are retryable, or what the LLM should do. e.g. 'If query times out after 30s, wait and retry' or 'If database connection fails, check network and retry', agents need recovery hints.
Security and authorization undocumented. No mention of whether queries are SQL-injection-safe (parameterized via $1, $2?), whether permission checks exist, or what scope/role each tool requires.
max_combinations parameter in hypopg_find_optimal_indexes lacks constraint. Schema shows default 10 but no mention of min/max bounds. LLM may pass 0 or 10000 unexpectedly. Add to description: 'max_combinations (integer, 1 - 100, default 10), bounds the search space to avoid excessive compute.'
Declare required permissions. Add a 'Permissions' field to each tool: 'Permissions: read:database' for SELECT tools, 'Permissions: write:database' for virtual index creation, 'Permissions: admin:database' for blocking session inspection. This clarifies least-privilege scope and audit trail.
Document SQL injection safeguards. Confirm that query_text parameter is passed to asyncpg as parameterized query (via $1, $2, ... placeholders) and add note: 'All parameters are SQL-escaped via asyncpg parameterization, safe from injection.' If query_text is not parameterized (e.g., string interpolation), flag as CRITICAL security issue.
Add optional dry_run parameter to hypopg_find_optimal_indexes to support confirmation pattern. When dry_run=true, return the proposed index plan without actually creating virtual indexes. This lets the agent preview changes before committing, reducing accidental bloat.
Provide tool aliases or a discovery tool listing the instance IDs and recommended entry points. A single list_databases(instance_id) tool returning available databases on each instance would support the discovery pattern and reduce initial friction.
Document HypoPG limitations and trade-offs in tool descriptions. Note: 'Virtual indexes are session-scoped and disappear on connection close. Use this for analysis and testing only, do not assume indexes persist across agent invocations. For permanent optimization, export the recommendations and apply real indexes manually.'
Add pagination info to blocking_sessions and session_counts if they can return large result sets. Specify: 'If more than 50 rows match, only the first 50 are returned. This is intentional to prevent overwhelming the LLM context. To see all, contact the DBA or use native database tools.'
Create a single 'status' or 'health' tool that pings both instances and returns connectivity, version, and config mismatch info. This supports the discovery and orchestration patterns and reduces redundant ping calls.