A Failed Migration That Left the Database in a Partial State

Overview

A migration adding three foreign keys failed on the third, left the first two committed, and the application shipped on top of the half-applied schema.

What happened

The migration file held three separate ALTER TABLE statements on orders, part of a data integrity project. The migration tool ran each one individually.

Why it broke

In PostgreSQL, each ALTER TABLE runs in its own implicit transaction unless wrapped in an explicit transaction block. A failed third statement could not undo the first two.

The fix

Recovery meant getting back to a known clean state before trying again, this time as one unit.

• Transaction scope — One implicit transaction per statement — One transaction for all three
• On a failed statement — Earlier constraints stay committed — Everything rolls back
• Data — Orphaned records present — Orphaned records fixed first

> [!WARNING] "Migration failed" does not mean "nothing changed". Check which statements committed before you deploy code that depends on them.

Key concepts

Transactional DDL

Running DDL statements inside an explicit transaction so that if any statement fails, all preceding statements in the transaction are rolled back. PostgreSQL supports transactional DDL; MySQL does not for most DDL.

Pre-migration data validation

Running a SELECT query to identify rows that would violate a constraint before applying the constraint, allowing data cleanup before the migration.

Idempotent migration

A migration that can be run multiple times safely, producing the same result — achieved using IF NOT EXISTS, IF EXISTS, or ON CONFLICT clauses.

When to use it

• Explaining why database migrations must be transactional
• Discussing data quality validation before running constraint migrations
• Justifying the use of migration testing on a production data replica

Interview questions

• How do you write a migration script that is safe to run against production data and handles failures cleanly?

More War Stories topics

• A Deployment That Could Not Be Rolled Back
• A Feature Flag Change That Triggered a Production Outage
• Accidental DELETE Without a WHERE Clause in Production
• An IAM Misconfiguration That Let a Developer Become Admin
• AWS Credentials Committed to a Public GitHub Repository
• Backups That Were Never Actually Completing
• BGP Route Leak That Rerouted Traffic Through an Unexpected AS
• Blue-Green Deployment That Resulted in Split-Brain Sessions
• Cache Stampede on a Flash-Sale Product Page
• Connection Pool Exhaustion That Brought Down 12 Microservices
• DynamoDB Hot Partition That Throttled a Flash Sale
• Failover to a Lagging Replica Lost 4 Minutes of Orders