PostgreSQL MCP Server

[
  {"schema_name": "public", "schema_owner": "postgres", "schema_type": "User Schema"},
  {"schema_name": "auth", "schema_owner": "supabase_admin", "schema_type": "User Schema"},
  {"schema_name": "pg_catalog", "schema_owner": "postgres", "schema_type": "System Schema"}
]

[
  {"schema": "public", "name": "users", "type": "BASE TABLE"},
  {"schema": "public", "name": "orders", "type": "BASE TABLE"},
  {"schema": "public", "name": "products", "type": "BASE TABLE"}
]

[
  {"name": "pg_stat_statements", "version": "1.10", "relocatable": false},
  {"name": "hypopg", "version": "1.4.0", "relocatable": true}
]

{
  "basic": {"schema": "public", "name": "users", "type": "table"},
  "columns": [
    {"column": "id", "data_type": "integer", "is_nullable": "NO", "default": "nextval('users_id_seq'::regclass)"},
    {"column": "email", "data_type": "character varying", "is_nullable": "NO", "default": null},
    {"column": "created_at", "data_type": "timestamp", "is_nullable": "YES", "default": "now()"}
  ],
  "constraints": [
    {"name": "users_pkey", "type": "PRIMARY KEY", "columns": ["id"]},
    {"name": "users_email_key", "type": "UNIQUE", "columns": ["email"]}
  ],
  "indexes": [
    {"name": "users_pkey", "definition": "CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)"},
    {"name": "users_email_idx", "definition": "CREATE INDEX users_email_idx ON public.users USING btree (email)"}
  ]
}

SELECT id, email, created_at
FROM users
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 10

[
  {"id": 1234, "email": "[email protected]", "created_at": "2025-01-03 14:23:45"},
  {"id": 1235, "email": "[email protected]", "created_at": "2025-01-03 10:15:22"}
]

QUERY PLAN:
Seq Scan on users  (cost=0.00..458.00 rows=100 width=532)
  Filter: (email = '[email protected]'::text)

{
  "sql": "SELECT * FROM users WHERE email = '[email protected]'",
  "hypothetical_indexes": [
    {"table": "users", "columns": ["email"], "using": "btree"}
  ]
}

QUERY PLAN (with hypothetical index):
Index Scan using <hypothetical_index> on users  (cost=0.29..8.31 rows=1 width=532)
  Index Cond: (email = '[email protected]'::text)

Cost reduced from 458.00 to 8.31 (98% improvement)

Top Resource-Intensive Queries:

1. Query: SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.created_at > $1
   Calls: 45,234
   Total Time: 1,234.56 ms
   Mean Time: 27.31 ms
   Rows Retrieved: 2,145,678

2. Query: SELECT COUNT(*) FROM products WHERE category = $1 AND price > $2
   Calls: 12,456
   Total Time: 892.34 ms
   Mean Time: 71.64 ms
   Rows Retrieved: 124,560

Workload Analysis Complete
Analyzed: 45 unique queries from pg_stat_statements

Recommended Indexes (5):

1. CREATE INDEX idx_users_email ON users (email);
   Estimated Impact: 87% faster queries
   Affected Queries: 12
   Size: 45 MB

2. CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
   Estimated Impact: 92% faster queries
   Affected Queries: 8
   Size: 128 MB

3. CREATE INDEX idx_products_category_price ON products (category, price);
   Estimated Impact: 76% faster queries
   Affected Queries: 6
   Size: 67 MB

Total Storage Required: 240 MB
Estimated Overall Performance Improvement: 73%

{
  "queries": [
    "SELECT * FROM users WHERE email = $1",
    "SELECT * FROM orders WHERE user_id = $1 AND created_at > $2",
    "SELECT COUNT(*) FROM products WHERE category = $1 AND price > $2"
  ],
  "max_index_size_mb": 5000,
  "method": "dta"
}

Query-Specific Index Recommendations

For Query 1: SELECT * FROM users WHERE email = $1
Recommendation: CREATE INDEX idx_users_email ON users (email);
Impact: Sequential scan → Index scan (98.2% faster)

For Query 2: SELECT * FROM orders WHERE user_id = $1 AND created_at > $2
Recommendation: CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
Impact: 345ms → 12ms (96.5% faster)

For Query 3: SELECT COUNT(*) FROM products WHERE category = $1 AND price > $2
Recommendation: CREATE INDEX idx_products_cat_price ON products (category, price);
Impact: Full table scan → Index-only scan (94.1% faster)

Total Storage: 156 MB
Overall Performance Gain: 96.3%

Database Health Report

✅ INDEX HEALTH
- Valid indexes: 47
- Invalid indexes: 1 (users_email_idx - needs rebuild)
- Duplicate indexes: 2 (consider removing)
- Bloated indexes: 3 (total waste: 456 MB)

✅ CONNECTION HEALTH
- Active connections: 45 / 100 (45%)
- Idle connections: 12
- Status: Healthy

⚠️ VACUUM HEALTH
- Last autovacuum: 3 days ago
- Transaction ID age: 1.2B (58% to wraparound)
- Recommendation: Schedule manual VACUUM soon

✅ BUFFER CACHE
- Table hit rate: 99.2% (Excellent)
- Index hit rate: 98.7% (Excellent)

⚠️ REPLICATION
- Replication lag: 234ms (Moderate)
- Replication slots: 2 active
- Status: Monitor closely

Overall Status: Healthy with 2 warnings

postgresql://postgres:[YOUR-PASSWORD]@db.xxxxxxxxxxxx.supabase.co:5432/postgres

CREATE EXTENSION IF NOT EXISTS hypopg;

postgresql://username:[email protected]/neondb

postgresql://username:[email protected]:5432/postgres

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS hypopg;

postgresql://username:[email protected]:5432/postgres

postgresql://username:[email protected]:25060/defaultdb?sslmode=require

postgresql://username@servername:[email protected]:5432/postgres?sslmode=require

postgresql://postgres:password@localhost:5432/postgres

-- Enable query statistics
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Enable hypothetical index simulation
CREATE EXTENSION IF NOT EXISTS hypopg;

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

docker run -d \
  --name postgres-mcp \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:16

postgresql://postgres:yourpassword@localhost:5432/mydb

docker exec -it postgres-mcp psql -U postgres -d mydb

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION hypopg;

# Ubuntu/Debian
sudo ufw allow 5432/tcp

# CentOS/RHEL
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

# Allow Studio connections with password
host    all    all    0.0.0.0/0    md5

# Test connection from command line
psql "postgresql://user:pass@host:5432/db"

# Check if server is reachable
telnet hostname 5432

"This query is timing out: SELECT * FROM large_table WHERE status = 'active'"
→ Server explains query, shows sequential scan
→ "What index would help?"
→ Server recommends: CREATE INDEX idx_large_table_status ON large_table(status)
→ Create index
→ Query now completes in <1 second

Seq Scan on users  (cost=0.00..458.00 rows=100 width=532)

Index Scan using users_email_idx on users  (cost=0.29..8.31 rows=1)