A query is fast in staging and slow in production. Same code, same migrations, same data volume roughly. After an hour someone discovers production is missing an index that exists in staging, added there months ago by someone debugging and never captured in a migration.
Schema drift is the accumulation of differences between environments that are supposed to be identical, and it is caused by exactly this kind of well-intentioned direct change.
What changed in 2026
- Automated drift detection became standard tooling. Comparison in continuous integration moved from bespoke scripts to available products.
- Emergency change discipline improved. The practice of back-filling a migration for any manual change became more widely enforced.
- Managed platform changes became a source. Automatic index recommendations applied by platforms introduced drift nobody made.
- Declarative schema management gained ground. Defining the desired state and letting tooling reconcile it reduced drift by construction.
Where drift comes from
Manual changes during incidents. The largest source. Someone adds an index at 2am to resolve a production problem. It works, the incident closes, and nobody writes the migration. Production now has something no other environment does.
Failed or partial migrations. A migration that errored halfway may have applied some statements. The migration table may show it as failed or as complete depending on implementation, and the actual schema is somewhere in between.
Direct fixes. A column type corrected by hand, a constraint dropped to unblock something, a default changed.
Environment-specific additions. Test data structures in staging, monitoring objects in production, debugging views nobody removed.
Platform-applied changes. Managed services that automatically apply index recommendations create drift without human involvement.
| Object |
Drift frequency |
| Indexes |
Highest |
| Constraints |
High |
| Column defaults |
Moderate |
| Column types |
Lower |
| Tables |
Lowest |
| Permissions |
Frequently overlooked |
Indexes dominate because they are the safest thing to add by hand and the easiest to forget.
Compare structure, not history
The migration table records which migrations were run. It does not record what the schema is.
Two databases with identical migration history can have different schemas — one had a manual change, one had a migration partially apply, one had a platform-applied index. The history matches and the structures do not.
So detection must compare actual structure: tables, columns, types, defaults, nullability, indexes, constraints, and — frequently forgotten — permissions and sequences.
The practical approach is to dump the schema from each environment in a normalised form and compare. Differences are drift, and the comparison should ignore genuinely environment-specific objects rather than flagging them every run.
Detect continuously
Drift found during an incident is drift found too late. Detection should run automatically and regularly.
In continuous integration, comparing the schema produced by running migrations from scratch against the schema in a reference environment. Catches migrations that do not produce what you think.
Scheduled against production, comparing it to staging or to the expected state. Catches manual changes.
Alert on any difference, with a documented exception list for legitimate environment-specific objects. An unexpected difference is a finding, not noise.
The response to detected drift is to back-fill a migration bringing every environment to the same state — not to apply the change manually elsewhere, which reproduces the problem.
Common mistakes
- Trusting the migration table. Records intent, not state.
- Manual changes without a follow-up migration. The dominant cause.
- Comparing only tables and columns. Indexes and constraints drift more.
- Ignoring permissions. Real drift, rarely checked.
- Detecting only at release time. Weeks of accumulated differences.
- Fixing drift manually in each environment. Recreates the problem.
- No exception list. Legitimate differences produce noise that gets ignored.
FAQ
How do I fix drift once found?
Decide which state is correct, write a migration bringing everything to it, and run it everywhere. Resist fixing environments individually by hand.
What about intentional differences?
Document them in an exception list the detection tool respects. Undocumented differences should always be flagged.
Does declarative schema management solve this?
Largely — defining the desired state and reconciling continuously means drift is corrected rather than accumulated. It is a meaningful change in approach rather than a tool swap.
How does this interact with expand-contract migrations?
During a multi-phase migration, environments legitimately differ between phases. Detection should account for in-progress migrations rather than flagging them — see expand and contract migrations.
Where to go next
For the migration pattern that minimises manual intervention, read expand and contract migrations. For testing migrations against a real database, testcontainers, and for the seeding side, database seeding strategies.