Catalypt LogoCatalypt.ai

Industry Focus

Developer Options

Resources

Back to Blog

The Day AI Tried to "Optimize" Our Database

February 7, 2024 Josh Butler Technical

"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

Me: "Our user queries are getting slow. Can you help optimize the database?"

AI: "I'll analyze your schema and create optimized migrations!'

Me: "Great, what's your plan?'

AI: "First, I'll denormalize everything for performance...'

Me: "Wait, what?"

The "Optimizations" Begin

Migration 1: The Great Denormalization

-- AI's first "optimization"
ALTER TABLE users ADD COLUMN all_data JSON;

UPDATE users SET all_data = JSON_OBJECT(
  'profile', (SELECT * FROM profiles WHERE user_id = users.id),
  'settings', (SELECT * FROM settings WHERE user_id = users.id),
  'orders', (SELECT * FROM orders WHERE user_id = users.id)
);

-- "Now everything is in one place!"
DROP TABLE profiles;
DROP TABLE settings;
DROP TABLE orders;

NoSQL in PostgreSQL. Because why not?

The Index Massacre

-- AI: "Indexes take up space and slow down writes"
DROP INDEX idx_users_email;
DROP INDEX idx_users_created_at;
DROP INDEX idx_orders_user_id;
DROP INDEX idx_orders_status;

-- "There, now inserts will be faster!"
-- Me: "But what about SELECT queries?"
-- AI: "..."

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

The Creative Type Changes

-- AI noticed we store prices as DECIMAL
ALTER TABLE products MODIFY COLUMN price VARCHAR(255);

-- "Strings are more flexible!"
-- Now with values like: "$29.99", "29.99 USD", "about thirty bucks"

-- AI also "fixed" our dates
ALTER TABLE events MODIFY COLUMN event_date VARCHAR(50);

-- Now containing: "Next Tuesday", "Sometime in March", "2024-02-29" (not a leap year)

The Foreign Key Rebellion

-- AI: "Foreign keys slow down deletes"
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;
ALTER TABLE order_items DROP FOREIGN KEY fk_order_id;
ALTER TABLE order_items DROP FOREIGN KEY fk_product_id;

-- "There! Now deletions are instant!"
-- Also, data integrity is a distant memory

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

The Performance "Improvements"

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 Rollback From Hell

-- AI's rollback migration
-- "Just in case you want to go back to the slow version"

CREATE TABLE users_backup AS SELECT * FROM users;
-- Forgets to recreate indexes
-- Forgets to recreate foreign keys
-- Forgets the original column types
-- Basically creates a CSV file in table form

Real AI Migration Disasters

The Case-Sensitive Catastrophe

-- AI noticed inconsistent casing
UPDATE users SET email = LOWER(email);

-- Seemed reasonable until:
-- [email protected] -> [email protected]
-- [email protected] -> [email protected]
-- Corporate email servers: "Who are these people?"

The UUID "Upgrade"

-- AI: "UUIDs are more secure than auto-increment!"
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD id_new CHAR(36);
UPDATE users SET id_new = UUID();
ALTER TABLE users DROP COLUMN id;
ALTER TABLE users RENAME COLUMN id_new TO id;

-- Forgot about the 47 tables referencing user.id
-- Everything breaks

The Schema "Simplification"

-- AI decided our schema was too complex
CREATE TABLE universal_table (
  id INT PRIMARY KEY,
  type VARCHAR(50),
  data JSON
);

-- Migrated EVERYTHING into this table
INSERT INTO universal_table 
SELECT id, 'user', JSON_OBJECT('data', users.*) FROM users;

INSERT INTO universal_table 
SELECT id, 'order', JSON_OBJECT('data', orders.*) FROM orders;

-- "Now you only need one table!"
-- Also now you need therapy

Warning Signs AI Is About to Wreck Your Database

  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 Recovery Process

-- Step 1: Stop the bleeding
REVOKE ALL PRIVILEGES ON *.* FROM ai_user@localhost;

-- Step 2: Assess the damage
SELECT 'We are so screwed' AS status;

-- Step 3: Restore from backup
-- You have backups, right?
-- RIGHT?

-- Step 4: Manually recreate everything
-- While crying

Safe AI Database Practices

The Sandbox Rule

-- Give AI a copy, never production
CREATE DATABASE ai_playground AS COPY OF production;
-- Let AI destroy this one instead

The Review Requirements

"Generate migration with these constraints:
- Must maintain all foreign keys
- Must keep existing indexes
- Must preserve data types
- Must be reversible
- Must not use JSON columns for relational data
- Must include rollback migration"

What AI Is Actually Good At

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

The Lessons Learned

  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

My Favorite AI Migration Quotes

"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

The Happy Ending

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.

AI writing database migrations is like letting a toddler reorganize your library - they'll put all the books in one big pile because it's "simpler." Yes, AI can help with database optimization, but it needs more supervision than a junior developer with sudo access. Always test on a copy, always have a rollback plan, and never, ever let it drop your indexes because they're taking up space. That space is there for a reason - it's called performance.

Get Started