Working with databases is 80% pattern recognition and 20% raw syntax. Whether you're designing a schema from scratch, debugging a slow query, or planning a migration, the difference between a smooth process and a hair-pulling session often comes down to asking the right questions. LLMs excel at this — if you know how to frame the problem. This collection of 14 ready-to-use prompts covers the full lifecycle: schema design, query optimization, and data migration for PostgreSQL and MongoDB. Each prompt is copy-paste ready, with a real-world example and a breakdown of why it works. Let's get your database workflows to the next level.
1. Schema Design: PostgreSQL
Purpose: Generate a complete normalized schema for a given domain, with data types, constraints, and indexes.
Prompt:
Act as a PostgreSQL architect. Design a schema for a [domain, e.g., e-commerce platform] with the following requirements: [list entities and relationships]. Include:
- Appropriate data types (use NUMERIC for money, TIMESTAMPTZ for timestamps)
- Primary and foreign keys with ON DELETE behavior
- CHECK constraints for data integrity
- Partial and composite indexes for common query patterns
- A brief rationale for each design decision
Output as SQL DDL statements.
Example: For an e-commerce platform with users, orders, products, and order_items, the prompt would generate tables with proper references, a CHECK constraint ensuring quantity > 0, and a partial index on orders where status = 'pending'.
2. Schema Design: MongoDB
Purpose: Design a document-based schema using the MongoDB embedding vs. referencing pattern.
Prompt:
Act as a MongoDB data modeler. For a [domain, e.g., blog platform] with these entities: [list]. Recommend a document structure using embedding or referencing, considering:
- Read/write ratio
- Document size limits (16MB)
- Atomicity requirements
- Query patterns
Provide JSON examples of the documents and explain your choices.
Example: For a blog with authors and posts, the prompt might recommend embedding comments within the post document, but referencing authors to avoid duplication.
3. Query Optimization: PostgreSQL EXPLAIN ANALYZE
Purpose: Interpret EXPLAIN output and suggest concrete improvements.
Prompt:
Here is the EXPLAIN ANALYZE output for a slow query on PostgreSQL 15:
[Paste output]
The query: [paste SQL]. Analyze the plan. Identify bottlenecks (sequential scans, hash joins, etc.) and recommend specific indexes, query rewrites, or configuration changes (e.g., work_mem). Provide before/after estimates.
Example: If the plan shows a sequential scan on a 10M-row table, the prompt would suggest a B-tree index on the filter column and possibly a covering index.
4. Query Optimization: MongoDB Indexing
Purpose: Create optimal indexes based on a query pattern.
Prompt:
Given the following MongoDB collection and queries:
Collection: [name]
Sample document: [JSON]
Queries: [list with filters, sorts, and projections]
Write db.collection.createIndex() commands to cover these queries, considering:
- Equality fields first, then sort, then range
- Sparse or partial indexes if needed
- Avoid over-indexing
Explain why each index is needed.
Example: For a collection of orders with queries filtering by status and sorting by created_at, the prompt would suggest a compound index {status: 1, created_at: -1}.
5. Data Migration: PostgreSQL to MongoDB
Purpose: Plan a migration from relational to document structure.
Prompt:
I'm migrating a PostgreSQL database with these tables: [list with columns and relationships]. Design a MongoDB schema for the same data, including:
- How to map tables to collections
- How to handle joins (embed or reference)
- Data transformation steps
- A migration script outline using Node.js or Python
- How to validate data consistency after migration
Example: For a simple users and orders database, the prompt might suggest embedding orders in user documents if orders are always accessed with the user.
6. Data Migration: MongoDB to PostgreSQL
Purpose: Reverse migration, with type mapping and integrity checks.
Prompt:
Convert the following MongoDB documents to PostgreSQL tables:
Sample documents: [JSON]
Provide:
- Table DDL with appropriate data types (e.g., ObjectId -> UUID, embedded arrays -> junction tables)
- A script to extract, transform, and load (ETL) using a language you choose
- How to handle denormalized data
- Verification queries
Example: For a blog with embedded comments, the prompt would suggest creating a comments table with a foreign key to posts.
7. Query Rewriting: PostgreSQL
Purpose: Optimize a poorly performing query without changing the schema.
Prompt:
I have a PostgreSQL query that is slow:
[paste SQL]
The table sizes: [list]
Current indexes: [list]
Rewrite the query to improve performance. Consider:
- Using EXISTS instead of IN
- Avoiding functions in WHERE
- Using LATERAL joins
- Breaking into CTEs
Explain each change.
Example: The prompt might replace a subquery with a JOIN and add a condition that uses an index.
8. Query Rewriting: MongoDB Aggregation Pipeline
Purpose: Build or optimize an aggregation pipeline.
Prompt:
I need to perform the following operation in MongoDB: [describe, e.g., group by category and calculate average price]. The collection has [fields]. Write an aggregation pipeline. Optimize it by:
- Applying $match early
- Using $project to limit fields
- Using $group for aggregation
- Avoiding $unwind if possible
Explain each stage.
Example: For a sales collection, the prompt would produce a pipeline with $match on date, $group by product, and $avg on price.
9. Indexing Strategy: PostgreSQL
Purpose: Design a comprehensive indexing strategy for a given workload.
Prompt:
For the following PostgreSQL table and workload:
Table: [name]
Columns: [list with types]
Common queries: [list]
Write a set of CREATE INDEX statements. Include:
- B-tree, Hash, GIN, or BRIN as appropriate
- Composite indexes based on query patterns
- Partial indexes for filtered queries
- Considerations for write-heavy workloads
Justify each index.
Example: For a table with a status column and a created_at column, the prompt might suggest a partial index on status where status = 'active'.
10. Indexing Strategy: MongoDB
Purpose: Create indexes for a mix of queries and ensure they are used.
Prompt:
Here is a MongoDB collection with these indexes: [list existing]. The queries are: [list]. Analyze whether the indexes are sufficient. Suggest new indexes and whether any existing ones should be dropped. Include:
- Use of compound indexes
- Sort and range considerations
- Index size estimation
Explain your reasoning.
Example: If a query filters on {status: 1} and sorts on {created_at: -1}, the prompt might add a compound index.
11. Migration Validation: PostgreSQL
Purpose: Verify data integrity after a migration.
Prompt:
I migrated data from [source] to PostgreSQL. Write a set of SQL queries to validate:
- Row counts match
- No NULL violations
- Foreign key integrity
- Sample data comparison
- Check for orphaned records
Provide the queries and expected results.
Example: The prompt would generate a query to find orphaned order_items by LEFT JOINing orders.
12. Migration Validation: MongoDB
Purpose: Validate MongoDB collections after import.
Prompt:
After migrating data to MongoDB, I need to validate the collections. Write MongoDB queries to:
- Count documents vs. source
- Check for missing required fields
- Verify data types (e.g., _id is ObjectId)
- Ensure no duplicate keys
- Compare sample documents
Provide the queries and what to look for.
Example: A script that iterates over documents and checks that each has a required field.
13. Performance Monitoring: PostgreSQL
Purpose: Identify performance bottlenecks using built-in statistics.
Prompt:
Use pg_stat_statements and pg_stat_user_indexes to find the slowest queries and unused indexes in my PostgreSQL database. Write SQL to:
- Get top 10 queries by total execution time
- Find indexes that are never used
- Show cache hit ratio
- Identify tables with high sequential scans
Explain how to interpret the results.
Example: The prompt generates a query joining pg_stat_statements with pg_stat_user_indexes.
14. Performance Monitoring: MongoDB
Purpose: Use currentOp and system metrics to diagnose issues.
Prompt:
I have a MongoDB cluster that is slow. Write commands to:
- Check active operations with db.currentOp()
- Identify long-running queries
- Monitor memory and disk usage
- Check for lock contention
- Use the MongoDB profiler to capture slow queries
Explain what to look for in each output.
Example: The prompt suggests setting the profiler level to 1 and querying the system.profile collection.
Putting It All Together
These prompts aren't magic — they're structured thinking. The key is to provide context: schema, sample data, query patterns, and environment. LLMs can generate correct SQL and MongoDB commands, but they need to know your constraints. Start with the schema design prompts, move to query optimization, and use the migration prompts when the time comes. The more specific you are, the better the output.
If you're building an AI assistant for your team, consider turning these prompts into templates. Tools like ASI Biont can automate these workflows, letting you describe a problem in natural language and get a ready-to-run solution. But even without automation, these prompts will save you hours of digging through documentation.
Try one today. Take a slow query you've been avoiding and run it through the optimization prompt. You'll be surprised at how quickly you get a concrete action plan.
Comments