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

> Get PostgreSQL insights with natural language queries. Ask questions like "Show top 20 slowest queries" and MCP-PostgreSQL-Ops translates them to SQL diagnostics. Explore your database easily.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/mcp_main.py), the server instance registers each diagnostic capability as a decorated async function using `@mcp.tool()`:

```python

# 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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/version_compat.py) module implements `VersionAwareQueries` to detect your server version and return appropriate SQL variants:

```python

# 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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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**:

```markdown

# 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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/version_compat.py), ensuring accurate metrics across different server capabilities.
- All tools are read-only and registered in [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py) to register new tools with the `@mcp.tool()` decorator, implementing custom SQL logic in [`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) while maintaining the same safe, read-only architecture.