Claude Code PostgreSQL Integration

Connect with psql

Opens an interactive session; use a read-only role for analysis work.

List tables in the current schema

A psql meta-command; pair with \d table_name to inspect columns and indexes.

Read a query plan with real timings

Feed the full output to Claude Code to locate sequential scans and bad estimates.

Create an index to speed up lookups

CONCURRENTLY avoids locking the table for writes while the index builds.

Create a table with constraints

Ask Claude Code to justify each type, key, and constraint before you run it.

Back up the database with pg_dump

The custom format (-Fc) restores with pg_restore and supports selective table restores.

I am building an application with these entities and relationships: [describe them]. Design a normalized PostgreSQL schema. Give me the CREATE TABLE statements with primary keys, foreign keys, appropriate data types, and constraints, and explain the tradeoffs of any normalization or denormalization decision you make.

Here is my current PostgreSQL schema. Review it for design problems: missing constraints, weak or missing foreign keys, questionable data types, columns that should be normalized, and anything that will cause pain as the data grows. List findings by severity and suggest concrete fixes.

Using this schema, write a PostgreSQL query that does the following: [describe the result you want]. Use joins, CTEs, or window functions where appropriate, explain how the query works step by step, and note any indexes it relies on to run efficiently.

Here is the output of EXPLAIN (ANALYZE, BUFFERS) for a query. Explain the plan in plain language: which nodes are expensive, where the time is actually spent, whether the row estimates are accurate, and the single change most likely to make it faster.

This query is slow. Here is the query, the schema of the tables involved, the existing indexes, and the EXPLAIN ANALYZE output. Identify why it is slow and propose a fix, either an index or a rewrite. Show the exact SQL and explain the tradeoff, including any write cost an index adds.

Here are the queries my application runs most often and the relevant table schemas. Recommend the indexes that would speed them up. For each index, give the CREATE INDEX statement, say whether a composite or partial index is better, and explain the write overhead it introduces.

I need to make this schema change: [describe it]. Write a PostgreSQL migration with clear up and down steps. Order the operations so they avoid long table locks on a production database, use CREATE INDEX CONCURRENTLY where relevant, and make sure the migration is fully reversible.

My application is hitting deadlocks in PostgreSQL. Here are the queries involved and the error message. Explain how the deadlock happens, which lock ordering causes it, and how to change the transactions or query order to prevent it without weakening correctness.

I am getting a connection refused error when connecting to PostgreSQL with this connection string. Walk me through the likely causes in order, from the server not running to the wrong host, port, pg_hba.conf rules, and listen_addresses, and tell me exactly how to check each one.

My application is failing with a too many clients error from PostgreSQL. Explain why this happens, how to size a connection pool correctly for my workload, and how to set up PgBouncer in transaction mode so the app stops exhausting max_connections.

I need to model this in PostgreSQL: [describe a many-to-many relationship, soft deletes, or an audit trail]. Show the table design, explain whether to use a join table, JSONB, or another approach, and describe the tradeoffs for query performance and data integrity.

Using this schema, write PostgreSQL queries that use window functions to do the following: [describe the ranking, running total, or per-group calculation you need]. Explain how PARTITION BY and ORDER BY affect the result and how the window frame is evaluated.

Here is a table definition. Add the constraints it should have to protect data integrity: primary key, foreign keys with a deliberate ON DELETE action, unique constraints, and check constraints. Explain what each constraint enforces and how it affects inserts and updates.

I want a reliable backup strategy for this PostgreSQL database. Give me the pg_dump commands for a full backup, a single-table backup, and a schema-only snapshot, explain when to use the custom format versus plain SQL, and show the matching pg_restore commands with a way to verify the restore.

I suspect index or table bloat is slowing down my PostgreSQL database. Show me the queries to measure bloat and dead tuples, explain how to read the results, and recommend whether to run VACUUM, VACUUM FULL, or REINDEX CONCURRENTLY, including the locking implications of each.

Queries are hanging and I think there is lock contention in PostgreSQL. Show me the queries against pg_locks and pg_stat_activity to find blocking and blocked sessions, explain how to read the output, and recommend how to resolve the contention safely.

I am getting a type mismatch or invalid input syntax error from PostgreSQL. Here is the query and the error. Explain what type PostgreSQL expected versus what it received, where the implicit cast is failing, and how to fix it with an explicit cast or a schema change.

This application endpoint is slow and it runs these PostgreSQL queries. Trace which query is the bottleneck, check for missing indexes and lock contention, and propose the smallest set of changes, whether indexes, rewrites, or caching, that will make the endpoint fast.

Two migrations conflict or a migration failed halfway and left the schema in an inconsistent state. Here is the migration history and the current schema. Explain how to reconcile them safely, whether to roll forward or back, and give me the exact SQL to bring the database to a known good state.

I am seeing encoding or collation errors in PostgreSQL, such as invalid byte sequence or unexpected sort order. Explain the difference between the database encoding, client encoding, and collation, identify which one is wrong here, and tell me how to fix it safely for existing data.

What is the Claude Code PostgreSQL integration?

It is a workflow that lets Claude Code work directly with your PostgreSQL database. Claude Code can read your schema, write and optimize SQL, interpret EXPLAIN ANALYZE plans, recommend indexes, and plan migrations, using your real database as the source of truth. It enhances your existing database workflow rather than replacing your judgement over what runs against production.

How do I connect Claude Code to PostgreSQL?

The most capable path is the Postgres MCP server. Install the crystaldba/postgres-mcp server, add it to your Claude Code configuration, and pass your connection string through an environment variable, ideally for a read-only role. Once connected, Claude Code can list tables, inspect schemas, and run guarded queries. You can also work manually by pasting your schema and query plans into a prompt.

Can Claude Code write and optimize SQL?

Yes. Claude Code can turn a plain-language requirement into a correct query using joins, CTEs, and window functions grounded in your schema. For optimization, give it the slow query, the table schemas, the existing indexes, and the EXPLAIN ANALYZE output, and it will propose an index or rewrite and explain the tradeoff so you can decide with evidence.

Can Claude Code read query plans from EXPLAIN ANALYZE?

Yes. Paste the output of EXPLAIN (ANALYZE, BUFFERS) and Claude Code will explain the plan in plain language: which nodes are expensive, whether the row estimates are accurate, where the time is actually spent, and the single change most likely to make the query faster.

Can Claude Code help with database migrations?

Yes. Describe the schema change and Claude Code will write a migration with clear up and down steps, order the operations to avoid long table locks, use CREATE INDEX CONCURRENTLY where relevant, and keep the migration reversible. Always review the generated migration and test it on a copy of the data before running it in production.

Which Postgres MCP server should I use with Claude Code?

Use the actively maintained crystaldba/postgres-mcp server, also known as Postgres MCP Pro. It supports schema inspection, EXPLAIN plan analysis, index tuning, health checks, and configurable read-only or read/write access. Anthropic's original reference Postgres server was deprecated and archived in 2025 with a known SQL-injection weakness, so treat it as a learning example only.

Can Claude Code recommend indexes?

Yes. Give Claude Code your most frequent queries and the relevant table schemas and it will recommend the indexes that speed them up, including composite and partial indexes. For each one it gives the CREATE INDEX statement and explains the write overhead the index adds, so you can weigh read speed against write cost.

Is it safe to give Claude Code access to my production database?

It is safe if you keep control of privileges. Connect through a read-only role for analysis, keep credentials in environment variables or a secrets manager rather than in prompts, and require explicit confirmation before any destructive statement runs. Review every generated migration and take a pg_dump backup before schema changes so you can always roll back.