Deploying PostgreSQL schema changes with zero downtime using pgroll

The night before a production deployment, most teams have experienced a message like this in the team channel: "Looks like we'll need to post a brief maintenance notice at 2 AM tonight — we have to change a column type." A single ALTER TABLE on a table with billions of rows holds an AccessExclusiveLock for several minutes, during which all queries pile up and timeout alerts flood in. This article is about how to dismantle that problem with pgroll.

pgroll is an open-source CLI built by Xata that exposes both the old and new versions of a schema simultaneously while a migration is in progress. Old app instances keep reading the old column name, and new app instances read the updated column name — without any data inconsistency. This article covers how that's possible under the hood and how to actually deploy it in production.

Specifically, this article covers how the Expand/Contract pattern works, the practical steps for deploying three scenarios — RENAME, type change, and adding NOT NULL — along with their JSON files, how to implement search_path switching at the application layer, and the real pitfalls you'll hit when introducing this in production.

Core Concepts

What is the Expand/Contract Pattern

Traditional migration tools execute DDL sequentially. Other transactions must wait until ALTER TABLE finishes, and the moment an old-version app and a new-version app are briefly running at the same time, schema mismatches cause errors.

The Expand/Contract pattern (also called Parallel Change) breaks a destructive change into three phases.

• Expand: Exposes the new version schema without touching the existing column. For cases that require data transformation like type changes, a new physical column, bidirectional triggers, and a batch backfill are added. For cases without data transformation like RENAME, only a view layer is added.
• Coexist: Old and new apps connect with different search_path settings, each looking at a different version view.
• Contract: Once all traffic has moved to the new version, running pgroll complete cleans up the old view and any remaining triggers and columns.

What pgroll Does Internally

There is a single physical table. pgroll overlays PostgreSQL views on top of it, one per version, each isolated in a separate schema. Schema names are auto-generated based on the migration name (e.g., pgroll_rename_name_to_full_name) — they are not fixed names like pgroll_v2.

The diagram below shows the structure for cases that require a new physical column, such as a type change or add-column.

In a RENAME-only case, no physical column or trigger is added. The two version views simply expose the same physical column under different names. This difference has a significant impact on backfill time and trigger overhead, which we'll revisit in the scenarios below.

Three Core Commands

The fact that pgroll start is blocking has a direct impact on CI/CD pipeline design. On tables with hundreds of millions of rows, this step can take hours, so pipeline timeouts and deployment gates must be designed accordingly.

Practical Application

Installation and Initialization

brew install xataio/tap/pgroll
 
pgroll init --postgres-url "$DATABASE_URL"

Initialization creates the pgroll schema and a migration history table. All migration state is managed there from that point on.

Switching search_path in Your Application

This is where the most hands-on work actually happens when adopting pgroll. It's true that "you don't need to write schema branching logic in your app code," but search_path must still be configured so that each deployment version looks at its own version's schema. There are three approaches.

1) Pass it as an option in the connection string

The simplest approach. Uses the options parameter supported by most PostgreSQL drivers.

postgres://user:pass@host:5432/mydb?options=-csearch_path%3Dpgroll_rename_name_to_full_name%2Cpublic

URL-encode search_path=<version_schema>,public after -c. Putting public second ensures tables not managed by pgroll (e.g., session tables) remain accessible as usual.

2) Run SET search_path in a connection initialization hook

Most drivers — HikariCP, pgx, node-postgres, etc. — provide a hook to register a query to run immediately after a connection is established.

// node-postgres example
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.on('connect', (client) => {
  client.query(`SET search_path TO ${process.env.PGROLL_SCHEMA}, public`);
});

3) Specify it in ORM configuration

Prisma accepts it via schemas, TypeORM via schema, and SQLAlchemy via connect_args={"options": "-csearch_path=..."}.

For all three approaches, it's common practice to manage the currently active pgroll version schema name as a deployment environment variable. Capturing the schema name output by pgroll start as a pipeline variable and injecting it at app deployment time makes automation clean.

PgBouncer transaction mode caveat: search_path is a session-level setting and is not preserved under PgBouncer transaction pooling. In that case, switch to session mode or hard-code it via the options parameter in the connection string, as in approach 1.

Scenario 1 — Column RENAME (users.name → users.full_name)

The most common case. In pgroll, a RENAME-only migration is handled without creating a new physical column — instead, the two version schema views expose the same physical column under different names. There's no backfill and no trigger, so the overhead is minimal.

{
  "name": "rename_name_to_full_name",
  "operations": [
    {
      "alter_column": {
        "table": "users",
        "column": "name",
        "name": "full_name",
        "up": "name",
        "down": "full_name"
      }
    }
  ]
}

Here, up and down are SQL expressions that define value transformations between versions. The direction can be confusing: per the pgroll documentation, up is the transformation from old version → new version, and down is from new version → old version. The naming feels counterintuitive because it comes from the convention of "migrating up." For RENAME there's no value transformation, so you just reference the column directly.

pgroll start rename_name_to_full_name.json --postgres-url "$DATABASE_URL"

Once this command returns, the new version schema (pgroll_rename_name_to_full_name) has been created. The old app reads public.users.name and the new app reads pgroll_rename_name_to_full_name.users.full_name. Once deployment is complete, run:

pgroll complete --postgres-url "$DATABASE_URL"

At this point, the physical column name is finalized as full_name and the old version view is removed.

Scenario 2 — Column Type Change (status VARCHAR(20) → TEXT)

Unlike RENAME, a type change requires creating a new physical column and running a backfill. The traditional approach would lock the entire table, but pgroll creates a separate column with the new type, transforms values using the specified SQL expression, and performs a batch backfill.

{
  "name": "widen_status_column",
  "operations": [
    {
      "alter_column": {
        "table": "orders",
        "column": "status",
        "type": "text",
        "up": "status",
        "down": "LEFT(status, 20)"
      }
    }
  ]
}

up is the expression used to pass the VARCHAR(20) value to TEXT; down is the expression used to narrow TEXT back to VARCHAR(20) on rollback. When data loss could occur in the down direction (a TEXT value longer than 20 characters), you must explicitly include truncation logic as shown above.

For tables with hundreds of millions of rows, backfill can take a long time. If I/O load is a concern, you can tune the batch size and delay.

pgroll start widen_status_column.json \
  --postgres-url "$DATABASE_URL" \
  --backfill-batch-size 5000 \
  --backfill-batch-delay 100ms

It's recommended to run pgroll start --help before executing to confirm the exact flag names and duration format (Go-style 100ms, 2s, etc.) for your current version.

Primary Key column type changes are a separate problem: PK type changes like INT → BIGINT involve referenced foreign keys, related sequences, indexes, and partition definitions, and cannot be automatically resolved with a single alter_column in pgroll. If you need to widen a PK, a dedicated migration design — add new column → update referencing tables → swap PK — must be done first.

Scenario 3 — Adding a NOT NULL Constraint

ALTER TABLE users ALTER COLUMN email SET NOT NULL scans the entire table while holding an AccessExclusiveLock. The more rows there are, the longer the lock lasts. pgroll minimizes lock time internally by first adding the constraint with NOT VALID and then separating out the validation with VALIDATE CONSTRAINT.

{
  "name": "add_not_null_to_email",
  "operations": [
    {
      "alter_column": {
        "table": "users",
        "column": "email",
        "nullable": false,
        "up": "COALESCE(email, 'unknown@example.com')",
        "down": "email"
      }
    }
  ]
}

The up expression determines how to fill existing null values. The example above fills them with a default email; adapt it to match your actual business logic (e.g., generating a placeholder based on user ID).

Pre-validating Migration Files

pgroll provides a validate subcommand to validate a migration file before running it.

pgroll validate migrations/add_not_null_to_email.json --postgres-url "$DATABASE_URL"

Beyond JSON schema validation, it also checks whether referenced tables and columns exist, filtering out errors that could be caught at the front of your deployment pipeline before actually running pgroll start. It's recommended to run this automatically in CI for any PR that modifies a migration file.

CI/CD Pipeline Integration

A deployment flow that accounts for pgroll start blocking until backfill is complete looks like this:

Here is a GitHub Actions example. The key part is how the search_path value is passed to the app in the deploy step, so that's shown concretely.

jobs:
  deploy:
    steps:
      - name: Validate migration
        run: pgroll validate migrations/widen_status_column.json --postgres-url $DATABASE_URL
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
 
      - name: Start migration (blocking; waits for backfill)
        id: pgroll_start
        timeout-minutes: 120
        run: |
          pgroll start migrations/widen_status_column.json --postgres-url $DATABASE_URL
          echo "schema=pgroll_widen_status_column" >> "$GITHUB_OUTPUT"
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
 
      - name: Deploy new version
        run: ./scripts/deploy.sh
        env:
          PGROLL_SCHEMA: ${{ steps.pgroll_start.outputs.schema }}
          DATABASE_URL: ${{ secrets.DATABASE_URL }}?options=-csearch_path%3D${{ steps.pgroll_start.outputs.schema }}%2Cpublic
 
      - name: Drain old instances
        run: ./scripts/drain-old.sh
 
      - name: Complete migration
        run: pgroll complete --postgres-url $DATABASE_URL
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

In the Drain old instances step, you must confirm that old version instances are completely shut down. If you run complete while an old app is still running, the view it was querying disappears and queries will fail.

Pros and Cons

Advantages

Disadvantages and Caveats

Common Mistakes in Practice

1. search_path lost in PgBouncer transaction mode

Transaction pooling reuses backend connections per transaction, so session-level SET search_path is not preserved. Switch to session mode or fix search_path via the options parameter in the connection string.

2. Running complete too early

Calling pgroll complete before old version instances have fully shut down removes the old version view, causing queries to fail. Put an explicit gate in your deployment pipeline to confirm old instances have terminated.

3. Leaving the coexistence period open for days

Starting a migration and leaving it without running complete or rollback causes trigger overhead and monitoring complexity to accumulate. It's safer to enforce something like an SLO of "run complete within N hours of deployment completion" in the pipeline.

4. Omitting up/down expressions or ignoring downward data loss

When narrowing a type with alter_column, if you leave the down expression as just a raw column reference, the trigger will fail in data-loss scenarios (e.g., rolling back from TEXT to VARCHAR(50)). For any direction where loss can occur, explicitly include truncation logic like LEFT() or SUBSTRING().

Closing Thoughts

What pgroll solves isn't just "zero downtime." The essence is making schema changes into rollbackable units and allowing old and new version apps to share the same DB while each seeing a different schema.

The flow of standing up the new version view in the Expand phase, letting both versions coexist in the Coexist phase, and cleaning up the old view in the Contract phase looks procedurally complex at first, but once it's established in a CI pipeline, deployment risk drops noticeably. The fact that a single pgroll rollback command can undo a change is enough on its own to stop pushing schema deployments into late-night maintenance windows.

Here are a few indicators that adoption is worth considering:

• A single table exceeds 100 million rows and ALTER TABLE lock time is starting to threaten your service SLA
• During rolling deployments, old and new instances coexist for several minutes, and you've seen 5xx errors spike due to schema mismatches
• Your team has had an incident where manually preparing a schema rollback in SQL led to accidental data corruption
• You're on managed PostgreSQL (RDS, Aurora, Cloud SQL, etc.) and need to improve your migration workflow without superuser access

Getting started is as simple as running pgroll init on staging and running a single RENAME migration all the way from Expand to Complete. Connecting with psql to see how the version schema is created and how the views are defined will naturally surface the discussions your team needs to have before applying this in production — pipeline ordering, search_path injection approach, and complete gate policy.

References

• pgroll official site
• GitHub - xataio/pgroll
• Introducing pgroll: zero-downtime, reversible, schema migrations for Postgres (Xata blog)
• Schema changes and the power of expand-contract with pgroll (Xata blog)
• How pgroll works under the hood (Xata blog)
• pgroll 0.14 - new commands and control over version schemas
• Zero downtime schema migrations with pgroll - Neon Guides
• pgroll in Action: Client-Side Evaluation (OpenSourceDB)
• Zero-Downtime Schema Migrations for Self-Hosted Postgres with pgroll (SyneHQ)
• Zero-Downtime PostgreSQL Schema Migrations: Expand/Contract vs Blue-Green Deployment (DEV Community)
• Top Open Source Postgres Migration Tools in 2026 (Bytebase)
• Introducing multi-version schema migrations (pgroll.com blog)