pgroll — Zero-Downtime PostgreSQL Schema Migrations

Introduction

pgroll is a schema migration tool for PostgreSQL that eliminates downtime during DDL changes. It applies the expand-and-contract pattern automatically: creating new columns/tables alongside old ones, syncing data via triggers, and cleaning up once all clients have switched over.

What pgroll Does

• Applies schema migrations without locking tables or blocking reads/writes
• Manages dual-version schemas so old and new application code can run simultaneously
• Auto-generates triggers that keep old and new columns in sync during transitions
• Tracks migration state in a dedicated schema within the target database
• Supports rollback by simply cancelling the incomplete migration

Architecture Overview

pgroll operates in two phases. During "start," it creates new schema objects (columns, tables, constraints) and installs row-level triggers that replicate writes between old and new shapes. During "complete," it drops the old objects and removes triggers. A version schema provides each application version its own search_path view.

Self-Hosting & Configuration

• Install via Go: go install github.com/xataio/pgroll@latest
• Define migrations in JSON files with a declarative operations DSL
• Point to your database via --postgres-url or PGROLL_PG_URL env var
• State is stored in a pgroll schema inside the target database
• Run in CI by chaining pgroll start followed by pgroll complete after deploy

Key Features

• True zero-downtime: no ACCESS EXCLUSIVE locks during migration
• Automatic trigger generation for column transforms and renames
• Versioned views let old and new code read the schema they expect
• JSON-based migration format is easy to review in pull requests
• Supports adding NOT NULL columns, changing types, and renaming without downtime

Comparison with Similar Tools

• Flyway — sequential SQL scripts but requires maintenance windows for breaking changes
• Atlas — declarative schema-as-code, supports expand/contract via versioned schemas
• golang-migrate — simple up/down SQL files, no zero-downtime strategy built in
• Sqitch — change-management focused, no automatic dual-schema support
• pg_squeeze — optimizes table bloat, not a general migration tool

FAQ

Q: Does pgroll work with managed PostgreSQL services like RDS or Cloud SQL? A: Yes. It only requires standard DDL and trigger permissions, no superuser access.

Q: Can I write migrations in SQL instead of JSON? A: pgroll uses a JSON DSL for its operations. Raw SQL can be embedded in a "sql" operation type for custom statements.

Q: What happens if I need to roll back? A: Run pgroll rollback before completing. It drops the new objects and removes triggers, restoring the original schema.

Q: How does it handle data backfills? A: Triggers sync new writes automatically. For existing rows, pgroll can run a backfill during the start phase in batches.

Sources

• https://github.com/xataio/pgroll
• https://pgroll.com/docs