AI for Database Management

TL;DR

• AI can generate SQL, suggest schemas, and draft migrations.
• It will write syntactically correct queries that perform terribly. Always verify execution plans.
• Use AI for drafts. You own correctness, indexes, and production safety.

Databases are unforgiving. A bad query can lock tables. A bad migration can lose data. AI accelerates the drafting — you ensure it's safe.

Query Optimization

Good use cases:

• "Optimize this query. Database: Postgres 15. Table sizes: [approx]"
• "Suggest indexes for this query pattern"
• "Explain this execution plan. What's the bottleneck?"

Critical checks:

• Execution plan — Run EXPLAIN. AI may suggest an index that doesn't help or makes things worse.
• Cardinality — AI doesn't know your data distribution. Low-cardinality columns, skewed data — verify.
• Locking — AI may suggest changes that increase lock contention. Consider MVCC and isolation.

Workflow: Generate → EXPLAIN → test on staging with prod-like data → then prod.

Schema Design

Good use cases:

• "Design a schema for [domain]. Requirements: [list]"
• "Suggest normalizations for this denormalized structure"
• "Add audit columns to this schema. Pattern: created_at, updated_at, etc."

Cautions:

• AI may over-normalize or under-normalize. You know your access patterns.
• Naming and conventions — align with your org. AI will use generic names.
• Future evolution — migrations, backfills — AI may not consider. You plan.

Migrations

Good use cases:

• "Generate a migration to add column X to table Y. Zero-downtime preferred"
• "Draft a migration from schema A to schema B"
• "Suggest a backfill strategy for this migration"

Critical:

• Data safety — Never run AI-generated migrations on prod without review. Test on copy of prod.
• Rollback — Does the migration have a rollback? AI may not include it.
• Concurrency — Long-running migrations on large tables need special handling. AI may not account for it.

Documentation

Good use cases:

• "Document this schema for a data dictionary"
• "Create a README for this database"
• "Explain this stored procedure"

Verify against actual objects. AI can hallucinate columns or behavior.

-- AI suggested "optimization" — always verify with EXPLAIN
-- Before: Full table scan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';

-- AI might suggest an index on status
-- Run EXPLAIN before AND after adding the index
-- Verify: Does it use the index? Did timing improve?
-- Check: What about your data distribution? Low cardinality = index might not help.

Do This Next

1. Feed AI a slow query you've seen. Get optimization suggestions. Run EXPLAIN before/after. Verify the improvement.
2. Have AI draft one migration (even a simple one). Review for safety, rollback, and concurrency. Run in dev only.