Comparing Production and Staging Schemas

In short

To compare production and staging, check every object type in both databases, not only the columns. On our PostgreSQL 16 demo, a column query caught 7 of 12 differences. Foreign keys, indexes, enum values and view bodies need their own queries, or a model comparison in DbSchema.

Table of contents

• How staging and production differ
• Why a staging schema drifts from production
• Comparing the two schemas with catalog queries
• What the column query misses
• Comparing production and staging in DbSchema
• What to check after the change
• Frequently asked questions
• Sources

Key takeaways

• Staging drifts from production through hotfixes, skipped migrations and refresh lag.
• A diff of information_schema.columns misses dropped foreign keys, extra indexes and new enum values.
• Five catalog queries per database covered all 12 differences in our run.
• DbSchema can compare two model files, one per environment, and generate the SQL without touching either database.
• After the change, run the same comparison again and expect an empty result.

Your release passed on staging, and now it has to run on production. That only proves something if the two schemas match. To compare production and staging, list every table, column, constraint, index, type and view in both databases and diff the lists. A column query alone is not enough, as the run below shows.

How staging and production differ

Production serves live traffic and holds the real data. Staging is the copy where you rehearse a release first. It usually runs the same engine and version, but with masked or older data and wider access for testing.

For a schema comparison, only one thing matters. The structure on staging should be the structure production will have after the release. When the two schemas differ before you start, a test on staging tells you little about production.

That is why the comparison runs before the deploy, not after a failure. It answers one question: what would you have to change so that staging and production hold the same objects?

Why a staging schema drifts from production

Staging and production drift apart even when every change is meant to go through migrations. Neon's article on why staging never matches production[1] names the usual causes.

Hotfixes bypass the migration process. A bug needs an immediate ALTER TABLE in production, and nobody backports it. Failed migrations get skipped. A script fails on staging because of a data issue, and someone marks it as applied. Indexes drift too: an index added on production to fix a slow query never reaches staging.

Refreshes don't close the gap either. Neon notes that a nightly refresh leaves staging 0 to 24 hours behind production. A weekly refresh stretches that window to 168 hours. Any schema change in that window exists on one side only.

Comparing the two schemas with catalog queries

You can compare two PostgreSQL schemas with the catalog views each database already has. We tested this on 25 October 2026 with PostgreSQL 16.14. We used two copies of our 14-table demo schema: one in the role of production and one in the role of staging. The staging copy carries 12 deliberate differences.

We ran the same column query on both databases:

SELECT table_name, column_name, data_type, character_maximum_length,
       is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, column_name;

Then we saved each result to a file and compared the files:

psql -d dbschema_demo -AtF '|' -f q_columns.sql > cols_prod.txt
psql -d dbschema_demo_stg -AtF '|' -f q_columns.sql > cols_stg.txt
diff cols_prod.txt cols_stg.txt

The diff had 21 changed lines. These are four of them, production first:

< projects|key|character varying|10|NO|
> projects|key|character varying|20|NO|
< tasks|priority|USER-DEFINED||NO|'medium'::task_priority
> tasks|priority|USER-DEFINED||YES|'medium'::task_priority

So staging allows a longer project key and accepts tasks without a priority. The same diff showed a column that exists only on each side, a changed default and one table that exists only on each side. The information_schema.columns documentation[2] lists every column this view returns.

What the column query misses

The column query caught 7 of the 12 differences. It saw nothing of the other five, because they are not columns. Each one needed a query on a different catalog:

The enum case is the easiest to miss. In information_schema.columns, tasks.status reads USER-DEFINED with the type name task_status on both databases. The PostgreSQL documentation[2] explains that user-defined types appear this way. Only pg_enum[3] holds the labels:

SELECT t.typname, e.enumlabel
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
ORDER BY t.typname, e.enumsortorder;

The diff of that query showed one extra line on staging:

> task_status|blocked

Indexes came from pg_indexes[4], which also returns each index definition. View bodies came from pg_views[5]. Comparing a checksum of each definition showed that one of the two views differs. In total, five queries per database covered all 12 differences. Every new object type you use, such as triggers or sequences, adds another one.

Comparing production and staging in DbSchema

DbSchema compares schemas object by object, so you don't maintain a query per catalog. The schema synchronization documentation describes two ways to run the comparison.

The safest way for production keeps both databases untouched. Reverse-engineer production into one model file and staging into a second one. Then use Compare Model with Other Model From File. DbSchema opens the Synchronize Model and Database dialog with the second file as Project From File. Its buttons are Apply to the model and Generate SQL script, so nothing runs in a database.

Two model files compared: the same column with two lengths, and a column that exists in only one file. Nothing runs in a database.

The dialog has one row per difference. Select a row, and the lower pane shows the SQL each side would run. For a view, it compares the script body line by line. Generate SQL script writes the statements to an SQL Editor or a file and runs none of them. You then review the script and deploy it the way your release process says.

The second way compares the model with a live database. Database → Refresh Model from Database reads the database and reports "Schemas are identical" when nothing differs. Otherwise you review each difference and choose the side that wins. Commit in the database then runs the chosen statements one at a time in the Execute Script In Database Dialog.

Two details matter on PostgreSQL. A generated script that changes a view starts with a plain DROP VIEW. PostgreSQL refuses it while other views depend on that view, so edit the script before you run it. And Export Report saves the differences as HTML or Markdown, with the SQL script as an appendix. That gives you a record of what changed between staging and production for the release ticket.

Schema synchronization needs DbSchema Pro. For a step-by-step comparison on a full drift list, see comparing two database schemas. To turn the result into a reviewed script, see turning a schema diff into a safe migration script.

What to check after the change

Run the same comparison again after the deploy. The result you want is empty: no lines from diff, or "Schemas are identical" in DbSchema. Anything left is drift you have not fixed yet.

Then check the objects the comparison cannot judge. On PostgreSQL, a new index can exist but be unusable. The pg_index catalog[6] marks it with indisvalid. False means the index is possibly incomplete and cannot safely be used for queries:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

An empty result means every index is valid. Finally, keep the model files of both environments with the release. Putting a database schema in Git shows how to version them, and finding schema drift between environments covers the checks between releases.

Download DbSchema, reverse-engineer your production and staging databases into two model files, and compare them before the next release. Saving model files and schema synchronization are part of DbSchema Pro.

Frequently asked questions

Is staging the same as production?

No. Staging uses the same engine but different data, and its schema drifts through hotfixes, skipped migrations and refresh gaps. Compare the schemas before each release instead of assuming they match.

Can I compare production and staging without connecting to production during the release?

Yes. Reverse-engineer each database into its own model file once, then compare the two files. The comparison and the generated SQL script change neither database.

Why does information_schema show my enum column as USER-DEFINED?

PostgreSQL reports columns of user-defined types such as enums as USER-DEFINED, with the type name in udt_name. The enum labels themselves live in pg_enum, so compare that catalog too.

What is a staging database?

A staging database is the last copy of production where you rehearse a release. It should have production's schema, with test or masked data.

Sources

1. neon.com
2. postgresql.org
3. postgresql.org
4. postgresql.org
5. postgresql.org
6. postgresql.org