SQL and MongoDB Prompts: 12 Scenarios from Query Optimization to Zero-Downtime PostgreSQL Migrations

SQL and MongoDB Prompts: 12 Scenarios from Query Optimization to Zero-Downtime PostgreSQL Migrations

Database work is often a bottleneck: slow queries, risky migrations, and complex schema changes can stall development. AI assistants like ChatGPT, Claude, or GitHub Copilot can help, but only if you ask the right questions. Generic prompts yield generic advice; precise prompts yield actionable SQL, execution plans, and migration strategies.

In this article, I've compiled 12 prompts covering real-world scenarios: from optimizing a sluggish query to migrating a production PostgreSQL cluster without downtime. Each prompt includes context, expected output, and a code example. Whether you're a DBA, backend developer, or data engineer, these prompts will save you hours of trial and error.

1. Explain and optimize a slow query

Task: Understand why a query is slow and get optimization suggestions.

Prompt:

You are a PostgreSQL expert. Analyze the following query and its EXPLAIN ANALYZE output. Identify the bottleneck and suggest optimizations (indexes, rewrites, configuration). Provide the improved query and estimated impact.

Query:
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'shipped'
ORDER BY o.created_at DESC
LIMIT 100;

EXPLAIN ANALYZE output:
[insert output]

Example result:
The AI might suggest creating a composite index on (status, created_at) and rewriting the join to use INCLUDE columns. For instance:

CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC) INCLUDE (customer_id, total);

This can turn a sequential scan into an index-only scan, reducing execution time from seconds to milliseconds.

2. Generate a safe migration script for PostgreSQL

Task: Add a new column with a default value without locking the table.

Prompt:

Write a PostgreSQL migration script to add a column `email_verified BOOLEAN DEFAULT FALSE` to a table `users` with 10 million rows. The migration must not lock the table for more than a few milliseconds. Use a step-by-step approach compatible with zero-downtime deployment.

Example result:
The AI suggests:

-- Step 1: Add column without default (fast)
ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

-- Step 2: Set default for new rows (fast)
ALTER TABLE users ALTER COLUMN email_verified SET DEFAULT FALSE;

-- Step 3: Backfill in batches (background)
UPDATE users SET email_verified = FALSE WHERE email_verified IS NULL;
-- Repeat until all rows updated, then set NOT NULL
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

This avoids a full table rewrite and long locks.

3. Design indexes for a MongoDB collection

Task: Improve query performance for a MongoDB collection with millions of documents.

Prompt:

I have a MongoDB collection `products` with documents like:
{ _id, name, category, price, tags: ["a","b"], created_at }
Queries: find by category and price range, sort by created_at, and text search on name.
Suggest indexes and explain the trade-offs.

Example result:
The AI recommends:

db.products.createIndex({ category: 1, price: 1 });
db.products.createIndex({ created_at: -1 });
db.products.createIndex({ name: "text" });

It also explains that the compound index supports queries on category alone, but not on price alone. For text search, a text index is needed.

4. Convert SQL to MongoDB aggregation

Task: Translate a complex SQL query to MongoDB aggregation pipeline.

Prompt:

Convert this SQL query to a MongoDB aggregation pipeline:
SELECT category, COUNT(*) as cnt, AVG(price) as avg_price
FROM products
WHERE price > 100
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

Example result:
The AI returns:

db.products.aggregate([
  { $match: { price: { $gt: 100 } } },
  { $group: { _id: "$category", cnt: { $sum: 1 }, avg_price: { $avg: "$price" } } },
  { $match: { cnt: { $gt: 5 } } },
  { $sort: { avg_price: -1 } }
]);

It also notes that $match before $group uses indexes, and $sort after $group may require memory.

5. Generate a zero-downtime migration plan for PostgreSQL

Task: Rename a column without breaking the application.

Prompt:

We need to rename column `user_name` to `username` in table `accounts` (5M rows) with zero downtime. The application is deployed continuously. Provide a step-by-step plan using views or dual-write.

Example result:
The AI suggests:
1. Add new column username.
2. Create a trigger to copy data from user_name to username on insert/update.
3. Backfill existing rows.
4. Update application to read/write username.
5. Drop old column and trigger.

It provides the trigger code:

CREATE OR REPLACE FUNCTION sync_username() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.username IS NULL THEN
    NEW.username := NEW.user_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_username
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION sync_username();

6. Analyze and fix N+1 query problem

Task: Detect and resolve N+1 queries in an ORM.

Prompt:

My Django app runs 1 query to fetch orders and then N queries to fetch each customer. How can I fix this using select_related or prefetch_related? Show before and after code.

Example result:
Before:

orders = Order.objects.all()
for order in orders:
    print(order.customer.name)

After:

orders = Order.objects.select_related('customer').all()
for order in orders:
    print(order.customer.name)

The AI explains that select_related uses a JOIN, while prefetch_related does a separate query and joins in Python.

7. Write a stored procedure for data archiving

Task: Move old records to an archive table.

Prompt:

Write a PostgreSQL stored procedure to move rows older than 1 year from `logs` to `logs_archive`, in batches of 10000, to avoid long locks. Include error handling.

Example result:

CREATE OR REPLACE PROCEDURE archive_logs() LANGUAGE plpgsql AS $$
DECLARE
  rows_moved INT;
BEGIN
  LOOP
    WITH moved AS (
      DELETE FROM logs
      WHERE created_at < NOW() - INTERVAL '1 year'
      RETURNING *
    )
    INSERT INTO logs_archive SELECT * FROM moved;
    GET DIAGNOSTICS rows_moved = ROW_COUNT;
    EXIT WHEN rows_moved = 0;
    COMMIT;
  END LOOP;
END;
$$;

8. Optimize a MongoDB aggregation pipeline

Task: Speed up a slow aggregation.

Prompt:

This MongoDB aggregation takes 10 seconds on 5M documents:
[ pipeline ]
Suggest optimizations: index usage, pipeline reordering, allowDiskUse.

Example result:
The AI might suggest moving $match to the beginning, adding an index on the match fields, and using $project early to reduce document size. It also notes that $group and $sort can use memory; if exceeding 100MB, enable allowDiskUse: true.

9. Generate test data for a database

Task: Populate tables with realistic fake data.

Prompt:

Write a Python script using Faker to insert 10000 rows into PostgreSQL tables `users` and `orders` with foreign keys. Ensure referential integrity.

Example result:
The AI provides a script using psycopg2 and Faker, inserting users first, then orders with valid user_id.

import psycopg2
from faker import Faker
fake = Faker()
conn = psycopg2.connect(...)
cur = conn.cursor()
for _ in range(10000):
    cur.execute("INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id", (fake.name(), fake.email()))
    user_id = cur.fetchone()[0]
    cur.execute("INSERT INTO orders (user_id, total) VALUES (%s, %s)", (user_id, fake.random_number(5)))
conn.commit()

10. Diagnose and fix deadlocks

Task: Understand and prevent deadlocks in PostgreSQL.

Prompt:

My application occasionally gets deadlock errors. Explain common causes and how to resolve them. Provide a PostgreSQL query to detect deadlocks from logs.

Example result:
The AI explains that deadlocks occur when transactions lock resources in different orders. It suggests consistent ordering of updates and using SELECT ... FOR UPDATE carefully. To detect, check pg_stat_activity and logs.

11. Migrate from MongoDB to PostgreSQL

Task: Plan a migration from a document store to a relational database.

Prompt:

We want to migrate a MongoDB collection `orders` with embedded items to PostgreSQL. Suggest a schema design and a migration strategy using a tool like pgloader or custom ETL.

Example result:
The AI recommends normalizing into orders and order_items tables, and provides a sample schema. For migration, it suggests exporting to CSV and using COPY, or using pgloader with a custom cast.

12. Automate index maintenance

Task: Rebuild fragmented indexes in PostgreSQL.

Prompt:

Write a script to detect bloated indexes in PostgreSQL and rebuild them concurrently without locking. Use pgstattuple and REINDEX CONCURRENTLY.

Example result:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey';

For bloated indexes, use:

REINDEX INDEX CONCURRENTLY index_name;

This rebuilds without blocking writes.

These 12 prompts cover the most common database challenges. By providing clear context and expected output, you can leverage AI to generate production-ready SQL, migration plans, and optimizations. Always test suggestions in a staging environment, and remember that AI is a tool, not a replacement for understanding your data.

Ready to dive deeper? Try these prompts in your next database task, and share your results. For more AI-powered development tips, explore our blog.