SQL Prompts That Actually Work: A PostgreSQL Practitioner's Playbook

Every PostgreSQL developer has been there: a query that ran fine in staging suddenly takes 40 seconds in production, and EXPLAIN ANALYZE output looks like a wall of hieroglyphs. Or you need to refactor a five-table join with CTEs and window functions, but your brain is already fried. This is where well-crafted prompts for SQL come in. They don't replace your expertise — they amplify it, turning a 30-minute debugging session into a 3-minute conversation with an AI assistant that understands PostgreSQL internals.

In this playbook, I've collected 12 battle-tested prompts for PostgreSQL optimization, SQL query generation, and database refactoring. Each one includes a real example, the exact prompt text, and a note on when to use it. These are the prompts I use daily as a data engineer, and they're designed to work with any LLM — ChatGPT, Claude, or your local model. No fluff, just practical SQL prompts that save hours.

Why Prompts Matter for PostgreSQL Work

PostgreSQL is powerful but verbose. The difference between a sequential scan and an index-only scan often comes down to a single WHERE clause or a missing ANALYZE. AI assistants can parse EXPLAIN output, suggest indexes, and rewrite queries — but only if you ask precisely. Vague prompts yield vague answers. The prompts below are structured to give the model enough context: schema, query, execution plan, and goal. This mirrors how you'd brief a senior DBA.

A quick note on safety: never paste production credentials or sensitive data into public LLMs. Anonymize table names and values. For real data, use self-hosted models or enterprise APIs with data-processing agreements.

12 Prompts for SQL and PostgreSQL Optimization

1. EXPLAIN ANALYZE Decoder

When to use: You have a slow query and a raw EXPLAIN (ANALYZE, BUFFERS) output that you can't interpret.

Prompt:

You are a PostgreSQL performance expert. Here is the EXPLAIN (ANALYZE, BUFFERS) output for a query on a table with 5 million rows. Identify the top 3 bottlenecks, explain what each node means in plain English, and suggest concrete fixes (indexes, query rewrite, or config changes). Output as a table with columns: Issue, Explanation, Fix.

Plan:
<paste plan here>

Example: A query joining orders and customers showed a Hash Join with 2M rows spilled to disk. The AI identified missing work_mem and suggested an index on orders(customer_id, created_at). Result: runtime dropped from 12s to 0.8s.

2. Index Advisor

When to use: You suspect missing indexes but don't want to guess.

Prompt:

Given this table schema and these 3 slow queries, recommend indexes that would benefit all of them. For each index, show the CREATE INDEX statement, explain which query it helps and why, and warn about write overhead. Use PostgreSQL 16 syntax. Schema:

CREATE TABLE events (id bigserial, user_id int, event_type text, payload jsonb, created_at timestamptz);

Queries:
1. SELECT * FROM events WHERE user_id = 42 AND created_at > now() - interval '7 days';
2. SELECT event_type, count(*) FROM events GROUP BY event_type;
3. SELECT * FROM events WHERE payload @> '{"source":"mobile"}';

Example: The AI suggested a composite B-tree on (user_id, created_at) and a GIN index on payload. It also warned that the GIN index would slow inserts by ~15% — a trade-off worth noting.

3. CTE and Window Function Generator

When to use: You need a complex analytical query but don't want to write it from scratch.

Prompt:

Write a PostgreSQL query that calculates, for each customer, their total revenue, rank within their country, and a 7-day moving average of daily revenue. Use CTEs and window functions. Tables: customers(id, country), orders(id, customer_id, amount, order_date). Include comments explaining each CTE.

Example: The generated query used SUM() OVER (PARTITION BY country ORDER BY revenue DESC) for ranking and a ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame for the moving average. It ran correctly on first try.

4. Join Refactoring Prompt

When to use: A query with 5+ joins is unreadable and slow.

Prompt:

Refactor this SQL query to improve readability and performance. Replace implicit joins with explicit JOIN syntax, push down WHERE filters, and suggest whether a CTE or subquery is better. Explain each change. Original query:

<paste query>

Example: A legacy query with comma joins and filters in the WHERE clause was rewritten with INNER JOIN ... ON and filters moved into CTEs. The planner chose a better join order, cutting execution time by 60%.

5. Migration Script Validator

When to use: You're writing a schema migration and want to catch locking issues.

Prompt:

Review this PostgreSQL migration script for production safety. Flag any operations that take ACCESS EXCLUSIVE locks, suggest CONCURRENTLY alternatives, and estimate downtime. Script:

ALTER TABLE users ADD COLUMN last_login timestamptz;
CREATE INDEX idx_users_email ON users(email);

Example: The AI flagged that CREATE INDEX without CONCURRENTLY would lock writes. It suggested CREATE INDEX CONCURRENTLY and noted that adding a column with a default is safe in PG 11+.

6. Slow Query Rewriter

When to use: A query uses SELECT *, NOT IN, or functions in WHERE clauses.

Prompt:

Rewrite this query to be sargable (avoid functions on indexed columns, replace NOT IN with NOT EXISTS, avoid SELECT *). Explain why each change helps the planner. Query:

SELECT * FROM logs WHERE DATE(created_at) = '2026-09-01' AND user_id NOT IN (SELECT id FROM banned_users);

Example: Rewritten to WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02' AND NOT EXISTS (...). The planner used an index range scan instead of a sequential scan.

7. Partitioning Strategy Prompt

When to use: A table has grown to hundreds of millions of rows.

Prompt:

Design a partitioning strategy for a PostgreSQL table with 500M rows of time-series data. Recommend partition type (range, list, hash), interval, and how to handle old partitions. Include DDL for creating partitions and a query to automate monthly partition creation.

Example: The AI recommended range partitioning by month, with a pg_cron job to create future partitions. It also suggested using DETACH PARTITION for archiving instead of DELETE.

8. JSONB Query Helper

When to use: You store semi-structured data in JSONB and need to query it efficiently.

Prompt:

Write PostgreSQL queries to: 1) find rows where jsonb column 'metadata' contains key 'tags' with value 'urgent'; 2) extract a nested field 'user.address.city'; 3) update a specific key. Explain which GIN index to create.

Example: The AI provided metadata @> '{"tags":["urgent"]}', metadata #>> '{user,address,city}', and jsonb_set. It recommended a GIN index with jsonb_path_ops for containment queries.

9. Connection Pool Tuning Prompt

When to use: You see too many connections errors or high latency under load.

Prompt:

Given these PostgreSQL settings and workload characteristics, recommend optimal values for max_connections, shared_buffers, work_mem, and effective_cache_size. Explain the trade-offs. Current: max_connections=500, shared_buffers=1GB, work_mem=4MB, RAM=16GB, workload=OLTP with 200 concurrent users.

Example: The AI suggested lowering max_connections to 200 and using PgBouncer in transaction mode, increasing shared_buffers to 4GB, and setting work_mem to 16MB. This reduced memory pressure and improved p99 latency.

10. Deadlock Diagnosis Prompt

When to use: You see deadlock errors in logs and need to find the cause.

Prompt:

Here is a PostgreSQL deadlock log entry. Identify the two transactions involved, explain why the deadlock occurred, and suggest code changes to prevent it (e.g., consistent lock ordering). Log:

<paste log>

Example: The AI showed that transaction A locked row 1 then row 2, while B locked row 2 then row 1. The fix was to always lock rows in ascending ID order.

11. Test Data Generator

When to use: You need realistic data for benchmarking.

Prompt:

Generate a PostgreSQL script to populate a 'sales' table with 1 million rows of realistic test data. Include random dates over the last 2 years, amounts with a normal distribution, and customer IDs referencing a 'customers' table. Use generate_series and random().

Example: The AI produced an INSERT INTO sales SELECT ... FROM generate_series(1, 1000000) with random() and normal_rand (from the tablefunc extension). The data was ready in seconds.

12. Query Plan Comparison Prompt

When to use: You changed a query or index and want to compare plans before and after.

Prompt:

Compare these two EXPLAIN ANALYZE outputs. Highlight differences in scan types, join methods, and estimated vs actual rows. Which plan is better and why? Old plan:

<plan A>

New plan:

<plan B>

Example: The AI noted that the new plan switched from Nested Loop to Hash Join, reducing actual time from 450ms to 120ms, but warned about increased memory usage. This guided a decision to keep the new index.

Table: Prompt Cheat Sheet

How to Get the Most Out of These Prompts

Always include the PostgreSQL version, table sizes, and relevant config settings. The more context you provide, the more accurate the AI's advice. For critical production changes, validate suggestions with EXPLAIN on a staging replica. And remember: AI is a co-pilot, not an autopilot. You still own the final SQL.

These prompts for SQL aren't magic — they're structured questions that force the model to reason about PostgreSQL's planner, indexes, and concurrency. Use them as a starting point, adapt them to your schema, and build your own library. The next time a query drags, you'll know exactly what to ask.

Sources: PostgreSQL 16 documentation (postgresql.org/docs/16/), "PostgreSQL 14 Administration Cookbook" by Simon Riggs and Gianni Ciolli, and practical experience from production systems.