Skip to main content
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.
Declarative schema management means you write the schema you want, and a tool computes the DDL to get there. Both Supabase and pgschema do this for PostgreSQL. Until recently the comparison was lopsided: Supabase’s 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, sync does not see it. You must supabase db pull --declarative first 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.
Shadow databases run in Docker or Podman by default. The Supabase CLI can use a native runtime instead, but that is itself experimental.

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:
If the target schema changed between plan and apply, apply refuses to run. Rollback is editing the file back and planning again. pg-delta’s 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
It does not model languages, parameter ACLs, or large objects, and reports those as 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_database and cron.unschedule
  • pgmq queues, via pgmq.create and pgmq.drop_queue
  • pg_partman configuration
Supabase Storage buckets are not covered by either tool. They are rows, and pg-delta does not diff rows outside these handlers. This is a genuinely novel feature, and pgschema has nothing comparable. Objects of a kind pg-delta cannot model surface as 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 dataLoss flag. apply refuses destructive plans without --allow-data-loss, render hides drops without --allow-drops, and --dry-run reports destructive actions.
  • Splits a plan that would overflow the lock table (--max-locks, --split-to-fit).
  • Has a concurrentIndexes plan parameter that emits CREATE INDEX CONCURRENTLY. It is settable through the library API or a profile’s serialize rules, not a CLI flag.
  • Accepts lockTimeoutMs and statementTimeoutMs in the library’s apply options. No CLI flag sets them today.
  • Emits NOT VALID only when the desired constraint is itself declared NOT VALID. It does not rewrite a plain new foreign key into NOT VALID plus VALIDATE CONSTRAINT.
What pgschema does by default (online DDL):
  • CREATE INDEX becomes CREATE INDEX CONCURRENTLY, run outside a transaction.
  • Adding a foreign key or check constraint becomes ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT.
  • apply --lock-timeout bounds 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-transaction and --pgschema-lock-timeout directives in schema files override behavior per statement.
The practical difference: pgschema’s online rewrites are on by default and reachable from the CLI. pg-delta has the building blocks but leaves the concurrent-index and timeout choices to library callers, and does not do the NOT VALID rewrite at all.

Verification

pg-delta ships a prove 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’s schema 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:
It is less expressive than a profile, but it is documented and takes a minute to write. Neither tool’s mechanism is a superset of the other’s.

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 a prove 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.