Why your database needs a copilot
Every backend developer knows the feeling: a query that ran fine on a 10k-row dev database suddenly takes 4 seconds in production. You open EXPLAIN ANALYZE, see a Seq Scan on a 50-million-row table, and realize the index you thought existed was never created. According to the PostgreSQL documentation, a sequential scan reads every row, while an index scan can jump directly to matching rows — but only if the planner chooses it, and only if statistics are fresh. Getting from symptom to fix quickly is a skill, and it's exactly where a well-crafted AI prompt saves hours.
This collection is not about asking ChatGPT to "write SQL." It's about using prompts as a diagnostic and design tool: reading execution plans, proposing indexes that won't bloat your write path, rewriting migrations safely, and reviewing schema changes before they hit production. Each prompt below is battle-tested in real projects, includes a usage example, and follows PostgreSQL's official syntax. I've verified the behavior against the PostgreSQL 16 documentation and common tooling like pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), and pg_dump.
Use them as-is or adapt them to your stack. The goal is to make the database a place where you're confident, not scared.
1. The execution plan translator
When to use: You have an EXPLAIN ANALYZE output that looks like a wall of numbers and you need a plain-language diagnosis.
Prompt:
You are a PostgreSQL performance engineer. I'll paste an EXPLAIN (ANALYZE, BUFFERS) output.
For each node:
1. Tell me what it does in one sentence.
2. Point out the most expensive operations (high actual time, rows removed by filter, disk reads).
3. Suggest concrete fixes: index type, query rewrite, or config change.
4. Flag any estimate vs actual row mismatch > 10x and explain the likely cause (stale stats, correlated columns, function volatility).
Output as a table with columns: Node
| What it does | Cost signal | Fix.
Example: Paste a plan showing Index Scan using idx_orders_user with rows=1 estimated but rows=8421 actual. The model will point to stale statistics and suggest ANALYZE orders; or raising the statistics target: ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;.
2. The index advisor that respects write cost
When to use: You need indexes but don't want to turn every INSERT into a nightmare.
Prompt:
Given this table definition and these 5 slow queries, propose the minimal set of indexes.
For each index:
- Exact CREATE INDEX statement (use CONCURRENTLY where appropriate).
- Which query it helps and why (selectivity estimate).
- Write amplification cost: how many extra writes per INSERT/UPDATE.
- Whether a partial index or covering index (INCLUDE) would be better.
Reject any index that duplicates an existing one. End with a summary table: Index
| Helps query | Est. read gain | Write overhead.
Example: For a users table with queries filtering on status = 'active' and created_at, the model may suggest CREATE INDEX CONCURRENTLY idx_users_active_created ON users (created_at) WHERE status = 'active'; — a partial index that stays small.
3. The N+1 hunter
When to use: Your ORM (Django, Rails, SQLAlchemy) fires dozens of queries per request.
Prompt:
Here is my ORM code and the SQL it generates (from the query log). Identify N+1 patterns.
For each:
- Show the loop causing it.
- Rewrite using JOIN, select_related/prefetch_related, or a single window function query.
- Provide the raw SQL equivalent and the ORM code.
- Estimate query count before and after.
Example: A Django view iterating over orders and accessing order.customer.name triggers one query per order. The fix: .select_related('customer') reduces 500 queries to 1.
4. The migration safety checker
When to use: Before running a migration on a live table.
Prompt:
Review this migration for PostgreSQL safety on a table with 100M rows and active writes.
Check for:
- Locking behavior (ACCESS EXCLUSIVE vs SHARE).
- Whether it rewrites the table (ALTER COLUMN TYPE, SET NOT NULL without default).
- Safe alternatives using CREATE INDEX CONCURRENTLY, NOT VALID constraints, or backfill in batches.
- Rollback plan.
Output a checklist with ✅/⚠️/❌ and the corrected SQL.
Example: ALTER TABLE users ADD COLUMN email_verified boolean NOT NULL; fails on large tables. Safe path: add nullable column, backfill in batches, then ALTER TABLE ... ADD CONSTRAINT ... CHECK (email_verified IS NOT NULL) NOT VALID; followed by VALIDATE CONSTRAINT.
5. The schema design sparring partner
When to use: You're starting a new feature and want to avoid future pain.
Prompt:
Design a normalized schema for this domain: [describe entities].
Requirements: support multi-tenancy, soft deletes, audit trail.
For each table:
- DDL with types, PK, FKs, and constraints.
- Justify normalization level (3NF vs denormalized for read speed).
- Suggest partitioning strategy if row count > 10M/year.
- List 3 queries this schema must support efficiently and the indexes for them.
Example: For a SaaS ticketing system, the model proposes tenants, tickets, ticket_events with a tenant_id leading column in every index — critical for row-level security and query isolation.
6. The JSONB query optimizer
When to use: You store semi-structured data in jsonb and queries are slow.
Prompt:
I have a jsonb column `metadata` in table `events`. These queries are slow: [paste].
Suggest:
- GIN vs GIN with jsonb_path_ops vs btree expression indexes.
- Exact CREATE INDEX statements.
- When to extract a field into a real column instead.
- How to rewrite queries to use the index (avoid functions on the column).
Example: SELECT * FROM events WHERE metadata @> '{"type":"click"}'; benefits from CREATE INDEX idx_events_meta ON events USING GIN (metadata jsonb_path_ops); — smaller and faster for containment queries.
7. The deadlock and lock-wait detective
When to use: You see deadlock detected or long lock waits.
Prompt:
Given these two transactions and the error log, explain the deadlock cycle.
Show:
- Which locks each transaction holds and waits for.
- The exact ordering that causes the cycle.
- Two fixes: reorder operations, or use SELECT ... FOR UPDATE with consistent ordering.
- How to monitor with pg_locks and pg_stat_activity.
Example: Transaction A updates row 1 then row 2; B updates row 2 then row 1. Fix: always acquire locks in ascending id order.
8. The query rewrite for readability and speed
When to use: A monster query with nested subqueries is both slow and unmaintainable.
Prompt:
Rewrite this SQL for clarity and performance:
[paste query]
Goals: replace correlated subqueries with JOINs or CTEs, push filters down, avoid SELECT *.
Provide the rewritten query and a short explanation of each change.
If a window function is better than a self-join, show it.
Example: A correlated subquery counting orders per user becomes SELECT u.id, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id; — often 10x faster.
9. The connection pool and config tuner
When to use: You're hitting too many connections or high latency under load.
Prompt:
My app uses [PgBouncer / HikariCP / asyncpg pool] with these settings: [paste].
Postgres has max_connections=100, shared_buffers=4GB, work_mem=4MB.
Analyze:
- Recommended pool size (formula: cores * 2 + effective_spindle_count).
- Whether transaction or session pooling fits my workload.
- Config changes: work_mem, effective_cache_size, random_page_cost for SSD.
- Risks of each change.
Example: For a 4-core app server, a pool of ~10–12 connections per instance is often enough; more connections increase context switching.
10. The data backfill script generator
When to use: You added a column and need to populate it for millions of rows without locking.
Prompt:
Write a batched backfill script in [Python/Go/SQL] for table `orders`, column `region_id`, 50M rows.
Requirements:
- Process in batches of 10k with a short sleep.
- Use a cursor or keyset pagination on the PK.
- Log progress and handle failures idempotently.
- Include the final validation query comparing counts.
Example: Keyset pagination: SELECT id FROM orders WHERE region_id IS NULL ORDER BY id LIMIT 10000; then update that batch.
11. The SQL code reviewer
When to use: Before merging a PR with SQL.
Prompt:
Review this SQL as a senior DBA. Check:
- SQL injection risks (string concatenation).
- Missing WHERE on UPDATE/DELETE.
- Implicit casts that kill indexes.
- Naming and formatting consistency.
- Transaction boundaries.
For each issue: severity (high/medium/low), explanation, corrected snippet.
Example: WHERE user_id = '123' on an integer column causes an implicit cast and may skip the index. Fix: WHERE user_id = 123.
12. The monitoring dashboard prompt
When to use: You want a quick health overview using built-in views.
Prompt:
Write SQL queries for a PostgreSQL health dashboard using pg_stat_statements, pg_stat_user_tables, pg_stat_activity.
Include:
- Top 10 queries by total_exec_time.
- Tables with most sequential scans.
- Cache hit ratio.
- Long-running transactions > 5 min.
- Bloat estimate via pgstattuple if available.
Format each as a standalone query with comments.
Example: SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; — requires CREATE EXTENSION pg_stat_statements;.
From prompts to production confidence
These twelve prompts cover the full lifecycle: from reading a confusing plan to designing a schema that won't collapse at scale. The key is to treat AI as a pair — it proposes, you verify with EXPLAIN, pg_stat_statements, and a staging environment. PostgreSQL's documentation is your ground truth; prompts just shorten the path to it.
Start with the execution plan translator on your slowest query this week. Then run the migration safety checker before your next deploy. If you want to go deeper with structured practice, explore the courses at asibiont.com — and keep your database boring, predictable, and fast.
Comments