Detecting Production Schema Drift Against a Desired State

A deploy fails in staging with index "idx_orders_status" already exists, and nobody on the team created it. Six weeks ago, during an incident, someone added that index directly on production to rescue a slow query, and later copied it to staging by hand. It was never added to the schema definition or the migrations. Now the repository, staging and production each describe a slightly different database, and the next migration that touches the table has become unpredictable. Drift like this is unavoidable in any team that fixes incidents under pressure; the failure is not noticing it. This guide turns the diff at the heart of Declarative Schema Management into a scheduled audit that reports drift within the hour, classifies it, and routes each kind to the right fix.

Kinds of Drift and What They Mean Matrix of drift types, how they typically arise, their risk, and the usual resolution. Extra index from an incident hotfix, adopt into the desired state. Missing index after a failed concurrent build, rebuild. Column type or default changed by hand, investigate and reconcile. Extra table from a tool or extension, exclude from management. Half-applied migration, finish or revert. Kinds of Drift and What They Mean Drift Typical origin Risk Resolution extra index in prod incident hotfix low adopt into desired state missing index in prod failed CONCURRENTLY build query regressions rebuild, drop INVALID leftover changed type / default console ALTER app behaviour differs investigate, then reconcile unmanaged table extension or tooling low exclude from comparison half-applied migration non-transactional DDL failed next deploy breaks finish or revert first
Most drift is benign and should be adopted; the dangerous kind is a half-applied migration, because the next deploy assumes a state that does not exist.

Symptom / Error Signatures

Drift announces itself indirectly long before anyone runs a comparison:

  • Migrations fail in one environment but not another: relation "idx_orders_status" already exists, column "region" of relation "orders" already exists, or MySQL ERROR 1061 (42000): Duplicate key name.
  • Query plans differ between staging and production for the same query, because an index exists in one and not the other.
  • A declarative tool proposes changes on a branch that did not touch the schema.
  • pg_index.indisvalid = false rows exist — invalid indexes left by failed concurrent builds, as described in cleaning up invalid indexes after a failed build.
  • A migration tool’s history table says a version is applied, but the object it created is missing — the signature of a half-applied non-transactional migration.

Root Cause Analysis

Drift arises whenever the schema is changed through any path other than the managed one: an incident console session, a DBA running maintenance, a cloud provider’s automatic index advisor, an extension creating its own tables, or a migration that failed partway on an engine or statement that cannot roll back. On MySQL every DDL statement commits implicitly, so a three-statement migration that fails on the second leaves the first applied; on PostgreSQL the same happens with CREATE INDEX CONCURRENTLY and any file run outside a transaction.

The detection principle is simple: compare what production is with what it should be, on a schedule, and treat any difference as an event. Declarative tools perform exactly this comparison. The work is in making the comparison quiet when nothing is wrong — excluding objects you deliberately do not manage and normalising on a dev database of the same version — so that when it does speak, people listen.

Hourly Drift Audit Sequence between a scheduler, the drift job, a read replica, a disposable dev database and the alert channel. The scheduler triggers the job; the job compiles schema.sql on the dev database, inspects the replica, computes the diff with exclusions applied, and posts an alert with the plan if the diff is non-empty. Hourly Drift Audit Scheduler Drift job Dev DB Read replica Alerts hourly trigger compile schema.sql inspect catalog diff minus exclusions non-empty: post plan + owner
Run the audit against a replica so it never adds load to the primary; the diff output doubles as the proposed fix.

Immediate Mitigation

1. Take a baseline diff now. Run the comparison against a replica and save the output; it is your drift inventory.

# Shell · operator workstation or CI · read-only replica credentials · Docker for the dev DB
atlas schema diff \
  --from "postgres://readonly:***@prod-replica:5432/app?sslmode=require" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/16/dev" > drift-baseline.sql
wc -l drift-baseline.sql

For MySQL with Skeema, skeema diff production prints the differences and exits non-zero when any exist; with a versioned workflow and no declarative tool, compare pg_dump --schema-only output from production with a scratch database built from migrations.

2. Check for invalid indexes and half-applied migrations first. These are the drifts that break the next deploy.

-- PostgreSQL · read-only · run on the primary or a replica
SELECT n.nspname AS schema, c.relname AS index_name, t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;

3. Triage each difference into adopt, revert or exclude. Adopt hotfixes that should stay by adding them to the desired state (and, in a hybrid workflow, generating a migration guarded with IF NOT EXISTS so other environments converge). Revert unauthorised changes with a reviewed migration. Exclude objects that are managed elsewhere.

4. Make the adoption idempotent everywhere. Because the object already exists in production, the migration that adopts it must not fail there.

-- PostgreSQL · adoption migration · non-transactional because of CONCURRENTLY
-- WARNING: IF NOT EXISTS matches by name only; confirm the existing index has the same definition.
SET lock_timeout = '2s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders (status);
-- ROLLBACK PATH: DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

Permanent Fix / Long-Term Pattern

Schedule the diff and give it an owner. An hourly job against a read replica, with exclusions for unmanaged schemas and extension objects, posts any non-empty result to the team channel along with the plan that would reconcile it. Treat alerts like failing tests: each one is either adopted into the desired state, reverted, or excluded, within a day. Add the same check as a pre-deploy gate, so a migration never runs against a database whose starting state differs from what the migration was written for — the approach behind ensuring environment parity between dev and prod.

Reduce the sources of drift as well as detecting them. Give incident responders a fast, sanctioned path for emergency DDL — a pre-approved migration template they can merge in minutes — so that console changes become the exception. Record any console change in the incident timeline so the drift alert that follows has an explanation. And prefer transactional or idempotent migrations so failures do not leave partial state, as described in how to write idempotent SQL scripts for safe deploys.

Drift Findings in the First Month of Auditing Horizontal bar chart of drift items found in a typical first month after enabling the hourly audit on a mid-sized schema. Extra hotfix indexes 7, unmanaged extension objects 5, invalid indexes 3, changed defaults 2, half-applied migrations 1. Drift Findings in the First Month of Auditing extra hotfix indexes 7 extension objects (exclude) 5 invalid indexes 3 changed defaults 2 half-applied migrations 1 items found, first 30 days (illustrative)
The first month is mostly harmless adoption work; after that, a single alert per month is typical, and each one arrives within an hour of the change.

Verification Checklist

Frequently Asked Questions

Should drift be fixed by changing production or by changing the desired state? It depends on which one is right. A hotfix index that solved a real problem should be adopted into the desired state. An unauthorised change that alters behaviour should be reverted in production through a reviewed migration. The audit’s job is to force that decision, not to make it automatically.

Is it safe to run the drift diff against the primary? Inspection only reads catalog tables and takes no locks on application tables, so it is low-risk. Running it against a replica is still preferable because it adds no load and cannot interfere with anything on the primary, and physical replicas carry an identical schema.

How do I handle objects created by extensions? Exclude them from the comparison. Extensions such as PostGIS or pg_stat_statements create tables, views and functions that are managed by CREATE EXTENSION, not by your schema definition; comparing them only creates noise.

Can a versioned-migration team detect drift without a declarative tool? Yes. Build a scratch database by replaying all migrations, dump both it and production with pg_dump --schema-only or mysqldump --no-data, normalise the dumps, and diff them. A declarative tool simply does this more precisely, because it compares structured models rather than text.

Related Articles