15 Prompts for Database Mastery: PostgreSQL, MongoDB, and Redis

Databases are the backbone of every modern application, but writing efficient queries, designing flexible schemas, and keeping performance high requires deep expertise. As an engineering lead and database consultant, I've seen teams spend days debugging a slow query or planning a migration. That's where AI prompts come in.

In this guide, I've curated 15 practical prompts for PostgreSQL, MongoDB, and Redis. You can use them with ChatGPT, Claude, or any LLM of your choice. Each prompt is designed to be a starting point — pasted into the chat, then refined with your actual schema and queries. I've also included concrete examples and explanations to help you understand the underlying concepts.

Note: The examples are simplified for readability. In production, always test on a staging copy of your data.

Prompt archive overview

Write and refactor SQL queries (PostgreSQL)

1. Find duplicate records

Prompt:

You are a DBA with 10 years of PostgreSQL experience. I have a table orders with columns customer_id, order_date, and total. Write a query to find all duplicate customer_id rows (i.e., customers with more than one order on the same day). Show the duplicate count and the total sum for each group.

Example result:

SELECT customer_id, order_date,
       COUNT(*) AS order_count,
       SUM(total) AS daily_total
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1
ORDER BY order_count DESC;

Why it works: This query groups by the natural key and uses HAVING to filter groups with more than one row. It's the most efficient way to find duplicates because it scans the table once.

2. Index suggestions for a slow query

Prompt:

I ran EXPLAIN ANALYZE on a query that filters WHERE status = 'pending' AND created_at > NOW() - INTERVAL '30 days' on a table with 5 million rows. Here is the execution plan: Seq Scan on jobs (cost=0.00..100000.00 rows=50 width=...) (I can paste the full plan). What indexes should I create and why?

Example result:

CREATE INDEX idx_jobs_status_created_at
ON jobs (status, created_at DESC);

Explanation: A composite (status, created_at) index speeds up equality + range queries. The order matters — equality columns first, then range columns. Using DESC helps with ORDER BY or > NOW() limits.

3. Rewrite a nested subquery into a CTE

Prompt:

Rewrite this query using a Common Table Expression (CTE) for readability. SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100 AND created_at > '2026-01-01');

Example result:

WITH high_value_orders AS (
    SELECT user_id
    FROM orders
    WHERE total > 100
      AND created_at > '2026-01-01'
    GROUP BY user_id
)
SELECT u.*
FROM users u
JOIN high_value_orders h ON u.id = h.user_id;

Note: CTEs are also optimization fences in PostgreSQL, but modern versions often inline them.

Model and aggregate in MongoDB

4. Embedding vs. referencing

Prompt:

I'm building a social media app. Posts can have infinite comments. Should I embed comments in the post document or store them in a separate collection? Explain tradeoffs and give a schema design.

Example result:

// For limited comments (e.g., 20 per post), embed:
const post = {
  _id: ObjectId("..."),
  title: "Hello",
  comments: [
    { user: "alice", text: "Great!" },
  ]
};

// For infinite comments, use separate collection with a compound index:
// comments: { _id, postId, text, createdAt }
// Index: { postId: 1, createdAt: -1 }

Why: Embedding reduces read round-trips but limits document size to 16 MB. Referencing scales better for unbounded arrays.

5. Aggregation pipeline for monthly sales

Prompt:

Generate a MongoDB aggregation pipeline that returns total revenue per month for the orders collection, where each document has createdAt (ISODate) and amount (number). Include the month name and year.

Example result:

db.orders.aggregate([
  {
    $group: {
      _id: {
        year: { $year: "$createdAt" },
        month: { $month: "$createdAt" }
      },
      revenue: { $sum: "$amount" }
    }
  },
  {
    $project: {
      _id: 0,
      year: "$_id.year",
      month: "$_id.month",
      revenue: 1
    }
  },
  { $sort: { year: 1, month: 1 } }
])

6. Optimize a query with compound indexes and hint()

Prompt:

I have a collection products with category, price, rating. This query is slow: db.products.find({ category: "electronics", price: { $gte: 100 } }).sort({ rating: -1 }). How do I create the best index and force it?

Example result:

db.products.createIndex(
  { category: 1, price: 1, rating: -1 }
);
db.products.find(...).hint("category_1_price_1_rating_-1");

Explanation: The equality field (category) goes first, followed by the range (price), and finally the sort key (rating) matches the sort direction.

Redis: patterns for performance

7. Rate limiter with INCR and EXPIRE

Prompt:

Implement a rate limiter in Redis using INCR and EXPIRE that allows max 100 requests per minute per user. The key should be ratelimit:user:{userId}. Provide a pseudocode or Python code.

Example result:

def rate_limit(redis, user_id):
    key = f"ratelimit:user:{user_id}"
    current = redis.incr(key)
    if current == 1:
        redis.expire(key, 60)
    if current > 100:
        return False
    return True

Note: Use INCR + EXPIRE atomically with a Lua script or SETEX for better reliability.

8. Caching strategy for a blog

Prompt:

Design a Redis caching layer for a blog. We have posts and comments. Read-heavy with occasional updates. Describe a cache-aside pattern and include TTL recommendations.

Example result:

# Keys:
post:{id}            -> JSON object, TTL 15 min
post:{id}:comments   -> JSON array, TTL 5 min

# On read: get from cache, on miss load from DB and set.
# On write: invalidate keys (delete) or update caches.

Why: TTL balances freshness with less DB load. Invalidate on writes to prevent stale content.

9. Lua script for atomic stock deduction

Prompt:

Write a Lua script for atomic inventory deduction. The key stock:item:{id} stores remaining stock. If stock >= quantity, decrement and return new stock; otherwise return -1.

Example result:

if redis.call('GET', KEYS[1]) >= tonumber(ARGV[1]) then
  return redis.call('DECRBY', KEYS[1], ARGV[1])
else
  return -1
end

Explanation: Redis runs Lua scripts atomically — perfect for caches and reservations without races.

Schema migrations that don't break production

10. PostgreSQL: backfill new column

Prompt:

I need to add a status column to the users table and backfill it based on last_login_at: 'active' if within 30 days, 'inactive' otherwise. Generate a safe migration (add column with default, then update, then add constraint).

Example result:

ALTER TABLE users ADD COLUMN status text;

UPDATE users SET status =
  CASE WHEN last_login_at > NOW() - INTERVAL '30 days'
       THEN 'active' ELSE 'inactive' END;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Tip: Use batching when updating millions of rows: UPDATE ... WHERE id > ? LIMIT 10000 LOOP.

11. MongoDB: change field type

Prompt:

I accidentally stored price as a string in MongoDB. Write a migration to convert all price strings to numbers, iterating in batches to avoid DB overload.

Example result:

let lastId = null;
while (true) {
  const docs = db.products.find(
    lastId ? { _id: { $gt: lastId }, price: { $type: "string" } } : { price: { $type: "string" } }
  ).limit(1000).toArray();
  if (docs.length === 0) break;

  docs.forEach(doc => {
    db.products.updateOne(
      { _id: doc._id },
      { $set: { price: parseFloat(doc.price) } }
    );
  });
  lastId = docs[docs.length - 1]._id;
}

12. Zero-downtime migration planning

Prompt:

I'm migrating our primary database from PostgreSQL to MongoDB. Create a step-by-step migration plan that includes dual-writes, backfill, and cutover, with minimal downtime.

Example result (abridged):

• Enable dual-writes in the application.
• Run initial backfill from a dump.
• Implement change data capture (CDC) to replay diffs.
• Verify data consistency with checksums.
• Perform cutover by switching the read/write connection.

Monitoring & profiling

13. Find and fix expensive queries with pg_stat_statements

Prompt:

Using pg_stat_statements, find the top 5 queries with the highest total execution time on a PostgreSQL server, and suggest how to optimize them based on the query text.

Example result:

SELECT query, calls, total_time / calls AS avg_time_ms
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Then analyze each query with EXPLAIN ANALYZE and create appropriate indexes or refactor.

14. MongoDB explain and index range

Prompt:

Run explain("executionStats") on a query with { userId: 123, createdAt: { $gte: ISODate("2026-01-01") } } and the explain shows COLLSCAN. What index would avoid a full collection scan?

Example result:

db.events.createIndex({ userId: 1, createdAt: 1 });

Check nReturned, totalDocsExamined, and executionTimeMillis in the explain output to verify improvement.

15. Redis slow logs and latency

Prompt:

How do I diagnose Redis latency using SLOWLOG GET and what are the common causes of high latency? Provide a checklist.

Example result:

SLOWLOG GET 50
CONFIG GET slowlog-log-slower-than

Common causes: large keys, KEYS * command (never use), expired key reclamation, and network round-trips.

Key takeaways

• LLM prompts are not magic — they're best used as accelerators for structured thinking.
• Always provide your actual schema, indexes, and EXPLAIN output for tailored advice.
• Test every AI-generated SQL/Ruby/я (LLM) instruction on a pre-production database.

For deeper dives, refer to the official documentation: PostgreSQL, MongoDB Manual, and Redis docs. I also recommend reading the famous “Use The Index, Luke” for SQL tuning.

Have you used AI prompts for database work? Tell me in the comments — I'm curious how your experience compares.