Database schema drift or just schema drift is the case where the actual schema in the live database (the actual state) is different from the source of truth (the desired state). It's also a recurring root cause of database-related outages.
The live database part is easy to pin down:
- For MySQL, it's the output of
mysqldump --no-data - For PostgreSQL, it's the output of
pg_dump --schema-only
While the source of truth part needs more explanation.
The source of truth
One may first wonder why we need a separate source of truth at all. The reason is that the schema in the live database may not always be the desired state: human error and software bugs both change schemas by accident. Keeping an independent record to check against is the same idea as classic double-entry bookkeeping in accounting: one book alone can't reveal its own mistakes.
Naturally, the place to keep this source of truth is the version control system, next to the application code. This is the database-as-code practice, supported by Liquibase, Flyway, Atlas, and Bytebase alike.
There are two formats for that source of truth:
- State-based stores the desired end state of the entire schema. Intuitive (one file = one schema), but it can't distinguish a table rename from a drop-and-create.
- Migration-based stores an ordered series of change scripts (CREATE/ALTER/DROP). The desired state is whatever results from replaying them in order.
The choice shapes drift detection: with state-based you diff two schemas directly; with migration-based you must first derive the expected schema by replaying history, then diff. See Database Version Control, State-based or Migration-based? for the full tradeoff.
How drift actually happens
Drift is rarely a mystery in hindsight. It enters through a small set of doors:
- The hotfix. Production is down at 2am, someone adds an index or widens a column directly with a SQL client, the incident closes, and the migration to record it never gets written. This is the number-one source.
- Partial migration failure. MySQL DDL is not transactional: a multi-statement migration that dies halfway leaves the schema in a state that matches neither before nor after. (Postgres's transactional DDL closes this door, one of its quiet advantages.)
- Environment skew. The migration ran on staging and three of the four production shards. The fourth was in maintenance that day.
- Sidechannel tools. An ORM's auto-sync feature, a BI tool creating "temporary" tables, a well-meaning script with DDL in it, each writing schema outside the pipeline.
- Restored or cloned databases. A replica rebuilt from an old backup, a dev environment restored from a prod snapshot taken mid-migration.
Why it causes outages
The damage is deferred, which is what makes drift nasty. The drifted database works fine, sometimes for months, until:
- The next migration fails because it was written and tested against the source of truth, and production doesn't match it. Now a routine deploy is an incident, at deploy time, under pressure.
- The application and schema disagree: the ORM model says the column exists everywhere, but one shard says otherwise, and only the queries hitting that shard fail.
- A disaster-recovery rebuild reproduces the source of truth, not production, silently dropping the 2am index that was load-bearing.
The pattern: drift converts your most-rehearsed operations (deploy, failover, restore) into your least-predictable ones.
Detection and remediation
Detection is conceptually one operation, run continuously: dump the live schema, compute the expected schema, diff. In practice three mechanisms cover it:
- Scheduled schema dump diff: cron
pg_dump --schema-onlyinto git and let the diff be the alert. Crude, effective, zero new tools. - Migration checksums: Flyway's
validateand Liquibase's checksums catch a modified migration file, though not out-of-band changes to the database itself. - Snapshot comparison: record a schema snapshot after each successful migration, then continuously compare the live schema against the latest snapshot. This catches out-of-band changes, which is where the real drift lives. This is the approach Bytebase implements, alerting on drift with the exact diff:
When drift is found, there are exactly two honest remediations. Either codify it: write the migration that represents the manual change and backfill it into the source of truth (right answer for the 2am index that was, in fact, a good idea). Or revert it: apply the change that brings the database back to the desired state. What is never an option is editing migration history to pretend the drift didn't happen; that just moves the lie into the books.
Summary
Schema drift is the gap between what your database is and what your records say it is, and it bills you at the worst possible moment: mid-deploy, mid-failover, mid-restore. Keep the schema in version control, close the side doors (hotfixes get codified the next morning, no exceptions), and run continuous detection so the gap is caught in hours instead of discovered by an incident.