[Tool Purpose]: Monitor current locks and potential deadlocks in PostgreSQL

[Exact Functionality]:

• List all current locks held and waited for by sessions

• Show blocked and blocking sessions, lock types, and wait status

• Help diagnose lock contention and deadlock risk

• Filter results by granted status, state, mode, lock type, or username

[Required Use Cases]:

• When user requests "lock monitoring", "deadlock check", "blocked sessions", etc.

• When diagnosing performance issues due to locking

• When checking for blocked or waiting queries

• When filtering specific types of locks or users

[Strictly Prohibited Use Cases]:

• Requests for killing sessions or force-unlocking

• Requests for lock configuration changes

• Requests for historical lock data (only current state is shown)

[Tool Purpose]: Monitor WAL (Write Ahead Log) status and statistics

[Exact Functionality]:

• Show current WAL location and LSN information

• Display WAL file generation rate and size statistics

• Monitor WAL archiving status and lag

• Provide WAL-related configuration and activity metrics

[Required Use Cases]:

• When user requests "WAL status", "WAL monitoring", "log shipping status", etc.

• When diagnosing replication lag or WAL archiving issues

• When monitoring database write activity and WAL generation

[Strictly Prohibited Use Cases]:

• Requests for WAL configuration changes

• Requests for manual WAL switching or archiving

• Requests for WAL file manipulation or cleanup

Returns: WAL status information including current LSN, WAL files, archiving status, and statistics

[Tool Purpose]: Monitor PostgreSQL replication status and statistics

[Exact Functionality]:

• Show current replication connections and their status

• Display replication lag information for standbys

• Monitor WAL sender and receiver processes

• Provide replication slot information and statistics

[Required Use Cases]:

• When user requests "replication status", "standby lag", "replication monitoring", etc.

• When diagnosing replication issues or performance problems

• When checking replication slot usage and lag

[Strictly Prohibited Use Cases]:

• Requests for replication configuration changes

• Requests for replication slot creation or deletion

• Requests for failover or switchover operations

Returns: Replication status including connections, lag information, slots, and statistics

[Tool Purpose]: Check basic information and connection status of PostgreSQL server

[Exact Functionality]:

• Retrieve PostgreSQL server version information

• Display connection settings (with password masking)

• Verify server accessibility

• Check installation status of extensions (pg_stat_statements, pg_stat_monitor)

[Required Use Cases]:

• When user requests "server info", "PostgreSQL status", "connection check", etc.

• When basic database server information is needed

• When preliminary check is needed before using monitoring tools

[Strictly Prohibited Use Cases]:

• Requests for specific data or table information

• Requests for performance statistics or monitoring data

• Requests for configuration changes or administrative tasks

Returns: Comprehensive information including server version, connection info, and extension status

[Tool Purpose]: Get information about the current database connection

[Exact Functionality]:

• Show the name of the currently connected database

• Display database-specific information like encoding, locale, and size

• Provide connection context for clarity in multi-database environments

[Required Use Cases]:

• When user asks "what database am I connected to?", "current database", etc.

• When clarifying database context for analysis operations

• When troubleshooting connection issues or confirming target database

[Strictly Prohibited Use Cases]:

• Requests for database structure changes or creation/deletion

• Requests for user authentication or permission changes

• Requests for configuration modifications

[Tool Purpose]: Retrieve list of all databases and their basic information on PostgreSQL server

[Exact Functionality]:

• Retrieve list of all databases on the server

• Display owner, encoding, and size information for each database

• Include database connection limit information

[Required Use Cases]:

• When user requests "database list", "DB list", "database info", etc.

• When need to check what databases exist on the server

• When database size or owner information is needed

[Strictly Prohibited Use Cases]:

• Requests for tables or schemas inside specific databases

• Requests for database creation or deletion

• Requests related to user permissions or security

Returns: Table-format information including database name, owner, encoding, size, and connection limit

[Tool Purpose]: Retrieve list of all tables and their information from specified database (or current DB)

[Exact Functionality]:

• Retrieve list of all tables in specified database

• Display schema, owner, and size information for each table

• Distinguish table types (regular tables, views, etc.)

[Required Use Cases]:

• When user requests "table list", "table listing", "schema info", etc.

• When need to understand structure of specific database

• When table size or owner information is needed

[Strictly Prohibited Use Cases]:

• Requests for data inside tables

• Requests for table structure changes or creation/deletion

• Requests for detailed column information of specific tables

[Tool Purpose]: Retrieve list of all user accounts and permission information on PostgreSQL server

[Exact Functionality]:

• Retrieve list of all database user accounts

• Display permission information for each user (superuser, database creation rights, etc.)

• Include account creation date and expiration date information

[Required Use Cases]:

• When user requests "user list", "account info", "permission check", etc.

• When user permission management or security inspection is needed

• When account status overview is needed

[Strictly Prohibited Use Cases]:

• Requests for user password information

• Requests for user creation, deletion, or permission changes

• Requests for specific user sessions or activity history

Returns: Table-format information including username, superuser status, permissions, and account status

[Tool Purpose]: Retrieve detailed schema information for specific table or all tables in a database

[Exact Functionality]:

• Retrieve detailed column information including data types, constraints, defaults

• Display primary keys, foreign keys, indexes, and other table constraints

• Show table-level metadata such as size, row count estimates

[Required Use Cases]:

• When user requests "table schema", "column info", "table structure", etc.

• When detailed table design information is needed for development

• When analyzing database structure and relationships

[Strictly Prohibited Use Cases]:

• Requests for actual data inside tables

• Requests for table structure changes or DDL operations

• Requests for performance statistics (use other tools for that)

[Tool Purpose]: Retrieve detailed information about database schemas (namespaces) and their contents

[Exact Functionality]:

• Show all schemas in a database with their owners and permissions

• Display schema-level statistics including table count and total size

• List all objects (tables, views, functions) within specific schema

• Show schema access privileges and usage patterns

[Required Use Cases]:

• When user requests "database schema info", "schema overview", "namespace structure", etc.

• When analyzing database organization and schema-level permissions

• When exploring multi-schema database architecture

[Strictly Prohibited Use Cases]:

• Requests for actual data inside tables

• Requests for schema structure changes or DDL operations

• Requests for individual table details (use get_table_schema_info for that)

[Tool Purpose]: Analyze table relationships including foreign keys, dependencies, and inheritance

[Exact Functionality]:

• Show foreign key relationships (inbound and outbound)

• Display view dependencies and table references

• Analyze inheritance and partition relationships

• Identify orphaned tables and relationship patterns

[Required Use Cases]:

• When user requests "table relationships", "foreign keys", "dependencies", etc.

• When analyzing database schema design and data model

• When planning data migration or schema changes

[Strictly Prohibited Use Cases]:

• Requests for actual data inside tables

• Requests for relationship modifications or DDL operations

• Requests for performance statistics (use other tools for that)

[Tool Purpose]: Retrieve all active connections and session information on current PostgreSQL server

[Exact Functionality]:

• Retrieve list of all currently active connected sessions

• Display user, database, and client address for each connection

• Include session status and currently executing query information

[Required Use Cases]:

• When user requests "active connections", "current sessions", "connection status", etc.

• When server load or performance problem diagnosis is needed

• When checking connection status of specific users or applications

[Strictly Prohibited Use Cases]:

• Requests for forceful connection termination or session management

• Requests for detailed query history of specific sessions

• Requests for connection security or authentication-related changes

Returns: Information including PID, username, database name, client address, status, and current query

[Tool Purpose]: Analyze top queries that consumed the most time using pg_stat_statements extension

[Exact Functionality]:

• Retrieve top query list based on total execution time

• Display call count, average execution time, and cache hit rate for each query

• Support identification of queries requiring performance optimization

[Required Use Cases]:

• When user requests "slow queries", "performance analysis", "top queries", etc.

• When database performance optimization is needed

• When query performance monitoring or tuning is required

[Strictly Prohibited Use Cases]:

• When pg_stat_statements extension is not installed

• Requests for query execution or data modification

• Requests for statistics data reset or configuration changes

[Tool Purpose]: Analyze recently executed queries and detailed monitoring information using pg_stat_monitor extension

[Exact Functionality]:

• Retrieve detailed performance information of recently executed queries

• Display client IP and time bucket information by execution period

• Provide more detailed monitoring data than pg_stat_statements

[Required Use Cases]:

• When user requests "recent queries", "detailed monitoring", "pg_stat_monitor", etc.

• When real-time query performance monitoring is needed

• When client-specific or time-based query analysis is required

[Strictly Prohibited Use Cases]:

• When pg_stat_monitor extension is not installed

• Requests for query execution or data modification

• Requests for monitoring configuration changes or data reset

[Tool Purpose]: Analyze size information and storage usage status of all databases in PostgreSQL server

[Exact Functionality]:

• Retrieve disk usage for each database

• Analyze overall server storage usage status

• Provide database list sorted by size

[Required Use Cases]:

• When user requests "database size", "disk usage", "storage space", etc.

• When capacity management or cleanup is needed

• When resource usage status by database needs to be identified

[Strictly Prohibited Use Cases]:

• Requests for data deletion or cleanup operations

• Requests for storage configuration changes

• Requests related to backup or restore

Returns: Table-format information with database names and size information sorted by size

[Tool Purpose]: Analyze size information and index usage of all tables in specified schema

[Exact Functionality]:

• Retrieve size information of all tables within schema

• Analyze index size and total size per table

• Provide table list sorted by size

[Required Use Cases]:

• When user requests "table size", "schema capacity", "index usage", etc.

• When storage analysis of specific schema is needed

• When resource usage status per table needs to be identified

[Strictly Prohibited Use Cases]:

• Requests for table data deletion or cleanup operations

• Requests for index creation or deletion

• Requests for table structure changes

[Tool Purpose]: Retrieve and analyze PostgreSQL server configuration parameter values

[Exact Functionality]:

• Retrieve all PostgreSQL configuration parameters (when config_name is not specified)

• Retrieve current value and description of specific configuration parameter

• Filter configurations by text pattern (when filter_text is specified)

• Display whether configuration can be changed and if restart is required

[Required Use Cases]:

• When user requests "PostgreSQL config", "config", "parameters", etc.

• When checking specific configuration values is needed

• When searching for configurations containing specific text

• When configuration status identification is needed for performance tuning

[Strictly Prohibited Use Cases]:

• Requests for configuration value changes or modifications

• Requests for PostgreSQL restart or reload

• Requests for system-level configuration changes

[Tool Purpose]: Analyze usage rate and performance statistics of all indexes in database

[Exact Functionality]:

• Analyze usage frequency and efficiency of all indexes

• Identify unused indexes

• Provide scan count and tuple return statistics per index

[Required Use Cases]:

• When user requests "index usage rate", "index performance", "unnecessary indexes", etc.

• When database performance optimization is needed

• When index cleanup or reorganization is required

[Strictly Prohibited Use Cases]:

• Requests for index creation or deletion

• Requests for index reorganization or REINDEX execution

• Requests for statistics reset

[Tool Purpose]: Analyze VACUUM and ANALYZE execution history and statistics per table

[Exact Functionality]:

• Retrieve last VACUUM/ANALYZE execution time for each table

• Provide Auto VACUUM/ANALYZE execution count statistics

• Analyze table activity with tuple insert/update/delete statistics

[Required Use Cases]:

• When user requests "VACUUM status", "ANALYZE history", "table statistics", etc.

• When database maintenance status overview is needed

• When performance issues or statistics update status verification is required

[Strictly Prohibited Use Cases]:

• Requests for VACUUM or ANALYZE execution

• Requests for Auto VACUUM configuration changes

• Requests for forced statistics update

[Tool Purpose]: Analyze table bloat based on dead tuple statistics and size information

[Exact Functionality]:

• Calculate bloat ratio based on dead tuples vs total tuples

• Estimate bloat size in bytes and human-readable format

• Show last VACUUM/AUTOVACUUM timestamps for maintenance tracking

• Identify tables requiring VACUUM maintenance

• Filter tables by name pattern using SQL LIKE or ILIKE matching

• Sort results by bloat severity (dead tuple ratio and count)

[Required Use Cases]:

• When user requests "table bloat", "bloat analysis", "dead tuples", etc.

• When identifying tables that need VACUUM maintenance

• When investigating database storage efficiency and space usage

• When troubleshooting performance issues related to table bloat

• When analyzing specific table groups (e.g., tables with "user", "log", "temp" in names)

[Strictly Prohibited Use Cases]:

• Requests for automatic VACUUM execution

• Requests for bloat removal or cleanup operations

• Requests for table restructuring or data modification

[Tool Purpose]: Provide database-wide bloat overview and summary statistics

[Exact Functionality]:

• Summarize bloat statistics across all schemas

• Identify schemas and tables with highest bloat ratios

• Calculate total estimated bloat size per schema

• Show aggregate dead tuple counts and maintenance status

[Required Use Cases]:

• When user requests "database bloat overview", "bloat summary", etc.

• When getting high-level view of database storage efficiency

• When planning database maintenance activities

• When investigating overall database performance issues

[Strictly Prohibited Use Cases]:

• Requests for automatic maintenance operations

• Requests for bloat cleanup or removal

• Requests for schema or database restructuring

[Tool Purpose]: Analyze autovacuum configuration and current maintenance status for tables

[Exact Functionality]:

• Analyze autovacuum trigger conditions based on dead tuple thresholds

• Calculate current dead tuple ratios vs autovacuum trigger points

• Show autovacuum configuration settings per table

• Identify tables requiring immediate autovacuum attention

• Estimate next autovacuum execution likelihood

[Required Use Cases]:

• When user requests "autovacuum status", "autovacuum configuration", "vacuum trigger analysis", etc.

• When planning autovacuum optimization and tuning

• When troubleshooting autovacuum performance issues

• When identifying tables with autovacuum problems

[Strictly Prohibited Use Cases]:

• Requests for autovacuum configuration changes

• Requests for manual VACUUM execution

• Requests for autovacuum process restart or control

[Tool Purpose]: Monitor recent autovacuum and autoanalyze activity patterns and execution history

[Exact Functionality]:

• Track recent autovacuum and autoanalyze execution patterns

• Analyze autovacuum frequency and timing intervals

• Show tables with most/least autovacuum activity

• Calculate average time between autovacuum executions

• Identify tables with irregular autovacuum patterns

[Required Use Cases]:

• When user requests "autovacuum activity", "autovacuum history", "vacuum patterns", etc.

• When monitoring autovacuum performance and effectiveness

• When troubleshooting autovacuum scheduling issues

• When analyzing autovacuum workload distribution

[Strictly Prohibited Use Cases]:

• Requests for autovacuum process control or restart

• Requests for autovacuum configuration modifications

• Requests for manual vacuum scheduling

[Tool Purpose]: Monitor currently running VACUUM and ANALYZE operations in real-time

[Exact Functionality]:

• Show all currently active VACUUM, ANALYZE, and REINDEX operations

• Display operation progress, elapsed time, and process details

• Identify blocking or long-running maintenance operations

• Provide process IDs for operation tracking

[Required Use Cases]:

• When user requests "running VACUUM", "active maintenance", "current VACUUM status", etc.

• When troubleshooting slow or stuck VACUUM operations

• When monitoring maintenance operation progress

• When identifying maintenance operations that may be affecting performance

[Strictly Prohibited Use Cases]:

• Requests for terminating or controlling VACUUM processes

• Requests for starting new VACUUM operations

• Requests for changing VACUUM parameters or configuration

[Tool Purpose]: Analyze VACUUM effectiveness and maintenance patterns using existing statistics

[Exact Functionality]:

• Compare manual VACUUM vs autovacuum effectiveness patterns

• Analyze VACUUM frequency vs table activity (DML operations)

• Identify tables with suboptimal VACUUM patterns

• Calculate maintenance efficiency ratios without performance impact

• Show VACUUM coverage analysis across all tables

[Required Use Cases]:

• When user requests "VACUUM effectiveness", "maintenance efficiency", "VACUUM analysis", etc.

• When planning manual VACUUM schedules or autovacuum tuning

• When identifying tables with poor maintenance patterns

• When analyzing overall database maintenance health

[Strictly Prohibited Use Cases]:

• Requests for VACUUM execution or scheduling

• Requests for autovacuum configuration changes

• Requests for maintenance operation control

[Tool Purpose]: Get comprehensive database-wide statistics and performance metrics

[Exact Functionality]:

• Show database-wide transaction statistics (commits, rollbacks)

• Display block I/O statistics (disk reads vs buffer hits)

• Provide tuple operation statistics (returned, fetched, inserted, updated, deleted)

• Show temporary file usage and deadlock counts

• Include checksum failure information and I/O timing data

[Required Use Cases]:

• When user requests "database statistics", "database performance", "transaction stats", etc.

• When analyzing overall database performance and activity

• When investigating I/O performance or buffer cache efficiency

• When checking for deadlocks or temporary file usage

[Strictly Prohibited Use Cases]:

• Requests for statistics reset or modification

• Requests for database configuration changes

• Requests for performance tuning actions

Returns: Comprehensive database statistics including transactions, I/O, tuples, and performance metrics

[Tool Purpose]: Analyze background writer and checkpoint performance statistics with version compatibility

[Exact Functionality]:

• Show checkpoint execution statistics (timed vs requested)

• Display checkpoint timing information (write and sync times)

• Provide buffer writing statistics by different processes

• Analyze background writer performance and efficiency

• Automatically adapts to PostgreSQL version (17+ uses separate checkpointer view)

[Required Use Cases]:

• When user requests "checkpoint stats", "bgwriter performance", "buffer stats", etc.

• When analyzing I/O performance and checkpoint impact

• When investigating background writer efficiency

• When troubleshooting checkpoint-related performance issues

[Strictly Prohibited Use Cases]:

• Requests for checkpoint execution or configuration changes

• Requests for background writer parameter modifications

• Requests for statistics reset

Returns: Background writer and checkpoint performance statistics with version-appropriate data

[Tool Purpose]: Analyze comprehensive I/O statistics across all database operations with version compatibility

[Exact Functionality]:

• PostgreSQL 16+: Shows detailed I/O statistics from pg_stat_io (reads, writes, hits, timing)

• PostgreSQL 12-15: Falls back to pg_statio_* views with basic I/O information

• Provides buffer cache efficiency analysis and I/O timing when available

• Identifies I/O patterns and performance bottlenecks

[Required Use Cases]:

• When user requests "I/O stats", "I/O performance", "buffer cache analysis", etc.

• When analyzing storage performance and buffer efficiency

• When identifying I/O bottlenecks across different backend types

• When comparing I/O patterns between relation types

[Strictly Prohibited Use Cases]:

• Requests for I/O configuration changes or buffer tuning

• Requests for storage or filesystem modifications

• Requests for I/O statistics reset

[Tool Purpose]: Analyze I/O performance statistics for tables (disk reads vs buffer cache hits)

[Exact Functionality]:

• Show heap, index, and TOAST table I/O statistics

• Calculate buffer hit ratios for performance analysis

• Identify tables with poor buffer cache performance

• Provide detailed I/O breakdown by table component

[Required Use Cases]:

• When user requests "table I/O stats", "buffer performance", "disk vs cache", etc.

• When analyzing table-level I/O performance

• When identifying tables causing excessive disk I/O

• When optimizing buffer cache efficiency

[Strictly Prohibited Use Cases]:

• Requests for I/O optimization actions

• Requests for buffer cache configuration changes

• Requests for statistics reset

[Tool Purpose]: Analyze I/O performance statistics for indexes (disk reads vs buffer cache hits)

[Exact Functionality]:

• Show index-level I/O statistics and buffer hit ratios

• Identify indexes with poor buffer cache performance

• Provide detailed I/O performance metrics per index

• Help optimize index and buffer cache usage

[Required Use Cases]:

• When user requests "index I/O stats", "index buffer performance", etc.

• When analyzing index-level I/O performance

• When identifying indexes causing excessive disk I/O

• When optimizing index buffer cache efficiency

[Strictly Prohibited Use Cases]:

• Requests for index optimization actions

• Requests for buffer cache configuration changes

• Requests for statistics reset

[Tool Purpose]: Get comprehensive statistics for all tables (including system tables if requested)

[Exact Functionality]:

• Show detailed access statistics for all tables in database

• Include sequential scans, index scans, and tuple operations

• Provide live/dead tuple estimates and maintenance history

• Option to include system catalog tables

[Required Use Cases]:

• When user requests "all tables stats", "complete table statistics", etc.

• When analyzing overall table usage patterns

• When investigating table maintenance needs across the database

• When getting comprehensive database activity overview

[Strictly Prohibited Use Cases]:

• Requests for table maintenance operations (VACUUM, ANALYZE)

• Requests for statistics reset or modification

• Requests for table optimization actions

[Tool Purpose]: Analyze performance statistics for user-defined functions

[Exact Functionality]:

• Show execution count and timing statistics for user functions

• Calculate average execution time per function call

• Identify performance bottlenecks in user-defined functions

• Provide total and self execution time breakdown

[Required Use Cases]:

• When user requests "function stats", "function performance", etc.

• When analyzing user-defined function performance

• When identifying slow or frequently called functions

• When optimizing application function usage

[Strictly Prohibited Use Cases]:

• Requests for function modification or optimization

• Requests for statistics reset

• Requests for function execution or testing

[Tool Purpose]: Analyze query conflicts in standby/replica database environments

[Exact Functionality]:

• Show conflict statistics for standby servers (only relevant on replicas)

• Display conflicts by type (tablespace, lock, snapshot, bufferpin, deadlock)

• Help diagnose replication-related performance issues

• Provide conflict resolution statistics

[Required Use Cases]:

• When user requests "replication conflicts", "standby conflicts", etc.

• When analyzing replica server performance issues

• When troubleshooting replication lag or conflicts

• When monitoring standby server health

[Strictly Prohibited Use Cases]:

• Requests for conflict resolution actions

• Requests for replication configuration changes

• Requests for statistics reset

[Tool Purpose]: List available wait event types and their descriptions (PostgreSQL 17+)

[Exact Functionality]:

• Show all wait event types with human-readable descriptions from pg_wait_events catalog

• Correlate with current active sessions waiting on events

• Filter by specific wait event type

• Falls back to basic pg_stat_activity wait info on PG < 17

[Required Use Cases]:

• When user requests "wait events", "wait event descriptions", "what are sessions waiting on"

• When diagnosing session waits and understanding wait event meanings

• When analyzing wait patterns across the database

[Strictly Prohibited Use Cases]:

• Requests for session termination or wait resolution

• Requests for configuration changes

[Tool Purpose]: Monitor WAL summarizer status for incremental backup support (PostgreSQL 17+)

[Exact Functionality]:

• Show WAL summarizer process state and progress

• Display available WAL summaries for incremental backups

• Report summarize_wal configuration status

• Falls back to version notice on PG < 17

[Required Use Cases]:

• When user requests "WAL summarizer", "incremental backup status", "WAL summary"

• When checking incremental backup readiness

• When monitoring WAL summarization progress

[Strictly Prohibited Use Cases]:

• Requests for WAL summarizer configuration changes

• Requests for backup execution

[Tool Purpose]: Monitor asynchronous I/O subsystem status (PostgreSQL 18+)

[Exact Functionality]:

• Show active async I/O operations from pg_aios view

• Display I/O method configuration and concurrency settings

• Report async I/O file handle usage

• Falls back to I/O configuration info on PG < 18

[Required Use Cases]:

• When user requests "async I/O status", "AIO monitoring", "I/O subsystem"

• When investigating I/O performance and concurrency

• When checking async I/O configuration

[Strictly Prohibited Use Cases]:

• Requests for I/O configuration changes

• Requests for storage modifications

[Tool Purpose]: Analyze per-backend I/O and WAL statistics (PostgreSQL 18+)

Note: Uses pg_stat_get_backend_io() and pg_stat_get_backend_wal() which are new in PG 18.

[Exact Functionality]:

• Show I/O statistics broken down by individual backend process

• Display per-backend WAL generation statistics

• Identify backends with highest I/O activity

• Falls back to version notice on PG < 18

[Required Use Cases]:

• When user requests "per-backend I/O", "backend I/O stats", "which backend is doing most I/O"

• When diagnosing I/O hotspots at the process level

• When analyzing per-connection I/O patterns

[Strictly Prohibited Use Cases]:

• Requests for backend termination

• Requests for I/O configuration changes

Returns the MCP prompt template (full, headings, or specific section). Args: section: Section number or keyword (optional) mode: 'full', 'headings', or None (optional)