We all know the drill: a data request lands in Slack, you open pgAdmin, and stare at a blank query window. Then you spend an hour writing a query that joins six tables, only to have it run for five minutes and time out. Frustrating, right? At ASI Biont, we hit this wall daily—until we started treating AI as a SQL co-pilot, not a toy. We compiled a set of battle-tested prompts that cut our query writing time by 80% and made our analytics team feel like senior DBAs. No magic, just smart prompting with a healthy dose of PostgreSQL expertise. Here’s the collection we wish we had a year ago.
Why SQL Prompts? A Quick Reality Check
Before diving in, let’s ground ourselves. SQL is declarative—you say what you want, not how. That’s why AI models, trained on millions of queries, can generate surprisingly good SQL. But without proper context, you get generic queries that ignore PostgreSQL’s strengths (like LATERAL joins or EXPLAIN ANALYZE). The prompts below are designed to force the AI to think like a performance-minded DBA. They include schema context, performance constraints, and verification steps. This isn’t about replacing DBAs—it’s about making them obsolete for routine tasks. We’ve seen a 10x speedup in report generation because we no longer wait for hand-written queries.
The Prompts: From Simple to Surgical
1. The Schema-Aware Query Builder
Prompt:
You are a PostgreSQL expert. Given the following schema:
- `users(id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE, created_at TIMESTAMP)`
- `orders(id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), amount NUMERIC(10,2), status VARCHAR(20), ordered_at TIMESTAMP)`
Write a query that returns the total revenue per month for the last 6 months, including months with zero revenue. Use `generate_series` to fill gaps. Include a column `month` formatted as YYYY-MM.
Why it works: It provides exact column names, types, and relationships, so the AI doesn’t guess. It also specifies the expected output format and suggests a PostgreSQL-specific function (generate_series).
Example output:
SELECT to_char(month, 'YYYY-MM') AS month, COALESCE(SUM(amount), 0) AS revenue
FROM generate_series(date_trunc('month', NOW()) - INTERVAL '5 months', date_trunc('month', NOW()), INTERVAL '1 month') AS month
LEFT JOIN orders ON date_trunc('month', ordered_at) = month
WHERE status = 'paid'
GROUP BY month
ORDER BY month;
2. The Performance Tuning Prompt
Prompt:
Here’s a query that runs slowly on PostgreSQL 16:
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE created_at > NOW() - INTERVAL '1 year');
Explain why it might be slow and rewrite it using a JOIN. Then add an index suggestion and show how to check if the index is used with EXPLAIN.
Why it works: It asks for explanation, rewrite, index suggestion, and verification—covering the full optimization loop. It also forces the AI to use EXPLAIN, which is the only way to confirm index usage.
Example response:
The subquery may cause a full scan of orders if the planner decides to materialize it. A JOIN is often better:
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.created_at > NOW() - INTERVAL '1 year';
Add an index on orders(user_id) and users(created_at) if not present. Check with:
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
Look for "Index Scan" in the output.
3. The Window Function Wizard
Prompt:
Using the `orders` table, write a query that computes a 7-day moving average of daily revenue for the last 30 days. Use a window function with `RANGE` or `ROWS`. Also include a column that shows the difference from the previous day.
Why it works: Window functions are a pain to write from scratch. This prompt specifies the exact problem, the table, and the expected output, so the AI can produce a correct query with AVG() OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
4. The Index Advisor with a Twist
Prompt:
Given the table `orders` with columns `status`, `ordered_at`, and `user_id`, and queries that filter on `status` and `ordered_at`, suggest an optimal index. Explain whether a composite index or separate indexes are better, and provide the `CREATE INDEX` statement. Consider the selectivity of each column.
Why it works: It asks for a recommendation with reasoning, not just a random index. The AI will discuss selectivity and query patterns, which is what a real DBA would do.
Example:
A composite index on (status, ordered_at) is likely best for queries that filter on both. If you also filter by user_id, consider adding it as a leading column: CREATE INDEX idx_orders_status_ordered_at ON orders(status, ordered_at);
5. The EXPLAIN Analyzer
Prompt:
Here’s the output of `EXPLAIN (ANALYZE, BUFFERS)` for a query on PostgreSQL. Identify any red flags (e.g., sequential scans on large tables, missing indexes, high buffer usage) and suggest concrete improvements. The table `orders` has 1 million rows.
[Paste EXPLAIN output here]
Why it works: You get a second opinion on execution plans. The AI can spot things like Seq Scan on a large table and recommend an index or a query rewrite.
6. The CTE Refactoring Prompt
Prompt:
Refactor the following query to use Common Table Expressions (CTEs) for readability, but ensure that CTEs are not materialized multiple times. Show both the original and the refactored version, and explain the performance implications.
Why it works: CTEs are great for readability but can be performance traps if used carelessly. This prompt makes the AI think about materialization and optimization.
7. The Dynamic SQL Builder
Prompt:
Write a PL/pgSQL function that takes a `status` parameter and returns orders with that status, but uses dynamic SQL to avoid SQL injection. Use `format()` with `%I` for identifiers and `%L` for literals. Also add a `RETURNS TABLE` clause and a `SECURITY DEFINER` option if appropriate.
Why it works: Dynamic SQL is risky, and this prompt forces the AI to use safe practices. It also asks for a function, which is a common need for analytics tooling.
8. The Data Cleaning Prompt
Prompt:
Given a table `events` with a `payload` JSONB column, write a query to extract all unique keys from the JSONB objects and output them as a single column. Use `jsonb_object_keys` and `DISTINCT`. Also, find rows where `payload` is not valid JSON (if any).
Why it works: JSONB is a PostgreSQL superpower, but extracting keys is non-obvious. The prompt specifies the function and the expected output, making the query correct.
9. The Migration Safety Net
Prompt:
I need to add a new column `discount` to the `orders` table. Write a migration that adds the column with a default of 0, backfills it from a related table `promotions` where `promotion_code` matches, and then removes the default to avoid locking. Provide the SQL in a transaction.
Why it works: Migrations are tricky; this prompt asks for a safe, transactional approach that avoids long locks.
10. The Explainable AI Prompt
Prompt:
Write a SQL query that finds the top 5 customers by total spending in the last 3 months, but also returns a column that explains why each customer was chosen (e.g., total orders, average order value). Use a CTE and a window function.
Why it works: This is about creating human-readable output, which is great for business stakeholders.
Real-World Impact: A Case Study
We applied these prompts to a client’s e-commerce database (2TB, PostgreSQL 15). One report that used to take 40 minutes of manual query writing and optimization now takes 5 minutes with AI assistance. The AI caught a missing index that our team had overlooked for weeks.
Conclusion
These prompts aren’t a replacement for SQL knowledge—they’re a force multiplier. Start with the schema-aware prompts, then move to performance tuning. You’ll still need to verify the output with EXPLAIN and test against your data, but the time savings are real. Try them on your next analytics request, and you’ll wonder how you ever worked without them.
Comments