Your migration passed in staging and failed in production. The error was a missing column, or a constraint that already existed, or a type that didn’t cast. You didn’t change the migration between the two runs — so the migration wasn’t the problem. The two databases were.

A postgres schema diff between environments is the check that would have caught it. It answers one question before you deploy: are dev, staging, and prod actually the same shape, or have they quietly drifted apart? Most teams assume the answer is yes and find out otherwise at the worst possible time.

Why environments diverge

Environments start identical and stop being identical the moment someone touches one directly. A support ticket gets unblocked with a manual ALTER on prod. An index gets added to staging to test a query plan and never makes it back to dev. A default changes in the dashboard. A migration gets applied to two environments in a different order because one deploy was rolled back halfway.

None of these are dramatic. That’s the point — each one is a small, reasonable act that leaves the environments a little further apart. The gap accumulates silently because nothing is watching for it, and Postgres will happily run against a schema that no longer matches the one your migrations describe.

Diffing by hand with the catalog

You don’t need a tool to start. Postgres describes its own structure through information_schema and pg_catalog, and you can read that structure the same way in every environment.

Here’s a query that returns the full column-level shape of the public schema — one row per column, ordered so the output is stable and comparable:

select
  table_name,
  column_name,
  data_type,
  is_nullable,
  coalesce(column_default, '') as column_default
from information_schema.columns
where table_schema = 'public'
order by table_name, column_name;

Run it against staging, save the output. Run it against prod, save that too. Then diff the two files with whatever you already use:

diff staging.txt prod.txt

Every line that differs is a real divergence: a column that exists in one environment and not the other, a nullability that changed, a default that drifted. Because the query is ordered deterministically, the diff is clean — you’re comparing like for like, not fighting row-order noise.

This is the cheapest environment diff there is, and it works today. For most teams, the first time they run it is also the first time they learn their environments disagree.

Where the hand-rolled approach runs out

The column query is a real check, but it’s a partial one, and the gaps are exactly where production incidents live.

Columns are only part of the schema. Indexes live in pg_indexes, constraints in pg_constraint, foreign keys and triggers and functions each in their own catalog. A complete diff means maintaining a query for each — a small pile of SQL that itself drifts between the people who wrote it.
You can’t join across databases. dev and prod are separate servers. Comparing them means dumping each side and diffing the exports out of band, every time, by hand.
A one-time diff has a short shelf life. The environments were identical when you checked this morning. Someone ran an ALTER at lunch. A diff you ran once tells you nothing about the state you’ll deploy against this afternoon — you’d have to re-run it, and remember to.

Supabase branching helps with provisioning — it spins up a preview database so you develop against a real copy — but a preview branch doesn’t tell you whether prod diverged after you branched. Provisioning an environment and continuously comparing environments are different jobs. We wrote more about that distinction in Supabase branching vs LeafSQL.

How LeafSQL diffs environments

LeafSQL turns the manual routine into a stored one. It takes a snapshot of each environment’s schema — we call these leaflets — and versions them per environment: dev, staging, prod. Every leaflet is hashed and kept, so you have a history instead of a single throwaway export.

To compare two environments, LeafSQL diffs their latest leaflets and returns a structured result: which tables exist in one and not the other, and for the tables they share, which columns differ in type, nullability, or default. It’s the same comparison the catalog query above produces, minus the dumping, ordering, and file-wrangling — and it runs against stored snapshots, so you can compare dev to prod without opening two connections yourself.

Being honest about the boundary: today that comparison is table-and-column level. Diffing the deeper objects — indexes, constraints, foreign keys, triggers, functions — is on the roadmap, not shipped. If you need those compared right now, the pg_catalog queries above are still the way to do it, and we’d rather tell you that than imply coverage we don’t have.

Where LeafSQL earns its place is the step after the diff. Once you can see how two environments differ, you can turn that difference into a migration PR — forward and rollback SQL, pushed to your GitHub repo as a pull request you review and merge. The divergence stops being a surprise in a failed deploy and becomes a reviewed change with an author and a diff. The mechanics of why we generate that SQL but never apply it silently are covered in why we let an LLM write SQL but never execute it unreviewed.

Where to start

Run the column query against two environments today. It takes five minutes and it will tell you, concretely, whether your databases already disagree — before a deploy tells you the same thing more expensively.

If you’d rather not run it by hand every afternoon, that’s the gap LeafSQL fills: versioned leaflets per environment, a stored diff between any two, and reconciliation as a migration PR. And if the change you’re chasing happened outside your migration flow in the first place, that’s schema drift — a related problem worth catching at the same time.

Two environments passing as identical when they aren’t is a normal failure. Not being able to see the difference is the part you can fix.