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

Introduction

Databases are the backbone of modern applications, but mastering them requires deep knowledge of query languages, indexing strategies, and performance tuning. Whether you are working with relational (PostgreSQL), document (MongoDB), or key-value stores (Redis), the right prompt can save hours of manual work. Below are 10 carefully crafted prompts you can use with an AI assistant to accelerate common database tasks — from writing complex queries to optimizing indexes and planning migrations.

Each prompt includes a clear task, the exact text to give the AI, and a realistic example output. The prompts are organized by database and use case.

1. Generate a PostgreSQL Aggregation Query

Task: Transform a business requirement into a SQL query with grouping and filtering.

Prompt:

I have a PostgreSQL database with tables:
- customers (id, name, email, signup_date)
- orders (id, customer_id, order_date, total)

Write a query to find the top 5 customers by total spend in the last 30 days. Return customer name, email, and total amount. Also explain the query logic.

Example Output:

SELECT c.name, c.email, SUM(o.total) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 5;

Explanation: This query joins customers and orders, filters orders from the last 30 days, groups by customer, sums the total, and returns the top five.

2. Build a MongoDB Aggregation Pipeline

Task: Write a MongoDB aggregation pipeline for a sales analytics report.

Prompt:

I have a MongoDB collection "orders" with documents:
{ _id: ObjectId, customerId: "...", items: [{ product: "...", quantity: 1, price: 10 }], orderDate: ISODate }

Create an aggregation pipeline that:
- Filters orders from the last quarter
- Unwinds the items array
- Groups by product name and sums quantity and revenue
- Sorts by total revenue descending
- Limits to 10 products

Example Output:

[ { $match: { orderDate: { $gte: new Date("2026-04-01"), $lt: new Date("2026-07-01") } } },
  { $unwind: "$items" },
  { $group: { _id: "$items.product", qty: { $sum: "$items.quantity" }, revenue: { $sum: { $multiply: ["$items.quantity", "$items.price"] } } } },
  { $sort: { revenue: -1 } },
  { $limit: 10 },
  { $project: { product: "$_id", qty: 1, revenue: 1, _id: 0 } }
]

3. Redis Command for Caching a User Session

Task: Implement a session caching pattern using Redis.

Prompt:

Design a Redis data structure and commands to store user sessions. Sessions expire after 1 hour. Include commands to:
- Create a session with user data
- Read a session by session ID
- Update the TTL on access
- Delete a session on logout

Example Output:

# Create session (store as a JSON string in a string key with expiry)
SET session:abc123 '{"userId": 42, "role": "admin"}' EX 3600

# Read session (GET returns JSON string)
GET session:abc123

# Update TTL on each access (sliding expiration)
EXPIRE session:abc123 3600

# Delete session on logout
DEL session:abc123

4. PostgreSQL Index Optimization Analysis

Task: Analyze existing indexes and suggest improvements.

Prompt:

Given the PostgreSQL table:
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    status VARCHAR(20),
    total NUMERIC(10,2)
);

Current indexes: only primary key on id. The most frequent query is:
SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2026-01-01';

Recommend the best index or indexes. Include CREATE INDEX statements and explain why a composite index may be better than single-column ones.

Example Output:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Explanation: A B-tree composite index on (customer_id, order_date) allows the query to first filter by customer and then by date range efficiently. Since order_date is a range condition, it should be placed after the equality column (customer_id).

5. MongoDB Index Strategy for a Common Query

Task: Design indexes for a MongoDB collection with heavy read patterns.

Prompt:

Collection "transactions" has fields: accountId, amount, timestamp, type ("credit" | "debit"). Common query:
db.transactions.find({ accountId: "acc789", type: "debit", timestamp: { $gte: ISODate("2026-01-01") } }).sort({ timestamp: -1 })

Propose an index that covers the query (covered index if possible). Write the createIndex command and explain the order of fields.

Example Output:

db.transactions.createIndex(
  { accountId: 1, type: 1, timestamp: -1 },
  { name: "acc_type_time_desc" }
);

Explanation: Equality fields (accountId, type) come before the sort field (timestamp). Using descending order on timestamp avoids an in-memory sort. To make it a covered query, include only projected fields in the index, but here we assume SELECT * is used.

6. Redis Performance Tuning – Identifying Slow Commands

Task: Identify and optimize expensive Redis commands.

Prompt:

Redis instance is experiencing high latency. I have enabled the slow log with threshold 10ms.
The slow log shows many KEYS * and SMEMBERS commands on sets with millions of elements.
Explain why these commands are slow and suggest how to refactor the data model to avoid them.

Example Output:
KEYS * is O(N) and blocks the event loop, scanning the entire keyspace. Use SCAN with a cursor instead. For large sets, SMEMBERS returns all members — use SSCAN or replace the set with a sorted set and query with ZRANGEBYSCORE if applicable. Alternative: store data in a hash and use HSCAN.

7. PostgreSQL Migration Script – Add a Not-Null Column

Task: Generate a safe migration script for adding a column to a large table.

Prompt:

I need to add a non-null column "last_login" of type TIMESTAMP to a PostgreSQL table "users" with 10 million rows. Write a migration script that avoids locking the table for a long time. Include steps for backfilling data and adding the constraint.

Example Output:

-- Step 1: Add column without NOT NULL (instant)
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Step 2: Backfill existing rows in batches (avoid long tx)
WITH batch AS (
  SELECT id FROM users WHERE last_login IS NULL LIMIT 10000 FOR UPDATE SKIP LOCKED
)
UPDATE users SET last_login = NOW() WHERE id IN (SELECT id FROM batch);
-- Repeat until no rows left.

-- Step 3: Set NOT NULL after backfill
ALTER TABLE users ALTER COLUMN last_login SET NOT NULL;

8. MongoDB Schema Migration – Rename a Field

Task: Write a migration to rename a field across all documents in a collection.

Prompt:

Collection "products" currently uses field "prod_name". We want to rename it to "name". Write a MongoDB script using updateMany with $rename, and also explain how to handle indexes.

Example Output:

db.products.updateMany({}, { $rename: { "prod_name": "name" } });

Note: The $rename operation has limitations: it fails if the new field name exists on the same document. Ensure no current field named "name". After renaming, drop and recreate any indexes that referenced "prod_name".

9. Explain PostgreSQL Query Plan

Task: Interpret an EXPLAIN ANALYZE output and suggest improvements.

Prompt:

Here is the EXPLAIN ANALYZE output for a slow query (attached). Review it and identify:
- Costliest node
- Whether sequential scans are happening on large tables
- Missing indexes
- Any other bottlenecks

Query: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.email LIKE '%@example.com';

Example Output:
The plan shows a Sequential Scan on customers (rows=500k) with a Filter on email, then a Nested Loop join with orders. An index on customers(email) using CREATE INDEX idx_customers_email ON customers (email text_pattern_ops); would convert the sequential scan to an index scan. Also consider a partial index if the pattern is fixed.

10. Redis Data Modeling for Session Store with Expiry

Task: Model a session store with session data and per-session expiry.

Prompt:

Design a Redis schema for web sessions containing: userId, role, lastAccess. Each session must expire 30 minutes after last access. Write the commands for creating, reading, and extending a session. Use hashes instead of JSON strings to allow atomic updates.

Example Output:

# Create session (hash fields)
HSET session:abc123 userId 42 role admin lastAccess "2026-07-27T12:00:00Z"
EXPIRE session:abc123 1800

# Read session (get all fields)
HGETALL session:abc123

# Extend TTL (sliding expiry) using EXPIRE again on each request
EXPIRE session:abc123 1800

Summary Table

Conclusion

These 10 prompts cover the most common database tasks you'll face across PostgreSQL, MongoDB, and Redis. By feeding them to an AI assistant, you can quickly generate accurate code, get expert advice on optimization, and avoid common pitfalls. Remember to always test generated queries against your actual data and monitor performance. For further depth, refer to the official documentation (PostgreSQL docs, MongoDB manual, Redis commands) — the AI output should always be validated. Try these prompts today and supercharge your database workflow!