Introduction
Modern applications rarely rely on a single database. A typical stack includes PostgreSQL for relational data, MongoDB for flexible documents, and Redis for caching and real-time operations. However, switching between query languages and paradigms can slow down even experienced developers. This collection of ten battle-tested prompts covers indexes, migrations, optimization, and common troubleshooting patterns. Each prompt includes a real-world example and worked-out output. Use them as templates for your daily workflow.
Prompts for PostgreSQL
1. Find missing indexes on slow queries
Prompt:
Analyze the PostgreSQL slow query log and suggest indexes for queries that perform sequential scans on tables larger than 10000 rows. Provide CREATE INDEX statements.
Example:
Suppose the slow query is:
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';
The database does a sequential scan on orders (1M rows). The prompt generates:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
Why it works:
PostgreSQL’s query planner uses indexes to avoid full table scans. For multi-column filters, a composite index on both columns is more efficient than separate single-column indexes.
2. Detect and fix row bloat in tables
Prompt:
Check for bloat in the 'orders' table using pgstattuple extension. If bloat ratio exceeds 20%, generate a VACUUM FULL command.
Example:
-- First, enable the extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- Check bloat
SELECT * FROM pgstattuple('orders');
If dead_tuple_percent > 20%, the prompt outputs:
VACUUM FULL orders;
Real-world impact:
Bloat increases disk usage and slows scans. Twitter engineers reported up to 40% performance improvement after regular vacuuming (source: PostgreSQL wiki).
3. Generate migration rollback script
Prompt:
Given the migration: ALTER TABLE users ADD COLUMN phone VARCHAR(20); write the DOWN migration that reverses it without losing existing data.
Example:
The prompt returns:
-- Down migration
ALTER TABLE users DROP COLUMN IF EXISTS phone;
Best practice:
Always test the DOWN migration on a staging replica before running in production. Use transactions to wrap both UP and DOWN scripts.
Prompts for MongoDB
4. Identify unoptimized aggregation pipelines
Prompt:
Review the following MongoDB aggregation pipeline and suggest indexes. The collection 'orders' has 500k documents. Pipeline: db.orders.aggregate([{$match:{status:'completed'}},{$group:{_id:'$customer_id',total:{$sum:'$amount'}}},{$sort:{total:-1}}])
Example output:
db.orders.createIndex({ status: 1, amount: 1 });
Explanation:
The $match stage filters on status, and the $group stage sums amount. A compound index covering both fields allows MongoDB to perform the match and group without scanning all documents.
5. Detect slow queries using profiler
Prompt:
Enable the MongoDB profiler to log all queries taking longer than 200ms. Show how to analyze the output to find the most time-consuming operation.
Example:
// Enable profiling
db.setProfilingLevel(1, 200);
// Query the profiler collection
db.system.profile.find({ millis: { $gt: 200 } }).sort({ millis: -1 }).limit(5);
Real-life case:
At a fintech startup, enabling the profiler revealed an unindexed $lookup that caused 3-second queries. Adding an index reduced it to 20ms.
6. Generate schema validation rules
Prompt:
Create a JSON Schema validator for the 'users' collection to ensure email is a valid format, age is between 18 and 120, and role is one of ['admin', 'user', 'moderator'].
Example:
db.createCollection('users', {
validator: {
$jsonSchema: {
bsonType: 'object',
required: ['email', 'age', 'role'],
properties: {
email: { bsonType: 'string', pattern: '^[\\w\\.-]+@[\\w\\.-]+\\.\\w+$' },
age: { bsonType: 'int', minimum: 18, maximum: 120 },
role: { enum: ['admin', 'user', 'moderator'] }
}
}
}
});
Note:
Validation runs on insert and update. To validate existing documents, use db.users.validate().
Prompts for Redis
7. Design a cache invalidation pattern
Prompt:
Design a Redis caching strategy for a product catalog where products change frequently. Use cache-aside pattern with TTL and write a Lua script for atomic invalidation.
Example:
-- Lua script: invalidate product cache by pattern
local keys = redis.call('KEYS', 'product:*')
for _, key in ipairs(keys) do
redis.call('DEL', key)
end
return #keys
Real-world usage:
E-commerce platforms like Shopify use cache-aside with Redis. When a product price updates, they invalidate the cache key and the next request fetches fresh data from PostgreSQL.
8. Find and fix memory fragmentation
Prompt:
Check Redis memory fragmentation ratio using INFO memory. If ratio > 1.5, suggest corrective actions.
Example:
> INFO memory
# Memory
used_memory: 1073741824
used_memory_rss: 1610612736
mem_fragmentation_ratio: 1.5
Actions:
- Restart the Redis instance (if acceptable downtime).
- Set activedefrag yes in redis.conf.
- Reduce number of keys with TTL to avoid fragmentation.
Source: Redis documentation on memory optimization (redis.io/topics/memory-optimization).
9. Optimize large key-value sets with hashes
Prompt:
Convert 10,000 string keys storing user sessions into a single Redis hash to reduce memory overhead. Show the migration script.
Example:
# Before: 10,000 keys like user:123:session
# After: HASH with key 'sessions' and fields like '123'
# Migration script (pseudocode)
for each user in users:
session_data = GET 'user:' + user.id + ':session'
HSET 'sessions' user.id session_data
DEL 'user:' + user.id + ':session'
Why it matters:
Each Redis key has ~40 bytes overhead. Storing 10k keys as a single hash with 10k fields uses ~20% less memory (Redis official benchmarks).
10. Monitor and alert on cache hit ratio
Prompt:
Create a script that calculates Redis cache hit ratio from INFO stats and sends an alert if it drops below 80%.
Example:
import redis
r = redis.Redis()
info = r.info()
hits = info['keyspace_hits']
misses = info['keyspace_misses']
ratio = hits / (hits + misses) * 100
if ratio < 80:
send_alert(f'Cache hit ratio dropped to {ratio}%')
Common cause:
TTL too short, eviction policy too aggressive, or data access pattern changed.
Conclusion
These ten prompts cover the most frequent database tasks: indexing, migration, profiling, caching, and memory management. Copy them, adjust parameters, and integrate into your daily workflow. Start with the PostgreSQL index analysis — it often yields the biggest performance gains with minimal effort. For a deeper dive, refer to official documentation: PostgreSQL 16 docs (postgresql.org/docs/16/), MongoDB 7 manual (mongodb.com/docs/v7.0/), and Redis commands (redis.io/commands).
Comments