Introduction
Every backend developer knows the sinking feeling: a query that flew in development crawls in production. The database is often the last place we look, yet it's where performance bottlenecks hide. With AI assistants like ChatGPT, you can now get expert-level guidance on PostgreSQL optimization without waiting for a senior DBA. But the quality of AI output depends on the quality of your prompts. This guide presents 12 carefully crafted prompts that address real-world PostgreSQL challenges: identifying slow queries, understanding execution plans, adding missing indexes, refactoring schemas, and generating safe migrations. These prompts are battle-tested in production scenarios and follow PostgreSQL best practices as documented in the official manual and use EXPLAIN (ANALYZE, BUFFERS) for deep insights.
1. The Diagnostic Deep Dive
Use this prompt to get a comprehensive analysis of a slow query. It's your first step when performance degrades.
Analyze this PostgreSQL query for performance issues. Provide:
- A breakdown of the execution plan (use EXPLAIN ANALYZE output if available)
- Identification of sequential scans that could be replaced by index scans
- Suggestions for indexes, including the exact CREATE INDEX statement
- Detection of inefficient joins or missing WHERE clause conditions
- Recommendations for rewriting the query if necessary
Query: SELECT u.id, u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'pending' AND u.created_at > '2024-01-01' ORDER BY o.total DESC;
Example: Paste your slow query and the output of EXPLAIN (ANALYZE, BUFFERS) your_query;. The AI will parse the plan and suggest concrete improvements.
2. Missing Index Finder
Often, the problem is simply missing indexes. This prompt helps you discover them from verbose logs or common patterns.
Based on the following PostgreSQL query patterns and table schemas, identify missing indexes. For each suggestion, provide the exact CREATE INDEX command and explain the expected performance gain. Consider composite indexes for multi-column WHERE clauses and covering indexes for frequently accessed columns.
Table schema: CREATE TABLE orders (id BIGSERIAL PRIMARY KEY, user_id INT, status VARCHAR(20), total DECIMAL(10,2), created_at TIMESTAMP);
Query: SELECT * FROM orders WHERE user_id = 123 AND status = 'shipped';
Example: The AI will likely suggest CREATE INDEX idx_orders_user_status ON orders (user_id, status); and explain why a composite index beats two single-column indexes.
3. EXPLAIN ANALYZE Decoder
Execution plans are cryptic. This prompt turns them into plain English.
Explain the following EXPLAIN ANALYZE output in simple terms. Highlight any red flags like sequential scans, high loop counts, or large sort operations. Provide specific actions to improve performance.
EXPLAIN ANALYZE output:
Seq Scan on orders (cost=0.00..1000.00 rows=50000 width=20) (actual time=0.010..20.000 rows=50000 loops=1)
Filter: (status = 'pending')
Example: The AI will note that a sequential scan indicates a missing index on status and suggest creating one.
4. Query Rewriting for Speed
Sometimes rewriting a query yields dramatic gains. This prompt asks for alternative formulations.
Rewrite the following PostgreSQL query to improve performance without changing the result. Consider using EXISTS instead of IN, restricting rows earlier, or using window functions. Provide the rewritten SQL and explain why it's faster.
Original: SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
Example: The AI might suggest SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id AND orders.total > 1000); and explain that it reduces intermediate result sets.
5. Schema Refactoring Assistant
When your schema has grown messy, this prompt helps you redesign it.
Review this PostgreSQL schema for normalization issues, redundant columns, and missing constraints. Suggest a refactored schema that is normalized to 3NF. Provide DDL statements for the new tables and a migration plan for existing data.
Current schema:
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_name VARCHAR(100), customer_email VARCHAR(100), product_name VARCHAR(100), product_price DECIMAL(10,2), order_date DATE);
Example: The AI will likely split into customers, products, and orders tables with foreign keys, explaining the benefits of reducing data duplication.
6. Migration Generation (with Rollback)
Writing safe migrations is tedious. This prompt generates them with error handling.
Generate a PostgreSQL migration script that adds a new column 'discount' to the 'orders' table, backfills it with a default value, and then creates an index on that column. Include a transaction with BEGIN/COMMIT and a ROLLBACK on error. Also provide a rollback migration.
Table: orders (id INT, total DECIMAL(10,2))
Example: Output includes ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2) DEFAULT 0; followed by an UPDATE and CREATE INDEX, wrapped in a transaction.
7. Index Strategy Advisor
Choosing the right index type (B-tree, GIN, etc.) is critical. This prompt helps.
For this PostgreSQL table and query workload, recommend an index strategy. Specify index types (B-tree, GIN, BRIN, etc.), covering indexes, and partial indexes if applicable. Provide CREATE INDEX statements and explain why you chose each index type.
Table: articles (id SERIAL, title TEXT, tags TEXT[], published_at TIMESTAMP)
Queries:
- SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
- SELECT * FROM articles WHERE published_at BETWEEN '2024-01-01' AND '2024-12-31';
Example: The AI will suggest a GIN index for the tags array and a BRIN index for published_at on large tables, with reasoning.
8. Slow Log Analyzer
If you have Postgres slow query logs, this prompt mines them for patterns.
Here is a sample of slow query logs from PostgreSQL. Identify the most common bottlenecks and suggest targeted fixes. Group the queries by type (e.g., heavy joins, missing indexes, lock contention). For each group, propose a solution and a specific query to test.
Log sample:
2024-09-03 10:00:01 UTC LOG: duration: 1500 ms statement: SELECT * FROM products WHERE category = 'electronics' ORDER BY price DESC;
2024-09-03 10:05:23 UTC LOG: duration: 2000 ms statement: SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;
Example: The AI will point out missing indexes on category and customer_id, and suggest a covering index for the join.
9. Lock Contention Troubleshooter
Deadlocks and lock waits are painful. Use this prompt to investigate.
Explain the following pg_locks output and suggest ways to reduce lock contention. Provide SQL queries to monitor locks and recommend isolation level adjustments or index changes that could minimize locking.
pg_locks output: (simulated)
Example: The AI explains row-level locks and suggests using SELECT ... FOR UPDATE SKIP LOCKED for queue processing.
10. JSONB Query Optimizer
PostgreSQL's JSONB is powerful but often misused. This prompt optimizes JSONB queries.
Optimize the following PostgreSQL query on a JSONB column. Suggest JSONB operators, GIN index creation, or schema changes (e.g., using generated columns) for better performance.
Query: SELECT * FROM events WHERE data->>'type' = 'click' AND (data->>'timestamp')::bigint > 1700000000;
Table: events (id BIGINT, data JSONB)
Example: The AI suggests a GIN index on data and using a generated column for the timestamp to allow a B-tree index.
11. Partitioning Strategy Designer
For huge tables, partitioning is a must. This prompt designs it.
Design a partitioning strategy for this PostgreSQL table that is expected to store 100 million rows. Choose partitioning type (range, list, hash) and provide CREATE TABLE statements with partitions, plus a maintenance plan for adding new partitions.
Table: logs (id BIGINT, created_at TIMESTAMP, level VARCHAR(10), message TEXT)
Example: The AI recommends range partitioning by month on created_at and gives DDL with PARTITION BY RANGE (created_at). It also suggests a script for auto-creating partitions.
12. Dead Query Killer
Long-running queries eat resources. This prompt helps you find and terminate them safely.
Provide a SQL query to identify long-running PostgreSQL queries from pg_stat_activity. Also, explain how to cancel a specific query using pg_cancel_backend(pid) and the risks involved. Include a safe procedure for terminating idle-in-transaction sessions.
Example: The AI gives a query like SELECT pid, now() - query_start AS duration, state, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC; and explains how to use pg_terminate_backend.
Conclusion
These 12 prompts are your starting point for leveraging AI in PostgreSQL optimization. Remember, always test AI suggestions in a staging environment first, and use EXPLAIN to verify improvements. Combine these prompts with your own database knowledge and the official PostgreSQL documentation for the best results. Start with the diagnostic prompt today, and you'll likely find a quick win. Happy tuning!
Comments