MCP PostgreSQL Operations

# PostgreSQL Operations MCP Server - Prompt Templates ## Server ### 📈 Database Performance Analysis - "Show database-wide performance statistics." - "Analyze transaction commit ratios and I/O patterns." - "Check buffer cache hit ratios for all databases." - "Monitor temporary file usage and deadlock counts." ### 🔧 Background Writer & Checkpoints - "Analyze checkpoint performance and timing." - "Show background writer efficiency statistics." - "Check buffer allocation and writing patterns." - "Monitor checkpoint scheduling vs requested ratios." professional MCP server for PostgreSQL database server operations, monitoring, and management. Provides advanced performance analysis capabilities using pg_stat_statements and pg_stat_monitor extensions. ## Key Features - ✅ **Version Compatibility**: Transparent PostgreSQL version support (12-17) - automatically detects and adapts functionality - ✅ **PostgreSQL Monitoring**: Performance analysis based on pg_stat_statements and pg_stat_monitor with backward compatibility - ✅ **Structure Exploration**: Database, table, and user listing with detailed schema information - ✅ **Schema Analysis**: Detailed table structure with columns, constraints, indexes, and relationships - ✅ **Performance Analysis**: Slow query identification and index usage analysis with version-aware optimization - ✅ **Capacity Management**: Database and table size analysis - ✅ **Configuration Retrieval**: PostgreSQL configuration parameter verification - ✅ **Replication Monitoring**: Version-compatible replication lag analysis and WAL status tracking - ✅ **Safe Read-Only**: All operations are read-only and safe ## Available Tools ### 📊 Server Information & Status 1. **get_server_info**: PostgreSQL server information and extension status 2. **get_current_database_info**: Current database connection details and properties 3. **get_active_connections**: Current active connections and session information 4. **get_postgresql_config**: PostgreSQL configuration parameters ### 🗄️ Structure Exploration 5. **get_database_list**: All database list and size information 6. **get_table_list**: Table list and size information 7. **get_table_schema_info**: Detailed table schema with columns, constraints, indexes, and relationships 8. **get_database_schema_info**: Database schema (namespace) information with objects, permissions, and statistics 9. **get_table_relationships**: Table relationship analysis with foreign key dependencies and cross-schema connections 10. **get_user_list**: Database user list and permissions ### ⚡ Performance Monitoring 11. **get_pg_stat_statements_top_queries**: Slow query analysis based on performance statistics 12. **get_pg_stat_monitor_recent_queries**: Real-time query monitoring 13. **get_index_usage_stats**: Index usage rate and efficiency analysis ### 💾 Capacity Management 14. **get_database_size_info**: Database capacity analysis 15. **get_table_size_info**: Table and index size analysis 16. **get_vacuum_analyze_stats**: VACUUM/ANALYZE status and maintenance history 17. **get_table_bloat_analysis**: Table bloat monitoring with dead tuple analysis 18. **get_database_bloat_overview**: Database-wide bloat summary by schema 19. **get_autovacuum_status**: Autovacuum configuration and trigger condition analysis 20. **get_autovacuum_activity**: Recent autovacuum activity patterns and execution history 21. **get_running_vacuum_operations**: Real-time monitoring of active VACUUM/ANALYZE operations 22. **get_vacuum_effectiveness_analysis**: VACUUM effectiveness and maintenance pattern analysis ### 🔒 Lock & Deadlock Monitoring 23. **get_lock_monitoring**: Current locks and blocked sessions analysis ### 📝 WAL & Replication Monitoring 24. **get_wal_status**: WAL status and archiving information 25. **get_replication_status**: Replication connections and lag monitoring ### 📈 Database Performance Statistics 26. **get_database_stats**: Comprehensive database-wide performance metrics 27. **get_bgwriter_stats**: Background writer and checkpoint performance analysis 28. **get_all_tables_stats**: Complete table statistics (including system tables) 29. **get_user_functions_stats**: User-defined function performance analysis ### 💿 I/O Performance Analysis 30. **get_table_io_stats**: Table I/O statistics (disk reads vs buffer cache hits) 31. **get_index_io_stats**: Index I/O performance and buffer efficiency analysis ### 🔄 Replication Monitoring 32. **get_database_conflicts_stats**: Query conflicts in standby/replica environments ## Sample Prompts ### � Database Performance Analysis - "Show database-wide performance statistics" - "Analyze transaction commit ratios and I/O patterns" - "Check buffer cache hit ratios for all databases" - "Monitor temporary file usage and deadlock counts" ### 🔧 Background Writer & Checkpoints - "Analyze checkpoint performance and timing" - "Show background writer efficiency statistics" - "Check buffer allocation and writing patterns" - "Monitor checkpoint scheduling vs requested ratios" ### 🔍 Server Health Check - "Check PostgreSQL server status." - "What database am I connected to?" - "Show current database connection details." - "Verify if extensions are installed." - "Show current active connection count." - "Display PostgreSQL version and configuration." ### 📊 Performance Analysis - "Show top 20 slowest queries." - "Find unused indexes." - "Analyze recent query activity." - "Identify performance bottlenecks." - "Show cache hit ratios for queries." ### 💾 Capacity Management - "Check database sizes." - "Find largest tables." - "Show tables that need VACUUM." - "Analyze disk usage by database." - "Display table and index sizes." - "Analyze table bloat and dead tuples." - "Show database-wide bloat summary." - "Check autovacuum status and trigger conditions." - "Monitor recent autovacuum activity patterns." - "Show currently running VACUUM operations." - "Analyze VACUUM effectiveness and maintenance patterns." ### 🗄️ Structure Analysis - "List all databases with owners." - "Show tables in public schema." - "Display user accounts and permissions." - "Explore database structure." ### 📈 Advanced Monitoring - "Monitor active sessions and queries." - "Analyze index usage efficiency." - "Check VACUUM and ANALYZE history." - "Review PostgreSQL configuration settings." - "Find memory-related configuration parameters." - "Show all logging configuration options." - "Search for connection-related settings." - "Identify connection patterns." ## Usage Guidelines ### When to Use Each Tool #### Server Information Tools - Use `get_server_info` first to verify connectivity and extensions - Use `get_active_connections` to check current load - Use `get_postgresql_config` for configuration analysis: - Specific parameter: `get_postgresql_config(config_name="shared_buffers")` - Keyword search: `get_postgresql_config(filter_text="memory")` for memory-related settings - Browse all: `get_postgresql_config()` without parameters #### Structure Exploration Tools - Use `get_database_list` to overview all databases - Use `get_table_list` to explore database structure - Use `get_table_schema_info` to analyze detailed table schema with columns, constraints, and indexes - Use `get_user_list` for user management overview #### Performance Analysis Tools - Use `get_pg_stat_statements_top_queries` for query optimization - Use `get_pg_stat_monitor_recent_queries` for real-time monitoring - Use `get_index_usage_stats` to identify inefficient indexes #### Capacity Management Tools - Use `get_database_size_info` for disk space planning - Use `get_table_size_info` for detailed table analysis - Use `get_vacuum_analyze_stats` for maintenance planning ### Best Practices 1. **Start with Server Info**: Always check server status first 2. **Use Limits**: Specify reasonable limits for query results 3. **Monitor Regularly**: Set up regular monitoring workflows 4. **Analyze Patterns**: Look for trends in performance data 5. **Document Findings**: Keep track of performance issues ## Tool Parameters ### Limit Parameters - Most tools accept `limit` parameter (default: 20, max: 100) - Use smaller limits for initial analysis - Increase limits for comprehensive reviews ### Database/Schema Parameters - `get_table_list(database_name)`: Specify target database - `get_table_schema_info(database_name, table_name, schema_name)`: **database_name is REQUIRED** - analyze specific table or all tables in schema - `get_database_schema_info(database_name, schema_name)`: **database_name is REQUIRED** - analyze specific schema or all schemas in database - `get_table_relationships(database_name, table_name, schema_name)`: **database_name is REQUIRED** - analyze table relationships (leave table_name empty for database-wide analysis) - `get_table_size_info(schema_name)`: Specify target schema - `get_postgresql_config(config_name, filter_text)`: Specify configuration parameter or search by keyword - `config_name`: Exact parameter name (optional) - `filter_text`: Search for parameters containing specific keywords (optional) ## Prerequisites ### Required Extensions ```sql -- Essential for query performance analysis CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Optional for advanced monitoring CREATE EXTENSION IF NOT EXISTS pg_stat_monitor; ``` ### Permissions - Read access to system catalogs - Connection to PostgreSQL database - Sufficient privileges for statistics views ## Troubleshooting Prompts ### Connection Issues - "Check PostgreSQL server connectivity." - "Verify connection parameters." - "Test database access permissions." ### Extension Problems - "Check if pg_stat_statements is installed." - "Verify pg_stat_monitor availability." - "Show installed extensions status." ### Performance Issues - "Analyze slow query performance." - "Check database load and connections." - "Review index usage efficiency." - "Monitor recent query patterns." ## Integration Examples ### Regular Health Checks 1. "Check server status and active connections." 2. "Show top 10 slowest queries from last hour." 3. "Verify all databases are accessible." 4. "Check if any tables need maintenance." ### Capacity Planning 1. "Analyze database sizes and growth trends." 2. "Identify largest tables and indexes." 3. "Review disk usage by schema." 4. "Plan storage capacity requirements." ### Performance Optimization 1. "Find queries consuming most resources." 2. "Identify unused or inefficient indexes." 3. "Analyze cache hit ratios." 4. "Monitor query execution patterns." ## Example Queries ### � Extension-Independent Tools (Always Available) **get_server_info** - "Show PostgreSQL server version and extension status." - "Check if pg_stat_statements is installed." - "Check server compatibility." - "Show server version and compatibility features." - "Check what MCP tools are available on this PostgreSQL version." - "Displays feature availability matrix and upgrade recommendations." **get_current_database_info** - "What database am I connected to?" - "Show current database information and properties." - "Display database encoding, collation, and connection limits." - "Get current database size and configuration details." - "Show connection context and database properties." **get_active_connections** - "Show all active connections." - "List current sessions with database and user." - "Monitor current sessions and their running queries." **get_postgresql_config** - "Show all PostgreSQL configuration parameters." - "Find all memory-related configuration settings." - "Show PostgreSQL configuration parameter for shared_buffers." - "Search for connection-related settings." - "Find logging configuration." - "Check specific parameter." **get_database_list** - "List all databases and their sizes." - "Show database list with owner information." - "List all databases with their owners and sizes." - "Show database encoding and connection limits." **get_table_list** - "List all tables in the ecommerce database." - "Show table sizes in the public schema." - "List all tables in the default database." - "Show tables in specific database." **get_table_schema_info** - "Show detailed schema information for the customers table in ecommerce database." - "Get column details and constraints for products table in ecommerce database." - "Analyze table structure with indexes and foreign keys for orders table in sales schema of ecommerce database." - "Show schema overview for all tables in public schema of inventory database." - "Get complete table structure including constraints and indexes for employees table in hr_system database." - "Display column information with data types and constraints for inventory_items table in inventory database." **get_database_schema_info** - "Show all schemas in ecommerce database with their contents." - "Get detailed information about sales schema in ecommerce database." - "Analyze schema structure and permissions for inventory database." - "Show schema overview with table counts and sizes for hr_system database." - "Display schema owners and access privileges for all schemas in ecommerce database." - "Get comprehensive schema statistics including object counts and sizes." **get_table_relationships** - "Show all relationships for customers table in ecommerce database." - "Analyze foreign key relationships for orders table in sales schema of ecommerce database." - "Get database-wide relationship overview for ecommerce database." - "Find all tables that reference products table in ecommerce database." - "Show cross-schema relationships in inventory database." - "Display all foreign key dependencies in hr_system database." - "Analyze table relationships including inbound and outbound foreign keys." - "Get complete relationship mapping for specific table with constraint details." **get_user_list** - "List all database users and their roles." - "Show user permissions for a specific database." - "Display all database users with their permissions." - "Show superuser status and account limitations for all users." **get_index_usage_stats** - "Analyze index usage efficiency." - "Find unused indexes in the current database." - "Analyze index usage in default database." - "Check index efficiency in specific database." - "Identify unused indexes with zero scans (look for 'Never used' entries)." **get_database_size_info** - "Show database capacity analysis." - "Find the largest databases by size." - "Show disk usage for all databases sorted by size." - "Calculate total storage consumption across all databases." **get_table_size_info** - "Show table and index size analysis." - "Find largest tables in a specific schema." - "Analyze table sizes in public schema." - "Check table sizes in specific database schema." **get_vacuum_analyze_stats** - "Show recent VACUUM and ANALYZE operations." - "List tables needing VACUUM." - "Review VACUUM and ANALYZE history for all tables." - "Check maintenance status in specific database." - "Find tables needing maintenance (check last_vacuum dates)." **get_table_bloat_analysis** - "Analyze table bloat in the public schema." - "Show tables with high dead tuple ratios in ecommerce database." - "Find tables requiring VACUUM maintenance." - "Check bloat for tables with more than 5000 dead tuples." - "Identify bloated tables in inventory database public schema." - "Show estimated bloat sizes and VACUUM recommendations." **get_database_bloat_overview** - "Show database-wide bloat summary by schema." - "Get bloat overview for inventory database." - "Identify schemas with highest bloat ratios." - "Database maintenance planning with bloat statistics." - "Compare bloat across all schemas in ecommerce database." - "Show total estimated bloat and schema sizes." **get_autovacuum_status** - "Check autovacuum configuration and trigger conditions." - "Show tables needing immediate autovacuum attention." - "Analyze autovacuum threshold percentages for all tables." - "Find tables approaching autovacuum trigger points in public schema." - "Check autovacuum status for log tables pattern." - "Show autovacuum urgency analysis for ecommerce database." - "Monitor tables with dead tuple ratios near autovacuum thresholds." **get_autovacuum_activity** - "Show autovacuum activity patterns for the last 48 hours." - "Monitor autovacuum execution frequency and timing." - "Find tables with irregular autovacuum patterns." - "Analyze recent autovacuum and autoanalyze history." - "Check autovacuum activity in public schema for last 7 days." - "Show tables with no recent autovacuum activity." - "Monitor autovacuum workload distribution across schemas." - "Identify tables with very high autovacuum frequency." **get_running_vacuum_operations** - "Show currently running VACUUM and ANALYZE operations." - "Monitor active maintenance operations and their progress." - "Check if any VACUUM operations are blocking queries." - "Find long-running maintenance operations in ecommerce database." - "Show real-time status of all running maintenance operations." - "Monitor VACUUM FULL operations and their impact level." **get_vacuum_effectiveness_analysis** - "Analyze VACUUM effectiveness and maintenance patterns." - "Compare manual VACUUM vs autovacuum efficiency." - "Find tables with suboptimal maintenance patterns in public schema." - "Check VACUUM frequency vs table activity ratios." - "Identify tables with poor maintenance effectiveness." - "Show maintenance pattern analysis for all schemas." - "Find tables that need better VACUUM scheduling." **get_lock_monitoring** - "Show all current locks and blocked sessions." - "Show only blocked sessions with granted=false filter." - "Monitor locks by specific user with username filter." - "Check exclusive locks with mode filter." **get_wal_status** - "Show WAL status and archiving information." - "Monitor WAL generation and current LSN position." **get_replication_status** - "Check replication connections and lag status." - "Monitor replication slots and WAL receiver status." - "Show replication status (version-compatible for PG12-18)." - "Check if replication is active or standby servers are connected." **get_database_stats** - "Show comprehensive database performance metrics." - "Analyze transaction commit ratios and I/O statistics." - "Monitor buffer cache hit ratios and temporary file usage." - "Show database-wide performance statistics." - "Analyze transaction commit ratios across all databases." - "Check buffer cache hit ratios and I/O statistics." - "Monitor temporary file usage and deadlock counts." **get_bgwriter_stats** - "Analyze checkpoint performance and timing." - "Show me checkpoint performance." - "Show background writer efficiency statistics." - "Monitor buffer allocation and fsync patterns." - "Show background writer efficiency and buffer statistics." - "Monitor checkpoint scheduling patterns." - "Check buffer allocation and fsync performance." - "Monitor checkpoint scheduling vs requested ratios." **get_all_tables_stats** - "Show comprehensive statistics for all tables." - "Include system tables with include_system=true parameter." - "Analyze table access patterns and maintenance needs." - "Show comprehensive statistics for all user tables (version-compatible for PG12-18)." - "Include system tables in statistics analysis with include_system=true." - "Monitor dead tuple ratios and table activity." - "Show insertions since vacuum statistics (PG13+ only)." **get_user_functions_stats** - "Analyze user-defined function performance." - "Show function call counts and execution times." - "Identify performance bottlenecks in custom functions." - "Identify slow or frequently called functions." - "Monitor function performance bottlenecks." - ⚠️ **Requires**: `track_functions = pl` in postgresql.conf **get_table_io_stats** - "Analyze table I/O performance and buffer hit ratios." - "Identify tables with poor buffer cache performance." - "Monitor TOAST table I/O statistics." - "Show disk reads vs buffer cache hits for tables in public schema." - 💡 **Enhanced with**: `track_io_timing = on` for accurate timing **get_index_io_stats** - "Show index I/O performance and buffer efficiency." - "Identify indexes causing excessive disk I/O." - "Monitor index cache-friendliness patterns." - "Analyze index buffer hit ratios in specific schema." - 💡 **Enhanced with**: `track_io_timing = on` for accurate timing **get_database_conflicts_stats** - "Check replication conflicts on standby servers." - "Analyze conflict types and resolution statistics." - "Monitor standby server query cancellation patterns." - "Check replication conflicts on standby server." - "Monitor replication slots and WAL receiver status." ### 🚀 Version-Aware Tools (Auto-Adapting) **get_io_stats** (New!) - "Show comprehensive I/O statistics." (PostgreSQL 16+ provides detailed breakdown) - "Analyze I/O statistics." - "Analyze buffer cache efficiency and I/O timing." - "Monitor I/O patterns by backend type and context." - 📈 **PG16+**: Full pg_stat_io with timing, backend types, and contexts - 📊 **PG12-15**: Basic pg_statio_* fallback with buffer hit ratios **get_bgwriter_stats** (Enhanced!) - "Show background writer and checkpoint performance." - 📈 **PG15**: Separate checkpointer and bgwriter statistics (unique feature) - 📊 **PG12-14, 16+**: Combined bgwriter stats (includes checkpointer data) **get_server_info** (Enhanced!) - "Show server version and compatibility features." - "Check server compatibility." - "Check what MCP tools are available on this PostgreSQL version." - "Displays feature availability matrix and upgrade recommendations." ### 🟡 Extension-Dependent Tools **get_pg_stat_statements_top_queries** (Requires `pg_stat_statements`) - "Show top 10 slowest queries." - "Analyze slow queries in the ecommerce database." - "Show top 20 slowest queries." - "Find queries consuming most resources." - "Monitor query execution patterns." - "Show cache hit ratios for queries." - 📈 **Version-Compatible**: Automatically adapts for PostgreSQL 12-17 (PG12: total_time→total_exec_time mapping) **get_pg_stat_monitor_recent_queries** (Optional, uses `pg_stat_monitor`) - "Show recent queries in real time." - "Monitor query activity for the last 5 minutes." - "Monitor recent 15 queries with detailed stats." - "Track recent queries in ecommerce database." - 📈 **Version-Compatible**: Automatically adapts for PostgreSQL 12-17 (PG12: total_time→total_exec_time mapping) ### 🔧 Advanced Usage Examples **Multi-Database Analysis** - "Compare table sizes across databases." - "Monitor performance across multiple databases using database_name parameter." - "Analyze slow queries in specific database." - "Check index efficiency in specific database." **Schema Analysis** - "Show detailed schema for products table with all constraints in ecommerce database." - "Analyze table relationships and foreign key dependencies for sales.orders table in ecommerce database." - "Get complete column information with data types for inventory_items table in inventory database." - "Show all indexes and constraints for employees table in hr_system database." - "Review schema structure for all tables in sales schema of ecommerce database." - "Compare table schemas across different databases." **Performance Deep Dive** - "Find unused indexes." - "Analyze recent query activity." - "Identify performance bottlenecks." - "Analyze cache hit ratios." - "Monitor query execution patterns." **Capacity Planning** - "Analyze database sizes and growth trends." - "Identify largest tables and indexes." - "Review disk usage by schema." - "Plan storage capacity requirements." **Configuration Troubleshooting** - "Search for connection-related settings." - "Find logging configuration." - "Check specific parameter." **💡 Pro Tip**: All tools support multi-database operations using the `database_name` parameter. This allows PostgreSQL superusers to analyze and monitor multiple databases from a single MCP server instance. This MCP server provides comprehensive PostgreSQL monitoring and management capabilities while maintaining read-only safety and providing detailed insights for database administration with automatic version compatibility across PostgreSQL 12-18.