Version 1.1.0 - NEW Feature!

Transform your PostgreSQL MCP Server from a tool collection into an intelligent database assistant with 10 MCP Resources for real-time meta-awareness and 10 MCP Prompts for guided workflows.

MCP Resources provide the AI with instant, automatic access to your database's current state without requiring explicit queries. Think of them as "knowledge hooks" that keep the AI constantly aware of your database schema, capabilities, health, and performance.

• ✅ Zero Query Overhead - AI accesses metadata instantly
• ✅ Always Current - Real-time database state awareness
• ✅ Context-Aware - AI knows what's possible before suggesting actions
• ✅ Intelligent Recommendations - Based on actual database capabilities
• ✅ Reduced Errors - AI won't suggest unavailable features

URI: postgres://database/schema

Provides instant access to all tables, columns, data types, and relationships.

What the AI Sees:

{
 "tables": [
 {
 "schema": "public",
 "table": "users",
 "columns": [
 {"name": "id", "type": "integer", "nullable": false},
 {"name": "email", "type": "varchar(255)", "nullable": false},
 {"name": "created_at", "type": "timestamp", "nullable": true}
 ]
 }
 ]
}

Use Cases:

• AI automatically knows your schema when writing queries
• Suggests valid column names and types
• Understands table relationships

URI: postgres://database/capabilities

Lists available PostgreSQL extensions and features.

What the AI Sees:

{
 "extensions": {
 "pg_stat_statements": {"installed": true, "version": "1.10"},
 "pgvector": {"installed": true, "version": "0.8.0"},
 "postgis": {"installed": false},
 "hypopg": {"installed": true, "version": "1.4.1"}
 },
 "features": {
 "vector_search": true,
 "geospatial": false,
 "hypothetical_indexes": true
 }
}

Use Cases:

• AI only suggests features you have installed
• Automatically recommends extension installation if needed
• Tailors advice to your database's capabilities

URI: postgres://database/performance

Real-time query performance data from pg_stat_statements.

What the AI Sees:

{
 "top_queries": [
 {
 "query": "SELECT * FROM orders WHERE...",
 "calls": 15234,
 "mean_time_ms": 45.2,
 "total_time_ms": 688677.68
 }
 ],
 "cache_hit_ratio": 0.987,
 "slow_queries": 3
}

Use Cases:

• AI identifies performance bottlenecks automatically
• Prioritizes optimization suggestions
• Tracks improvement over time

URI: postgres://database/health

Comprehensive health status across indexes, connections, vacuum, and more.

What the AI Sees:

{
 "overall_status": "healthy",
 "issues": {
 "invalid_indexes": 0,
 "bloated_indexes": 2,
 "connection_usage": 0.45,
 "vacuum_health": "good"
 },
 "warnings": ["Index users_email_idx is bloated (45%)"]
}

Use Cases:

• AI proactively suggests maintenance
• Identifies issues before they become critical
• Prioritizes health interventions

URI: postgres://database/extensions

Detailed status of optional PostgreSQL extensions.

What the AI Sees:

{
 "pgvector": {
 "installed": true,
 "version": "0.8.0",
 "functions": ["vector_dims", "cosine_distance"]
 },
 "postgis": {
 "installed": false,
 "available_version": "3.5.0",
 "installation_guide": "CREATE EXTENSION postgis;"
 }
}

Use Cases:

• AI guides extension setup when needed
• Verifies compatibility before suggesting features
• Provides installation commands

URI: postgres://database/indexes

Index usage, size, and effectiveness metrics.

What the AI Sees:

{
 "indexes": [
 {
 "table": "users",
 "index": "users_email_idx",
 "size_mb": 12.5,
 "scans": 125430,
 "tuples_read": 125430,
 "efficiency": 1.0
 }
 ],
 "unused_indexes": ["old_idx_name"],
 "recommendations": ["Consider dropping unused indexes"]
}

Use Cases:

• AI recommends which indexes to create/drop
• Identifies duplicate or redundant indexes
• Optimizes index strategy

URI: postgres://database/connections

Active database connections and pool utilization.

What the AI Sees:

{
 "total_connections": 100,
 "active_connections": 45,
 "idle_connections": 55,
 "utilization": 0.45,
 "max_connections": 100,
 "status": "healthy"
}

Use Cases:

• AI warns about connection exhaustion
• Suggests connection pool tuning
• Identifies connection leaks

URI: postgres://database/replication

Replication lag and health for replicas.

What the AI Sees:

{
 "is_primary": true,
 "replicas": [
 {
 "application_name": "replica1",
 "state": "streaming",
 "lag_bytes": 2048,
 "lag_seconds": 0.5
 }
 ],
 "status": "healthy"
}

Use Cases:

• AI monitors replication health
• Alerts on excessive lag
• Suggests replication improvements

URI: postgres://database/vacuum

VACUUM and ANALYZE status for all tables.

What the AI Sees:

{
 "tables": [
 {
 "schema": "public",
 "table": "orders",
 "last_vacuum": "2025年10月04日 08:30:00",
 "last_autovacuum": "2025年10月04日 10:15:00",
 "dead_tuples": 1250,
 "live_tuples": 125000,
 "bloat_ratio": 0.01
 }
 ],
 "recommendations": ["Table logs needs manual VACUUM"]
}

Use Cases:

• AI suggests vacuum schedule optimization
• Identifies bloated tables
• Recommends maintenance windows

URI: postgres://database/locks

Current database locks and blocking queries.

What the AI Sees:

{
 "active_locks": 5,
 "blocking_queries": [
 {
 "blocked_pid": 12345,
 "blocking_pid": 12344,
 "blocked_query": "UPDATE users...",
 "blocking_query": "SELECT * FROM users FOR UPDATE",
 "duration": "00:00:45"
 }
 ],
 "status": "warning"
}

Use Cases:

• AI identifies lock contention
• Suggests query optimization to reduce locks
• Helps debug blocking queries

MCP Prompts are pre-built, guided workflows for complex database operations. They provide step-by-step instructions, tool calls, and best practices for common DBA tasks.

• ✅ Step-by-Step Guidance - No guessing, clear instructions
• ✅ Best Practices Built-In - Industry-standard approaches
• ✅ Reduced Errors - Validated workflows
• ✅ Knowledge Transfer - Learn while you work
• ✅ Time Savings - Don't reinvent the wheel

Prompt: optimize-query

Comprehensive query optimization workflow with EXPLAIN analysis, index recommendations, and rewrite suggestions.

What It Does:

1. Analyzes query execution plan
2. Identifies slow operations (seq scans, nested loops)
3. Tests hypothetical indexes
4. Suggests query rewrites
5. Validates improvements

When to Use:

• Query is slower than expected
• High execution time in pg_stat_statements
• Need to optimize a critical query

Prompt: index-tuning

Systematic index analysis and recommendation workflow.

What It Does:

1. Analyzes current index usage
2. Identifies unused/duplicate indexes
3. Reviews table access patterns
4. Recommends new indexes
5. Estimates impact

When to Use:

• Table scans are slow
• Need to optimize workload
• Planning index strategy

Prompt: database-health-check

Complete database health assessment workflow.

What It Does:

1. Checks all health metrics
2. Analyzes vacuum status
3. Reviews connection pool
4. Examines index health
5. Provides prioritized action plan

When to Use:

• Regular maintenance
• Before production deployment
• Troubleshooting performance issues

Prompt: setup-pgvector

Complete pgvector installation and configuration guide.

What It Does:

1. Checks extension availability
2. Provides installation instructions
3. Creates sample vector table
4. Sets up HNSW index
5. Demonstrates similarity search

When to Use:

• Setting up semantic search
• Building AI/ML features
• Implementing vector embeddings

Prompt: setup-postgis

Complete PostGIS installation and configuration guide.

What It Does:

1. Checks extension availability
2. Provides installation instructions
3. Creates sample spatial table
4. Sets up GIST index
5. Demonstrates spatial queries

When to Use:

• Adding geospatial features
• Working with location data
• Building mapping applications

Prompt: jsonb-best-practices

Comprehensive guide to JSONB optimization.

What It Does:

1. Reviews JSONB usage patterns
2. Suggests appropriate indexes (GIN vs GiST)
3. Optimizes query patterns
4. Validates JSON structure
5. Security best practices

When to Use:

• Storing semi-structured data
• Optimizing JSON queries
• Planning JSONB schema

Prompt: performance-baseline

Establish baseline metrics for critical queries.

What It Does:

1. Identifies critical queries
2. Runs multiple iterations
3. Calculates statistical baselines
4. Sets up monitoring
5. Creates comparison framework

When to Use:

• Before major changes
• Establishing SLAs
• Performance regression testing

Prompt: backup-strategy

Comprehensive backup planning and validation.

What It Does:

1. Analyzes database size and change rate
2. Recommends backup schedule
3. Plans logical vs physical backup
4. Validates restore readiness
5. Estimates storage needs

When to Use:

• Planning backup strategy
• Compliance requirements
• Disaster recovery planning

Prompt: extension-setup

Step-by-step extension installation guide.

What It Does:

1. Lists available extensions
2. Checks compatibility
3. Provides installation commands
4. Verifies installation
5. Configures extension settings

When to Use:

• Adding new capabilities
• Following installation guides
• Troubleshooting extensions

Prompt: query-analysis

Deep dive into query behavior and optimization.

What It Does:

1. Runs EXPLAIN ANALYZE
2. Identifies bottlenecks
3. Analyzes statistics quality
4. Tests hypothetical scenarios
5. Provides optimization roadmap

When to Use:

• Complex query debugging
• Performance investigation
• Learning query optimization

Resources work automatically! Your AI assistant can access them anytime without explicit commands:

# AI automatically checks capabilities before suggesting features
User: "I need semantic search on my documents table"
AI: *checks postgres://database/capabilities*
AI: "I see you have pgvector 0.8.0 installed. Let me help you set up vector search..."

Invoke prompts by name when you need guided workflows:

# User explicitly requests a prompt
User: "I want to optimize this slow query"
AI: "Let me use the optimize-query prompt to guide you through this..."
AI: *invokes optimize-query prompt*
AI: "Step 1: Let's analyze the execution plan..."

• Let the AI access them automatically
• Resources are always current, no need to refresh
• AI will reference them in recommendations

• For complex multi-step operations
• When you need best-practice guidance
• To learn proper procedures
• For consistent, repeatable workflows

The real power comes from using them together:

1. Resource tells AI what's possible
2. Prompt guides AI through the implementation
3. Resource validates the result

Example:

AI checks database/capabilities (Resource)
→ Sees pgvector is installed
→ User asks for semantic search setup
→ AI invokes setup-pgvector (Prompt)
→ AI verifies installation with database/extensions (Resource)

Resources and prompts are enabled by default in v1.1.0+. No configuration needed!

Verify Resources Are Available:

# In your MCP client, resources should appear automatically
# Claude Desktop: Resources tab
# Cursor: MCP panel

List Available Prompts:

# Prompts appear in your MCP client's prompt library
# Look for names like "optimize-query", "index-tuning", etc.

• Minimal Overhead - Cached and refreshed intelligently
• Query Reduction - 50-70% fewer metadata queries
• Faster Responses - AI has instant context

• Consistency - Same workflow every time
• Error Reduction - Best practices built-in
• Learning Curve - Faster onboarding for new users

Without Resources/Prompts:

Dev: "What tables do I have?"
AI: "Let me query that..." → execute_sql()
Dev: "Do I have pgvector?"
AI: "Let me check..." → list_objects()
Dev: "How do I set it up?"
AI: *provides generic instructions*

With Resources/Prompts:

AI: *Already knows schema, capabilities via Resources*
AI: "I see you have users, orders, and products tables."
AI: "You have pgvector 0.8.0 installed."
AI: "Let me guide you through setup..." *invokes setup-pgvector Prompt*

Without Resources/Prompts:

User: "This query is slow"
AI: "Let me check..." → multiple tool calls
AI: *analyzes piece by piece*
AI: *may miss context*

With Resources/Prompts:

AI: *Checks performance Resource*
AI: "I see this query has been called 15K times with 45ms mean time."
AI: *Invokes optimize-query Prompt*
AI: "Following the optimization workflow..."
AI: *Systematic, complete analysis*

docker pull writenotenow/postgres-mcp-enhanced:v1.1.0
# or
pip install --upgrade postgres-mcp-enhanced

# In your MCP client
# Resources should appear automatically
# Look for "postgres://database/*" URIs

User: "Can you help me optimize my slow query?"
AI: *Automatically invokes optimize-query prompt*

• Quick Start - Get running in 30 seconds
• Core Database Tools - Understanding the tool layer
• Performance Intelligence - Deep dive into optimization
• Security Best Practices - Secure usage patterns

1. Verify you're on v1.1.0+:

   postgres-mcp --version

2. Check MCP client compatibility:

   • Claude Desktop: v0.7.0+
   • Cursor: Latest version

3. Restart MCP server and client

1. Ensure DATABASE_URI is set correctly
2. Verify database connectivity
3. Check that required extensions are installed

Resources refresh automatically, but you can force refresh:

# Resources update on:
# - Schema changes (detected automatically)
# - Performance metrics (every 60 seconds)
# - Health checks (every 5 minutes)

"Resources saved us from writing 100+ lines of schema exploration code. The AI just knows our database structure instantly!" - Enterprise User

"The optimize-query prompt walks us through proper analysis every time. No more guessing!" - DBA Team

"We onboard new developers 3x faster now. They just ask questions and the AI guides them with real context." - Startup CTO

Version 1.1.0 - AI-Native Intelligence Release - October 4, 2025