Natural Language Query Examples for PostgreSQL Database Insights with MCP-PostgreSQL-Ops

You can retrieve deep PostgreSQL insights by asking plain English questions like "Show top 20 slowest queries" or "Which indexes are unused?" and the MCP-PostgreSQL-Ops server will automatically translate these into version-aware SQL diagnostics.

The MCP-PostgreSQL-Ops repository provides a read-only Machine-Centric Prompt (MCP) server that bridges conversational AI and PostgreSQL operations. By mapping natural language query examples to specialized database tools, it enables safe, production-grade database inspection without writing manual SQL. The server supports PostgreSQL versions 12 through 17, automatically adapting queries to match your specific server capabilities.

How Natural Language Queries Work in MCP-PostgreSQL-Ops

The architecture converts conversational prompts into executable database operations through three integrated layers. When you submit a natural language query, the system parses your intent, selects the appropriate tool, and returns formatted results as Markdown tables.

The FastMCP Architecture

The server builds on the FastMCP framework from the fastmcp library. In src/mcp_postgresql_ops/mcp_main.py, the server instance registers each diagnostic capability as a decorated async function using @mcp.tool():

# From src/mcp_postgresql_ops/mcp_main.py

@mcp.tool()
async def get_server_info(ctx: Context) -> str:
    """Get PostgreSQL server information"""
    # Implementation returns version, extensions, and connection details

This registration pattern exposes database functions as natural language-accessible endpoints that AI assistants can invoke based on conversational context.

Version-Aware Query Adaptation

PostgreSQL features vary significantly between versions 12 and 17. The src/mcp_postgresql_ops/version_compat.py module implements VersionAwareQueries to detect your server version and return appropriate SQL variants:

# Conceptual flow from version_compat.py

version = await get_postgresql_version(conn)
if version >= 16:
    # Use pg_stat_io for I/O statistics

    query = "SELECT * FROM pg_stat_io ..."
else:
    # Fallback to pg_statio_user_tables

    query = "SELECT * FROM pg_statio_user_tables ..."

This ensures that natural language queries like "Show I/O stats" work correctly whether you run PostgreSQL 12 or the latest PostgreSQL 17 release.

Natural Language Query Examples by Category

The src/mcp_postgresql_ops/prompt_template.md file defines the mapping between conversational phrases and specific tool invocations. Below are production-ready natural language query examples organized by the insights they provide.

Server Health and Configuration

Use these queries to verify server status and configuration parameters:

• "Show PostgreSQL server version and extension status"

  • Tool: get_server_info (defined in src/mcp_postgresql_ops/mcp_main.py lines 398-406)
  • Returns: Version string, active extensions including pg_stat_statements and pg_stat_monitor availability

• "Find all memory-related PostgreSQL parameters"

  • Tool: get_postgresql_config(filter_text='memory')
  • Returns: Configuration table filtered to show shared_buffers, work_mem, maintenance_work_mem, and related settings

Database and Schema Discovery

Explore database structure without writing catalog queries:

• "What database am I connected to?"

  • Tool: get_current_database_info (mcp_main.py lines 483-491)
  • Returns: Database name, size, encoding, and connection limits

• "List all databases with their owners and sizes"

  • Tool: get_database_list (mcp_main.py lines 558-566)
  • Returns: Complete inventory of all databases on the cluster

• "Show all tables in the finance database"

  • Tool: get_table_list(database_name='finance') (mcp_main.py lines 604-613)
  • Returns: Schema-qualified table names with owners and disk usage

• "Give me a schema overview for the public schema"

  • Tool: get_database_schema_info(database_name='mydb', schema_name='public') (mcp_main.py lines 1114-1129)
  • Returns: Summary of tables, views, functions, and total size per schema

• "Show the full schema for the orders table"

  • Tool: get_table_schema_info(database_name='mydb', table_name='orders') (mcp_main.py lines 706-718)
  • Returns: Column definitions, constraints, indexes, sizes, and row estimates

• "What foreign key relationships does the orders table have?"

  • Tool: get_table_relationships(database_name='mydb', table_name='orders') (mcp_main.py lines 1225-1235)
  • Returns: Inbound and outbound foreign key links with referenced tables

Performance and Query Analysis

Diagnose bottlenecks using natural language:

• "Show top 20 slowest queries"

  • Tool: get_pg_stat_statements_top_queries (implemented via get_pg_stat_statements_data in functions.py lines 91-100)
  • Returns: Ranked list by total execution time, with version-aware column selection for PostgreSQL 12-17

• "Show recent queries from the last 5 minutes"

  • Tool: get_pg_stat_monitor_recent_queries (via get_pg_stat_monitor_data in functions.py lines 102-110)
  • Returns: Recent activity using pg_stat_monitor if installed, with graceful fallback

• "Find unused indexes in the current database"

  • Tool: get_index_usage_stats (declared in prompt template)
  • Returns: Indexes with idx_scan = 0 from pg_stat_user_indexes, indicating candidates for removal

• "Show I/O stats for all tables"

  • Tool: get_table_io_stats (adapted via VersionAwareQueries.get_io_stats in version_compat.py lines 47-55)
  • Returns: Reads, writes, and buffer hits per table, using pg_stat_io on PostgreSQL 16+ or legacy pg_statio_* views on older versions

Replication and WAL Monitoring

Monitor high availability infrastructure:

• "Analyze checkpoint performance and timing"

  • Tool: get_bgwriter_stats (uses VersionAwareQueries.get_bgwriter_checkpointer_stats in version_compat.py lines 18-30)
  • Returns: Background writer and checkpointer metrics, automatically splitting statistics on PostgreSQL 15+ where the checkpointer became a separate process

• "What is the current WAL generation rate?"

  • Tool: get_wal_status (mcp_main.py lines 237-250)
  • Returns: Current LSN, WAL generation in MB, server role (primary/standby), and archiver statistics

• "Show replication lag for all standby servers"

  • Tool: get_replication_status (mcp_main.py lines 315-332)
  • Returns: pg_stat_replication entries, replication slots, and WAL receiver status with lag calculations

Maintenance and Security

Manage storage and access control:

• "List all current locks where wait time > 5 seconds"

  • Tool: get_lock_monitoring (mcp_main.py lines 98-106)
  • Returns: Blocked sessions, lock types, owners, and wait events filtered by duration

• "Give me a list of all database users and their privileges"

  • Tool: get_user_list (mcp_main.py lines 558-566)
  • Returns: Usernames, superuser flags, create database/role rights, login capability, and connection limits

• "Show recent vacuum activity and tables that need vacuum"

  • Tool: get_vacuum_analyze_stats (prompt template)
  • Returns: last_vacuum, autovacuum_count, and tables with high dead tuple ratios from pg_stat_user_tables

• "Which databases are using the most disk space?"

  • Tool: get_database_size_info (prompt template)
  • Returns: Database sizes formatted with pg_size_pretty for capacity planning

Implementing Custom Natural Language Workflows

The src/mcp_postgresql_ops/prompt_template.md file defines the mapping between user phrases and tool invocations. When building custom queries, follow the pattern of specific entity + action + optional filter:

# Effective patterns from prompt_template.md

"Show [metric] for [entity]"
"List all [entities] with [attribute]"
"Find [condition] in [scope]"
"Analyze [process] performance"

For example, to check replication health specifically for streaming replicas, you would phrase: "Show replication lag for streaming standby servers" — the prompt template parser in mcp_main.py routes this to get_replication_status and applies the appropriate filters on pg_stat_replication.

Summary

• MCP-PostgreSQL-Ops exposes PostgreSQL diagnostics as natural language query examples through a FastMCP server architecture, enabling conversational database monitoring without manual SQL composition.
• The system automatically adapts queries for PostgreSQL versions 12 through 17 via version_compat.py, ensuring accurate metrics across different server capabilities.
• All tools are read-only and registered in mcp_main.py using @mcp.tool() decorators, providing safe production inspection of server health, schema metadata, query performance, replication status, and maintenance statistics.
• Natural language phrases map directly to specific tools—such as "Show top 20 slowest queries" invoking get_pg_stat_statements_top_queries—with results formatted as human-readable Markdown tables via functions.py.

Frequently Asked Questions

What PostgreSQL versions does MCP-PostgreSQL-Ops support?

The server supports PostgreSQL versions 12 through 17. The src/mcp_postgresql_ops/version_compat.py module detects your server version at runtime and supplies the correct SQL query variants for features that differ across versions, such as pg_stat_io (introduced in PostgreSQL 16) and the checkpointer process split (PostgreSQL 15+).

Is it safe to use these natural language queries on a production database?

Yes. All tools exposed by the MCP server are strictly read-only. The implementation in src/mcp_postgresql_ops/functions.py includes sanitize_connection_info to mask passwords in logs, and the SQL queries are restricted to system catalogs and statistics views. No INSERT, UPDATE, DELETE, or DDL operations are exposed through the natural language interface.

How does the server understand my natural language questions?

The mapping between conversational phrases and database tools is defined in src/mcp_postgresql_ops/prompt_template.md. When you ask a question like "Show replication lag for all standby servers," the FastMCP framework matches your phrasing to the get_replication_status tool registered in mcp_main.py. The tool then executes the appropriate version-aware SQL and returns formatted results.

Can I customize which natural language queries are available?

Yes. You can modify src/mcp_postgresql_ops/prompt_template.md to add new phrasing patterns or adjust existing ones to better match your team's terminology. Additionally, you can extend mcp_main.py to register new tools with the @mcp.tool() decorator, implementing custom SQL logic in functions.py while maintaining the same safe, read-only architecture.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}