From Slow Queries to Smart Schemas: 10 Battle-Tested AI Prompts for SQL, PostgreSQL, and MongoDB

If you've ever stared at a query that runs for 47 seconds, or wrestled with a MongoDB aggregation pipeline that returns the wrong shape, you know the pain. Data work is 80% thinking and 20% typing—but the typing part can eat your whole day. That's where AI prompts come in. I've spent the last three years as a data engineer, and I've refined a set of prompts that turn AI from a toy into a serious colleague. This isn't a list of generic "write a query" prompts. These are battle-tested, specific prompts that solve real problems: optimizing a slow join, designing a schema that won't fall apart, or explaining a complex window function. Each prompt includes a concrete example and a real output snippet, so you can copy, adapt, and use them immediately. Let's dive in.

Why AI Prompts for Databases? The Productivity Case

A 2024 survey by Redgate Software found that developers spend nearly 30% of their workweek on data-related tasks, and a significant chunk of that is debugging queries or rewriting them for performance. The same survey noted that AI tools can cut query writing time by up to 50% when used with structured prompts. But the key word is "structured." A vague prompt like "optimize this query" gets you vague results. A specific prompt with context, table schemas, and constraints gets you a query that actually works. Below, I've organized the prompts by use case, starting with the most common pain point: performance.

1. Optimizing a Slow Query in PostgreSQL

When to use: You have a query that works but takes too long. You need a step-by-step analysis and a rewritten query, not just a one-line answer.

You are a PostgreSQL performance expert. I have a query that runs in 12 seconds on a table with 5 million rows. The query is:

[INSERT QUERY]

Here are the table schemas:

[INSERT CREATE TABLE statements]

Please:
1. Explain the likely bottleneck (e.g., full table scan, missing index, inefficient join)
2. Provide the EXPLAIN ANALYZE output if you can simulate it (or tell me what to look for)
3. Suggest indexes that would help, with the exact CREATE INDEX statement
4. Rewrite the query using best practices (e.g., use CTEs, avoid SELECT *, use appropriate join types)
5. Show the rewritten query with comments explaining each change

Example: For a query joining orders and customers on customer_id without an index, the AI will likely suggest creating an index on orders(customer_id) and rewriting the query to use an explicit JOIN instead of a subquery. A real output snippet might look like:

-- Original: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE signup_date > '2025-01-01');
-- Rewritten: SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.signup_date > '2025-01-01';
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

This approach has saved me hours on countless occasions. It's especially effective when you're new to a codebase and don't know where the indexes are.

2. Writing a Complex SQL Query Step-by-Step

When to use: You need to build a complex query (e.g., with window functions, multiple CTEs, or conditional aggregation) and you want to understand each part as you go.

I need to write a SQL query in PostgreSQL that does the following:

[DESCRIBE THE BUSINESS LOGIC, e.g., "for each customer, show the total revenue from the last 3 months, and compare it to the previous 3 months, with a percentage change"]

The tables are:

[INSERT TABLE SCHEMAS]

Please write the query step-by-step, explaining each step in plain English. After each step, show the SQL that implements that step. At the end, combine all steps into a final query. Also, point out any edge cases I should handle (e.g., customers with no orders in either period).

Example: The AI might break it down into: (1) calculate total revenue per customer per month using a subquery, (2) use LAG to get the previous period, (3) compute the percentage change. The final query might look like:

WITH monthly_revenue AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1, 2
),
ranked AS (
  SELECT *, LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT customer_id, month, revenue, prev_revenue,
       (revenue - prev_revenue) / NULLIF(prev_revenue, 0) * 100 AS pct_change
FROM ranked
WHERE month >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3 months';

This prompt is a lifesaver for analytics reports. It turns a blank page into a well-structured query in minutes, and you learn the reasoning behind it.

3. Designing a Database Schema for a New Application

When to use: You're starting a new project and need a normalized schema that follows best practices and avoids common pitfalls.

You are a database architect. I'm building a [DESCRIBE APPLICATION, e.g., "a task management app with users, projects, tasks, and comments"]. I need a PostgreSQL schema that is normalized to 3NF, uses appropriate data types, and includes primary keys, foreign keys, and indexes for common queries. Please:
1. Provide the CREATE TABLE statements, with comments on each column
2. Explain your design choices (e.g., why you chose UUID vs serial, or why you used a junction table)
3. Suggest indexes for the most likely queries (e.g., "get all tasks for a project")
4. Note any constraints (e.g., CHECK for status values)
5. If there are any tricky relationships (e.g., self-referencing), explain how you handled them

Example: For a task management app, the AI might produce something like:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL
);
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  owner_id UUID REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  status TEXT CHECK (status IN ('todo', 'in_progress', 'done')),
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);

I've used this prompt to scaffold schemas for client projects, and it's remarkably close to what I'd design manually. It's not a replacement for a human architect, but it's a great starting point.

4. Explaining a Difficult Concept in MongoDB

When to use: You're stuck on a MongoDB concept like aggregation pipelines, indexing, or the $lookup stage, and you need a clear, example-driven explanation.

You are a MongoDB expert. Explain [CONCEPT, e.g., "the aggregation pipeline with $match, $group, $project, and $lookup"] in simple terms with a real-world example. Use a concrete dataset (e.g., a collection of sales orders) and show how to use the concept to answer a business question. Include:
1. A brief definition
2. A step-by-step breakdown of the syntax
3. A complete example that I can run in mongosh, with sample input and output
4. Common mistakes to avoid

Example: For $lookup, the AI might show a query that joins orders and customers collections:

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $lookup: { from: "customers", localField: "customer_id", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" },
  { $project: { total: 1, customer_name: "$customer.name" } }
])

This prompt is perfect when you're learning MongoDB or when you need to explain something to a colleague. The AI's examples are usually accurate, but I always verify against the official MongoDB docs (docs.mongodb.com) because the syntax can change between versions.

5. Converting SQL to MongoDB Aggregation (and Vice Versa)

When to use: You're migrating from SQL to MongoDB, or you need to write a MongoDB query but you think in SQL.

Convert the following SQL query to a MongoDB aggregation pipeline. Explain each stage in terms of its SQL equivalent (e.g., WHERE becomes $match, GROUP BY becomes $group, JOIN becomes $lookup).

SQL query:
[INSERT SQL QUERY]

Also, provide the opposite: a MongoDB aggregation pipeline converted to SQL. If the conversion is not straightforward (e.g., because of dynamic fields), explain the limitations.

Example: The SQL query SELECT customer_id, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY customer_id HAVING SUM(amount) > 1000 would become:

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$customer_id", total: { $sum: "$amount" } } },
  { $match: { total: { $gt: 1000 } } }
])

This has been a game-changer for teams I've worked with that are moving from relational to document databases. It's also useful for cross-referencing when you're not sure if your MongoDB query is correct.

6. Generating Realistic Test Data

When to use: You need to populate a database with sample data for development or testing, and you want it to look realistic.

Generate realistic test data for a PostgreSQL table with the following schema:

[INSERT CREATE TABLE STATEMENT]

Please provide 50 INSERT statements with realistic values. Use a variety of names, dates, and numbers that mimic real-world data (e.g., for a users table, use plausible emails and signup dates). For foreign keys, use valid IDs from a range. Also, include some edge cases like NULL values for nullable columns and extreme values where appropriate.

Example: For a users table, the AI might generate:

INSERT INTO users (id, email, name, signup_date) VALUES
(1, 'john.doe@example.com', 'John Doe', '2025-01-15'),
(2, 'jane.smith@example.com', 'Jane Smith', '2025-02-20');

This prompt saves me from writing boring dummy data like 'test1', 'test2'. It's especially useful for load testing or when you need to demo an application. Just remember to seed your sequences correctly if you use serial IDs.

7. Writing a MongoDB Index Strategy

When to use: Your MongoDB queries are slow, and you need to know which indexes to create for your access patterns.

You are a MongoDB performance expert. I have a collection named [COLLECTION NAME] with the following document structure:

[INSERT SAMPLE DOCUMENT]

My most common queries are:
[LIST QUERIES, e.g., "find by status and created_at", "aggregate by user_id"]

Please recommend an index strategy:
1. For each query, suggest the optimal index (with the exact createIndex syntax)
2. Explain the ordering of fields (e.g., equality first, then sort, then range)
3. Mention any trade-offs (e.g., additional index vs. write performance)
4. Suggest how to measure the impact using explain()

Example: For a collection of orders, the AI might recommend:

db.orders.createIndex({ status: 1, created_at: -1 })

This prompt is a must-have for any MongoDB developer. I've used it to cut query times from seconds to milliseconds. The AI's recommendations align with the official MongoDB documentation on indexes (docs.mongodb.com/manual/indexes/).

8. Debugging a MongoDB Aggregation Pipeline

When to use: Your aggregation pipeline returns unexpected results or errors, and you need to pinpoint the issue.

I have a MongoDB aggregation pipeline that is not working as expected. Here is the pipeline:

[INSERT PIPELINE]

Here is a sample document from the collection:

[INSERT SAMPLE DOCUMENT]

What I expect as output:
[DESCRIBE EXPECTED OUTPUT]

What I actually get:
[DESCRIBE ACTUAL OUTPUT, INCLUDING ANY ERRORS]

Please:
1. Identify the likely problem (e.g., wrong field name, incorrect $group _id, missing $unwind)
2. Explain the issue in simple terms
3. Provide the corrected pipeline with comments
4. Suggest debugging techniques (e.g., adding $limit, using $project to inspect intermediate results)

Example: If you use a field name that doesn't exist, the AI will point it out and suggest checking the document structure. This prompt is like having a senior engineer looking over your shoulder.

9. Comparing SQL vs. NoSQL for Your Use Case

When to use: You're deciding between a relational database and MongoDB for a new project, and you need an objective analysis.

Compare PostgreSQL and MongoDB for the following use case:

[DESCRIBE YOUR APPLICATION, e.g., "a social media app with user profiles, posts, and comments. We need to query posts by user, get a feed of posts, and support full-text search"]

Please provide:
1. A table comparing the two in terms of data modeling, query flexibility, scalability, and consistency
2. Recommendations for which one to choose and why, based on the specific needs
3. Potential pitfalls if we choose the wrong one
4. If you recommend MongoDB, show how to model the data; if PostgreSQL, show a sample schema

Example: The AI might produce a table like:

This prompt is great for architecture discussions. It forces you to think about trade-offs and gives you a solid base for a decision.

10. Optimizing MongoDB Queries with explain()

When to use: You have a slow MongoDB query and you need to know exactly what's happening (full collection scan vs. index scan).

You are a MongoDB performance expert. I have a query that is slow:

[INSERT QUERY]

Here is the output of .explain("executionStats"):

[INSERT EXPLAIN OUTPUT]

Please analyze the explain output and:
1. Identify the winning plan (e.g., COLLSCAN vs. IXSCAN)
2. Point out any red flags (e.g., high number of documents examined, high execution time)
3. Recommend an index that would improve performance, with the exact createIndex command
4. Explain how to read the explain output for future reference

Example: If the explain shows COLLSCAN, the AI will likely recommend an index on the fields used in the query filter. This prompt is a lifesaver for diagnosing performance issues in production. It's like having a MongoDB DBA on call.

11. Writing a Recursive CTE for Hierarchical Data

When to use: You have hierarchical data (e.g., employee-manager relationships, category trees) and you need to query it efficiently in PostgreSQL.

I need to write a recursive CTE in PostgreSQL to traverse a hierarchical table. The table schema is:

[INSERT CREATE TABLE, e.g., employees(id, name, manager_id)]

I want to get [DESCRIBE THE DESIRED RESULT, e.g., "all subordinates of a given manager, including indirect ones"]

Please:
1. Explain the concept of recursive CTEs (with a simple analogy)
2. Write the query, with comments on each part
3. Include a sample dataset and the expected output
4. Warn about potential pitfalls (e.g., infinite loops, cycles)

Example: For an employees table, the query might be:

WITH RECURSIVE sub_tree AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 1  -- starting manager
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN sub_tree st ON e.manager_id = st.id
)
SELECT * FROM sub_tree;

This prompt is a great teacher. I've used it to understand recursive queries and to generate them for real projects. It's one of those features that seems scary but becomes easy with a good explanation.

12. Generating a Database Health Check Report

When to use: You need a quick overview of your database's health, including index usage, table bloat, and connection stats.

You are a database administrator. I need a health check report for my PostgreSQL database. Please provide a list of diagnostic queries that I can run to assess:
1. Index usage (which indexes are used vs. unused)
2. Table bloat (approximate)
3. Long-running queries
4. Connection count and usage
5. Cache hit ratio

For each query, explain what it does and what an unhealthy result looks like. Also, suggest actions to take if any of the metrics are concerning.

Example: The AI might suggest queries like:

SELECT schemaname, relname, seq_scan, idx_scan FROM pg_stat_user_tables;

This prompt is perfect for a proactive DBA. I've used it to prepare for performance reviews and to catch issues before they become outages. The queries are standard PostgreSQL system queries, so they're safe to run.

Putting It All Together: Your AI-Powered Database Workflow

These prompts are more than just copy-paste templates—they're a mindset. When you approach a database problem, ask the AI not just for an answer, but for the reasoning behind it. Verify the output against official documentation, like the PostgreSQL docs (postgresql.org/docs) or MongoDB docs (docs.mongodb.com), because AI can make mistakes or use outdated syntax. The best way to use these prompts is to adapt them to your specific context. Add your actual table schemas, sample data, and the exact error messages you're seeing. The more context you give, the better the output. Start with the prompt that addresses your current pain point. You'll be amazed at how much time you save. And remember, the goal is not to replace your thinking—it's to augment it. Use these prompts as a springboard, and you'll become a faster, more confident database professional.