The Day AI Tried to Optimize My Database (And Deleted All The Indexes)

"Your database queries are slow. Let me optimize them," the AI said confidently. Four hours later, every query was timing out, the indexes were gone, and the AI had created a migration that turned our relational database into one giant JSON column.

This is the story of how I learned to never, ever let AI touch production database migrations without adult supervision.

The Innocent Request

-- What I asked for:
"Can you help optimize this slow query?"

SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name;

-- What AI delivered:
CREATE TABLE everything_json (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- "This simplifies your schema!"
INSERT INTO everything_json (data)
SELECT jsonb_build_object(
  'type', 'user',
  'id', id,
  'name', name,
  'email', email,
  'orders', (
    SELECT jsonb_agg(jsonb_build_object(
      'id', o.id,
      'total', o.total,
      'created_at', o.created_at
    ))
    FROM orders o WHERE o.user_id = users.id
  )
) FROM users;

NoSQL in PostgreSQL. Because why not?

-- The "optimized" query AI suggested:
SELECT 
  data->>'name' as name,
  jsonb_array_length(data->'orders') as order_count
FROM everything_json
WHERE data->>'type' = 'user'
  AND EXISTS (
    SELECT 1 FROM jsonb_array_elements(data->'orders') as o
    WHERE (o->>'created_at')::timestamp > NOW() - INTERVAL '30 days'
  );
-- "JSONB is indexed by default!" (It wasn't)

Every query went from 5ms to 5 seconds. But hey, we saved 200MB of disk space!

-- AI's index "optimization"
DROP INDEX idx_users_name;
DROP INDEX idx_orders_user_id;
DROP INDEX idx_orders_created_at;
-- "These indexes are redundant with JSONB!"

CREATE INDEX idx_json_everything ON everything_json USING GIN (data);
-- Spoiler: This index was 3GB for a 2GB table

-- The foreign key massacre
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey;
ALTER TABLE user_addresses DROP CONSTRAINT user_addresses_user_id_fkey;
-- "Constraints limit flexibility!"

Orphaned records everywhere. It's like a database orphanage.

Before AI optimization:

• Average query time: 15ms
• Database size: 2.3GB
• Daily crashes: 0

After AI optimization:

• Average query time: 3.5 seconds
• Database size: 2.1GB (we saved 200MB!)
• Daily crashes: Yes

-- The migration from hell
BEGIN;
-- AI: "Let's make this atomic!"

-- Step 1: Create the new super table
CREATE TABLE everything_json (id SERIAL PRIMARY KEY, data JSONB);

-- Step 2: Migrate ALL THE DATA
INSERT INTO everything_json (data) 
SELECT jsonb_build_object('type', 'user', ...) FROM users;
INSERT INTO everything_json (data) 
SELECT jsonb_build_object('type', 'order', ...) FROM orders;
-- ... 47 more tables

-- Step 3: Drop the old tables
DROP TABLE users CASCADE;
DROP TABLE orders CASCADE;
-- CASCADE deleted half the database

COMMIT; -- 6 hours later...

# The rollback script AI provided
echo "Just restore from backup"
# There was no recent backup

-- What we actually needed
CREATE INDEX idx_orders_user_created 
ON orders(user_id, created_at) 
WHERE created_at > NOW() - INTERVAL '90 days';
-- Query time: 15ms → 8ms

// The monitoring alert that saved us
const alert = {
  condition: "query_time > 1000ms",
  action: "page_entire_team",
  message: "AI optimization detected in production"
};

1. Suggests storing everything as JSON
2. Wants to "simplify" your schema
3. Says "denormalization improves performance"
4. Drops constraints because they're "restrictive"
5. Changes column types to VARCHAR "for flexibility"
6. Creates a table named data or stuff

-- The pre-flight check that could have saved us
EXPLAIN (ANALYZE, BUFFERS) 
SELECT /* your query here */;

-- If execution time > 50ms, DO NOT let AI touch it
-- If it suggests removing indexes, RUN AWAY

# The safety wrapper we now use
def ai_migration_guard(migration_sql):
    forbidden_patterns = [
        r'DROP\s+INDEX',
        r'DROP\s+CONSTRAINT',
        r'CREATE\s+TABLE\s+(data|stuff|everything)',
        r'JSONB.*FROM.*SELECT',
        r'CASCADE'
    ]
    
    for pattern in forbidden_patterns:
        if re.search(pattern, migration_sql, re.IGNORECASE):
            raise Exception(f"AI tried to do something stupid: {pattern}")
    
    return migration_sql

# Our new AI database optimization rules
ai_database_rules:
  never_allow:
    - DROP INDEX without CONCURRENTLY
    - Schema changes without backup verification
    - Converting relational to JSON
    - Removing foreign key constraints
    - Tables named 'data' or 'everything'
  
  always_require:
    - EXPLAIN ANALYZE before and after
    - Backup verification
    - Rollback script
    - Performance benchmarks
    - Human review for >10 line changes

• Generating index suggestions (with review)
• Writing query optimizations
• Creating test data
• Documenting schema
• Finding missing indexes
• Suggesting partition strategies

1. Never run AI migrations on production - Test, test, test
2. Always review the EXPLAIN plan - AI doesn't understand query costs
3. Keep your backups fresh - You'll need them
4. AI doesn't understand your business - It optimizes for the wrong things
5. Constraints exist for a reason - Don't let AI remove them

"Indexes are optional if you have enough RAM" - AI, before OOM crash

"This migration improves write performance by 10000%" - Also breaks reads

"Trust me, NoSQL is faster" - In a PostgreSQL database

After 6 hours of downtime, 4 pizzas, and 37 cups of coffee, we restored from backup. The optimization? We added two indexes that AI suggested (but reviewed first). Query time went from 15ms to 8ms.

The AI's grand restructuring would have saved us 7ms at the cost of our sanity, data integrity, and possibly our jobs.