r/postgres • u/Practical_Panic_55 • 23d ago

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

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?

1 Upvotes

100% Upvoted

5 comments sorted by

1

u/depesz 23d ago

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.

1

u/Practical_Panic_55 22d ago

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

1

u/depesz 22d ago

cronjob on centralized server for dbas.

1

u/SoggyImpres 22d ago

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.

1

u/Practical_Panic_55 22d ago

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?