Introduction
Every developer I know spends a non-trivial chunk of their day writing database queries, debugging slow indexes, or planning migrations. Over the past two years, I've collected a set of prompts that I actually use—not theoretical ones from blog posts, but prompts that have saved me hours of manual work. These are battle-tested with real databases: PostgreSQL 16, MongoDB 7, and Redis 7.4. If you're a hands-on developer who uses AI to speed up your workflow, this list is for you.
Why These Prompts Matter
AI assistants have gotten remarkably good at database tasks—if you ask the right questions. A generic prompt like "write a query" often yields mediocre results. But when you structure your prompt with context (schema, version, performance goal), the output becomes production-ready. I've compiled 15 prompts across three major databases, each with a real usage example. No fluff, just what works.
PostgreSQL Prompts
1. Index Candidate Detection
Prompt:
Given this PostgreSQL 16 table schema and a slow query (EXPLAIN ANALYZE output attached), suggest 1-3 indexes that would reduce the sequential scan. Provide CREATE INDEX statements and estimate the improvement.
Real usage example:
I had a query on an orders table with 5 million rows that took 12 seconds. The prompt suggested a composite index on (customer_id, status, created_at). After adding it, the same query ran in 40 ms.
2. Query Rewrite for JOIN Performance
Prompt:
Rewrite this PostgreSQL query to use EXISTS instead of IN for subqueries. Show both versions and explain why EXISTS is faster when the subquery returns many rows.
Real usage example:
A reporting query with WHERE id IN (SELECT ...) was timing out. The rewrite cut execution time from 8 seconds to 0.3 seconds.
3. Migration Safety Check
Prompt:
I need to add a NOT NULL column to a PostgreSQL table with 10 million rows. Write a migration that uses a default value, adds the column, backfills existing rows in batches of 10,000, and then sets the constraint. Include rollback logic.
Real usage example:
Used this to add user_id to a logs table. The batching prevented table-level locks, and the migration ran without downtime.
4. Vacuum and Analyze Schedule
Prompt:
Given autovacuum settings in postgresql.conf, suggest custom vacuum thresholds for a high-write table (1000 inserts/second). Include the SQL to set per-table autovacuum parameters.
Real usage example:
A sessions table was bloating to 20 GB. The suggested settings (autovacuum_vacuum_scale_factor = 0.01) reduced bloat by 70%.
5. JSONB Query Optimization
Prompt:
Write a GIN index for JSONB queries on this PostgreSQL table. Then show how to query for documents where nested field 'metadata->>price' > 100. Include EXPLAIN plan comparison.
Real usage example:
An e-commerce app with JSONB product attributes. The GIN index turned a 2-second query into 15 ms.
MongoDB Prompts
6. Aggregation Pipeline Builder
Prompt:
Design a MongoDB aggregation pipeline that groups orders by customer, calculates total spend, sorts by spend descending, and returns top 10 customers. Use $match to filter by last month only. Include explain() output.
Real usage example:
A dashboard query that was timing out with 10 million documents. The pipeline with proper $match at the start reduced data scanned from 10M to 200K documents.
7. Index Strategy for Compound Queries
Prompt:
Given this MongoDB collection with fields {user_id, status, created_at, amount}, create a compound index that supports: (1) equality on user_id, (2) sort by created_at DESC, (3) filter on status. Use analyzeShardKey() if sharded.
Real usage example:
A social feed query was scanning 50% of the collection. The compound index cut it to index-only scans.
8. Schema Validation Rules
Prompt:
Write a MongoDB schema validation rule that requires 'email' to be a valid email format, 'age' to be between 18 and 120, and 'status' to be one of ['active', 'inactive']. Use $jsonSchema.
Real usage example:
A user registration API was saving malformed emails. The validation caught 5% of bad data before it hit the database.
9. Text Search Optimization
Prompt:
Create a MongoDB text index on 'title' and 'description' fields. Then write a query that searches for 'laptop' with case-insensitive and diacritic-insensitive matching. Show how to boost relevance by field weight.
Real usage example:
A search feature was returning irrelevant results. Weighted text indexes improved precision from 60% to 89%.
10. Change Streams for Real-Time Sync
Prompt:
Write a Python script that listens to MongoDB change streams on the 'orders' collection, filters for 'insert' operations only, and sends the new document to a Redis pub/sub channel. Include error handling and resume token.
Real usage example:
Used for real-time order notifications. The change stream processed 1000 ops/second with < 50 ms latency.
Redis Prompts
11. Cache Invalidation Strategy
Prompt:
Design a Redis caching layer for PostgreSQL query results. Use a hash for user profiles with TTL 1 hour. Write the logic to invalidate cache when the source row is updated. Use Lua scripting for atomicity.
Real usage example:
A profile API was hitting PostgreSQL on every request. Caching with invalidation reduced DB load by 80%.
12. Rate Limiting with Sorted Sets
Prompt:
Implement a sliding window rate limiter in Redis using sorted sets. Allow 100 requests per minute per user_id. Return current count and remaining TTL. Write the Lua script.
Real usage example:
An API gateway was being hammered. The sorted set approach handled 10,000 concurrent users with < 1 ms overhead.
13. Session Store with RedisJSON
Prompt:
Store user sessions as RedisJSON documents with fields: user_id, roles, expires_at. Write commands to set a session, get it, and delete on logout. Include TTL of 30 minutes.
Real usage example:
Migrated sessions from a PostgreSQL table to RedisJSON. Session lookup went from 5 ms to 0.2 ms.
14. Distributed Lock Pattern
Prompt:
Implement a Redlock-compatible distributed lock in Redis using SET NX EX. Write the acquire and release functions with lock ID and auto-expiry. Handle clock drift.
Real usage example:
A cron job was running on multiple servers. Distributed locks ensured only one instance executed the job at a time.
15. Stream Processing for Event Logs
Prompt:
Create a Redis stream 'event_logs' with fields: type, payload, timestamp. Write a consumer group that processes events in batches of 10 and acknowledges them. Include XREADGROUP and XACK.
Real usage example:
Replaced a Kafka queue for low-volume event logging. Redis streams handled 500 events/second reliably.
Putting It All Together
These prompts aren't one-size-fits-all. You'll need to adapt them to your schema, data volume, and version. But they give you a starting point that's already proven in production. I keep this list in my notes and use it daily.
Conclusion
Database work is 80% asking the right questions. These 15 prompts cover the most common pain points: slow queries, index design, migrations, caching, and real-time processing. Try them on your next task. If you find a prompt that consistently works, share it with your team. And if you want a structured way to practice these patterns, ASI Biont supports connecting to databases like PostgreSQL, MongoDB, and Redis directly from the browser — no local setup required. Learn more at asibiont.com/courses.
Happy querying.
Comments