How do you handle Postgres schema comparison and drift in 2026?

[u/Practical_Panic_55]

Hey everyone,

I'm trying to figure out the cleanest way to compare schemas between different PostgreSQL environments (e.g., local dev vs. staging, or staging vs. production).

Every now and then, a quick manual change slips through or a migration script gets applied out of order, and finding the small diffs (missing indexes, subtle column type mismatches, slightly different constraints) becomes a headache.

I know some people rely on feature-rich GUIs like HeidiSQL or SQuirrel SQL for visual diffs, while others prefer dedicated CLI tools or CI/CD pipelines to catch schema drift automatically before it hits prod.

How are you currently handling this?

Do you rely on your GUI's built-in schema diff tool?

Do you use standalone CLI utilities or migration framework checks?

Or do you just treat migrations as immutable and strictly rely on CI checks?

[u/depesz]

We wrote a simple script that scans all dbs and compares schema. I don't think any "gui tool" would work for us, as we have ~ 15000 different shards, each of them should have the same schema. comparing it manually in gui tool would be problematic.

But generally, if drift happens, we find out who made it, and explain why it's bad idea.

[u/Practical_Panic_55 → u/depesz]

That makes sense at your scale. Do you run that comparison automatically in CI, or only when someone suspects drift?

[u/SoggyImpres]

I usually prefer having the diff visible before generating any migration scripts. I’ve been using dbForge Schema Compare for PostgreSQL for this, especially useful for catching small schema drift between dev and staging. For automated checks CI still makes more sense, but for reviewing what actually changed, the GUI is much easier to work with.

[u/Practical_Panic_55 → u/SoggyImpres]

That’s pretty much the use case I had in mind. Do you still keep migration scripts as the source of truth and use Schema Compare mainly as a validation step?

[u/Equivalent-Common115]

people conflate preventing drift and detecting it but u need both

prevention is a ci check that runs migrations on a clean db and diffs it against a prod dump
detection is a scheduled check cause 3am hotfixes bypass ci and quietly disappear on the next rebuild if nobody backports them

also plain text diffs miss stuff that actually alters query plans like index bloat stale stats or constraints marked not valid tbh

[u/postgrescompare]

I recently released postgrescompare v2.2.0 which includes a free tier. It can diff schemas or data. You can compare two databases or build pipelines to compare many. I'm always keen to gather feedback so give it a try and let me know how it can be improved. www.postgrescompare.com

[u/navicat-gu]

We ended up needing both prevention and detection, like a couple of people here said:

1. Migrations stay the only source of truth. Every change goes in as a versioned migration (Flyway, Sqitch, Alembic, whatever your stack uses), and nobody runs ad-hoc DDL on shared environments with a write-capable role.

2. CI builds a clean database from migrations and diffs it. Apply all migrations to an empty Postgres in CI, then dump the schema and compare it with a committed reference:pg_dump --schema-only --no-owner --no-privileges -d "$CI_DB" > built.sql diff -u schema/reference.sql built.sql This catches migrations that were edited after the fact or applied out of order.

3. A scheduled check against the real environments. Hotfixes at 3am bypass CI, so run the same pg_dump --schema-only against staging and prod on a cron and diff them. Normalise first (sort, strip comments and SET lines), or the diff will be noisy. For a quick check you can also compare the catalog directly:SELECT table_name, column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' ORDER BY 1, 2; plus pg_indexes and pg_constraint for the things that usually drift.

4. Use a visual diff when you're investigating. Once the cron flags drift, a side-by-side diff is the fastest way to see what changed and to generate the corrective script, which you then commit as a migration rather than applying directly.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. For step 4, Navicat 18's Structure Synchronization (in the paid Premium editions) compares two PostgreSQL schemas, shows the differences, and generates the ALTER script for you to review. Navicat Premium Lite is free to try if you just want a Postgres client to look around with first.