new-migration

name

new-migration

description

Use when authoring a database schema migration — adding or altering a column, creating an index, renaming a table, backfilling data — especially against a large production table where a careless DDL statement takes a lock and stalls every query behind it. Triggers on "write a migration", "add a column", "create an index", "alter the schema", "backfill", or any change to the database that has to ship safely and be reversible. Pulls in the org's migration template and the lock/backfill gotchas before the first line of SQL.

New Migration

Overview

A migration that's fine on a 10-row dev table can take a production table offline for minutes. ALTER TABLE ... ADD COLUMN ... DEFAULT rewrites the whole table under a lock; CREATE INDEX (non-concurrent) blocks writes; a single UPDATE ... SET over millions of rows holds one giant transaction and bloats the WAL. And a migration with no down section can't be rolled back when the deploy goes wrong at 2am.

This skill gives you the org's migration template — transactional guard, paired up/down, a header that forces you to declare the table's size class — plus the hard-won rules about what's safe to run online. Start every migration from the template so the reversibility and locking decisions are in front of you, not discovered in an incident.

When to Use

Reach for this when:

• You're adding, dropping, or altering a column, index, constraint, or table.
• You need to backfill or transform existing data as part of a schema change.
• A migration touches a table you know (or suspect) is large in production.
• You're reviewing someone else's migration and want to check it against the lock/backfill/reversibility rules.

Do NOT use this for:

• Read-only analytics queries or one-off manual data fixes that aren't part of a versioned migration — those don't go through the migration tooling.
• ORM-managed auto-migrations where you don't write the SQL — though you should still read the Gotchas, because the same locking traps apply.

How to use the template

1. Copy assets/migration_template.sql to your migrations directory with the next sequence number and a descriptive name, e.g. 0042_add_last_seen_to_accounts.sql.
2. Fill in the header: declare the table's size class (small / large) — this is the single decision that determines whether you can use plain DDL or must go online/concurrent.
3. Write the -- up section. Write the -- down section that exactly reverses it. If you can't write a down, stop — the change isn't safe to ship as one migration (see Gotchas).
4. If the change includes a backfill, it goes in a SEPARATE migration from the schema change (and often a separate deploy). Use the batched-backfill block in the template, not one big UPDATE.

The template's transactional guard wraps the migration so a failure mid-way rolls back cleanly — EXCEPT for statements that can't run inside a transaction (like CREATE INDEX CONCURRENTLY), which the template calls out explicitly.

Gotchas

ALWAYS treat these as real production incidents — each pattern has locked a table or corrupted a deploy before.

• Every migration needs a down section that actually reverses the up. A migration you can't roll back means a bad deploy can only be fixed by rolling forward under pressure. "Add a column" reverses to "drop the column"; "rename" reverses to the opposite rename. If a change is genuinely irreversible (you dropped a column and discarded the data), split it: ship the additive/reversible part first, and gate the destructive part behind a separate, later migration once you're sure. An empty or -- TODO down section must never merge.
• Don't take a table-rewriting or write-blocking lock on a large table. On large tables: adding a column with a volatile/large DEFAULT rewrites every row under an exclusive lock — add the column nullable first, backfill in batches, then set the default/not-null separately. Build indexes with CREATE INDEX CONCURRENTLY (Postgres) / online DDL (MySQL ALGORITHM=INPLACE, LOCK=NONE), never the blocking form. The size-class header exists so you make this call deliberately.
• Backfill in batches, never one UPDATE over the whole table. A single UPDATE accounts SET ... across millions of rows holds one transaction for the entire run: it blocks vacuum, bloats the WAL/redo log, and if it fails at 90% you roll back everything and start over. Loop in bounded batches (e.g. by primary-key range, a few thousand rows per commit), with a short sleep between batches so production traffic isn't starved.
• Never mix a schema change and a data backfill in the same deploy. Ship the schema change (e.g. add nullable column) in one deploy, let it bake, then run the backfill, then in a later deploy add the not-null constraint or start reading the column. Doing all three at once means old app instances (still running during a rolling deploy) hit a schema they don't expect, and a failed backfill leaves you mid-change with no clean rollback point.
• CREATE INDEX CONCURRENTLY cannot run inside a transaction. The template's BEGIN/COMMIT guard must be removed (or that statement moved out of it) for concurrent index builds — Postgres errors if you wrap it. The template flags this; don't blindly leave the transaction block around a concurrent index.
• Renames and type changes break in-flight app code. A column rename is not reversible from the app's perspective mid-deploy — old code reads the old name, new code reads the new one. Prefer expand/contract: add the new column, dual-write, backfill, switch reads, then drop the old column in a much later migration.

Files

• assets/migration_template.sql — the starting point for every migration: size-class header, transactional BEGIN/COMMIT guard with the CREATE INDEX CONCURRENTLY caveat called out, paired -- up / -- down sections, and a commented batched-backfill loop block to copy when a backfill is needed. Referenced by How to use the template above.