Schema Drift

The database's actual schema diverges from what the application, and the migration history that is supposed to describe the database, both assume. Unlike schema_migration_lock, which is about a migration blocking traffic while it runs, drift is what happens after: a persistent mismatch that causes query failures, ORM errors, or silent data truncation, sometimes not triggered until a rare code path runs weeks later.

Schema drift is a persistent state where the applied schema differs from the schema the application, and the migration history, believe is applied. Whether a failed migration can leave the database in a partially-changed state without anyone noticing depends on whether DDL is transactional on the engine involved, which is why the same root cause produces a different failure shape on PostgreSQL than on MySQL.

PostgreSQL DDL is transactional: CREATE TABLE, ALTER TABLE, and most other DDL statements can be wrapped in BEGIN/COMMIT along with the rest of a migration, and if any statement in that transaction fails, the whole thing rolls back cleanly, leaving no partial state at all. On PostgreSQL, silent partial drift from a failed migration specifically requires either a migration tool that does not wrap the migration in one transaction, or a migration that includes a statement which cannot run inside a transaction block in the first place, most commonly CREATE INDEX CONCURRENTLY (see schema_migration_lock), where a mid-build failure leaves a real, partial, invalid index rather than a rolled-back no-op.

MySQL's InnoDB DDL is not transactional in the same sense: most DDL statements implicitly commit any open transaction and cannot be rolled back by wrapping them in one. A multi-statement migration that fails on its third of five statements leaves the first two permanently applied, with no rollback available from the migration tool at all. This is the scenario "a migration script runs, applies some changes, then fails partway through, and is marked applied in the migration table despite the partial state" describes most directly; on PostgreSQL, the same symptom means either the transaction wrapping was skipped or a non-transactional statement was involved.

Other drift causes, independent of the transactional-DDL question:

1. Environment divergence: a migration applied in staging was never applied in

production, or vice versa, common when migration ordering is not strictly

enforced across environments.

2. Manual schema changes: an operator runs a direct ALTER TABLE in production for an

"emergency fix" with no corresponding migration file. Development and staging

never see the change, and a later migration that conflicts with it fails.

3. ORM model mismatch: application code adds a column to a model and relies on the

ORM to create it (Django's historical syncdb, an unmigrated Rails model change)

rather than generating a tracked migration. The column exists in development but

not production.

4. Silent data truncation: a column's type or length constraint is narrower in the

database than the application assumes (VARCHAR(100) vs the 255 the application

validates against). Inserts either raise a constraint violation or, depending on

the database's strictness mode, silently truncate.

Checksum-based drift detection is the concrete mechanism behind "schema comparison tooling," worth naming precisely rather than treating as a black box. Flyway computes a checksum of each migration file's content and stores it alongside that migration's entry in its schema_history table when the migration is applied. On every subsequent run, Flyway recomputes the checksum of each already-applied migration file and compares it against the stored value; a mismatch means the file was edited after being applied, a strong signal of exactly the kind of unmanaged manual change (cause 2 above) that produces drift, and Flyway refuses to proceed until it is resolved. Liquibase's checksum mechanism works the same way against its own changelog. This catches file-level tampering; it does not catch drift caused by DDL that bypassed the migration tool entirely, which is why prohibiting direct DDL access remains a separate, necessary mitigation.