AI-Assisted Database Migration Script Cost: Postgres and MySQL Schema Changes

Not All Migrations Cost the Same

A one-line ALTER TABLE users ADD COLUMN age INT NULL; is cheap for an LLM to generate. A migration that adds a NOT NULL column to a 40M-row table without blocking writes is a completely different exercise: it needs a backfill strategy, a coordinated double-write with the application code, a plan for rollback, and a plan for what to do when the migration is 60% done and a query timeout fires.

This post breaks down realistic LLM cost by migration shape, on Postgres and MySQL, and highlights where the model reliably fails.

Change-Shape Taxonomy

• Trivial: add nullable column, add non-unique index on small table, add table.
• Medium: add index on large table (needs CONCURRENTLY or online DDL), rename column with backward compatibility, add check constraint.
• Hard: add NOT NULL column to large table with default, change column type on hot path, denormalize a shape-heavy join.
• Very hard: split table, merge tables, change primary key, move column across tables with FK preservation.

Cost Per Migration by Shape

Cost assumes Sonnet 5 as the generator, one initial pass, one review pass, and one "reversibility check" pass. Numbers include context loading of the current schema (typically 2-4K tokens for a mid-sized app).

MySQL is consistently more expensive because online-DDL rules differ across storage engines and versions; the model has to generate more caveats and version-specific branches.

Why Hard Migrations Multiply Tokens

A hard migration is not one script — it is a sequence:

1. Add new column, still nullable.
2. Deploy app code that writes to both old and new location.
3. Backfill old rows into the new column, in batches.
4. Verify backfill completeness (count query, sample check).
5. Add NOT NULL constraint (Postgres: NOT VALID + VALIDATE; MySQL: online ALTER).
6. Deploy app code that reads only from the new location.
7. Drop old location.

Each step needs its own SQL, plus coordination notes for the deploy pipeline. The LLM cost scales with the number of coordinated steps, not with SQL line count.

Where LLMs Silently Fail

Three recurring failure modes across Postgres and MySQL migrations:

• Locking assumptions. LLMs frequently forget that ALTER TABLE in Postgres takes an ACCESS EXCLUSIVE lock unless you use CONCURRENTLY or version-16 non-blocking DDL. On a busy table this brings the app down.
• Foreign-key gotchas. Adding a FK does not scan existing data in some Postgres versions unless you validate. Data integrity can be silently wrong.
• Backfill batching. Models default to UPDATE ... WHERE id BETWEEN a AND b style batching, which is fine for sequential IDs but explodes on tables with UUID primary keys.

For every hard migration, run EXPLAIN on the generated queries before running them. It is the only way to catch the model's plan-vs-reality gaps.

Bottom Line

LLM cost for migration generation ranges from $0.05 for a trivial add-column to $2 for a table-split. That is negligible compared to the cost of a bad migration (production incident, data loss, hours of on-call time). Treat LLMs as fast draft writers for migrations, and always run the review passes plus EXPLAIN before executing.