pg-schema-diff plans Postgres migrations instead of writing them by hand

A schema diff becomes a migration plan, not a script

pg-schema-diff computes the differences between Postgres database schemas and generates the SQL required to get your database schema from point A to B with minimal downtime and locks. The point of that sentence is in the last few words, and the whole design follows from them.

The conventional way to change a schema is to write the migration by hand. You decide that an index needs a second column, you write CREATE INDEX, you consider what it locks, you write it down. The tool replaces that with a different loop. You keep your schema in plain DDL files, you change a file, and the tool computes the difference between what the database looks like now and what the files describe, and produces a plan of statements that will get there.

The audience is a team that already treats a directory of SQL files as the source of truth for their schema. The repository's example workflow starts by creating a schema directory and writing the table definitions into it, and the plan commands all take a --to-dir pointing at that directory. The alternative is to dump an existing database and start from that.

The tooling attempts to use native Postgres migration operations to perform online migrations and avoid locking wherever possible. Not all migrations will be lock free and some might require downtime, but the hazards system will warn you ahead of time when that is the case. That last clause is the important one, because it is the difference between a tool that silently takes your table offline and one that tells you it is about to.

It is a Stripe project, the primary language is Go, the licence is MIT, and the recent releases are v1.0.9 on 2026-08-06, v1.0.8 on 2026-07-28 and v1.0.7 on 2026-07-03, with the last push on 2026-08-06.

Index replacement builds the new index before dropping the old one

The first worked example in the README is the one that shows the difference in approach, and it is an index that gains a column.

The project's diff is the change you would write in your schema file, an index on message becoming an index on message and created_at. The generated plan is what the tool does about it:

$ pg-schema-diff plan --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir ./schema
/*
Statement 0
*/
SET SESSION statement_timeout = 3000;
SET SESSION lock_timeout = 3000;
ALTER INDEX "public"."message_idx" RENAME TO "pgschemadiff_tmpidx_message_idx_cWw18w4cQ_i7MKzfy7ek9g";

The first statement renames the existing index out of the way, under a generated temporary name, so that the new index can take the original name. That is the trick that makes the whole thing work without a window where the name is missing.

Then the new index is built concurrently:

SET SESSION statement_timeout = 1200000;
SET SESSION lock_timeout = 3000;
CREATE INDEX CONCURRENTLY message_idx ON public.foobar USING btree (message, created_at);

/*
Statement 2
  - INDEX_DROPPED: Dropping this index means queries that use this index might perform worse because they will no longer will be able to leverage it.
*/
SET SESSION statement_timeout = 1200000;
SET SESSION lock_timeout = 3000;
DROP INDEX CONCURRENTLY "public"."pgschemadiff_tmpidx_message_idx_cWw18w4cQ_i7MKzfy7ek9g";

The old index is dropped only at the end, concurrently, and the plan annotates that step with a hazard, because queries using the old index may get slower between the build finishing and the drop happening.

The effect is the one the README claims in the key features list: online index replacement, where the new version is built before the old version is dropped, preventing a window where no index is backing queries. And the timeouts are set per statement, so the concurrent build gets a long statement timeout and a short lock timeout, which is exactly the right shape for an operation that is allowed to take a long time but is not allowed to wait for a lock.

NOT NULL is added with a check constraint, not a table rewrite

The second example is the classic migration that people get wrong. The diff makes a column NOT NULL:

ALTER TABLE "public"."foobar" ADD CONSTRAINT "pgschemadiff_tmpnn_GimngG1rRkODhKvgjhGfNA" CHECK("created_at" IS NOT NULL) NOT VALID;

/*
Statement 1
*/
SET SESSION statement_timeout = 3000;
SET SESSION lock_timeout = 3000;
ALTER TABLE "public"."foobar" VALIDATE CONSTRAINT "pgschemadiff_tmpnn_GimngG1rRkODhKvgjhGfNA";

The approach is to add the NOT NULL requirement first as a check constraint that is not yet validated. The NOT VALID keyword is the important part: Postgres accepts the constraint immediately, without checking the existing rows, and without taking a lock that blocks reads and writes for the duration of the scan. Validation happens as a separate statement, which takes a weaker lock, and only after that is the column actually set to NOT NULL. Finally the temporary constraint is dropped, because it has served its purpose and the column's own NOT NULL now enforces the rule.

The README describes this as leveraging check constraints to eliminate the need for a long-lived access-exclusive lock on the table, and the key features list says the same thing about online NOT NULL constraint creation. The generalisation in the feature list is that online constraint builds work the same way for check and foreign key constraints, which are added as INVALID before being validated.

So there are two different online techniques in this tool, and they are worth keeping apart. Indexes are replaced by building the new one first and dropping the old one last. Constraints are added as not valid, validated, and then promoted to the real constraint. Both exist to avoid the same thing, an access exclusive lock on a table that the application is using.

The hazards system makes you approve the dangerous parts

The tool's second safety mechanism is the hazards system, and it is visible in the plan output as the annotations before a statement.

The example plan carries two of them. The concurrent index build is annotated with INDEX_BUILD, which warns that it might affect database performance, that concurrent index builds require a non-trivial amount of CPU, that they can take a while, and that they do not lock out writes. The index drop carries INDEX_DROPPED, which explains that queries using that index might perform worse once it is gone.

The key features list describes the system as a comprehensive set of features to ensure the safety of planned migrations, with operators warned of dangerous operations. And the CLI makes the approval explicit, because any hazards in the generated plan must be approved by name:

pg-schema-diff apply --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir schema --allow-hazards INDEX_BUILD

So the apply command refuses to run a plan containing an unapproved hazard, and you have to name the hazard class you are willing to accept. That is a good default for a tool that is going to run against a production database, because it turns a judgement call into something you make on the record.

The second safety feature is that migration plans are validated first against a temporary database exactly as they would be performed against the real database. The CLI is a demonstration implementation of the library, and the library documentation is on pkg.go.dev, so the same guarantee is available if you embed the tool in your own migration runner.

The prioritisation rule is also worth knowing. Building new indexes is always prioritized over deleting old indexes, which keeps the window in which both exist, and therefore the load on the database, as short as the dependency order allows.

The CLI is three verbs: apply, dump and plan

Using the command line is a three step workflow, and the README walks through it in full.

You start by creating a directory for your schema files and putting SQL in it:

mkdir schema
echo "CREATE TABLE foobar (id int);" > schema/foobar.sql
echo "CREATE TABLE bar (id varchar(255), message TEXT NOT NULL);" > schema/bar.sql

Then you apply it to a fresh database:

pg-schema-diff apply --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir schema

If you already have a database, the README gives the starting point instead, dumping the current schema into the directory:

mkdir -p schema && pg-schema-diff dump --dsn "postgres://postgres:postgres@localhost:5432/postgres" > schema/schema.sql

From then on, editing a file and applying again is the whole loop. The example appends an index to a file and applies with the hazard approved:

echo "CREATE INDEX message_idx ON bar(message)" >> schema/bar.sql

One operational detail deserves attention. The README recommends setting the PGPASSWORD env var, because it will override any password set in the connection string. That matters because the example commands put the password in the connection string itself, in plain text in your shell history, and a DSN on a command line is visible in the process list to other users on the machine.

The plan verb is the preview. It takes the same arguments as apply and prints the statements without running them, and the README uses it for every example, which is the intended habit: plan, read the hazards, then apply.

The library is the product and the CLI is one implementation of it

The distinction between the tool and the library is the one to make before you build on this.

The repository is a Go module, github.com/stripe/pg-schema-diff, and it is published on pkg.go.dev with the documentation for using the library. The CLI lives in cmd/pg-schema-diff and the README describes it as an example implementation with the library, which is the phrasing that tells you the CLI is the thin part.

The module requires Go 1.25.5. Its direct dependencies describe the implementation. The Postgres drivers are pgx and lib/pq, so the tool speaks to the database with the standard Go database interfaces. cobra is the command line framework and promptui is the interactive prompt library, which is what the hazard approval prompt is built from. hashicorp/go-version appears for version comparisons, go-logfmt for structured logging, and google/go-cmp and stretchr/testify for tests, along with uuid for the generated temporary object names you saw in the plan output.

The internal structure is visible in the file listing. There is internal/, pkg/ and cmd/ at the top level, a build/ directory, a scripts/ directory, and the Makefile shows the generation step: code_gen depends on go_mod_tidy and sqlc, and sqlc runs generate inside internal/queries. So the tool's SQL access is itself generated, with sqlc generating the Go code from the queries, and there is a .sqlfluff configuration for linting the SQL it produces, with a sql_lint target and a separate script for fixing multiline SQL strings.

That generation step is the part with the longest lead time for a new contributor, and it is the part to look at first if you are planning to modify the tool, since the queries are the source and the Go is not.

What it does not do, and what it costs to run

Two limitations are stated and one is worth inferring from the design.

The stated one is that stateful online migration techniques, like shadow tables, aren't yet supported. The README is direct about it. So a migration that would need to copy data into a new table, backfill it, and switch over is out of scope, and the tool will not silently attempt a version of it.

The second is that not all migrations will be lock free and some might require downtime. The promise is not zero downtime, it is minimal downtime and locks wherever possible, with a warning when that is not achievable.

The third is implied by the workflow rather than stated. The plan is computed from the live database and a directory of files, and it is validated against a temporary database. That means the tool needs to connect to the database it is migrating, and it needs somewhere to create that temporary database, which is a real requirement for a deployment pipeline that runs migrations against a replica or a restored copy.

So the honest summary is that this is a tool for schema shape changes, where a table, an index or a constraint changes but the data does not need to move. It handles those cases with genuinely online Postgres operations, it tells you when it cannot, and it makes you approve the operations that will cost you performance. It is not a general data migration engine, and the README does not pretend otherwise.

Editorial conclusion

Adopt pg-schema-diff if you maintain a Postgres schema as plain SQL files and you want migrations that are planned, previewable and aware of locking, and if you are willing to run the plan command before the apply command every time. Do not adopt it if you need stateful online migrations, because the README says shadow tables and similar techniques are not yet supported, and do not adopt it for a database whose schema is owned by an ORM's migration tool, since the whole model here is a directory of DDL files as the source of truth. Before you adopt it, read the hazards your plans will raise, because the tool requires you to approve them explicitly, and check that the schema directory, not the database, is the artefact your team actually reviews and versions.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. README
4. Releases
5. stripe/pg-schema-diff on GitHub