Introduction
If you've ever stared at a query that takes 30 seconds to run while your users wait impatiently, you know the pain. Database optimization is often seen as a dark art, reserved for DBAs with decades of experience. But in 2026, with the rise of AI assistants, you can put that expertise on autopilot. I've compiled ten battle-tested prompts that have saved me countless hours when working with PostgreSQL and MongoDB. These aren't theoretical exercises—they're the exact prompts I use to diagnose slow queries, design indexes, and refactor schemas. Whether you're a backend developer, a data engineer, or a solo founder wearing all hats, these prompts will help you identify bottlenecks and apply fixes faster than ever. Let's dive in.
1. The EXPLAIN Interpreter: Decoding PostgreSQL's Query Plan
Prompt: "I'm running the following PostgreSQL query: [INSERT QUERY]. It's slow. Please explain the output of EXPLAIN ANALYZE for this query, focusing on: 1) Which steps are the most expensive (highest execution time), 2) Why a sequential scan might be happening instead of an index scan, 3) Any red flags like nested loops over large datasets. Provide actionable recommendations to improve performance."
Why it works: EXPLAIN ANALYZE output is cryptic for beginners. This prompt forces the AI to act as a translator, turning raw plan nodes into human-readable insights. It also guides the AI to focus on specifics, not generic advice.
Example: I once used this on a query joining three tables with multiple WHERE clauses. The AI immediately spotted a sequential scan on a 10-million-row table because of a function in the WHERE clause that prevented index usage. The fix: rewrite the query to avoid the function, cutting execution time from 15 seconds to 200ms.
2. Index Designer: Creating the Perfect Index Set
Prompt: "Given the following PostgreSQL table: [CREATE TABLE statement] and the most common queries: [LIST OF QUERIES], design an optimal set of indexes. For each index, explain: 1) The columns and their order, 2) Why this index helps these specific queries, 3) Potential downsides (write overhead, storage). Also, suggest which indexes to drop if they are redundant."
Why it works: Index design is a science. The AI can analyze query patterns and suggest composite indexes, partial indexes, or even covering indexes that you might not have considered. It also helps you avoid index bloat.
Example: For a time-series table with queries filtering by user_id and created_at, the AI suggested a composite index (user_id, created_at DESC). This single index replaced two separate ones, reducing write overhead and speeding up range scans by 40%.
3. Slow Query Autopsy: Profiling and Root Cause Analysis
Prompt: "I have a PostgreSQL query that was fast last week but is now slow. [INSERT QUERY] and the output of \dt+ for the tables involved. What could have changed? Check for: 1) Lock contention, 2) Bloated indexes or tables, 3) Changes in data distribution (e.g., many NULLs). Provide a step-by-step diagnostic plan."
Why it works: This prompt mimics a DBA's thought process. It forces the AI to consider environmental factors, not just the query itself. It also guides you to gather relevant diagnostics (like table stats) before asking.
Example: A query that joined two tables suddenly took 10 seconds. The AI suggested checking pg_stat_user_tables for bloat and pg_locks for contention. Turns out, a nightly batch job was holding a lock on one table, blocking the query. The fix was simple: reschedule the job.
4. MongoDB Schema Refactor: From Embedded to Referenced
Prompt: "I have a MongoDB collection with documents like [SAMPLE DOCUMENT]. My application frequently queries [LIST OF QUERIES]. The current schema uses embedded arrays, but they're getting too large. Should I refactor to a separate collection? If so, provide a new schema design, including example documents, and explain how to migrate existing data with a script."
Why it works: MongoDB schema design is flexible but tricky. The AI can help you decide between embedding and referencing based on your access patterns, and even generate migration scripts.
Example: A social app had users with embedded arrays of posts. As posts grew, queries became slow. The AI suggested a separate posts collection with a userId index. The migration script used forEach to move data. Query time dropped from 2 seconds to 20ms.
5. The Missing Index Detective: MongoDB
Prompt: "I'm running this MongoDB query: [INSERT QUERY]. Please explain how to check if it's using an index (via .explain('executionStats')). If not, suggest the best index to create. Also, show me how to verify the index is being used after creation."
Why it works: Many developers forget to check explain() output. This prompt teaches you to do that systematically and provides a concrete index recommendation.
Example: A query filtering by status and createdAt was doing a collection scan. The AI suggested a compound index {status: 1, createdAt: -1}. After creating it, the explain() output showed 'IXSCAN' instead of 'COLLSCAN', and query time went from 3 seconds to 50ms.
6. Query Rewrite: Turning Correlated Subqueries into Joins
Prompt: "I have a PostgreSQL query that uses a correlated subquery: [INSERT QUERY]. It's slow. Can you rewrite it using a JOIN or a window function? Please explain the performance difference and verify that the rewritten query returns the same results."
Why it works: Correlated subqueries are often performance killers. The AI can suggest alternatives that are more efficient, and even explain the logic behind the rewrite.
Example: A query using WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100) was rewritten as a JOIN with DISTINCT. The execution time dropped from 8 seconds to 1 second on a large dataset.
7. N+1 Query Buster: Optimizing Mongoose/ODM Calls
Prompt: "I'm using Mongoose (MongoDB ODM) and my code does [INSERT CODE SNIPPET with N+1 problem]. How can I refactor this to use aggregation or population to reduce the number of queries? Provide a code example."
Why it works: N+1 queries are a common performance pitfall in Node.js apps. The AI can show you how to use $lookup for aggregations or Mongoose's populate() to fetch all needed data in one go.
Example: A blog app fetched posts and then for each post, fetched the author. The AI refactored it to use aggregate() with $lookup, reducing the query count from 101 to 1. Response time improved dramatically.
8. Pagination Perfection: Keyset vs. Offset
Prompt: "I'm implementing pagination in PostgreSQL for a table with [COLUMNS]. The current query uses OFFSET/LIMIT and is slow for large offsets. Please show me how to implement keyset pagination (using WHERE + LIMIT) and provide a comparison of performance."
Why it works: OFFSET pagination becomes slower as you go deeper. The AI can help you switch to keyset pagination, which is O(1) regardless of page number.
Example: For a table with 1 million rows, fetching page 100,000 with OFFSET took 4 seconds. With keyset pagination using WHERE id > ?, it took 10ms. The AI also explained how to handle sorting by non-unique columns.
9. Schema Documentation Generator
Prompt: "I have this PostgreSQL schema: [INSERT CREATE TABLES]. Generate a comprehensive Markdown document that: 1) Describes each table and its purpose, 2) Lists all columns with types and constraints, 3) Documents relationships, 4) Suggests indexes. Make it suitable for onboarding new developers."
Why it works: Good documentation is essential but often neglected. The AI can generate a solid starting point, saving you hours of writing.
Example: I used this for a legacy database with 30 tables. The AI produced a clear document that my team uses as a reference. It also caught a missing foreign key that we added.
10. Performance Budget: Setting Up Alerts and Monitoring
Prompt: "I want to monitor my PostgreSQL database for slow queries. Please write a query that finds queries taking longer than 5 seconds from pg_stat_statements, and explain how to set up a simple alerting system (e.g., using cron + email). Also, suggest metrics to track for MongoDB."
Why it works: Proactive monitoring beats reactive debugging. The AI can provide the exact SQL and configuration steps.
Example: The AI gave me a query that lists the top 10 slow queries by total time. I set up a cron job to run it daily and email me the results. This helped catch a regression early.
Conclusion
These ten prompts have become my go-to toolkit for database optimization. They've saved me hundreds of hours and helped me deliver faster, more reliable applications. But remember, prompts are just starting points—always verify the AI's suggestions in a staging environment. Start with one or two prompts that address your current pain point, and integrate them into your workflow. The result? You'll spend less time on routine tuning and more time on features that matter. Happy optimizing!
Ready to supercharge your database skills? Try these prompts with your favorite AI assistant, and share your results in the comments below.
Comments