This post was rewritten after Supabase shipped Declarative Schemas 2.0 on top of a new diff engine, pg-delta. The earlier version of this article compared pgschema against the migra-based
supabase db diff. Most of that comparison no longer holds, so we start over here. Claims about pg-delta were checked against its source at 1.0.0-alpha.60, not only its README.supabase db diff was built on migra, which could not see RLS policies, triggers, partitions, comments, or domains, and Supabase’s own docs told you to fall back to hand-written migrations for those.
That is no longer the situation. Supabase replaced migra with pg-delta, a clean-room engine that reads the catalog, builds a dependency graph, and covers more object kinds than pgschema does. This post compares the two as they stand today. Where pg-delta is ahead, we say so.
What each tool is
pg-delta is a TypeScript library and CLI (pgdelta) published on npm under the PostgreSQL License. It compares two live databases and emits an ordered migration. It is embedded in the Supabase CLI (2.119+) behind an [experimental.pgdelta] flag in config.toml, where it powers supabase db diff, supabase db pull, and the new supabase db schema declarative sync command. It is also usable standalone against any Postgres. The package is a 1.0.0-alpha.x release, and the README says breaking changes may land between versions.
pgschema is a single Go binary under Apache 2.0. It dumps a schema to SQL files, plans a migration from those files against a target database, and applies the plan. It has no Supabase dependency and no migration history table.
Both support PostgreSQL 14 through 18. Both resolve the desired state by loading your SQL into a real Postgres instance and reading it back from the catalog, rather than parsing SQL text. Both order output with a dependency graph built from pg_depend.
Workflow
Supabase CLI + pg-delta
pgschema
Supabase CLI workflow
Inside the Supabase CLI, pg-delta still produces versioned migration files.sync spins up two throwaway databases: one loaded from supabase/schemas/, one built by replaying supabase/migrations/. pg-delta diffs them and writes a new timestamped migration. You then run supabase db push, which applies pending migrations and records them in supabase_migrations.schema_migrations.
Two consequences follow from this design:
- The desired state is compared against your migration history, not the live database. If someone changed production through the dashboard or
psql,syncdoes not see it. You mustsupabase db pull --declarativefirst to bring the files back in line. - You maintain two directories plus a history table. The schema files are the thing you edit; the migrations directory is a derived artifact that still has to be committed and kept consistent.
Standalone pg-delta workflow
Outside the Supabase CLI,pgdelta is a different and arguably cleaner tool. pgdelta plan --source <db> --desired <db> --out plan.json diffs two live databases. pgdelta schema apply --dir ./schema --target <db> loads your SQL files into a shadow database and plans and applies against the target directly, with no migrations directory. That is the same stateless model pgschema uses.
The standalone CLI also has commands pgschema does not: prove, drift, snapshot, schema lint. More on those below.
pgschema workflow
pgschema always compares schema files against the live target. There is one input (your SQL files) and one output (a plan). The plan is a JSON file carrying the SQL statements and a fingerprint of the target schema at plan time:apply is also described as fingerprint-gated, so this is no longer a point of difference. Both tools catch a stale plan.
Object coverage
This is where pg-delta has moved furthest. The tables below are drawn from pg-delta’s COVERAGE.md and pgschema’s unsupported page. They are split by level, because the two tools draw their boundaries differently: pgschema manages one schema at a time and leaves everything above it to your provisioning layer. pg-delta manages the whole database, roles included.Schema-level objects
Both tools target this layer. The differences here are real gaps, not scoping choices.
On ownership: pgschema deliberately ignores
OWNER TO, like pg_dump --no-owner, so one schema file works across environments with different owners. pg-delta models ownership as an edge and emits ALTER ... OWNER TO. Which you prefer depends on whether your environments share role names.
Database- and cluster-level objects
pgschema does not manage this layer at all. Its docs scope it out in one sentence: the tool operates at the schema level, and anything above that is expected to come from infrastructure tooling or a setup script. Roles referenced in grants and policies are stubbed during planning so the schema file still loads, but nothing at this level is dumped or diffed. pg-delta made the opposite choice. It models:- Schemas
- Roles, role configuration, and memberships
- Extensions
- Global
ALTER DEFAULT PRIVILEGES - Event triggers
- Publications and subscriptions
- Foreign data wrappers, servers, and user mappings
- Collations
unmodeled_kind.
If you want one tool to reconcile roles and extensions alongside tables, pg-delta does that today. There is an open pgschema issue, pgplex/pgschema#627, proposing opt-in declarative roles, memberships, and ownership, but nothing has shipped.
Extension-managed state
Some extensions keep their configuration in ordinary tables rather than the catalog. Neither pg_dump nor a plain catalog diff sees it, and pgschema does not manage it. pg-delta’s extension intent handlers read those tables and emit the extension’s own API calls instead of DDL:- pg_cron jobs, via
cron.schedule_in_databaseandcron.unschedule - pgmq queues, via
pgmq.createandpgmq.drop_queue - pg_partman configuration
unmodeled_kind warnings, and --strict-coverage turns them into failures. pgschema warns on some skipped statements (OWNER TO, global default privileges) but does not inventory every unsupported object kind in the target.
Online DDL and production safety
Here pgschema is ahead, but the gap is narrower than pg-delta’s blog post suggests. The post says zero-downtime rollout is out of scope. The source code (1.0.0-alpha.60) tells a more nuanced story:
What pg-delta does:
- Groups actions into transactional segments, runs statements that cannot be in a transaction (such as
CREATE INDEX CONCURRENTLY) alone, and rolls back a failed segment. - Marks each action with a
dataLossflag.applyrefuses destructive plans without--allow-data-loss,renderhides drops without--allow-drops, and--dry-runreports destructive actions. - Splits a plan that would overflow the lock table (
--max-locks,--split-to-fit). - Has a
concurrentIndexesplan parameter that emitsCREATE INDEX CONCURRENTLY. It is settable through the library API or a profile’s serialize rules, not a CLI flag. - Accepts
lockTimeoutMsandstatementTimeoutMsin the library’s apply options. No CLI flag sets them today. - Emits
NOT VALIDonly when the desired constraint is itself declaredNOT VALID. It does not rewrite a plain new foreign key intoNOT VALIDplusVALIDATE CONSTRAINT.
CREATE INDEXbecomesCREATE INDEX CONCURRENTLY, run outside a transaction.- Adding a foreign key or check constraint becomes
ADD CONSTRAINT ... NOT VALIDfollowed byVALIDATE CONSTRAINT. apply --lock-timeoutbounds how long any statement waits on a lock.- Statements that cannot run inside a transaction are executed outside one; the rest run in a transaction.
--pgschema-no-transactionand--pgschema-lock-timeoutdirectives in schema files override behavior per statement.
Verification
pg-delta ships aprove command: apply the plan to a throwaway clone, re-extract, and check that no drift remains and that row counts (or content fingerprints) survive per table. Its test corpus of 331 scenarios is proven in both directions on every supported Postgres version. The README is candid that prove only validates what it checked, so an empty table proves nothing about data preservation.
pgschema has no equivalent. Its correctness story is its own test suite (several hundred diff cases plus plan-and-apply integration tests), and the recommendation to run plan and apply against a staging copy first. pg-delta’s prove turns that recommendation into a command. That is a real advantage.
pg-delta also has drift (compare a live database to a saved snapshot) and schema lint (check SQL files for load-order problems without a database). pgschema’s fingerprint check covers the narrow case of “did the target change since I planned,” not general drift reporting against a baseline.
Runtime and dependencies
pgschema is lighter to put into a CI job: one binary, no container runtime, no Node. pg-delta inside the Supabase CLI needs Docker for its shadow databases. Standalone pg-delta needs Node and two reachable databases.
Organizing schema files
Both tools can export an existing database into one file per object. pg-delta’sschema export writes one directory per schema with tables/, views/, functions/ subdirectories and a _cluster/ directory for roles and extensions. It offers three layouts (by-object, ordered, grouped) and refuses to overwrite SQL files it did not generate, except in a _custom/ folder reserved for hand-written SQL for unmodeled objects. Load order is resolved automatically by pg-topo, so you no longer hand-order files in config.toml.
pgschema’s dump --multi-file writes a main.sql entry point with \i includes and the same kind of per-type directories. Because \i is a psql directive, the files load with plain psql -f main.sql without the tool. Ordering is whatever you put in main.sql; pgschema does not reorder on load failure.
Scoping what is managed
pg-delta uses profiles (--profile raw | supabase | <path>). A profile holds policy rules separating user objects from platform objects, extension handlers, assumed roles and schemas, and optionally a baseline snapshot subtracted from both sides. The supabase profile is how the auth, storage, and other platform schemas and roles stay out of your migrations. The rule DSL is evaluated first-match-wins and is readable in the source under src/policy/, but the README does not document it, so writing a custom profile today means reading code.
pgschema uses a .pgschemaignore file with glob patterns per object type:
Summary
Conclusion
The old version of this article could point at a long list of Postgres features Supabase simply could not diff. pg-delta closes that list and then goes past pgschema on roles, extensions, publications, foreign data wrappers, ownership, and extension-managed state. It also adds aprove step that pgschema lacks. Credit where due: it is a serious engine.
Choose Supabase with pg-delta if you are on Supabase, want roles and extensions managed in the same files as your tables, and are comfortable with an alpha package behind an experimental flag. If you are not on Supabase, the standalone pgdelta CLI is still worth a look for its coverage and prove.
Choose pgschema if you need the migration to be safe to run against a live production database without hand-editing it, want a dependency-free binary in CI, or prefer a stable tool that scopes itself to the schema level and leaves roles and extensions to your provisioning layer.
Both are open source and both work against any PostgreSQL 14 to 18. Try each on a copy of your database. The gaps that matter to you will show up in the first plan.
If you are moving from Supabase’s declarative schemas to pgschema, the community discussion covers the experience and gaps others hit.
