Finding Schema Drift Between Database Environments

A migration passed on staging and failed on production. Or a query is slow on one server and fast on the other, and the plans differ. You suspect schema drift: a change that reached one database without going through your model or your migration scripts. To confirm it, pick one environment as the reference. Load a model from it, compare that model with the other database, and decide each difference. Then schedule a small check, so the next drift shows up before the next deploy.

In short

Schema drift is a change that reached a database outside the tracked model. Find it by refreshing a DbSchema model from the environment you trust and comparing it with the other database. A catalog fingerprint query, run on a schedule, tells you when to compare again.

Key takeaways

• Pending work in the model is not drift. Drift is a live change the model never saw.
• Choose the reference first, usually production, and load the model from it.
• Every difference goes into the model, goes into the database, or is ignored with a written reason.
• A SELECT-only account can hide drift: in our run it saw 0 foreign keys where 23 exist.
• A fingerprint query run by DbSchemaCLI shows which object types changed.

What counts as drift, and what does not

Drift is a change that reached the database without passing through your source of truth. An index added during an incident is drift. So is a column widened by hand, or a foreign key dropped for a bulk load and never restored. None of these got a migration script, so your model and your scripts still describe the old schema.

A change that exists in the model but not yet in the database is not drift. It's pending work, the normal state between design and deployment. The difference decides your next step. Pending work needs a deployment. Drift needs a decision about which side is right.

Martin Fowler and Pramod Sadalage describe the practice that prevents drift: representing every change to the database as a database migration script[1]. They also name the cost of skipping it. When DBAs compare databases only at release time, "the context for the changes in development is lost". Drift detection catches the changes that skipped the process anyway.

This article covers the recurring job. For a single comparison, step by step, read Comparing Production and Staging Schemas.

Picking the reference before picking a tool

Decide which environment is right before you compare anything. Pick production unless you have a reason not to, because production is the state of record. Staging, test and development are then measured against it.

DbSchema keeps its own copy of the schema, the design model, in a .dbs file. Schema synchronization compares that model with a database. It doesn't compare two live databases with each other. So the order matters. First, load the model from production. Second, connect the same model to staging and compare.

Three settings on the Access and Model tab of the connection dialog make this safer:

• Read Only Connection on the production connection. DbSchema then refuses to change the schema through it.
• Highlight set to Production, Development or Test, so the connection button shows where you are.
• Schema Mapping, when staging uses another schema name. It lets one model fit several databases.

Save the model once it's loaded from production. Saving the model to a file needs DbSchema Pro. The saved .dbs file is your baseline. You can commit it to Git next to your migration scripts, as database design tools with Git explains.

Reading the diff in the Synchronize Model and Database dialog

When you connect the production model to the staging connection, DbSchema asks how to bring the model and the database together. Choose Compare. DbSchema opens the Synchronize with the Database dialog, where you tick the schemas and tables to check, and then lists the differences.

For a quick yes or no, use Database → Refresh Model from Database instead. If nothing differs, DbSchema says "Schemas are identical". Otherwise it counts the differences and offers two buttons. Choose Review Differences. Refresh Model writes every difference into the model, and the documentation warns that changes that exist only in the model are lost. On a drift check, that would overwrite your production baseline with staging's state.

The Synchronize Model and Database dialog has one row per difference. The DbSchema Model column shows the production side. The Database column shows the staging side. Select a row, and the lower pane shows the SQL that each side would run. Read the rows by what they mean for drift:

The click-by-click walkthrough of this dialog is in Comparing Two Database Schemas.

The three choices for every difference

Every row needs a decision. In the Action column, you click the side that wins. The left button writes the difference into the model. Ignore leaves it as it is. The right button writes it into the database.

Write into the model when the live change was legitimate. A hotfix index that fixed an outage usually belongs here. Apply it, save the .dbs file, and write the migration script, so the other environments get the index too.

Write into the database when the model is right and the live state is the mistake. Commit in the database runs the statements one at a time. On production, press Generate SQL script instead and review the script first. Turning a Schema Diff Into a Safe Migration Script covers that review, and Rolling Back a Database Schema Change covers the way back.

Ignore only with a reason that lives outside your memory. Press Export Report in the dialog and include the ignored differences. The report is HTML or Markdown, so it can sit in the Git repository next to the model. Some differences are expected forever, such as a table that exists only on staging. A Synchronization Filter in Database → Synchronization Settings leaves those out of every comparison.

What a restricted connection quietly leaves out

A clean diff only means something if the connecting account could see everything. Database catalogs filter by privilege. Take PostgreSQL 16. Its view information_schema.table_constraints "contains all constraints belonging to tables that the current user owns or has some privilege other than SELECT on", says the PostgreSQL documentation[2].

We ran this on 25 October 2026 against two local PostgreSQL 16.14 databases with the same 14-table demo schema. One plays production. The other plays staging and carries 12 deliberate drifts. A role called drift_reader got USAGE on the schema and SELECT on every table, and nothing else. This query counts foreign keys in information_schema and in the pg_constraint catalog[3]:

SELECT current_user AS connected_as,
       (SELECT count(*) FROM information_schema.table_constraints
         WHERE constraint_schema = 'public' AND constraint_type = 'FOREIGN KEY') AS fks_information_schema,
       (SELECT count(*) FROM pg_constraint
         WHERE connamespace = 'public'::regnamespace AND contype = 'f') AS fks_pg_catalog;

The owner and drift_reader see different numbers:

Staging is missing 2 foreign keys. Through information_schema, drift_reader sees 0 on both sides, so the two environments look identical. Columns stay visible, because the columns view[4] shows every column the user has some privilege on.

Run this count with the account that runs your comparison before you trust a clean result. Use the account that owns the schema where you can. Row data is not part of a schema comparison, so check it separately when it matters.

Making the check run again, not just once

A comparison you remember to run is not a check. The cheap part can run on a schedule: a query that tells you whether anything changed, and in which kind of object. When it reports a change, you open DbSchema and compare.

This query builds a fingerprint per object kind from the PostgreSQL catalog. It sorts the definitions of columns, constraints, indexes, enum values and views, and hashes them:

SELECT kind, count(*) AS objects, left(md5(string_agg(def, ',' ORDER BY def)), 12) AS fingerprint
FROM (
  SELECT 'column' AS kind,
         c.relname || '.' || a.attname || ' ' || format_type(a.atttypid, a.atttypmod)
         || CASE WHEN a.attnotnull THEN ' not null' ELSE '' END
         || coalesce(' default ' || pg_get_expr(d.adbin, d.adrelid), '') AS def
  FROM pg_attribute a
  JOIN pg_class c ON c.oid = a.attrelid
  LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
  WHERE c.relnamespace = 'public'::regnamespace AND c.relkind IN ('r', 'p')
    AND a.attnum > 0 AND NOT a.attisdropped
  UNION ALL
  SELECT 'constraint', conrelid::regclass || ' ' || pg_get_constraintdef(oid)
  FROM pg_constraint WHERE connamespace = 'public'::regnamespace
  UNION ALL
  SELECT 'index', indexdef FROM pg_indexes WHERE schemaname = 'public'
  UNION ALL
  SELECT 'enum value', t.typname || '.' || e.enumlabel
  FROM pg_enum e JOIN pg_type t ON t.oid = e.enumtypid
  WHERE t.typnamespace = 'public'::regnamespace
  UNION ALL
  SELECT 'view', viewname || ' ' || md5(definition) FROM pg_views WHERE schemaname = 'public'
) objects
GROUP BY kind
ORDER BY kind;

DbSchemaCLI comes with every DbSchema installation. It runs SQL on several databases at once, and it runs script files, for example in a scheduled task. Our script file, drift-check.sql, starts with these three lines. The fingerprint query follows, and the file ends with exit:

connection prod -d PostgreSQL -h 127.0.0.1 -P 5432 -D dbschema_demo -u dora
connection staging -d PostgreSQL -h 127.0.0.1 -P 5432 -D dbschema_demo_stg -u dora
connect prod,staging

We started it with /Applications/DbSchema/DbSchemaCLI drift-check.sql on macOS.

DbSchemaCLI 10.5.2 printed one result per connection:

Every kind differs, so this pair needs a full comparison. Look at the view row. Both sides have 2 views, but the fingerprints differ, because a view definition changed on staging. A check that only counted objects would have missed it.

Schedule the script with cron or the Windows Task Scheduler and save each run's output. When a fingerprint changes, open the model in DbSchema and compare. DbSchema can also run a Groovy script without opening its window, and the Automation Scripts dialog ships examples in a Schema Synchronization folder. A script run that way ends with exit code 0 even when it fails, so have the scheduler check the output for errors. For keeping the model itself under version control, see PostgreSQL schema version control.

Download DbSchema, load a model from the environment you trust, and compare it with the one you don't. Connecting and reverse-engineering are in the free Community Edition. Saving the model and schema synchronization need DbSchema Pro.

Frequently asked questions

Can DbSchema compare two live databases directly?

No. DbSchema compares its model with one database. Load the model from the first database, then connect it to the second and compare. Two saved .dbs files can also be compared with the Compare Model with Other Model From File command.

Is schema synchronization in the free Community Edition?

No. Schema synchronization and saving the model to a file are in DbSchema Pro. Community connects, reverse-engineers, draws diagrams and runs SQL.

Does a matching fingerprint prove that two schemas are identical?

It shows that the kinds it covers match, for the objects the account can see. Triggers, functions, grants and data are outside this query, so add them if they matter to you.

Sources

1. martinfowler.com
2. postgresql.org
3. postgresql.org
4. postgresql.org