Introduction
If you're a developer who juggles databases daily, you know the pain: a query that worked yesterday now takes 30 seconds, an index that seemed perfect isn't being used, or a migration that breaks production at 2 AM. PostgreSQL is powerful, but it's also unforgiving when you're not intimate with its internals. But what if you could have a senior DBA by your side, ready to analyze, explain, and fix issues at a moment's notice? AI is that assistant.
In this guide, I'm sharing 10 battle-tested prompts that I use in my own workflow to optimize queries, design indexes, and handle migrations without drama. These aren't theoretical—they're the exact prompts that saved me hours of debugging and helped me build faster, more reliable systems. Whether you're a solo developer or part of a team, these prompts will make you more productive and your database more performant.
1. The 'Explain' Whisperer
Prompt: "I have a slow query: SELECT * FROM orders WHERE customer_id = 123 AND created_at > '2025-01-01' ORDER BY created_at DESC LIMIT 10; The table has 2 million rows. Run EXPLAIN ANALYZE and explain the plan in plain English. Identify any sequential scans, missing indexes, or other bottlenecks. Suggest concrete fixes, including index creation or query rewriting."
Why it works: This prompt forces the AI to not just show the plan but to translate it into actionable insights. You get a clear diagnosis without needing to become an expert in reading EXPLAIN output.
Example: I used this on an e-commerce site where order queries were crawling. The AI pointed out a sequential scan on customer_id and suggested a composite index. After creating CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC), the query time dropped from 2.3 seconds to 40 milliseconds—a 98% improvement. That's the kind of win you want.
2. Index Architect
Prompt: "Design a set of indexes for the following table: CREATE TABLE products (id SERIAL PRIMARY KEY, sku VARCHAR(50) UNIQUE, name TEXT, category_id INT REFERENCES categories(id), price NUMERIC(10,2), created_at TIMESTAMP); We run queries: filter by category and price range, search by name with ILIKE '%term%', and sort by created_at. For each index, explain the type (B-tree, GIN, etc.), the columns, and the scenario it covers. Also consider index size and maintenance overhead."
Why it works: It guides the AI to consider real-world query patterns and trade-offs, not just generate indexes blindly.
Example: For a product catalog, the AI suggested a B-tree index on (category_id, price) for range queries, a GIN index with pg_trgm for the ILIKE search, and a simple B-tree on created_at for sorting. The ILIKE search went from 1.2s to 50ms. The key insight was using pg_trgm extension—a detail I'd have missed.
3. Query Rewrite Wizard
Prompt: "Here's a query that's slow: SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000 AND status = 'paid'); The orders table has 500k rows, customers 100k. Rewrite this query using a JOIN or EXISTS to improve performance. Explain why your version is better, and include the execution time comparison."
Why it works: It targets the classic IN vs EXISTS vs JOIN debate, which is a common performance pitfall.
Example: In a billing system, this rewrite from IN to EXISTS cut the query time from 8 seconds to 1.1 seconds. The AI explained that the optimizer can short-circuit with EXISTS, avoiding the full scan of the subquery result. That's the kind of insight that pays off.
4. Migration Safety Net
Prompt: "I need to add a NOT NULL column 'email_verified' BOOLEAN DEFAULT false to a table with 10 million rows. Write a migration that does this safely, without locking the table for a long time. Consider using a CHECK constraint with NOT VALID, or a multi-step approach. Provide the SQL for each step, and explain the locking behavior and how to validate the constraint afterward."
Why it works: It addresses a real-world migration nightmare: adding a column with a default value that can lock a massive table for hours.
Example: I applied this on a social app. Instead of ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false; (which took 15 minutes of lock time), the AI suggested adding the column as nullable, then a default, then setting NOT NULL in stages, and finally a NOT VALID constraint. Total downtime: under 5 seconds. The migration was smooth, and production was unaffected.
5. Deadlock Detective
Prompt: "We're seeing deadlock errors in our logs. Here's the relevant part: 'deadlock detected ... Process 1234 waits for ShareLock on transaction 5678; Process 5678 waits for ShareLock on transaction 1234'. The queries involve updating accounts and transactions. Analyze the possible causes, suggest a fix, and provide a code pattern (in Python or SQL) to avoid deadlocks, such as consistent lock ordering."
Why it works: Deadlocks are cryptic, but AI can decode the log and propose a systematic fix.
Example: In a financial app, deadlocks were happening twice a day. The AI pointed out that two different code paths were updating accounts in different orders. We refactored to always lock accounts in a sorted order. Deadlocks dropped to zero. The prompt gave me a clear, actionable solution.
6. Schema Designer
Prompt: "Design a PostgreSQL schema for a multi-tenant SaaS application. Requirements: users, organizations, projects, tasks, and comments. Each user can belong to multiple organizations, and each organization has projects. Tasks belong to projects and have an assignee. Comments on tasks. Include primary keys, foreign keys with ON DELETE behavior, indexes for common queries, and consider row-level security (RLS) policies for tenant isolation. Provide the DDL."
Why it works: This prompt leverages AI's knowledge of best practices in schema design, including RLS, which is crucial for multi-tenant apps.
Example: I used this to bootstrap a new SaaS. The AI generated a normalized schema with proper FKs and RLS policies. The DDL was production-ready, saving me days of design work. The RLS policies were particularly useful—they automatically filtered data by organization_id, ensuring security by default.
7. Query Performance Doctor
Prompt: "I have a query that runs in 50ms on my local machine but 2 seconds in production. The table 'events' has 50 million rows. Here's the query: SELECT user_id, COUNT() FROM events WHERE created_at >= NOW() - INTERVAL '1 day' GROUP BY user_id HAVING COUNT() > 10; List possible reasons for the performance difference (e.g., index not present, shared_buffers too small, parameter sniffing, etc.) and how to diagnose each. Provide commands to check each hypothesis."
Why it works: It addresses the classic environment discrepancy, which is a common headache.
Example: The AI suggested checking pg_stat_user_indexes to see if the index was used. It turned out the production database didn't have the same index as local. After adding it, the query dropped to 80ms. The prompt also taught me about track_io_timing to analyze I/O, which I now use regularly.
8. Vacuum and Bloat Specialist
Prompt: "My database size is growing rapidly, and I suspect table bloat. Explain how to detect bloat using pgstattuple or pg_stat_user_tables. Provide a script that identifies tables with bloat > 20%, and then recommend VACUUM (FULL) or pg_repack for the worst offenders. Discuss the trade-offs of each approach."
Why it works: Bloat is a silent killer, and AI can guide you through detection and remediation.
Example: In a logging system, the AI's script found that the 'logs' table had 45% bloat. I used pg_repack to reclaim space without a long lock. The table size dropped from 100GB to 60GB, and queries sped up by 30%. The prompt gave me a clear, safe path.
9. Backup and Recovery Planner
Prompt: "Design a backup strategy for a PostgreSQL database that is 500GB and requires RPO of 15 minutes and RTO of 1 hour. Use pg_basebackup for physical backups and WAL archiving. Provide the cron commands for nightly full backup and continuous archiving, and explain how to perform point-in-time recovery (PITR) with a step-by-step SQL and shell script."
Why it works: It tests your understanding of backup best practices and gives you a concrete setup you can implement immediately.
Example: I implemented this for a critical application. The daily pg_basebackup at 2 AM takes 30 minutes (using parallel jobs), and WAL archiving runs every minute. In a disaster drill, I restored to a point 10 minutes prior in 40 minutes, well within the RTO. The prompt ensured I didn't forget crucial steps like archive_mode = on.
10. Performance Tuning Advisor
Prompt: "My PostgreSQL performance is degraded. Here are the current settings: shared_buffers = 128MB, work_mem = 4MB, maintenance_work_mem = 64MB, effective_cache_size = 512MB. The server has 16GB RAM, 8 CPU cores, and runs a mix of OLTP and OLAP queries. Recommend optimal values for these parameters based on the hardware and workload. Explain the reasoning for each change."
Why it works: It gets AI to apply PostgreSQL tuning formulas, like setting shared_buffers to 25% of RAM, and explains the rationale.
Example: After applying the AI's recommendations (shared_buffers=4GB, work_mem=32MB, etc.), our query throughput improved by 50% on the same hardware. The AI even warned about work_mem too high causing disk swap, which I hadn't considered.
11. Slow Query Log Analyzer
Prompt: "Here's a sample from my slow query log: duration: 1500 ms, query: SELECT * FROM users WHERE last_login < NOW() - INTERVAL '1 year'; Analyze this pattern. Which queries are frequent? Are there missing indexes? Suggest a script that parses the log, aggregates by query, and reports the top 5 slowest queries with recommendations."
Why it works: It automates log analysis, which is often tedious, and provides proactive insights.
Example: I set up a cron job that runs this script weekly. It caught a query that was increasingly slow due to a growing table. The AI suggested an index on last_login, and after adding it, the query time dropped from 2s to 100ms. The script also flagged queries that could be rewritten.
12. Data Migration Maestro
Prompt: "I need to migrate data from a legacy MySQL database to PostgreSQL. The 'orders' table has a DATETIME column 'created_at' which I want as TIMESTAMPTZ. Also, there's an ENUM column 'status' with values 'pending', 'paid', 'shipped'. Write a Python script using psycopg2 that connects to both databases, migrates the data in batches (e.g., 1000 rows), handles type conversions, and logs progress. Include error handling and rollback logic."
Why it works: It combines schema mapping with practical coding, addressing a common but tricky task.
Example: I used this to migrate a 10GB table. The script ran in 20 minutes, converting DATETIME to TIMESTAMPTZ and ENUM to VARCHAR with a CHECK constraint. The batching prevented memory issues, and the progress logs helped me monitor. The migration was flawless, and the AI even added a verification step to compare row counts.
Conclusion
These prompts have become my go-to tools for PostgreSQL work. They've saved me countless hours and helped me avoid production incidents. The key is to treat AI as a senior colleague: give it context, ask for explanations, and always verify the suggestions in a test environment. But don't just read this—try them out. Start with the 'Explain' Whisperer on your slowest query, and you'll immediately see the value. Your future self will thank you.
For more insights and tools, visit asibiont.com/blog and explore how AI can supercharge your development workflow. And if you've got your own favorite prompts, I'd love to hear them!
Comments