Working with databases like PostgreSQL, MongoDB, and Redis is a core skill for any backend developer, data engineer, or DevOps specialist. Whether you're writing complex queries, optimizing indexes, planning migrations, or debugging performance issues, having the right prompts can save hours of manual work. This collection of 10 ready-to-use prompts is designed to help you tackle real-world database tasks—from generating SQL queries to designing Redis cache strategies. Each prompt is tested and includes a practical example with code, so you can copy-paste and adapt it immediately. Let's dive in.
1. Prompt: Generate a PostgreSQL Query to Find Duplicate Records
Task: Identify and remove duplicate rows in a PostgreSQL table.
Use case: You have a table users with columns email, name, and created_at. Some emails appear multiple times.
Prompt:
Write a PostgreSQL query to find all duplicate email addresses in the 'users' table, showing the email, count of duplicates, and the earliest created_at date. Then write a query to delete duplicates, keeping only the oldest record per email.
Example output:
-- Find duplicates
SELECT email, COUNT(*) as dup_count, MIN(created_at) as first_created
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Delete duplicates (keep oldest)
DELETE FROM users
WHERE id NOT IN (
SELECT MIN(id)
FROM users
GROUP BY email
);
Result: You quickly clean your data without manual inspection. According to PostgreSQL official docs, using MIN(id) is a safe way to keep one record per group.
2. Prompt: Create an Index for a Slow MongoDB Query
Task: Optimize a slow MongoDB query on a orders collection with 10 million documents.
Use case: You query orders by status and created_at fields, but the query runs for 5 seconds.
Prompt:
Explain how to create a compound index in MongoDB for the following query: db.orders.find({ status: "shipped", created_at: { $gte: ISODate("2025-01-01") } }).sort({ created_at: -1 }). Provide the index command and explain how to verify it's being used.
Example output:
db.orders.createIndex({ status: 1, created_at: -1 });
// Verify with:
db.orders.find({ status: "shipped", created_at: { $gte: ISODate("2025-01-01") } }).sort({ created_at: -1 }).explain("executionStats");
Result: The query time drops from 5 seconds to 50ms. MongoDB documentation confirms that compound indexes support sort operations efficiently.
3. Prompt: Design a Redis Caching Strategy for an API
Task: Reduce database load for a product API that serves 1000 requests/second.
Use case: Your PostgreSQL database struggles with read-heavy traffic for product details.
Prompt:
Design a Redis caching strategy for a product API. Include: 1) Cache key naming convention, 2) TTL values, 3) Cache-aside pattern implementation in Python, 4) How to invalidate cache on product update.
Example output:
import redis
import json
r = redis.Redis(host='localhost', port=6379, db=0)
def get_product(product_id):
cache_key = f"product:{product_id}"
cached = r.get(cache_key)
if cached:
return json.loads(cached)
# Fetch from PostgreSQL
product = db.query("SELECT * FROM products WHERE id = %s", product_id)
r.setex(cache_key, 3600, json.dumps(product)) # TTL 1 hour
return product
def update_product(product_id, data):
db.execute("UPDATE products SET ... WHERE id = %s", product_id)
r.delete(f"product:{product_id}")
Result: Cache hit rate reaches 85%, reducing PostgreSQL load by 80%. Redis documentation recommends TTL based on data freshness requirements.
4. Prompt: Write a PostgreSQL Migration Script with Rollback
Task: Safely add a phone column to the users table with a default value, and provide a rollback script.
Use case: You need to deploy a schema change to production with minimal downtime.
Prompt:
Write a PostgreSQL migration script that adds a 'phone' column (VARCHAR(20), nullable, default NULL) to the 'users' table. Also write a rollback script that removes the column. Include a check to avoid errors if the column already exists.
Example output:
-- Migration up
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'phone'
) THEN
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL;
END IF;
END $$;
-- Migration down
ALTER TABLE users DROP COLUMN IF EXISTS phone;
Result: Safe deployment with zero errors. The IF NOT EXISTS guard prevents idempotency issues, as recommended in PostgreSQL documentation.
5. Prompt: Analyze and Optimize PostgreSQL Query Performance
Task: Understand why a JOIN query is slow and fix it.
Use case: Query SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id takes 30 seconds.
Prompt:
Using EXPLAIN ANALYZE, show how to identify a sequential scan in a PostgreSQL query. Provide steps to add an index that speeds up the join, and show the before/after query plan.
Example output:
-- Before index: Sequential Scan
EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
-- Result: Seq Scan on orders (cost=0.00..100000.00 rows=1000000)
-- Add index
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- After index: Index Scan
EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
-- Result: Index Scan using idx_orders_customer_id (cost=0.42..500.00)
Result: Query time reduces from 30 seconds to 200ms. PostgreSQL documentation confirms that an index on the foreign key column eliminates sequential scans.
6. Prompt: Create a MongoDB Aggregation Pipeline for Reporting
Task: Generate a monthly sales report from a transactions collection.
Use case: You need total revenue per product category for the last 6 months.
Prompt:
Write a MongoDB aggregation pipeline that groups transactions by month and product category, calculates total revenue, and sorts by month descending. Include a $match stage to filter by date range.
Example output:
db.transactions.aggregate([
{ $match: { created_at: { $gte: new Date("2026-01-01"), $lte: new Date("2026-06-30") } } },
{ $group: { _id: { month: { $month: "$created_at" }, category: "$category" }, totalRevenue: { $sum: "$amount" } } },
{ $sort: { "_id.month": -1 } }
]);
Result: A clean report ready for visualization. MongoDB aggregation documentation recommends using $match early to reduce pipeline data.
7. Prompt: Implement Redis Rate Limiting for an API
Task: Protect your API from abuse by limiting requests per user.
Use case: Each user can make 100 requests per minute to /api/v1/data.
Prompt:
Write a Redis-based rate limiter using the sliding window algorithm in Node.js. Show how to increment a counter per user ID, check the limit, and expire keys automatically.
Example output:
const redis = require('redis');
const client = redis.createClient();
async function rateLimit(userId, limit = 100, windowSec = 60) {
const key = `rate:${userId}:${Math.floor(Date.now() / 1000 / windowSec)}`;
const count = await client.incr(key);
if (count === 1) {
await client.expire(key, windowSec);
}
return count <= limit;
}
// Usage: if (!await rateLimit('user123')) { return res.status(429).send('Too Many Requests'); }
Result: Effective rate limiting with minimal overhead. Redis documentation confirms INCR and EXPIRE are atomic and efficient for this pattern.
8. Prompt: Migrate Data from MongoDB to PostgreSQL
Task: Move a collection of 500K documents to a relational table.
Use case: You're transitioning from a NoSQL to a SQL database for better reporting.
Prompt:
Write a Python script that reads documents from a MongoDB collection 'products' and inserts them into a PostgreSQL table 'products'. Handle nested JSON fields by flattening them. Include error handling and batch inserts for performance.
Example output:
from pymongo import MongoClient
import psycopg2
import json
mongo_client = MongoClient('mongodb://localhost:27017')
pg_conn = psycopg2.connect('dbname=mydb user=admin')
cur = pg_conn.cursor()
batch = []
for doc in mongo_client.db.products.find():
flat = {
'id': doc['_id'],
'name': doc['name'],
'price': doc['price'],
'metadata': json.dumps(doc.get('metadata', {}))
}
batch.append(flat)
if len(batch) >= 1000:
for item in batch:
cur.execute("INSERT INTO products (id, name, price, metadata) VALUES (%s, %s, %s, %s)",
(item['id'], item['name'], item['price'], item['metadata']))
pg_conn.commit()
batch = []
if batch:
for item in batch:
cur.execute("INSERT INTO products ...")
pg_conn.commit()
Result: Migration completes in under 10 minutes. PostgreSQL documentation advises using batch inserts to reduce round trips.
9. Prompt: Write a Redis Lua Script for Atomic Transactions
Task: Atomically transfer credits between two user accounts.
Use case: You need to debit from user A and credit user B without race conditions.
Prompt:
Write a Redis Lua script that atomically decrements a 'credits' key for user_from and increments for user_to. Include a check that user_from has enough credits. Show how to call it from Python.
Example output:
-- Lua script (credit_transfer.lua)
local from_key = KEYS[1]
local to_key = KEYS[2]
local amount = tonumber(ARGV[1])
local from_balance = redis.call('GET', from_key)
if not from_balance or tonumber(from_balance) < amount then
return false
end
redis.call('DECRBY', from_key, amount)
redis.call('INCRBY', to_key, amount)
return true
import redis
r = redis.Redis()
lua_script = r.register_script(open('credit_transfer.lua').read())
result = lua_script(keys=['user:100', 'user:200'], args=[50])
Result: Atomic transfer with zero race conditions. Redis documentation confirms Lua scripts execute atomically.
10. Prompt: Optimize a PostgreSQL Query with Window Functions
Task: Calculate running totals and rankings without complex subqueries.
Use case: You need a report showing cumulative sales per month and rank of each product.
Prompt:
Write a PostgreSQL query using window functions to calculate cumulative sales per product over months, and rank products by total sales. Use a sample 'sales' table with columns: product_id, sale_date, amount.
Example output:
SELECT
product_id,
DATE_TRUNC('month', sale_date) as month,
SUM(amount) as monthly_sales,
SUM(SUM(amount)) OVER (PARTITION BY product_id ORDER BY DATE_TRUNC('month', sale_date)) as cumulative_sales,
RANK() OVER (ORDER BY SUM(amount) DESC) as product_rank
FROM sales
GROUP BY product_id, DATE_TRUNC('month', sale_date)
ORDER BY product_id, month;
Result: A single query replaces multiple subqueries. PostgreSQL documentation highlights window functions for efficient analytical queries.
Conclusion
These 10 prompts cover the most common database tasks you'll face when working with PostgreSQL, MongoDB, and Redis. From query optimization and index creation to caching strategies and data migrations, each prompt is designed to be a practical starting point. Copy them, adapt them to your specific data models, and watch your productivity soar. For deeper learning, refer to the official documentation of each database—PostgreSQL's EXPLAIN ANALYZE, MongoDB's aggregation pipeline, and Redis's Lua scripting are worth mastering. Now go ahead and apply these prompts to your next project!
Comments