enhanced-postgres-mcp-server

Executes arbitrary SQL queries against PostgreSQL databases through the Model Context Protocol, translating LLM-generated SQL into database operations via a standardized MCP resource interface. Implements query parsing, connection pooling, and result serialization to JSON for LLM consumption, enabling Claude and other MCP-compatible clients to read and write data without direct database access.

Automatically discovers and exposes PostgreSQL schema metadata (tables, columns, indexes, constraints, data types) through MCP resources, allowing LLMs to understand database structure without manual schema documentation. Uses information_schema queries to build a queryable schema representation that Claude can reference when generating SQL.

Implements configurable access control to distinguish between read-only (SELECT) and read-write (INSERT, UPDATE, DELETE) operations, allowing operators to restrict LLM agents to safe query patterns. Uses query parsing to classify operations and enforce policies before execution, preventing unintended data mutations.

Manages a pool of PostgreSQL connections with configurable pool size, idle timeout, and connection recycling to handle multiple concurrent LLM queries efficiently. Implements connection lifecycle management (acquire, release, evict) to prevent connection leaks and resource exhaustion when Claude makes rapid sequential or parallel queries.

Streams query results in chunks and supports pagination to handle large result sets without loading entire datasets into memory. Implements cursor-based pagination or limit/offset patterns to allow Claude to iteratively fetch results, preventing memory exhaustion on the MCP server and reducing response latency for initial results.

Validates SQL queries before execution and provides detailed error messages when queries fail, including syntax errors, constraint violations, and permission errors. Maps PostgreSQL error codes to human-readable messages that Claude can understand and use to refine subsequent queries, improving the feedback loop for LLM-driven query generation.

Supports explicit transaction control (BEGIN, COMMIT, ROLLBACK) to allow Claude to execute multi-statement operations with ACID guarantees. Maintains transaction state across multiple MCP calls, enabling complex data mutations that require atomicity (e.g., transferring funds between accounts).

Tracks query execution metrics (duration, rows affected, query plan) and exposes them to Claude for performance analysis. Collects statistics on slow queries and resource usage, enabling Claude to optimize queries or alert operators to performance issues.

Supports parameterized queries using PostgreSQL prepared statements to prevent SQL injection attacks and improve query performance through statement caching. Allows Claude to pass query parameters separately from SQL text, enabling safe dynamic query construction without string concatenation.

Allows Claude to invoke PostgreSQL stored procedures and custom functions through MCP, treating them as callable capabilities. Introspects function signatures (parameters, return types) and exposes them as MCP tools that Claude can invoke with type-safe arguments.