Claude Code is where Postgres MCP earns its keep fastest, because the CLI's agentic loop already does multi-step tool calling well — it'll list schemas, drill into a table, run an EXPLAIN, and adjust its query in one continuous conversation without you re-pasting context at each step. This topic walks through wiring crystaldba/postgres-mcp into Claude Code (CLI and the VS Code extension share the same mcp.json), then three realistic workflows: mapping an unfamiliar schema, generating validated SQL from natural language, and diagnosing a slow query from real EXPLAIN ANALYZE output.
Installing and Connecting Postgres MCP to Claude Code
Add the server with claude mcp add, scoping it to the project so the connection string lives in project config rather than your global user settings:
claude mcp add postgres --scope project -- \
uvx postgres-mcp --access-mode=restricted "$POSTGRES_MCP_URI"
That writes to .mcp.json in the project root:
{
"mcpServers": {
"postgres": {
"command": "uvx",
"args": [
"postgres-mcp",
"--access-mode=restricted",
"${POSTGRES_MCP_URI}"
]
}
}
}
Set POSTGRES_MCP_URI in your shell profile or a project-local .env that's git-ignored — never commit the literal connection string. .mcp.json is meant to be checked in so teammates get the same server automatically, which is exactly why the URI itself has to live outside it.
export POSTGRES_MCP_URI="postgresql://mcp_agent:CHANGE_ME@localhost:5432/app_dev"
Verify the connection before doing anything else:
claude mcp list
The VS Code extension reads the same .mcp.json — no separate config. Open the Claude panel, and under MCP servers you should see postgres listed with the same connected state. If it shows disconnected in VS Code but works from the CLI, it's almost always because VS Code launched with a different shell environment and never picked up POSTGRES_MCP_URI — set it in a .vscode/settings.json terminal.integrated.env block or switch to an absolute connection string in .mcp.json for local dev.
Tips
-claude mcp add --scope projectover--scope userfor anything with a real connection string — you want it tied to the repo, not silently active in unrelated projects.
- Runclaude mcp listafter every.mcp.jsonchange; a malformed JSON server entry fails silently in some client versions rather than erroring loudly.
- If VS Code and CLI disagree on connection status, it's an environment-variable propagation issue nine times out of ten, not an actual server problem.
Letting the Agent Map an Unknown Schema Before Writing Application Code
The highest-value habit to build: before asking Claude Code to write a feature against a database you didn't design, make it map the relevant tables first and state its understanding back to you. This catches wrong assumptions before they're baked into code.
Prompt:
Using the postgres MCP tools, list all tables in the public schema, then
get full details (columns, types, FKs, indexes) for orders, order_items,
and inventory_reservations. Summarize how these three relate, and flag
anything that looks like a modeling gotcha (nullable FKs, soft deletes,
enum-like text columns, etc.) before we write any code against them.
A real response pattern I see from this prompt on a mid-size e-commerce schema:
- orders.customer_id -> customers.id (FK, NOT NULL)
- order_items.order_id -> orders.id (FK, NOT NULL, ON DELETE CASCADE)
- inventory_reservations.order_item_id -> order_items.id (FK, nullable!)
Gotchas:
1. orders.status is `text`, not an enum or FK to a lookup table — values
observed via a quick SELECT DISTINCT: 'pending', 'paid', 'Paid',
'shipped', 'cancelled', 'CANCELLED'. Inconsistent casing in production
data; any code doing exact string matching on status will miss rows.
2. inventory_reservations.order_item_id is nullable, meaning reservations
can exist unlinked to an order item — likely used for manual/admin
holds. Confirm before assuming every reservation traces to an order.
3. order_items has no unique constraint on (order_id, product_id) —
duplicate line items for the same product on one order are possible
at the schema level even if the app logic prevents it.
That third point — an app-level invariant with no database-level backing — is exactly the kind of thing a static schema dump won't surface unless you also inspect indexes and constraints live, which is what get_object_details gives you and a stale ERD diagram doesn't.
Tool call the agent makes underneath this prompt:
get_object_details(schema_name="public", object_type="table", object_name="orders")
get_object_details(schema_name="public", object_type="table", object_name="order_items")
Tips
- Explicitly ask for "modeling gotchas" — without that framing the agent tends to just restate the DDL back to you, which you could've read yourself.
- For schemas over ~50 tables, name the specific tables relevant to the current task rather than asking it to map everything — full-schema mapping burns a lot of context for tables you won't touch this session.
- Cross-check one or two of the agent's claims yourself with a direct\d+ ordersinpsqlthe first few times you use this workflow, until you trust itsget_object_detailsreading on your specific schema quirks (custom types, inherited tables, partitioned tables all trip up naive summarization).
Generating and Validating Complex SQL Queries from Natural Language
Claude Code's advantage over a plain LLM chat here is that it can iterate against the real database instead of guessing column names. Ask for a query, let it test the query itself via execute_sql, and only accept the final version.
Prompt:
Write a query that returns, per customer, their total lifetime order
value and the number of days since their last order — but only for
customers with at least 3 orders and whose most recent order was in
the last 12 months. Run it against the postgres MCP connection and
show me the first 10 rows before I approve it.
A typical first-draft-then-corrected result:
-- draft 1 (agent runs this, sees a type error on the date subtraction)
SELECT
customer_id,
SUM(total_amount) AS lifetime_value,
CURRENT_DATE - MAX(order_date) AS days_since_last_order
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
AND MAX(order_date) >= CURRENT_DATE - INTERVAL '12 months';
-- draft 2, corrected after execute_sql revealed order_date is
-- timestamptz, not date, and total_amount is stored in cents (int)
SELECT
customer_id,
SUM(total_amount) / 100.0 AS lifetime_value_usd,
EXTRACT(DAY FROM CURRENT_DATE - MAX(order_date)::date) AS days_since_last_order
FROM orders
WHERE deleted_at IS NULL
GROUP BY customer_id
HAVING COUNT(*) >= 3
AND MAX(order_date) >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY lifetime_value_usd DESC
LIMIT 10;
The total_amount being stored in cents and the missing deleted_at IS NULL filter are exactly the kind of thing that only surfaces from looking at real rows — the agent caught the cents issue because execute_sql returned 184300 instead of a plausible dollar figure and it re-checked the column's actual values, not just its declared type.
Tips
- Tell the agent up front to run the query against real data and sanity-check the output range before presenting it — "does this number look right" catches unit and scale errors that type-checking never will.
- For anything going into application code, ask it to also show the equivalent parameterized query ($1,$2) or ORM call — the raw SQL it validated via MCP and the SQL your app will actually execute should match exactly, not just be "similar."
- Watch for soft-delete columns (deleted_at,is_deleted) getting silently omitted from generatedWHEREclauses — this is the single most common correctness bug I see in agent-generated queries on real schemas.
Using EXPLAIN ANALYZE Output to Let AI Diagnose Slow Queries
Feed the agent a real slow query and let it use explain_query (or analyze_query_indexes if the server supports it) rather than describing the symptom in prose — the plan tree carries more diagnostic signal than "this endpoint is slow."
Prompt:
This query takes 4.2 seconds in production. Run EXPLAIN ANALYZE on it
via postgres MCP and tell me exactly what's wrong and what index would
fix it. Don't create the index yet — just show me the analysis.
SELECT o.id, c.name, o.total_amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC;
Real EXPLAIN ANALYZE output on a 12M-row orders table with no supporting index:
Sort (cost=458213.11..458715.44 rows=200932 width=48) (actual time=3891.204..4106.775 rows=4821 loops=1)
Sort Key: o.created_at DESC
Sort Method: external merge Disk: 15872kB
-> Hash Join (cost=15234.00..438291.55 rows=200932 width=48) (actual time=201.442..3812.991 rows=4821 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..410122.00 rows=200932 width=24) (actual time=0.031..3502.117 rows=4821 loops=1)
Filter: ((status = 'pending'::text) AND (created_at > (now() - '7 days'::interval)))
Rows Removed by Filter: 11987221
-> Hash (cost=8934.40..8934.40 rows=498440 width=24) (actual time=189.204..189.205 rows=498440 loops=1)
-> Seq Scan on customers c (cost=0.00..8934.40 rows=498440 width=24) (actual time=0.008..85.331 rows=498440 loops=1)
Planning Time: 0.842 ms
Execution Time: 4106.912 ms
Diagnosis the agent typically produces, correctly, from this:
The orders seq scan reads 12M rows and discards 11,987,221 of them
(Rows Removed by Filter) to find 4,821 matches — that's a 99.96%
discard rate, and it's the dominant cost (3.5s of the 4.1s total).
The sort also spills to disk (external merge, 15MB), which adds
latency but is secondary to the scan.
Recommended index:
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
WHERE status = 'pending';
Partial + composite because the query always filters on a specific
status literal and needs created_at DESC order — a partial index
matching that exact predicate stays small even as the full table
grows, and the DESC order avoids a separate sort step for this
specific access pattern. I ran analyze_query_indexes with a
hypothetical version of this index and the planner switched to an
Index Scan with estimated execution time of ~12ms.
That last line — validating the suggestion with analyze_query_indexes / HypoPG before recommending CREATE INDEX CONCURRENTLY on a 12M-row table — is the difference between a plausible-sounding suggestion and one you can trust enough to run in production. Always ask for that validation step explicitly if the server supports it; not every agent invokes it unprompted.
Tips
- Always requestEXPLAIN ANALYZE, not justEXPLAIN, for real diagnosis — estimated costs and actual row counts diverging (as inrows=200932estimated vsrows=4821actual above) is itself often the root cause, via stale table statistics (ANALYZE orders;fixes that specific case).
-CREATE INDEX CONCURRENTLYin the final recommendation, always, for any table an agent suggests indexing in a live environment — a plainCREATE INDEXtakes a lock that blocks writes for the build duration.
- Ifanalyze_query_indexes/HypoPG isn't available, explicitly ask the agent to state its index suggestion as a hypothesis to verify manually, not a certainty — plan-tree reasoning without a hypothetical-index check is educated guessing, still useful, but weaker evidence.
Tips
Tips
- Wire Postgres MCP at--scope projectwith the connection string in an untracked.env; commit.mcp.jsonitself so the whole team gets the same server config for free.
- Make "map the schema first, state assumptions back to me" a standing habit before any feature work touching an unfamiliar table — it's the single check that prevents the most expensive class of agent mistakes.
- For performance work, always push through toEXPLAIN ANALYZEon real data and, where available, aHypoPG-validated index suggestion — plan-reading without validation is a hypothesis, not a fix.