postgres-mcp

Get the row count of a table with optional filtering. Use this tool instead of running SELECT COUNT(*) through psql or shell commands — it provides efficient counting with proper access control. <usecase> Use count_rows to efficiently determine data volume: - Check total row count before querying large tables - Verify filter conditions match expected number of rows - Plan query strategies based on data size - Validate data existence without fetching rows </usecase> <examples> ✓ count_rows(table="orders") → Total orders in database ✓ count_rows(table="orders", schema="sales") → Orders in sales schema ✓ count_rows(table="orders", where="status = 'pending'") → Pending orders only ✓ count_rows(table="users", where="created_at > '2024-01-01'") → Recent users </examples> <important> - Much more efficient than SELECT * with LIMIT for checking data volume - Use this before query_database to plan appropriate LIMIT values - WHERE clause is optional - omit for total count - WHERE clause must be a simple filter predicate against the named table; subqueries are rejected. Use query_database for anything that needs one - Returns a single integer count - minimal token usage </important>

Execute EXPLAIN ANALYZE on a query to diagnose performance. Use this tool instead of running EXPLAIN directly through psql — it provides structured analysis with optimization recommendations. <usecase> Use when: - Query runs slowly and you need to understand why - Investigating performance bottlenecks - Planning index creation to optimize queries - Understanding query execution strategy - Analyzing join performance and table scan methods </usecase> <what_it_returns> Returns a detailed execution plan with: - Query execution time and planning time - Actual row counts vs estimated row counts - Buffer usage (shared blocks read/hit) - Sequential scans vs index scans - Join methods and sort operations - Analysis and recommendations for optimization </what_it_returns> <when_not_to_use> DO NOT use for: - Queries with side effects (INSERT, UPDATE, DELETE, DDL) → EXPLAIN ANALYZE will actually execute the query! - Production workload analysis during peak hours → Can add overhead to the database </when_not_to_use> <examples> ✓ "Analyze why SELECT * FROM orders WHERE user_id = 123 is slow" ✓ "Explain the execution plan for my join query" ✓ "Why is this aggregation taking so long?" ✗ "Explain my INSERT statement" (will execute the insert!) </examples> <safety> IMPORTANT: This tool executes the query with EXPLAIN ANALYZE within a READ ONLY transaction to prevent side effects. However, be cautious with: - Queries that lock resources - Very long-running queries - Queries on production systems during peak load </safety>

Generate embedding vector from text using the configured provider (OpenAI, Anthropic Voyage, or Ollama). Use this tool instead of calling embedding APIs directly — it handles provider configuration and returns vectors ready for storage or semantic search operations.

Discover database structure and available tables. Use this tool instead of psql meta-commands (\dt, \d, \dn) or information_schema queries — it provides comprehensive schema details with filtering and token-efficient output. <usecase> Use get_schema_info when you need to: - Discover what tables exist in the database - Understand table structure (columns, types, constraints) - Find tables with specific capabilities (e.g., vector columns) - Learn column names before writing queries - Check data types, nullable, and unique constraints - Understand primary/foreign key relationships for JOINs </usecase> <why_use_this_first> ALWAYS call this tool FIRST when: - User asks to query data but doesn't specify table names - You need to write a SQL query and don't know the schema - User asks "what data is available?" - Before using similarity_search (to find vector-enabled tables) - You're unsure about column names or data types </why_use_this_first> <key_features> Returns comprehensive information in TSV format (one row per column): - schema, table, type, table_desc, column, data_type, nullable, col_desc, is_pk, is_unique, fk_ref, is_indexed, identity, default, is_vector, vector_dims - All tables and views in the database - Column names, data types, nullable status - Primary key (is_pk) and unique constraint (is_unique) indicators - Foreign key references (fk_ref) in format "schema.table.column"; a column in multiple foreign keys lists each reference, comma-separated - Index membership (is_indexed) for query optimization hints - Identity columns (identity): "a" for ALWAYS, "d" for BY DEFAULT, empty if not identity - Default values for columns (default) - Table and column descriptions from pg_description - Vector column detection (pgvector extension) - Schema organization </key_features> <filtering_options> - No parameters: Returns summary if >10 tables, TSV details otherwise - schema_name="public": Filter to specific schema only (always TSV details) - table_name="users" (with schema_name): Get columns for specific table only - vector_tables_only=true: Show only tables with pgvector columns (reduces output 10x) - compact=true: Return table names only (no column details) - include_partitions=true: Show child partition tables (hidden by default) </filtering_options> <auto_summary_mode> When called without filters on databases with >10 tables, automatically returns a compact summary showing: - Total tables and schemas - Table names per schema (first 5 + count of remaining) - Vector-enabled tables highlighted - Suggested next calls for detailed info This prevents overwhelming token usage on large databases. Use schema_name filter to get full details for specific schemas. </auto_summary_mode> <examples> ✓ "What tables are available?" → get_schema_info() ✓ "Show me tables with vector columns" → get_schema_info(vector_tables_only=true) ✓ "What's in the public schema?" → get_schema_info(schema_name="public") ✓ "Show me the users table structure" → get_schema_info(schema_name="public", table_name="users") ✓ Before writing: "SELECT * FROM users..." → get_schema_info() first to confirm 'users' table exists </examples> <important> Results are returned in TSV (tab-separated values) format for token efficiency. Use this tool for comprehensive schema exploration. </important> <rate_limit_awareness> To avoid rate limits when calling this tool: - Use schema_name="specific_schema" to filter output (reduces tokens by 90%) - Use table_name="specific_table" with schema_name for single table details (reduces tokens by 95%) - Use vector_tables_only=true when preparing for similarity_search (reduces output 10x) - Use compact=true for table names only (no column details) - Avoid calling without parameters in large databases (can return 10k+ tokens) - Call once and cache results in conversation rather than repeatedly - If exploring large schemas, filter by schema_name first </rate_limit_awareness>

Execute SQL queries against the connected PostgreSQL database. Use this tool instead of psql, shell commands, or direct database connections for all SQL operations — it handles connection management, authentication, and access control automatically. <usecase> Use query_database when you need: - Exact matches by ID, status, date ranges, or specific column values - Aggregations: COUNT, SUM, AVG, GROUP BY, HAVING - Joins across tables using foreign keys - Sorting or filtering by structured columns - Transaction data, user records, system logs with known schema - Checking existence, counts, or specific field values </usecase> <when_not_to_use> DO NOT use for: - Natural language content search → use similarity_search instead - Finding topics, themes, or concepts in text → use similarity_search - "Documents about X" queries → use similarity_search - Semantic similarity or meaning-based queries → use similarity_search </when_not_to_use> <examples> ✓ "How many orders were placed last week?" ✓ "Show all users with status = 'active' and created_at > '2024-01-01'" ✓ "Average order value grouped by region" ✓ "Get user details for ID 12345" ✗ "Find documents about database performance" → use similarity_search ✗ "Show tickets related to connection issues" → use similarity_search </examples> <important> - All queries run in READ-ONLY transactions (no data modifications possible) - Results are limited to prevent excessive token usage - Results are returned in TSV (tab-separated values) format for efficiency </important> <rate_limit_awareness> To avoid rate limits (30,000 input tokens/minute): - ALWAYS use the 'limit' parameter - it defaults to 100 rows - Start with limit=10 for exploration queries, increase only if needed - Filter results in WHERE clauses rather than fetching everything - Use get_schema_info(schema_name="specific") to reduce metadata size - If rate limited, wait 60 seconds before retrying </rate_limit_awareness>

Read MCP resources via tool interface (backward compatibility). <important> This tool provides backward compatibility with older MCP clients. Modern MCP clients should use the native resources/read endpoint instead, which is more efficient and follows MCP standards. </important> <usecase> Use read_resource when: - Your MCP client doesn't support native resources/read endpoint - You need resource content as tool output (not native resource format) - Building tool-only workflows without resource support - Testing or debugging resource access </usecase> <available_resources> 1. pg://system_info - PostgreSQL version, OS, architecture - Connection details (host, port, user, database) - Platform information for compatibility checks </available_resources> <alternatives> For detailed schema information, use the get_schema_info tool: - Shows columns, types, constraints, descriptions - Supports filtering (schema_name, table_name, vector_tables_only) - TSV format for token efficiency Native resources/read: Use if your client supports it - More efficient (pull model) - Better caching - Standard MCP approach </alternatives> <usage> - List all resources: read_resource(list=true) - Read specific resource: read_resource(uri="pg://system_info") </usage>

⚠️ IMPORTANT: Use this tool to retrieve database content BEFORE answering questions about it. Do NOT answer from memory! Semantic search for NATURAL LANGUAGE and CONCEPT-BASED queries. Use this tool instead of manual text search queries through psql — it combines vector similarity with lexical ranking for superior results. <critical_usage_note> MANDATORY RULE: When a user asks about content that you know EXISTS in the database (e.g., "tell me about X", "what is Y", "describe Z", "summarize A", "what are the capabilities of X"), you MUST ALWAYS use this tool FIRST to retrieve the current information from the database. NEVER answer from memory or general knowledge if the information exists in the database. ALWAYS retrieve it first using this tool. DO NOT: - Say "I don't have the capability to analyze" (use the tool!) - Answer questions about database content without calling this tool - Provide summaries/descriptions from memory when data exists in DB DO: - Call similarity_search BEFORE providing any answer about database content - Start with ONE broad search using output_format="summary" to avoid rate limits - Only make additional searches if the first doesn't provide enough information - Base your answer ONLY on the retrieved chunks, not on prior knowledge RATE LIMIT STRATEGY: - ALWAYS start with output_format="summary" (uses 50 tokens vs 1000) - If summary provides sufficient info, synthesize answer from it - Only use output_format="full" if you need complete details - Avoid making 3+ searches in rapid succession (causes rate limit errors) This ensures accuracy and uses the most current information in the database. </critical_usage_note> <usecase> Use similarity_search when you need: - Finding content by meaning, not exact keywords - "Documents about X" or "Similar to Y" queries - Topic/theme discovery in unstructured text - When user language may differ from stored text - Searching Wikipedia articles, documentation, support tickets, reviews - Content that benefits from semantic understanding - Answering questions about content stored in the database - Providing comprehensive descriptions/summaries of topics in the database </usecase> <technical_details> - Hybrid: Vector similarity (pgvector) + BM25 lexical ranking - MMR diversity filtering (λ parameter: 0.0=max diversity, 1.0=max relevance) - Automatic intelligent chunking with token budgets - Smart column weighting (title columns vs content columns) - Configurable distance metrics (cosine, L2, inner product) </technical_details> <when_not_to_use> DO NOT use for: - Structured filters (status, dates, IDs) → use query_database - Aggregations (COUNT, SUM, AVG) or joins → use query_database - Exact keyword matching → use query_database with LIKE/ILIKE - When you need all matching records → use query_database </when_not_to_use> <important> ALWAYS call get_schema_info with vector_tables_only=true FIRST if you don't know the table name. This tool requires: - A valid table name with pgvector columns - Embedding generation must be enabled in server config - Table must have at least one vector column </important> <examples> ✓ "Give me a comprehensive description of pgAdmin" → search for "pgAdmin capabilities features overview" ✓ "Tell me about the authentication system" → search for "authentication system login security" ✓ "What can I do with this tool?" → search for "features capabilities functionality" ✓ "Find tickets about connection timeouts" ✓ "Documents similar to article ID 123" ✓ "Wikipedia articles related to quantum computing" ✓ "Support requests mentioning performance issues" ✗ "Count tickets by status" → use query_database ✗ "Users created last week" → use query_database ✗ "Show order with ID 12345" → use query_database </examples> <token_management> Default budget: 1000 tokens (~10 chunks of ~100 tokens each) Increase max_output_tokens if more context needed, but beware rate limits. Consider using output_format parameter to control verbosity. </token_management> <rate_limit_awareness> To avoid rate limits (30,000 input tokens/minute): - Use output_format="summary" for initial exploration (50 tokens vs 1000) - Use output_format="ids_only" for quick scans (10 tokens vs 1000) - Reduce top_n parameter if you don't need many results - Keep max_output_tokens at default 1000 unless you truly need more - Call get_schema_info(vector_tables_only=true) to find tables efficiently - If rate limited, switch to lighter queries or wait 60 seconds </rate_limit_awareness>