Zero-Downtime Postgres Schema Changes in 2026: pgroll vs Reshape vs graphile-migrate

ALTER TABLE users ADD COLUMN email_verified boolean NOT NULL DEFAULT false; on a table with 40 million rows takes a lock. In Postgres 11+ adding a column with a non-volatile default is metadata-only, so that particular statement is safe — but ALTER COLUMN ... TYPE, adding a constraint, or backfilling a new index will still queue behind every open transaction and block all writes behind it. One long-running analytics query, and your “quick migration” becomes a five-minute outage.

The industry answer is expand and contract: deploy the new schema shape first, run both shapes side by side, and remove the old one only after every application instance speaks the new dialect. Three open-source tools automate that pattern for Postgres, and they automate it very differently.

TL;DR: Quick Verdict

Use pgroll if you want the most explicit, versioned two-phase workflow with rollback and a schema-version search_path for gradual rollout — it is the most complete implementation of expand/contract and the best fit for teams running multiple app versions simultaneously. Use Reshape if you prefer migrations declared in YAML and want the tool to generate the views and triggers for you, with a straightforward start/complete/abort lifecycle. Use graphile-migrate if you already live in the Node/graphile ecosystem and want idempotent SQL files with a shadow database that validates migrations in CI before they touch production. None of them removes the need to understand locks — they remove the tedium.

Side-by-Side Comparison

Star counts and last-commit dates come from the GitHub API at publish time.

Decision Matrix: Pick in 10 Seconds

pgroll: Schema Versions You Can Switch Between

pgroll’s insight is that a migration creates a new schema version rather than mutating the existing one. Under the hood it keeps the physical table, adds views per version, and uses triggers to route writes to the right columns. Your application chooses a version with search_path.

Real CLI flow from the project README:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# initialize pgroll's metadata schema
pgroll --postgres-url postgres://user:pass@host:5432/appdb init

# adopt an existing, already-populated database
pgroll --postgres-url postgres://user:pass@host:5432/appdb \
  baseline 000_existing_schema ./migrations/

# start a migration (creates the new version alongside the old)
pgroll --postgres-url postgres://user:pass@host:5432/appdb \
  start initial_migration.json

# point a new app version at the new shape
# SET search_path TO 'public_initial_migration';

# once no clients use the old version
pgroll --postgres-url postgres://user:pass@host:5432/appdb complete

# or back out entirely
pgroll --postgres-url postgres://user:pass@host:5432/appdb rollback

A migration file describes operations declaratively:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

{
  "name": "add_email_verified",
  "operations": [
    {
      "add_column": {
        "table": "users",
        "column": {
          "name": "email_verified",
          "type": "boolean",
          "default": "false",
          "nullable": false
        }
      }
    }
  ]
}

The operational catch is connection routing. Every client must set search_path — which means your connection pooler, ORM, and background workers all need to agree on the version. Forget a worker and it writes to the old view while the new one reports stale data. Set the version in one place (a single env var injected into every consumer) or accept that debugging will be painful.

Reshape: YAML Migrations With an Abort Button

Reshape implements the same dual-schema trick but with a YAML migration file and a three-verb lifecycle: start, complete, abort. From the README:

1
2
3
4
5
6

docker run -v $(pwd):/usr/share/app fabianlindfors/reshape reshape migration start
# ... application deploys and runs ...
reshape migration complete

# if the deploy went badly:
reshape migration abort

The lifecycle is documented precisely: start sets up views and triggers so both the old and new schema are usable at once; complete removes the old schema, intermediate data, and triggers; abort rolls back everything start did without losing data.

For applications in supported languages, Reshape provides helper libraries so the connection automatically gets the right schema. For everything else, generate the query yourself:

1

RESHAPE_SCHEMA_QUERY=$(reshape schema-query)

1
2

# apply on every new connection
cursor.execute(os.environ["RESHAPE_SCHEMA_QUERY"])

Development loop — the part teams get wrong:

1
2
3
4

reshape migration start     # iterate on the YAML
reshape migration abort     # discard
reshape migration start     # try again
# only run `complete` after the app is fully rolled out

Running complete during development is a trap: it drops the intermediate structures you need to keep iterating, and the next start begins from a different baseline.

graphile-migrate: Idempotent SQL Plus a Shadow Database

graphile-migrate takes the least magical approach: you write SQL, and it validates that SQL against a shadow database before it ever reaches production. Its README describes the model directly — in development it uses two databases (main and shadow), while in production most users only run graphile-migrate migrate, which operates solely on the main database.

The workflow revolves around current.sql, which must be idempotent — safe to run repeatedly:

1
2
3
4
5

npm install -g graphile-migrate
graphile-migrate init                  # creates .gmrc and migrations/
graphile-migrate watch                 # dev loop: re-runs current.sql on save
graphile-migrate commit -m "add email_verified"   # freeze current.sql into a numbered batch
graphile-migrate migrate               # production: apply committed batches

A typical current.sql that stays idempotent and does not lock your table:

1
2
3
4
5
6
7
8
9

-- safe on large tables: metadata-only column add on PG 11+
alter table users add column if not exists email_verified boolean not null default false;

-- build the index without blocking writes
create index concurrently if not exists users_email_verified_idx
  on users (email_verified);

-- backfill in batches from the application side, not in a single UPDATE
-- (see the batching pattern below)

Because there is no dual schema, graphile-migrate does not let two application versions disagree about column semantics. That is a feature and a constraint: you must keep migrations backward compatible by hand — add columns as nullable, deploy code, backfill, then add the NOT NULL constraint in a later migration.

Backfilling Without an Outage

Every tool above handles schema shape. None of them can save you from a naive backfill. The pattern that works on large tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1) run in a loop from a script until zero rows are affected
with batch as (
  select id from users
  where email_verified is null
  limit 5000
  for update skip locked
)
update users u
set email_verified = false
from batch
where u.id = batch.id;

Two things make this safe: limit bounds the lock footprint, and for update skip locked avoids waiting on rows another worker already grabbed. Sleep briefly between batches, watch replica lag, and stop the loop if lag crosses your threshold.

Then, and only then, tighten the schema:

1
2

-- 2) after the backfill completes and the app writes the column reliably
alter table users alter column email_verified set not null;

Add the constraint with NOT VALID first if you want to avoid a long validation lock, then VALIDATE CONSTRAINT separately — validation takes a weaker lock that does not block writes.

Pitfalls That Take Production Down

Long transactions block DDL. Every ALTER TABLE waits for a lock behind open transactions. Set lock_timeout (e.g. SET lock_timeout = '3s') so a migration fails fast instead of queueing every write behind it.

Trigger overhead is real. pgroll and Reshape install triggers to keep dual schemas consistent. On write-heavy tables that is measurable overhead and it applies to every insert and update until complete runs. Do not leave a migration half-completed for a week.

Forgetting one consumer of the schema. A worker, cron job, or BI tool that still uses the old search_path will quietly write to the wrong view. Grep your entire deployment for connection strings before starting a pgroll migration.

Running complete before the old version is gone. Both pgroll and Reshape drop the old schema on completion. If a single instance is still running the previous release, it will start throwing “column does not exist” errors.

Non-idempotent current.sql in graphile-migrate. watch re-runs the file on every save. Without if not exists clauses you will get duplicate-object errors that look like tool bugs.

Index creation without CONCURRENTLY. CREATE INDEX takes a SHARE lock that blocks writes for the duration of the build. On a large table that is minutes. CONCURRENTLY is slower overall but does not block.

No rehearsal. Run the exact migration against a production-sized copy. Our database dev/test environment comparison covers how to get a realistic clone cheaply.

Assuming MySQL lessons transfer. The MySQL world solves this with gh-ost and pt-online-schema-change, which copy tables and swap them. Postgres has no equivalent table-swap toolchain, so the expand/contract approach is not optional — see our MySQL zero-downtime migration comparison for how the other side does it.

For plain versioned migrations where zero downtime is not a requirement, our schema management tool comparison covers Atlas, sqldef, and Skeema — simpler tools that are, for many teams, entirely sufficient.

FAQ

Does pgroll work with an existing production database? Yes. pgroll init creates its metadata schema and pgroll baseline imports your current structure as version zero, after which new migrations are applied on top. The baseline step is what people miss when they try pgroll on a legacy database.

Which tool has the least runtime overhead? graphile-migrate, because it does not create parallel schemas or triggers. pgroll and Reshape both install triggers to keep old and new shapes consistent, which costs write throughput until the migration completes — acceptable for most workloads, but worth measuring on hot tables.

Can I use these with Django/Rails/Prisma migrations? You can, but you are mixing two philosophies. Framework migrations usually assume the schema changes atomically with the deploy. If you adopt pgroll or Reshape, let it own schema DDL and use the framework only for application code, otherwise two tools will fight over the same columns.

What happens if a deploy fails halfway through? With pgroll, pgroll rollback removes the new version. With Reshape, reshape migration abort undoes everything start created without data loss. With graphile-migrate, you roll forward with a compensating migration — it deliberately does not support down migrations.

Is ALTER TABLE ADD COLUMN with a default still safe on Postgres 16+? Yes for non-volatile defaults — Postgres 11 and later store the default in the catalog instead of rewriting the table. It stops being free when the default is volatile (e.g. now()), when the column is added with a constraint that requires validation, or when you change a column type.

Do I need search_path management if I only run one app version? Not for correctness, but pgroll still requires clients to select a version schema while a migration is in progress. If managing that is unacceptable, Reshape’s schema-query approach or graphile-migrate’s single-schema model will be less friction.

💰 想测试你的市场判断力？我用 Polymarket 做预测市场交易——这是全球最大的预测市场平台，从大选结果到技术监管时间线，什么都可以押注。和赌博不同，这是真正的信息市场：你懂的信息越多，胜率越高。我靠预测技术相关事件的走向已经赚了不少。用我的邀请链接注册：Polymarket.com