Best PostgreSQL Migration Tools: Schema Changes Without Downtime

The most dangerous moment in a PostgreSQL database's life is not when it is under heavy load. It is when someone runs ALTER TABLE on a 500-million-row table during business hours. Schema migrations are where production incidents are born.

I have watched a 3-second ALTER TABLE ADD COLUMN take down an application for 45 minutes. Not because the DDL itself was slow, but because it queued behind a long-running analytics query, and every subsequent query on that table stacked up behind the waiting DDL. By the time anyone noticed, 2,000 connections were blocked and the connection pool was exhausted.

The tools you use for schema migrations determine whether your deployments are routine or terrifying. This guide covers the full landscape: migration frameworks that manage your SQL files, zero-downtime DDL tools that make ALTER TABLE safe, and data migration tools for moving between databases. More importantly, it covers the practices that keep you out of trouble regardless of which tool you choose.

Why Schema Migrations Are Hard in PostgreSQL

Before comparing tools, you need to understand the three forces that make PostgreSQL schema changes dangerous.

DDL Locks Are Aggressive

Most ALTER TABLE operations acquire an ACCESS EXCLUSIVE lock. This is the strongest lock level PostgreSQL offers. It blocks everything -- SELECT, INSERT, UPDATE, DELETE, even other DDL. While the lock is held, the table is completely unavailable.

Some common operations and their lock levels:

ACCESS EXCLUSIVE (blocks everything):
  ALTER TABLE ... ADD COLUMN (with DEFAULT before PG 11)
  ALTER TABLE ... DROP COLUMN
  ALTER TABLE ... ALTER COLUMN TYPE
  ALTER TABLE ... ADD CONSTRAINT (with validation)
  CREATE INDEX (non-concurrent)

SHARE UPDATE EXCLUSIVE (blocks writes):
  ALTER TABLE ... ADD COLUMN (nullable, no default, PG 11+)
  CREATE INDEX CONCURRENTLY

ACCESS SHARE (blocks nothing):
  SELECT

PostgreSQL 11 made ADD COLUMN ... DEFAULT much safer by storing the default in the catalog rather than rewriting the table. But many other operations still require ACCESS EXCLUSIVE.

The Lock Queue Problem

Here is the behavior that catches people off guard. When an ALTER TABLE waits for a lock, it enters a queue. Every new query that arrives after the DDL is also blocked -- even though those queries only need a lightweight lock that would normally be compatible.

Timeline:
  T=0:  Long analytics query holds ACCESS SHARE on orders table
  T=1:  ALTER TABLE orders ADD COLUMN ... waits for ACCESS EXCLUSIVE
  T=2:  SELECT * FROM orders -- blocked behind the ALTER TABLE
  T=3:  INSERT INTO orders -- blocked
  T=4:  SELECT * FROM orders -- blocked
  ...
  T=45: Connection pool exhausted, application down

This cascading failure is why a SET lock_timeout before any DDL is not optional. It is mandatory.

Transaction ID Wraparound During Long Migrations

Long-running DDL holds open a transaction, which prevents VACUUM from freezing old row versions. If your database is already approaching the transaction ID wraparound threshold (2 billion XIDs), a migration that takes hours can push you into emergency autovacuum territory -- or worse, a database shutdown.

This is not theoretical. I have seen a data backfill migration that ran for 6 hours block autovacuum on a table that was 200 million transactions from wraparound. The database froze itself to prevent data corruption.

Schema Migration Frameworks

These tools manage your migration files, track what has been applied, and ensure ordering. They do not make DDL safer by themselves, but they provide the structure that prevents "I thought someone already ran that ALTER TABLE" disasters.

Flyway

Flyway is the most widely adopted migration framework in the Java ecosystem, but it works with any language since migrations are plain SQL files.

How it works: Versioned SQL files in a directory, named V1__create_users.sql, V2__add_email_index.sql. Flyway tracks applied migrations in a flyway_schema_history table. Run flyway migrate and it applies anything new.

-- V3__add_orders_table.sql
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    order_total_amount_usd NUMERIC(12,2) NOT NULL,
    created_at_utc TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX CONCURRENTLY idx_orders_user_id
    ON orders(user_id);

# Apply migrations
flyway -url=jdbc:postgresql://localhost:5432/myapp migrate

# Check current status
flyway info

Strengths: Simple mental model (sequential numbering), wide adoption across teams, excellent CI/CD integration, supports callbacks for pre/post migration hooks.

Weaknesses: Requires a Java runtime (or the Docker image). Limited rollback support (Community edition has no undo migrations). No awareness of PostgreSQL lock behavior -- it will happily run a blocking DDL without lock_timeout.

Best for: Teams with Java infrastructure who want a battle-tested, no-surprises migration runner.

Liquibase

Liquibase takes a different approach: migrations are defined as abstract changesets that can be written in XML, YAML, JSON, or SQL.

# changelog.yaml
databaseChangeLog:
  - changeSet:
      id: 3
      author: deploy
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: last_login_at_utc
                  type: TIMESTAMPTZ
      rollback:
        - dropColumn:
            tableName: users
            columnName: last_login_at_utc

Strengths: Built-in rollback support (each changeset defines its undo). Database diff generation can scaffold migrations from model changes. Format flexibility lets teams use SQL for complex migrations and YAML for simple ones.

Weaknesses: XML-heavy legacy makes first impressions rough. Steeper learning curve than Flyway. The abstraction layer can obscure what actually runs against PostgreSQL, which matters when lock behavior is critical.

Best for: Enterprise teams that need rollback guarantees and multi-database support.

golang-migrate

A lightweight, compiled binary with zero runtime dependencies. No JVM, no interpreter, no framework.

# Install
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Create migration
migrate create -ext sql -dir ./migrations add_orders_table

# Apply all pending
migrate -database "postgresql://localhost:5432/myapp?sslmode=disable" \
        -path ./migrations up

# Roll back last migration
migrate -database "..." -path ./migrations down 1

Migration files come in pairs:

-- 000003_add_orders_table.up.sql
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    order_total_amount_usd NUMERIC(12,2) NOT NULL,
    created_at_utc TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 000003_add_orders_table.down.sql
DROP TABLE IF EXISTS orders;

Strengths: Tiny binary, no dependencies. First-class Docker support. Works from CI/CD pipelines without installing a runtime. Supports multiple database sources (PostgreSQL, MySQL, SQLite, etc.).

Weaknesses: No dependency graph (strictly sequential). No built-in diff generation. Bare-bones -- intentionally provides no guardrails around unsafe DDL.

Best for: Go projects, microservices, CI/CD pipelines where simplicity and small image size matter.

Sqitch

Sqitch breaks from sequential numbering entirely. Migrations are organized as a dependency graph defined in a plan file.

# sqitch.plan
users 2024-01-15T10:00:00Z deploy <>
orders [users] 2024-01-15T10:05:00Z deploy <>
order_items [orders] 2024-01-15T10:10:00Z deploy <>

Each change has three files: deploy/, revert/, and verify/.

-- deploy/orders.sql
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    order_total_amount_usd NUMERIC(12,2) NOT NULL,
    created_at_utc TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- verify/orders.sql
SELECT order_id, user_id, order_total_amount_usd, created_at_utc
FROM orders WHERE FALSE;

-- revert/orders.sql
DROP TABLE orders;

Strengths: Dependency-based ordering handles branching and parallel development without numbering conflicts. Rebase support for rewriting migration history. Verify scripts confirm each migration applied correctly.

Weaknesses: Perl dependency. Smaller community means fewer Stack Overflow answers. The dependency model is powerful but adds complexity.

Best for: Teams with complex branching workflows where sequential numbering causes constant conflicts.

Alembic

The Python ecosystem's answer, built on SQLAlchemy. Alembic can auto-generate migrations by comparing your SQLAlchemy models to the database schema.

# Initialize
alembic init migrations

# Auto-generate from model changes
alembic revision --autogenerate -m "add orders table"

# Apply
alembic upgrade head

Auto-generated migration:

def upgrade():
    op.create_table(
        'orders',
        sa.Column('order_id', sa.BigInteger(), primary_key=True),
        sa.Column('user_id', sa.BigInteger(), sa.ForeignKey('users.user_id'),
                  nullable=False),
        sa.Column('order_total_amount_usd', sa.Numeric(12, 2), nullable=False),
        sa.Column('created_at_utc', sa.DateTime(timezone=True),
                  server_default=sa.func.now(), nullable=False),
    )

def downgrade():
    op.drop_table('orders')

Strengths: Autogenerate saves significant time. Tight integration with SQLAlchemy/FastAPI/Flask. Branch-aware revision tree handles parallel development. You can write raw SQL within migrations when needed.

Weaknesses: Coupled to SQLAlchemy. Autogenerate does not catch everything (it misses function changes, partial indexes, and some constraint modifications). Python-only.

Best for: Python/FastAPI/Django projects already using SQLAlchemy.

dbmate

Similar philosophy to golang-migrate but with more PostgreSQL-aware features out of the box.

# Create migration
dbmate new add_orders_table

# Apply
dbmate --url "postgresql://localhost:5432/myapp" up

# Rollback
dbmate rollback

Strengths: Supports --wait for database readiness in containers. Schema dump for diffing. Simple, fast, database-agnostic. No runtime dependencies.

Weaknesses: Less community adoption than golang-migrate. Limited advanced features.

Best for: Teams wanting golang-migrate's simplicity with slightly more features.

Zero-Downtime DDL Tools

Migration frameworks manage files. These tools make the actual schema changes safe by avoiding ACCESS EXCLUSIVE locks on large tables.

pgroll

pgroll implements the expand/contract pattern for PostgreSQL schema evolution. Instead of modifying a table in place, it creates a new version of the schema and maintains both simultaneously.

{
  "name": "02_add_email_verified",
  "operations": [
    {
      "add_column": {
        "table": "users",
        "column": {
          "name": "is_email_verified",
          "type": "boolean",
          "default": "false",
          "nullable": false
        }
      }
    }
  ]
}

# Start migration (creates new schema version)
pgroll start 02_add_email_verified.json

# Both old and new schema versions are now live
# Old application code uses the old version
# New application code uses the new version

# Complete migration (drop old version)
pgroll complete

How it works: pgroll creates versioned views over the underlying table. During the migration window, both the old and new schema versions are accessible through different schema paths. This means you can deploy new application code that uses the new column while old application instances still see the old schema.

Strengths: True zero-downtime schema changes. Dual-version support means no coordination between application deployment and migration. Automatic backfill of new columns. Rollback by simply dropping the new version.

Weaknesses: Relatively new project (Xata). Adds complexity with the versioned schema approach. Requires application awareness of schema versions. Not suited for all DDL types.

Best for: Teams deploying continuously who cannot tolerate any downtime window, even a brief one.

pg_repack

pg_repack rebuilds a table online without holding ACCESS EXCLUSIVE for the duration. It is primarily used for removing bloat, but it also handles column reordering and table moves.

# Install
sudo apt-get install postgresql-17-repack

# Repack a bloated table (removes dead tuples, reclaims space)
pg_repack -d myapp -t orders

# Repack specific table with a new tablespace
pg_repack -d myapp -t orders --tablespace fast_ssd

# Repack all tables in the database
pg_repack -d myapp

How it works: Creates a shadow copy of the table, applies a trigger to capture ongoing changes, copies all data, replays the captured changes, then performs an atomic swap. The ACCESS EXCLUSIVE lock is only held for the brief swap at the end.

Strengths: Proven, widely used in production. Handles large tables (hundreds of GB) safely. Reduces bloat without VACUUM FULL's table-level lock.

Weaknesses: Requires installing the extension on the server. Temporarily doubles disk usage. Does not help with adding columns or changing types -- it is a repacking tool, not a general DDL tool.

Best for: Reclaiming space from bloated tables without downtime. Essential in any PostgreSQL DBA's toolkit.

pg Online Schema Change (pg_osc)

Inspired by MySQL's pt-online-schema-change, pg_osc brings the shadow-table approach to PostgreSQL for general schema changes.

# Add a column to a large table without locking
pg_osc perform \
  --dbname myapp \
  --table orders \
  --alter "ADD COLUMN shipped_at_utc TIMESTAMPTZ"

# Change a column type
pg_osc perform \
  --dbname myapp \
  --table orders \
  --alter "ALTER COLUMN order_total_amount_usd TYPE NUMERIC(15,2)"

How it works: Creates a new table with the desired schema, sets up triggers to replicate changes, copies data in batches, then atomically swaps the tables. Similar to pg_repack but designed for arbitrary ALTER TABLE operations.

Strengths: Handles any ALTER TABLE operation without long locks. Batched copying reduces impact on running workload. Atomic swap means the change is instant from the application's perspective.

Weaknesses: Trigger-based replication adds write overhead during the migration. Complex foreign key relationships require careful handling. Less mature than pg_repack.

Best for: Changing column types or adding constraints on large tables where the standard ALTER TABLE would lock for too long.

Data Migration Tools

These tools move data between databases -- from MySQL to PostgreSQL, from one PostgreSQL instance to another, or from flat files into PostgreSQL.

pgloader

pgloader migrates entire databases into PostgreSQL from MySQL, SQLite, MS SQL, CSV, and other sources. It handles type mapping, index recreation, and data transformation in a single command.

# Migrate from MySQL to PostgreSQL
pgloader mysql://root:pass@mysql-host/myapp \
         postgresql://postgres:pass@pg-host/myapp

For more control, use a configuration file:

-- migrate.load
LOAD DATABASE
    FROM mysql://root:pass@mysql-host/legacy_app
    INTO postgresql://postgres:pass@pg-host/new_app

WITH include drop, create tables, create indexes,
     reset sequences, workers = 8, concurrency = 2

SET maintenance_work_mem to '512MB',
    work_mem to '48MB'

CAST type datetime to timestamptz using zero-dates-to-null,
     type int with extra auto_increment to bigserial

ALTER SCHEMA 'legacy_app' RENAME TO 'public';

Strengths: Single tool handles the entire migration. Smart type mapping (MySQL TINYINT(1) to BOOLEAN, DATETIME to TIMESTAMPTZ). Parallel loading with configurable workers. Handles edge cases like MySQL's zero dates.

Weaknesses: The Lisp-like configuration syntax takes getting used to. Some complex MySQL features (stored procedures, triggers) require manual migration.

Best for: Anyone migrating from MySQL (or SQLite, or CSV files) to PostgreSQL. This is the standard tool for the job.

AWS Database Migration Service (DMS)

AWS DMS provides continuous replication between source and target databases, supporting both full load and ongoing change data capture (CDC).

Strengths: Managed service, no infrastructure to maintain. Supports continuous replication for minimal-downtime migrations. Handles dozens of source and target combinations.

Weaknesses: AWS-only. Can be expensive for large datasets. CDC has latency that varies by source type. Configuration through the AWS console is tedious.

Best for: Large-scale migrations into AWS RDS or Aurora where ongoing replication is needed during the transition period.

Foreign Data Wrappers (FDW)

PostgreSQL's built-in approach to querying external databases. No external tool needed.

-- Install the MySQL foreign data wrapper
CREATE EXTENSION mysql_fdw;

-- Create a connection to the source database
CREATE SERVER mysql_legacy
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host 'mysql-host', port '3306');

CREATE USER MAPPING FOR postgres
    SERVER mysql_legacy
    OPTIONS (username 'readonly', password 'secret');

-- Import the remote schema
IMPORT FOREIGN SCHEMA legacy_app
    FROM SERVER mysql_legacy
    INTO staging;

-- Migrate data using standard SQL
INSERT INTO orders (user_id, order_total_amount_usd, created_at_utc)
SELECT user_id, total_amount, created_at
FROM staging.orders
WHERE created_at >= '2024-01-01';

Strengths: No external tools. Full SQL power for transformations during migration. Can run incrementally. Supports postgres_fdw for PostgreSQL-to-PostgreSQL migrations with full pushdown.

Weaknesses: Performance is limited by network latency. Large initial loads are slower than pgloader. Requires the FDW extension for non-PostgreSQL sources.

Best for: Incremental migrations where you need SQL-level control over the data transformation, or PostgreSQL-to-PostgreSQL consolidation using postgres_fdw.

Safe Migration Practices

Regardless of which tools you choose, these practices prevent the most common migration disasters.

Always Set lock_timeout

Every DDL statement should have a lock_timeout. If the lock cannot be acquired quickly, it is better to fail and retry than to queue behind long-running queries and cascade into an outage.

-- In every migration file
SET lock_timeout = '5s';

ALTER TABLE orders ADD COLUMN shipped_at_utc TIMESTAMPTZ;

If the lock is not acquired within 5 seconds, the ALTER TABLE fails. Retry during a quieter period or after canceling the blocking query.

Use CREATE INDEX CONCURRENTLY

Standard CREATE INDEX acquires a SHARE lock that blocks writes for the entire build time. On a large table, that can be minutes or hours.

-- Bad: blocks all writes until the index is built
CREATE INDEX idx_orders_created_at ON orders(created_at_utc);

-- Good: builds in the background, no write blocking
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at_utc);

Important: CONCURRENTLY cannot run inside a transaction block. Most migration frameworks run each file in a transaction by default. You need to configure your framework to skip the transaction wrapper for files containing CONCURRENTLY.

Add Columns Safely: Nullable First, Backfill, Then Constrain

Instead of adding a NOT NULL column with a default in one step (which rewrites the table in pre-PG11), split it into three:

-- Step 1: Add nullable column (instant, minimal lock)
ALTER TABLE orders ADD COLUMN shipped_at_utc TIMESTAMPTZ;

-- Step 2: Backfill in batches (no lock)
UPDATE orders SET shipped_at_utc = created_at_utc + interval '3 days'
WHERE order_id BETWEEN 1 AND 100000;
-- Repeat for additional batches

-- Step 3: Add constraint (validates existing rows)
ALTER TABLE orders
    ADD CONSTRAINT orders_shipped_at_not_null
    CHECK (shipped_at_utc IS NOT NULL) NOT VALID;

-- Step 4: Validate separately (only needs SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_shipped_at_not_null;

The NOT VALID / VALIDATE CONSTRAINT split is critical. Adding the constraint as NOT VALID is instant. VALIDATE checks existing rows but only holds a weaker lock that does not block reads or writes.

Test Against a Production Copy

Never run a migration against production for the first time. Use a branch or snapshot of your production database to test timing, lock behavior, and disk usage.

Neon's database branching is excellent for this -- you can create a branch of your production database in seconds and test the migration against real data without affecting the live system.

Monitor During Migrations

Even with lock_timeout and safe practices, you need visibility into what is happening during a migration. Are queries queuing? Is lock contention building? Are connections piling up?

Tools like myDBA.dev show lock contention, active queries, and connection status in real time, so you can see the impact of a migration as it runs. If a migration is causing unexpected blocking, you want to know immediately -- not 10 minutes later when the connection pool is exhausted.

Comparison Table

Tool	Type	Language	Zero-Downtime	Rollback	PG-Specific	Learning Curve
Flyway	Framework	Java	No	Paid only	No	Low
Liquibase	Framework	Java	No	Yes	No	Medium
golang-migrate	Framework	Go	No	Yes	No	Low
Sqitch	Framework	Perl	No	Yes	No	Medium
Alembic	Framework	Python	No	Yes	No	Medium
dbmate	Framework	Go	No	Yes	No	Low
pgroll	DDL	Go	Yes	Yes	Yes	Medium
pg_repack	DDL	C	Yes	No	Yes	Low
pg_osc	DDL	Ruby	Yes	No	Yes	Medium
pgloader	Data	Lisp	N/A	No	Yes	Medium
AWS DMS	Data	Managed	Yes (CDC)	No	No	High
FDW	Data	SQL	N/A	No	Yes	Low

My Recommendations

There is no single best tool. The right choice depends on your stack, team size, and what keeps you up at night.

Small team, Go or polyglot stack: golang-migrate or dbmate. No dependencies, fast, works in any CI/CD pipeline. Add SET lock_timeout to every migration file manually.

Python/FastAPI project: Alembic. Autogenerate from your SQLAlchemy models saves time and reduces drift between code and schema. Review every generated migration before applying.

Enterprise or Java ecosystem: Flyway (if you want simplicity) or Liquibase (if you need rollback guarantees and multi-database support). Both are mature and well-supported.

Zero-downtime DDL on large tables: pgroll for the expand/contract pattern, or pg_osc for one-off large table changes. Combine with your existing migration framework -- use Flyway or Alembic to manage the migration files, and pgroll or pg_osc for the actual DDL execution when tables are too large for standard ALTER TABLE.

Migrating to PostgreSQL from MySQL: pgloader. Nothing else comes close for automated type mapping and parallel loading. Use FDW if you need ongoing incremental synchronization.

Removing table bloat in production: pg_repack. No substitute for this one.

The common thread across all these recommendations: no tool eliminates the need to understand PostgreSQL's locking behavior. Set lock_timeout. Use CONCURRENTLY. Split large changes into small, safe steps. Monitor while migrations run. The tool manages the files and execution; the safety practices are on you.