You know the feeling: a dashboard that takes 30 seconds to load, a report that times out, a migration that locks the entire users table for minutes. You open EXPLAIN ANALYZE, stare at a wall of nested loops, and wonder where to start. This isn't about lacking SQL skills — it's about the sheer volume of context a database engine holds. That's where well-crafted prompts for an AI assistant become a superpower. They don't replace your expertise; they amplify it, letting you iterate on query plans, indexes, and migrations at the speed of thought. In this guide, I've collected 13 battle-tested prompts I use daily to debug PostgreSQL performance, design safe migrations, and audit access — each with real examples and explanations. Whether you're a backend developer, DBA, or data engineer, these will save you hours.
1. Decode EXPLAIN ANALYZE output and suggest fixes
Prompt:
"You are a PostgreSQL performance expert. Analyze this EXPLAIN (ANALYZE, BUFFERS) output and identify the top 3 bottlenecks. For each, explain in plain English why it's slow and propose a concrete fix (index, query rewrite, or config change). Use only standard PostgreSQL features. Output as a table with columns: Issue, Evidence, Fix."
When to use: When you have a slow query and need a quick diagnostic without manually parsing every node.
Example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name, SUM(oi.quantity * oi.price) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= '2026-01-01'
GROUP BY o.id, c.name
ORDER BY total DESC
LIMIT 10;
The AI might spot a sequential scan on orders and suggest an index on created_at. It could also note a hash join spilling to disk and recommend increasing work_mem for that session.
2. Find the missing index for a slow JOIN
Prompt:
"Given this query and the table schemas, suggest the optimal index to speed up the JOIN. Consider composite indexes and column order. Explain how the index will be used by the planner. Provide the CREATE INDEX statement."
When to use: When a JOIN is doing a sequential scan or nested loop without an index.
Example:
-- Query
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'DE';
-- Schemas
-- customers: id (PK), name, country
-- orders: id (PK), customer_id, amount, created_at
AI suggests: CREATE INDEX idx_orders_customer_id ON orders(customer_id); and possibly CREATE INDEX idx_customers_country ON customers(country); if the filter is selective.
3. Rewrite N+1 queries into a single JOIN
Prompt:
"I have an N+1 query problem in my application. Here are the queries executed in a loop. Rewrite them as a single SQL query using JOINs or subqueries. Ensure the result set matches the original logic. Provide the new query and explain the performance gain."
When to use: When your ORM generates one query per parent row.
Example:
Original (in a loop):
SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM order_items WHERE order_id = 1;
-- repeated for each order
AI rewrites:
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 1;
This reduces round trips and allows the planner to optimize the join.
4. Generate safe migration for adding a column with default
Prompt:
"Write a PostgreSQL migration to add a new column status of type text with default 'active' to the users table, which has 10 million rows. Ensure the migration does not lock the table for long. Use the safest approach for PostgreSQL 14+. Provide the SQL and explain the locking behavior."
When to use: When you need to add a column without downtime.
Example:
AI might suggest:
ALTER TABLE users ADD COLUMN status text;
-- then set default in batches
UPDATE users SET status = 'active' WHERE status IS NULL;
-- then add NOT NULL constraint
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
It will explain that adding a column with a default in PostgreSQL 11+ is fast (metadata only), but setting NOT NULL requires a full scan unless you use a constraint. It might recommend ALTER TABLE users ADD CONSTRAINT users_status_not_null CHECK (status IS NOT NULL) NOT VALID; then VALIDATE CONSTRAINT to avoid long locks.
5. Implement partitioning for a large table
Prompt:
"I have a table events with 500 million rows, growing by 10 million per month. I want to partition it by range on created_at (monthly). Provide the steps to convert it to a partitioned table without downtime. Include SQL for creating partitions, migrating data, and updating queries. Explain the trade-offs."
When to use: When a table becomes too large for efficient queries and maintenance.
Example:
AI suggests using declarative partitioning:
-- Create new partitioned table
CREATE TABLE events_partitioned (LIKE events INCLUDING ALL) PARTITION BY RANGE (created_at);
-- Create partitions
CREATE TABLE events_2026_01 PARTITION OF events_partitioned FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- Migrate data in batches
INSERT INTO events_partitioned SELECT * FROM events WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
-- Then swap tables
It will warn about foreign keys and indexes needing to be recreated on partitions.
6. Audit user permissions and roles
Prompt:
"List all roles in the PostgreSQL database, their attributes (superuser, createdb, etc.), and the objects they own or have privileges on. Highlight any roles with excessive privileges. Provide SQL queries to extract this information."
When to use: For security audits or compliance checks.
Example:
AI provides:
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;
SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'app_user';
It may flag a role with superuser that shouldn't have it.
7. Monitor and resolve blocking locks
Prompt:
"Write a query to show current locks, blocking sessions, and the queries they are running. Include session IDs, lock modes, and wait times. Provide a query to terminate a blocking session safely."
When to use: When queries are hanging due to locks.
Example:
AI suggests:
SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
To terminate: SELECT pg_terminate_backend(blocking_pid); — but only after confirming it's safe.
8. Optimize a query with window functions
Prompt:
"Rewrite this query to use window functions instead of self-joins or subqueries. The goal is to calculate running totals and rankings per group. Provide the new query and explain the performance improvement."
When to use: When you have complex aggregations that can be simplified.
Example:
Original:
SELECT o.id, o.customer_id, o.amount, (SELECT SUM(amount) FROM orders o2 WHERE o2.customer_id = o.customer_id AND o2.created_at <= o.created_at) AS running_total
FROM orders o;
AI rewrites:
SELECT id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;
This avoids correlated subqueries and is much faster.
9. Generate a materialized view for a slow report
Prompt:
"I have a complex report query that runs daily and takes 10 minutes. Suggest a materialized view definition to precompute the results. Include the refresh strategy (e.g., REFRESH MATERIALIZED VIEW CONCURRENTLY) and any required indexes. Explain the trade-offs."
When to use: For dashboards or reports with heavy aggregations that don't need real-time data.
Example:
AI suggests:
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) AS month, customer_id, SUM(amount) AS total
FROM orders
GROUP BY 1, 2;
CREATE UNIQUE INDEX ON monthly_sales (month, customer_id);
-- Refresh concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
It notes that CONCURRENTLY requires a unique index and doesn't lock reads.
10. Tune autovacuum for a high-write table
Prompt:
"My table transactions has heavy inserts and updates, causing bloat and slow queries. Suggest autovacuum settings (scale_factor, threshold) for this table. Provide the ALTER TABLE statements and explain how they affect performance."
When to use: When a table experiences frequent updates/deletes and you see dead tuples.
Example:
AI suggests:
ALTER TABLE transactions SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);
ALTER TABLE transactions SET (autovacuum_analyze_scale_factor = 0.005, autovacuum_analyze_threshold = 500);
It explains that lowering scale_factor makes autovacuum run more often, keeping bloat low but increasing I/O.
11. Detect and fix index bloat
Prompt:
"Provide a query to estimate index bloat in PostgreSQL. Then suggest how to rebuild bloated indexes with minimal locking. Include REINDEX CONCURRENTLY if available, and explain the impact."
When to use: When indexes have grown larger than necessary, wasting space and slowing scans.
Example:
AI provides a bloat estimation query using pgstatindex (from pgstattuple extension) or a custom query. Then:
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
It notes that REINDEX CONCURRENTLY is available from PostgreSQL 12 and avoids locking writes.
12. Generate a query to find unused indexes
Prompt:
"Write a query to list all indexes in the database that have never been used since the last statistics reset. Include index name, table, size, and number of scans. Explain how to interpret the results and when to drop indexes."
When to use: To clean up unnecessary indexes that slow down writes.
Example:
AI suggests:
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
It warns that some indexes are for constraints (unique, primary key) and should not be dropped even if unused.
13. Explain a complex query plan in plain English
Prompt:
"Take this EXPLAIN output and explain it in simple terms for a developer who knows SQL but not internals. Highlight what each node does, where the time is spent, and what to focus on. Avoid jargon where possible."
When to use: When you need to communicate performance issues to your team or understand a plan yourself.
Example:
Given a plan with a Hash Join and Seq Scan, the AI might say: "The database is scanning the entire orders table (Seq Scan) because there's no index on the filter column. Then it builds a hash table from customers to match rows. Most time is spent on the sequential scan — adding an index on orders.created_at would let it jump directly to recent rows."
These prompts are not magic — they're conversation starters. The real value comes from iterating: run the suggested index, re-check EXPLAIN, and refine. Always test in a staging environment before production. For deeper reading, consult the official PostgreSQL documentation on EXPLAIN, partitioning, and autovacuum. Have a favorite prompt of your own? Share it in the comments — let's build a collective playbook for faster databases.
Comments