DEV Community: Mickel Samuel

The 5 PostgreSQL Migration Mistakes That Cause Production Outages

Mickel Samuel — Mon, 02 Mar 2026 14:05:42 +0000

Most PostgreSQL migration outages are caused by the same five patterns. Each one involves a lock that blocks queries longer than expected, a table rewrite that no one anticipated, or a cascade effect that turns a simple schema change into minutes of downtime. Here are the five mistakes, the production incidents they cause, and the safe patterns to avoid them.

Mistake #1: CREATE INDEX Without CONCURRENTLY

The dangerous migration

-- This looks harmless
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

What happens in production

A regular CREATE INDEX acquires a SHARE lock on the table. This lock blocks all INSERT, UPDATE, and DELETE operations for the entire duration of the index build.

On a table with 1 million rows, an index build might take 5-30 seconds. On a table with 100 million rows, it can take minutes. During that entire time, every write to the table is blocked. Every API request that tries to insert or update a row in that table will queue up, waiting.

The cascading effect is what makes this dangerous: as write queries queue up, they consume connection pool slots. When the connection pool is exhausted, new requests start failing. What started as an index build on one table becomes a total service outage.

The safe pattern

-- Add CONCURRENTLY — builds the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CREATE INDEX CONCURRENTLY acquires only a SHARE UPDATE EXCLUSIVE lock , which allows reads and writes to continue while the index is being built. It takes longer (two table scans instead of one), but your application stays up.

Important caveat: CONCURRENTLY cannot run inside a transaction. If your migration framework wraps migrations in transactions by default (Flyway, Liquibase, Knex), you need to disable the transaction for this specific migration.

-- Flyway: disable transaction for this migration
-- flyway:executeInTransaction=false
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

-- If the concurrent build fails, clean up the invalid index:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id;
-- Then retry the CREATE INDEX CONCURRENTLY

Mistake #2: ALTER TABLE SET NOT NULL Without the CHECK Pattern

The dangerous migration

-- Adding NOT NULL to an existing column
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

What happens in production

SET NOT NULL acquires an ACCESS EXCLUSIVE lock — the most restrictive lock in PostgreSQL. This lock blocks everything : reads, writes, even otherSELECT queries.

While holding this lock, PostgreSQL scans the entire table to verify that no existing rows have NULL values in the column. On a 50-million-row table, this scan can take minutes. During those minutes, nothing else can touch the table. Not even reads.

"Nothing quite like the surprise you get the first time you rewrite every tuple on a table with 20M rows because you added a column with a default value." — Hacker News

The safe pattern (PostgreSQL 12+)

-- Step 1: Add a CHECK constraint with NOT VALID
-- This only checks new/modified rows, no full table scan
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint in a separate transaction
-- This scans the table but only holds a SHARE UPDATE EXCLUSIVE lock
-- (allows reads AND writes to continue)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: Now set NOT NULL — PostgreSQL sees the valid CHECK constraint
-- and skips the full table scan entirely
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the CHECK constraint (redundant now)
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

This four-step pattern is the standard safe approach. The key insight: once PostgreSQL sees a validated CHECK constraint that guarantees no NULLs exist, the SET NOT NULL becomes a metadata-only operation that completes instantly.

PostgreSQL 18 update: As of PostgreSQL 18 (September 2025), you can use ALTER TABLE ... ADD COLUMN ... NOT NULL NOT VALID directly. This simplifies the pattern for new columns, though the CHECK pattern is still needed for existing columns.

Mistake #3: ALTER COLUMN TYPE (Silent Table Rewrite)

The dangerous migration

-- Changing a column type
ALTER TABLE events ALTER COLUMN payload TYPE jsonb USING payload::jsonb;

-- Or seemingly innocent changes like:
ALTER TABLE users ALTER COLUMN name TYPE varchar(500);  -- was varchar(255)

What happens in production

Most column type changes trigger a full table rewrite. PostgreSQL creates a new copy of the table with the new column type, copies every row, then swaps the tables. The entire operation runs under an ACCESS EXCLUSIVE lock.

On a 10GB table, a rewrite can take 10-30 minutes. During that time, no reads or writes are possible. Your application is completely blocked on that table. If the table is central to your application (users, orders, events), this is a full outage.

What makes this especially dangerous is that many type changes look safe. Changingvarchar(255) tovarchar(500) seems like it should be a metadata change, but PostgreSQL rewrites the entire table.

Safe alternatives

There is no universal safe pattern for column type changes. The approach depends on the specific change:

-- varchar(255) → text: SAFE, no rewrite (just removes the length limit)
ALTER TABLE users ALTER COLUMN name TYPE text;

-- varchar(255) → varchar(500): REWRITES the table
-- Safe alternative: just use text instead
ALTER TABLE users ALTER COLUMN name TYPE text;

-- int → bigint: REWRITES the table
-- Safe alternative: expand-contract migration
-- 1. Add new column
ALTER TABLE events ADD COLUMN id_new bigint;
-- 2. Backfill in batches (application-level)
-- 3. Swap columns
-- 4. Drop old column

The general principle: if the new type requires more storage per row or a different binary representation, PostgreSQL rewrites the table. The safe alternative is usually an expand-contract migration: add a new column, backfill, swap, drop.

Mistake #4: Running Migrations Without lock_timeout

The dangerous migration

-- No lock_timeout set — will wait indefinitely for the lock
ALTER TABLE orders ADD COLUMN discount_pct numeric(5,2);

What happens in production

Even a fast DDL operation like adding a nullable column needs to acquire a lock on the table. If there is a long-running query or transaction holding a conflicting lock on that table, your DDL statement will wait indefinitely for the lock.

While your DDL statement is waiting, it goes into the lock queue. Here's the critical part:every subsequent query on that table also gets queued behind your waiting DDL. Even simple SELECT queries. The lock queue is FIFO, and your DDL operation is blocking everything behind it.

This is the "lock cascade" pattern. One long-running analytics query + one migration without lock_timeout = total table lockout. Your migration is waiting for the analytics query, and every application query is waiting for your migration.

The safe pattern

-- Always set lock_timeout before DDL operations
SET lock_timeout = '5s';

-- Now if the lock can't be acquired within 5 seconds,
-- the statement fails instead of waiting indefinitely
ALTER TABLE orders ADD COLUMN discount_pct numeric(5,2);

-- Reset for safety
RESET lock_timeout;

With lock_timeout set, a failed lock acquisition gives you an error you can handle: retry after a short delay, kill the blocking query, or run the migration during a lower-traffic window. Without it, your only option is to manually kill the migration session while your application is down.

A good deployment wrapper retries the migration 3-5 times with a short delay between attempts. If a 5-second lock_timeout fails 5 times in a row (25 seconds of retrying), something is genuinely blocking the table and you need to investigate — not wait forever.

-- Retry wrapper pattern (pseudocode)
for attempt in 1..5:
  BEGIN;
    SET LOCAL lock_timeout = '5s';
    ALTER TABLE orders ADD COLUMN discount_pct numeric(5,2);
  COMMIT;
  -- Success, exit loop

  -- On lock_timeout error:
  ROLLBACK;
  sleep(2 * attempt);  -- Exponential backoff

-- If all 5 attempts fail, alert and investigate

Mistake #5: Adding a Foreign Key Without NOT VALID

The dangerous migration

-- Adding a foreign key constraint
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id);

What happens in production

Adding a foreign key constraint acquires locks on both tables — the referencing table (orders) and the referenced table (customers). PostgreSQL needs to scan the entire referencing table to verify that every value in the foreign key column exists in the referenced table.

GoCardless documented a 15-second API outage caused by exactly this pattern: a foreign key constraint that locked both tables simultaneously, blocking all queries on both.

The lock on the referenced table (customers) is what most developers don't expect. You think you are modifying the orders table, but you are actually locking customers too. If customers is your most-queried table, this cascades quickly.

The safe pattern

-- Step 1: Add the constraint with NOT VALID
-- This only checks new/modified rows, no full table scan
-- Still locks both tables briefly, but completes instantly
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

-- Step 2: Validate the constraint in a separate transaction
-- This scans the table but holds a weaker lock (SHARE UPDATE EXCLUSIVE)
-- Reads and writes continue normally on both tables
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

The NOT VALID + VALIDATE pattern splits the operation into two phases: a fast metadata change (milliseconds, locks both tables briefly) and a slow validation scan (seconds to minutes, but only holds a SHARE UPDATE EXCLUSIVE lock that allows reads and writes).

Common Thread: The Lock Queue Problem

All five mistakes share the same underlying mechanism: they acquire a lock that's too strong for too long. The PostgreSQL lock queue makes this worse than you'd expect:

Your DDL statement requests an ACCESS EXCLUSIVE lock
A long-running query is holding a conflicting lock — your DDL waits
While your DDL waits, all new queries on the table queue behind your DDL
The lock queue grows: 10 queries, 50 queries, 200 queries
Connection pool fills up with blocked queries
New requests can't get a connection — service returns 5xx errors

The total outage duration is not the time your DDL takes. It's the time the blocking query was already running + the time your DDL takes + the time to drain the queued requests. A 3-second DDL operation behind a 60-second analytics query causes a 63+ second outage.

Preventing These Mistakes

The safe patterns above are well-documented but rarely enforced. A developer who writes migrations twice a month is unlikely to remember the NOT VALID + VALIDATE pattern or the lock_timeout requirement every time.

The most effective prevention is automated linting in CI. When a migration is opened in a pull request, a linter can catch all five patterns before they reach production:

# Check all migration files for safety issues
npx migrationpilot check migrations/*.sql --fail-on critical

# Example output:
# migrations/V42__add_index.sql
#   MP001 [critical] CREATE INDEX without CONCURRENTLY
#     Acquires: SHARE lock (blocks writes)
#     Safe alternative:
#       CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
#
#   MP004 [warning] Missing lock_timeout
#     Safe alternative:
#       SET lock_timeout = '5s';

Free tools that catch these patterns:

Squawk — 32 rules, Rust, GitHub Action
strong_migrations — Rails only, excellent error messages
MigrationPilot — 80 rules, lock classification, auto-fix, GitHub Action

The specific tool matters less than having any automated check. These five mistakes are repetitive patterns that a linter catches in milliseconds. The production incidents they cause take hours to diagnose and recover from.

Quick Reference

Mistake	Lock Acquired	Safe Pattern
CREATE INDEX (no CONCURRENTLY)	SHARE	CREATE INDEX CONCURRENTLY
SET NOT NULL (direct)	ACCESS EXCLUSIVE	CHECK NOT VALID + VALIDATE
ALTER COLUMN TYPE	ACCESS EXCLUSIVE	Expand-contract migration
No lock_timeout	Any (queues indefinitely)	SET lock_timeout = '5s'
FK without NOT VALID	SHARE ROW EXCLUSIVE (both tables)	NOT VALID + VALIDATE

PostgreSQL Migration Best Practices for Zero-Downtime Deployments

Mickel Samuel — Mon, 02 Mar 2026 14:05:38 +0000

Zero-downtime schema migrations are not about a single trick. They require a combination of techniques: lock management, phased rollouts, backfill strategies, and CI enforcement. This guide covers the full playbook.

1. Always Set lock_timeout

The single most important practice for safe migrations. Every DDL statement that acquires a table lock should be preceded by a lock_timeout:

-- Set a 5-second lock timeout
-- If the lock cannot be acquired in 5s, the statement fails
-- This prevents the lock queue problem (see below)
SET lock_timeout = '5s';

ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- Reset after DDL
RESET lock_timeout;

Without lock_timeout, a DDL statement will wait indefinitely for a lock. While it waits, every subsequent query that needs a conflicting lock queues behind it. A single long-running analytics query can trigger a cascading outage:

Long query holds ACCESS SHARE on the table
ALTER TABLE waits for ACCESS EXCLUSIVE lock
All new queries queue behind the ALTER TABLE
Connection pool exhausts within seconds
Application-wide outage

With lock_timeout, the ALTER fails fast. Your deployment script can retry after a delay, and the application continues serving traffic in the meantime.

The retry wrapper pattern

-- Retry wrapper: try up to 5 times with increasing delays
DO $$
DECLARE
  retries INT := 0;
  max_retries INT := 5;
BEGIN
  LOOP
    BEGIN
      SET lock_timeout = '5s';
      ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
      RESET lock_timeout;
      RETURN;  -- Success
    EXCEPTION WHEN lock_not_available THEN
      retries := retries + 1;
      IF retries >= max_retries THEN
        RAISE EXCEPTION 'Failed to acquire lock after % attempts', max_retries;
      END IF;
      PERFORM pg_sleep(retries * 2);  -- Backoff: 2s, 4s, 6s, 8s, 10s
    END;
  END LOOP;
END $$;

2. Use statement_timeout for Long-Running DDL

While lock_timeout limits how long you wait to acquire a lock, statement_timeout limits how long the statement itself can run. Set both:

-- Prevent DDL from running longer than expected
SET lock_timeout = '5s';
SET statement_timeout = '30s';

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

RESET lock_timeout;
RESET statement_timeout;

3. The Expand-Contract Pattern

The expand-contract pattern (also called parallel change) is the safest way to make backwards-incompatible schema changes. It splits a dangerous change into three deployments:

Example: Renaming a column

Directly renaming a column breaks all existing queries and application code that references the old name. The expand-contract approach:

-- DEPLOY 1: Expand — add the new column alongside the old one
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Create a trigger to keep both columns in sync
CREATE OR REPLACE FUNCTION sync_name_columns()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    IF NEW.full_name IS NULL AND NEW.name IS NOT NULL THEN
      NEW.full_name := NEW.name;
    ELSIF NEW.name IS NULL AND NEW.full_name IS NOT NULL THEN
      NEW.name := NEW.full_name;
    END IF;
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_names
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_name_columns();

-- Backfill existing rows
UPDATE users SET full_name = name WHERE full_name IS NULL;


-- DEPLOY 2: Migrate — update application to use "full_name"
-- All reads and writes now use the new column name
-- Deploy this code change and verify in production


-- DEPLOY 3: Contract — remove the old column and trigger
DROP TRIGGER trg_sync_names ON users;
DROP FUNCTION sync_name_columns();
ALTER TABLE users DROP COLUMN name;

Example: Changing a column type

-- Changing INT to BIGINT directly acquires ACCESS EXCLUSIVE
-- and rewrites the entire table. Instead, use expand-contract:

-- DEPLOY 1: Add new column
ALTER TABLE orders ADD COLUMN amount_v2 BIGINT;

-- Sync trigger + backfill (same pattern as above)

-- DEPLOY 2: Switch reads/writes to amount_v2

-- DEPLOY 3: Drop old column
ALTER TABLE orders DROP COLUMN amount;
-- Optionally: ALTER TABLE orders RENAME COLUMN amount_v2 TO amount;

4. Batched Backfills

Never run a single UPDATE against millions of rows. A single large UPDATE:

Holds ROW EXCLUSIVE locks for the entire duration
Generates massive WAL (write-ahead log) volume
Creates millions of dead tuples that autovacuum must clean up
Can cause replication lag on replicas
Might fill up your disk with WAL and dead tuples

-- BAD: Single update that touches all 50M rows
UPDATE users SET status = 'active' WHERE status IS NULL;

-- GOOD: Batched update with pauses for autovacuum
DO $$
DECLARE
rows_updated INT;
total_updated INT := 0;
batch_size INT := 10000;
BEGIN
LOOP
UPDATE users
SET status = 'active'
WHERE id IN (
SELECT id FROM users
WHERE status IS NULL
ORDER BY id
LIMIT batch_size
);
```
GET DIAGNOSTICS rows_updated = ROW_COUNT;
total_updated := total_updated + rows_updated;

RAISE NOTICE 'Updated % rows (% total)', rows_updated, total_updated;

EXIT WHEN rows_updated = 0;

-- Give autovacuum time to clean up dead tuples
PERFORM pg_sleep(0.5);

-- Commit each batch independently (PG 11+ procedures)
COMMIT;
```
END LOOP;
END $$;

5. Safe Constraint Addition

NOT NULL constraints

Adding NOT NULL directly acquires ACCESS EXCLUSIVE and scans the entire table. Use the CHECK constraint pattern instead:

-- BAD: Scans entire table under ACCESS EXCLUSIVE
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- GOOD: Two-step pattern
-- Step 1: Add CHECK constraint as NOT VALID (instant, no scan)
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint (scans table but only holds
-- SHARE UPDATE EXCLUSIVE — does not block reads or writes)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- PG 12+: Once a valid CHECK (col IS NOT NULL) exists,
-- you can SET NOT NULL without a table scan:
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT chk_email_not_null;

Foreign key constraints

-- BAD: Validates all existing rows under SHARE ROW EXCLUSIVE
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);

-- GOOD: Add NOT VALID, then validate separately
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Unique constraints

-- BAD: Creates an index + validates under ACCESS EXCLUSIVE
ALTER TABLE users ADD CONSTRAINT uniq_email UNIQUE (email);

-- GOOD: Build the index concurrently, then attach it
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users (email);
ALTER TABLE users ADD CONSTRAINT uniq_email UNIQUE USING INDEX idx_users_email;

6. One DDL Per Transaction

Multiple DDL statements in a single transaction compound lock duration. Each statement acquires its own locks, and all locks are held until COMMIT:

-- BAD: Three DDL statements in one transaction
-- ACCESS EXCLUSIVE held for the entire combined duration
BEGIN;
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN address TEXT;
ALTER TABLE users ADD COLUMN city TEXT;
COMMIT;

-- GOOD: Separate transactions (or separate migration files)
ALTER TABLE users ADD COLUMN phone TEXT;
-- locks released
ALTER TABLE users ADD COLUMN address TEXT;
-- locks released
ALTER TABLE users ADD COLUMN city TEXT;
-- locks released

7. Test Migrations Against Production Data

Migrations that work on a dev database with 100 rows can cause outages on a production database with 100 million rows. The locking behavior is the same, but the duration is completely different.

Best practices for migration testing:

Maintain a staging database with production-sized data (or at least production-like volumes)
Time your migrations on staging and set alerts for migrations that take longer than expected
Use EXPLAIN on any data migration queries to check for sequential scans
Monitor pg_stat_activity and pg_locks during staging runs
Run static analysis on migration files in CI to catch common issues early

8. Deployment Ordering

The order of application deployment vs migration execution matters:

Change Type	Deploy Order	Why
Add column	Migration first, then app code	Old code ignores new columns
Remove column	App code first, then migration	Old code still references the column
Rename column	Expand-contract (3 deploys)	Both old and new code need to work
Add NOT NULL	App code first (ensure no NULLs), then constraint	Constraint validation fails if NULLs exist
Add index	Migration (CONCURRENTLY) anytime	Indexes are transparent to app code

9. Monitor During Migrations

Always monitor these metrics during migration execution:

-- Active locks on the table being migrated
SELECT mode, granted, count(*)
FROM pg_locks
WHERE relation = 'users'::regclass
GROUP BY mode, granted;

-- Queries waiting for locks
SELECT pid, query, wait_event_type, wait_event,
       age(now(), query_start) AS duration
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

-- Replication lag (if using replicas)
SELECT client_addr, state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_behind,
       replay_lag
FROM pg_stat_replication;

10. Enforce Best Practices in CI

The best migration practices are worthless if they are not enforced. Add automated checks to your CI pipeline that catch common mistakes before they reach production:

Missing CONCURRENTLY on index operations
Missing lock_timeout before DDL
Volatile defaults that cause table rewrites
Direct NOT NULL additions without the CHECK pattern
Multiple DDL in a single transaction
Unbatched backfills

MigrationPilot checks all of these patterns (and 80 more) as a CLI, GitHub Action, or Node.js library. It runs in under a second, requires no database connection for static analysis, and posts results as PR comments so your team catches issues during code review.

# Add to .github/workflows/migration-check.yml
- uses: mickelsamuel/migrationpilot@v1
  with:
    migration-path: "migrations/*.sql"
    fail-on: critical

The Complete Guide to PostgreSQL Lock Types for Schema Changes

Mickel Samuel — Mon, 02 Mar 2026 14:05:34 +0000

If you run DDL against a production PostgreSQL database without understanding locks, you will cause an outage. That is not a maybe. This guide covers every lock level, which statements acquire which locks, and what that means for your running application.

PostgreSQL Lock Levels: The Full Hierarchy

PostgreSQL has eight table-level lock modes, ordered from least restrictive to most restrictive. Two locks conflict when one would violate the guarantees of the other. Understanding this hierarchy is the foundation of safe schema changes.

Lock Level	Blocks Reads?	Blocks Writes?	Acquired By
ACCESS SHARE	No	No	SELECT
ROW SHARE	No	No	SELECT FOR UPDATE/SHARE
ROW EXCLUSIVE	No	No	INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE	No	No	VACUUM, CREATE INDEX CONCURRENTLY, ALTER TABLE (some)
SHARE	No	Yes	CREATE INDEX (without CONCURRENTLY)
SHARE ROW EXCLUSIVE	No	Yes	CREATE TRIGGER, some ALTER TABLE
EXCLUSIVE	No	Yes	REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE	Yes	Yes	DROP, TRUNCATE, most ALTER TABLE, VACUUM FULL, CLUSTER

The key insight: ACCESS EXCLUSIVE is the only lock that blocks reads. Everything else allows SELECT queries to continue. But anything from SHARE upward blocks writes (INSERT, UPDATE, DELETE), which is still devastating for a production application.

ACCESS SHARE: The Harmless Lock

Every SELECT statement acquires an ACCESS SHARE lock. It only conflicts with ACCESS EXCLUSIVE. This is why you can run queries while most DDL is happening, but a DROP TABLE will block all your reads.

ACCESS SHARE is automatically released at the end of the statement (or transaction if inside a transaction block). You never need to worry about this lock in practice.

SHARE UPDATE EXCLUSIVE: The Safe DDL Lock

This lock is the sweet spot for online operations. It self-conflicts (you cannot run two concurrent operations that both need it) but does not block reads or writes. Operations that acquire this lock include:

VACUUM (without FULL)
CREATE INDEX CONCURRENTLY
ALTER TABLE VALIDATE CONSTRAINT
ALTER TABLE SET STATISTICS
REINDEX CONCURRENTLY (PG 12+)

This is why CREATE INDEX CONCURRENTLY is the gold standard for adding indexes to production tables. It takes longer, but your application keeps running without any interruption.

SHARE: The Write-Blocking Lock

A regular CREATE INDEX (without CONCURRENTLY) acquires a SHARE lock. This blocks all INSERT, UPDATE, and DELETE operations on the table for the entire duration of the index build.

-- This blocks all writes for the entire index build duration
-- On a 100M row table, this could be 10+ minutes
CREATE INDEX idx_users_email ON users (email);

-- This is the safe alternative — only blocks other DDL
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

On a small table (under 100K rows), the difference is negligible. On a 50M row table, a regular CREATE INDEX might take several minutes, and every write to that table queues up behind it. Connection pools fill up, application requests time out, and your users see errors.

ACCESS EXCLUSIVE: The Total Lockout

ACCESS EXCLUSIVE is the most dangerous lock in PostgreSQL. It blocks everything: reads, writes, and all other lock types. Nothing can touch the table until the lock is released.

These operations acquire ACCESS EXCLUSIVE:

DROP TABLE
TRUNCATE
ALTER TABLE ADD COLUMN ... DEFAULT (volatile)
ALTER TABLE ALTER COLUMN TYPE
ALTER TABLE SET NOT NULL (without CHECK pattern)
ALTER TABLE ADD CONSTRAINT ... UNIQUE (without USING INDEX)
VACUUM FULL
CLUSTER
LOCK TABLE

The Lock Queue Problem

Here is the part that surprises most engineers: PostgreSQL lock acquisition is a FIFO queue. If your DDL statement is waiting for an ACCESS EXCLUSIVE lock, every subsequent query that needs a conflicting lock queues up behind it.

Consider this scenario:

A long-running analytics query holds ACCESS SHARE on the users table
Your migration runs ALTER TABLE users SET NOT NULL ... which needs ACCESS EXCLUSIVE
The ALTER waits behind the analytics query
Every new SELECT on users queues behind the ALTER
Your connection pool fills up within seconds
Application outage

This is why SET lock_timeout is essential. If you set a lock timeout of 5 seconds, the ALTER will fail fast instead of queuing indefinitely:

-- Always set a lock_timeout before DDL in production
SET lock_timeout = '5s';
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- If the lock cannot be acquired within 5 seconds,
-- PostgreSQL raises: ERROR: canceling statement due to lock timeout

Lock Conflicts: Which Locks Block Which

The lock conflict matrix is not obvious. Two locks conflict only when one would violate the guarantees the other provides. Here are the most important conflicts to remember:

ACCESS EXCLUSIVE conflicts with everything, including ACCESS SHARE (SELECT)
SHARE conflicts with ROW EXCLUSIVE (INSERT/UPDATE/DELETE), so CREATE INDEX blocks writes
SHARE UPDATE EXCLUSIVE only conflicts with itself, SHARE, and the EXCLUSIVEs
ROW EXCLUSIVE does not conflict with itself (concurrent writes are fine) but conflicts with SHARE and above

Common DDL Statements and Their Lock Types

Here is a practical reference for the DDL you are most likely to run in migrations:

-- ACCESS EXCLUSIVE (blocks everything)
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';  -- PG < 11
ALTER TABLE users ALTER COLUMN age TYPE bigint;
ALTER TABLE users ADD CONSTRAINT uniq UNIQUE (email);
DROP TABLE old_users;
TRUNCATE users;
VACUUM FULL users;
CLUSTER users USING idx_id;

-- SHARE (blocks writes, allows reads)
CREATE INDEX idx_email ON users (email);

-- SHARE UPDATE EXCLUSIVE (blocks nothing important)
CREATE INDEX CONCURRENTLY idx_email ON users (email);
ALTER TABLE users VALIDATE CONSTRAINT chk_email;
VACUUM users;

-- SHARE ROW EXCLUSIVE (blocks writes)
ALTER TABLE users ADD CONSTRAINT fk_org
  FOREIGN KEY (org_id) REFERENCES orgs(id) NOT VALID;

-- ROW EXCLUSIVE (normal DML — no DDL concern)
INSERT INTO users ...
UPDATE users SET ...
DELETE FROM users ...

Practical Guidelines

1. Always set lock_timeout

Before any DDL in production, set a lock_timeout. Five seconds is a good default. If the lock cannot be acquired in that time, it is better to fail and retry than to queue up all traffic.

2. Use CONCURRENTLY whenever possible

CREATE INDEX, DROP INDEX, REINDEX, and REFRESH MATERIALIZED VIEW all have CONCURRENTLY variants. They take longer but do not block reads or writes. Use them in production without exception.

3. Avoid ACCESS EXCLUSIVE unless necessary

Most ACCESS EXCLUSIVE operations have safer alternatives. Adding NOT NULL can use the CHECK pattern. Adding a UNIQUE constraint can use CREATE UNIQUE INDEX CONCURRENTLY + ADD CONSTRAINT USING INDEX. Column type changes can use expand-contract with a new column.

4. Keep transactions short

Locks are held until the end of the transaction. If you wrap multiple DDL statements in a single transaction, you hold the most restrictive lock for the entire duration. Split large migrations into separate transactions when possible.

How to Check Current Locks

You can inspect active locks with this query:

SELECT
  l.locktype,
  l.relation::regclass AS table_name,
  l.mode,
  l.granted,
  l.pid,
  a.query,
  a.state,
  age(now(), a.query_start) AS duration
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
  AND a.pid <> pg_backend_pid()
ORDER BY a.query_start;

And to see blocked queries:

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  age(now(), blocked.query_start) AS waiting_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid AND NOT bl.granted
JOIN pg_locks l ON bl.relation = l.relation
  AND bl.locktype = l.locktype AND l.granted
JOIN pg_stat_activity blocking ON l.pid = blocking.pid
WHERE blocked.pid <> blocking.pid;

Automate Lock Analysis

Memorizing lock types is error-prone. Tools can catch lock issues before they reach production. MigrationPilot analyzes your migration SQL and reports the exact lock type each statement will acquire, flags dangerous patterns like missing CONCURRENTLY or lock_timeout, and suggests safe alternatives. It runs as a CLI, GitHub Action, or Node.js library with 80 safety rules.

npx migrationpilot analyze migrations/002_add_index.sql

CREATE INDEX CONCURRENTLY: The Complete PostgreSQL Guide

Mickel Samuel — Mon, 02 Mar 2026 14:05:30 +0000

CREATE INDEX CONCURRENTLY is the single most important command for safely adding indexes to production PostgreSQL tables. This guide covers how it works, why it sometimes fails, how to handle failures, and the related REINDEX CONCURRENTLY command.

Why Regular CREATE INDEX Is Dangerous

A regular CREATE INDEX acquires a SHARE lock on the table. This lock blocks all INSERT, UPDATE, and DELETE operations for the entire duration of the index build. On a large table, that can mean minutes or even hours of write downtime.

-- DANGEROUS: Blocks all writes for the entire build duration
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- On a 50M row table, this might take 3-5 minutes
-- Every INSERT, UPDATE, DELETE queues behind this lock
-- Connection pools fill up, application errors cascade

How CREATE INDEX CONCURRENTLY Works

CREATE INDEX CONCURRENTLY builds the index without holding a lock that blocks writes. Instead of scanning the table once under a SHARE lock, it performs the build in three phases:

Phase 1 — Catalog entry: PostgreSQL creates the index entry in the system catalog with an "invalid" flag. This requires a brief SHARE UPDATE EXCLUSIVE lock (does not block reads or writes). The index is not yet usable by the query planner.
Phase 2 — First table scan: PostgreSQL scans the entire table and builds the index entries. This happens under a SHARE UPDATE EXCLUSIVE lock, which allows concurrent INSERT, UPDATE, and DELETE operations to continue. Changes that happen during the scan are tracked.
Phase 3 — Second table scan: PostgreSQL does a second pass to catch any rows that were modified during Phase 2. Once this is complete, the index is marked as valid and becomes usable by the query planner. Another brief SHARE UPDATE EXCLUSIVE lock is acquired to wait for any transactions that started before Phase 2 to complete.

-- SAFE: Does not block reads or writes
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

-- Takes longer than regular CREATE INDEX (roughly 2-3x)
-- but your application continues running normally

The Transaction Restriction

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. This is a hard PostgreSQL limitation. If you try, you get an error:

-- This fails immediately
BEGIN;
CREATE INDEX CONCURRENTLY idx_email ON users (email);
COMMIT;
-- ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

-- The solution: run it outside a transaction
CREATE INDEX CONCURRENTLY idx_email ON users (email);

This matters because many migration frameworks wrap each migration file in a transaction by default. If your framework does this (Flyway, Alembic, Django, Rails), you need to configure it to run specific migrations outside a transaction. Check your framework's documentation for how to do this.

Framework	How to Disable Transaction
Django	atomic = False on the Migration class
Rails	disable_ddl_transaction!
Flyway	executeInTransaction=false (in SQL comment)
Alembic	op.execute() with connection.execution_options(isolation_level="AUTOCOMMIT")
Knex	knex.schema.raw() outside transaction

Failure Modes and Recovery

CREATE INDEX CONCURRENTLY can fail partway through. Unlike a regular CREATE INDEX (which rolls back cleanly on failure), a failed CONCURRENTLY operation leaves behind an invalid index.

Common failure causes:

Deadlock with another transaction
Unique constraint violation (for UNIQUE indexes)
Out of disk space
statement_timeout or lock_timeout reached
A long-running transaction that prevents Phase 3 from completing

Detecting invalid indexes

-- Find all invalid indexes in the database
SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE indexname IN (
  SELECT indexrelid::regclass::text
  FROM pg_index
  WHERE NOT indisvalid
);

Recovering from a failed build

You have two options when a concurrent index build fails:

-- Option 1: Drop and rebuild
DROP INDEX CONCURRENTLY idx_orders_customer_id;
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

-- Option 2: Use REINDEX CONCURRENTLY (PG 12+)
-- This replaces the invalid index in-place
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

REINDEX CONCURRENTLY (available since PostgreSQL 12) is generally preferred because it rebuilds the index in a single operation. It creates a new index with a temporary name, swaps it with the old one, and drops the old one — all without blocking writes.

REINDEX CONCURRENTLY (PostgreSQL 12+)

REINDEX CONCURRENTLY was added in PostgreSQL 12. It rebuilds an existing index without blocking writes, similar to how CREATE INDEX CONCURRENTLY works. This is useful for:

Rebuilding invalid indexes left by failed CONCURRENTLY operations
Rebuilding bloated indexes (index bloat can slow down queries significantly)
Rebuilding corrupt indexes
Upgrading index parameters (e.g., changing fillfactor)

-- Rebuild a single index without blocking writes
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- Rebuild all indexes on a table
REINDEX TABLE CONCURRENTLY orders;

-- Rebuild all indexes in a schema
REINDEX SCHEMA CONCURRENTLY public;

-- DANGEROUS: Without CONCURRENTLY, REINDEX holds ACCESS EXCLUSIVE
-- This blocks all reads and writes
REINDEX INDEX idx_orders_customer_id; -- Don't do this in production

Performance Considerations

CREATE INDEX CONCURRENTLY is slower than regular CREATE INDEX. Expect roughly 2-3x the build time because:

It scans the table twice instead of once
It needs to track concurrent modifications between the two scans
It waits for existing transactions to complete before finalizing

You can speed up concurrent index builds by tuning these settings (for the session only):

-- Increase maintenance_work_mem for faster index builds
-- Default is typically 64MB — increase for large tables
SET maintenance_work_mem = '1GB';

-- Increase max_parallel_maintenance_workers (PG 11+)
-- Allows parallel index builds
SET max_parallel_maintenance_workers = 4;

-- Then create the index
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

-- Reset settings
RESET maintenance_work_mem;
RESET max_parallel_maintenance_workers;

DROP INDEX CONCURRENTLY

Just like creation, dropping an index normally acquires an ACCESS EXCLUSIVE lock. Use DROP INDEX CONCURRENTLY to avoid blocking reads and writes:

-- DANGEROUS: Blocks all reads and writes
DROP INDEX idx_orders_old;

-- SAFE: Does not block reads or writes
DROP INDEX CONCURRENTLY idx_orders_old;

-- Note: Like CREATE, DROP INDEX CONCURRENTLY cannot run
-- inside a transaction block

Retry Pattern for CI/CD

Since CREATE INDEX CONCURRENTLY can fail due to transient conditions (deadlocks, long-running transactions), a robust migration strategy includes retry logic:

#!/bin/bash
# retry-create-index.sh
MAX_RETRIES=3
RETRY_DELAY=10

for i in $(seq 1 $MAX_RETRIES); do
  echo "Attempt $i: Creating index..."

  # Drop invalid index if it exists from a previous failed attempt
  psql -c "DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id;" 2>/dev/null

  # Try to create the index
  if psql -c "CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);"; then
    echo "Index created successfully"
    exit 0
  fi

  echo "Failed on attempt $i, waiting $RETRY_DELAY seconds..."
  sleep $RETRY_DELAY
done

echo "Failed after $MAX_RETRIES attempts"
exit 1

Unique Index Considerations

Creating a UNIQUE index concurrently has an additional failure mode: if duplicate values exist in the column, the build fails and leaves an invalid index. Always check for duplicates first:

-- Check for duplicates before creating a unique index
SELECT email, count(*)
FROM users
GROUP BY email
HAVING count(*) > 1
LIMIT 10;

-- If duplicates exist, resolve them first
-- Then create the unique index
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users (email);

Summary: The Rules

Always use CONCURRENTLY for CREATE INDEX, DROP INDEX, and REINDEX in production
Never run CONCURRENTLY operations inside a transaction block
Check for invalid indexes after failed CONCURRENTLY operations
Use REINDEX CONCURRENTLY (PG 12+) to rebuild failed or bloated indexes
Implement retry logic in your deployment scripts
Increase maintenance_work_mem for large table index builds

Automate the Check

Forgetting CONCURRENTLY on a CREATE INDEX is one of the most common migration mistakes. MigrationPilot catches this with rule MP001 (require-concurrent-index) and can auto-fix it — adding CONCURRENTLY and flagging if the statement is inside a transaction block. It also catches missing CONCURRENTLY on DROP INDEX (MP009) and REINDEX (MP021). Add it to your CI pipeline to enforce these patterns automatically.

npx migrationpilot analyze migrations/004_add_indexes.sql --fix

How to Safely Add a Column with a Default Value in PostgreSQL

Mickel Samuel — Mon, 02 Mar 2026 14:05:26 +0000

Adding a column with a DEFAULT value is one of the most common migration operations. It is also one of the most misunderstood. Whether it takes milliseconds or causes a full table rewrite depends on your PostgreSQL version and what your DEFAULT expression looks like.

The Problem: Table Rewrites

Before PostgreSQL 11, adding a column with a DEFAULT value always rewrote the entire table. PostgreSQL had to physically write the default value into every existing row. During this rewrite, the table was locked with an ACCESS EXCLUSIVE lock, blocking all reads and writes.

-- PostgreSQL 10 and earlier: FULL TABLE REWRITE
-- On a 100M row table, this could take 10+ minutes
-- The table is completely locked during the entire operation
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

On a table with 100 million rows, this could take 10 minutes or more. During that time, every query against the table — reads included — queues up. Connection pools exhaust. Application timeouts cascade. Users see errors. This was one of the most common causes of database outages in production PostgreSQL deployments.

PostgreSQL 11: The Fast-Path Default

PostgreSQL 11 (released October 2018) introduced a significant optimization. When you add a column with a non-volatile DEFAULT, PostgreSQL stores the default value in the catalog (pg_attribute.attmissingval) instead of writing it to every row. The operation completes in milliseconds regardless of table size.

-- PostgreSQL 11+: INSTANT operation (no table rewrite)
-- Works because 'active' is a constant (non-volatile)
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- Also instant — integer constant
ALTER TABLE orders ADD COLUMN version INT DEFAULT 1;

-- Also instant — boolean constant
ALTER TABLE features ADD COLUMN enabled BOOLEAN DEFAULT false;

-- Also instant — NULL default (always was instant)
ALTER TABLE users ADD COLUMN middle_name TEXT;

When a query reads a row that was created before the column was added, PostgreSQL automatically returns the stored default value. Rows created after the ALTER TABLE include the column value physically. Over time, as rows are updated, the default value gets written to disk naturally through MVCC.

Volatile vs Non-Volatile Defaults

The optimization only works for non-volatile defaults. A non-volatile expression always returns the same value. A volatile expression might return a different value each time it is evaluated.

Expression	Volatility	Table Rewrite?
'active'	Immutable	No (instant)
42	Immutable	No (instant)
true / false	Immutable	No (instant)
'{}'::jsonb	Immutable	No (instant)
now()	Volatile	Yes (full rewrite)
gen_random_uuid()	Volatile	Yes (full rewrite)
random()	Volatile	Yes (full rewrite)
clock_timestamp()	Volatile	Yes (full rewrite)
nextval()	Volatile	Yes (full rewrite)

The distinction matters because a volatile function must produce a unique value per row. PostgreSQL cannot store a single value in the catalog and use it for all existing rows — it needs to evaluate the function for each one, which requires a table rewrite.

The Safe Pattern for Volatile Defaults

When you need a volatile default (like gen_random_uuid() or now()), split the operation into three steps:

-- Step 1: Add the column without a default (instant, no rewrite)
ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ;

-- Step 2: Set the default for new rows (instant, metadata-only)
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();

-- Step 3: Backfill existing rows in batches (no lock)
UPDATE users SET created_at = now()
WHERE id BETWEEN 1 AND 10000 AND created_at IS NULL;

UPDATE users SET created_at = now()
WHERE id BETWEEN 10001 AND 20000 AND created_at IS NULL;
-- ... continue in batches

This approach never locks the table for more than milliseconds. The backfill runs as normal DML, which only acquires ROW EXCLUSIVE locks. You can batch the updates to avoid holding locks for too long and to give autovacuum time to clean up dead tuples between batches.

Common Pitfall: UUID Primary Keys

One of the most common mistakes is adding a UUID column with gen_random_uuid() as the default:

-- DANGEROUS: gen_random_uuid() is volatile
-- This rewrites the entire table on PG 11+
ALTER TABLE orders ADD COLUMN external_id UUID DEFAULT gen_random_uuid();

-- SAFE: Split into separate operations
ALTER TABLE orders ADD COLUMN external_id UUID;
ALTER TABLE orders ALTER COLUMN external_id SET DEFAULT gen_random_uuid();
-- Then backfill existing rows in batches

Common Pitfall: Timestamps

Another frequent mistake is adding a created_at or updated_at column with now():

-- DANGEROUS: now() is stable within a transaction
-- but PostgreSQL classifies it as volatile for this purpose
ALTER TABLE events ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();

-- SAFE: Use the three-step pattern
ALTER TABLE events ADD COLUMN created_at TIMESTAMPTZ;
ALTER TABLE events ALTER COLUMN created_at SET DEFAULT now();
-- Backfill in batches...

A subtle detail: now() is technically STABLE (returns the same value within a transaction), but PostgreSQL still treats it as volatile for the purposes of the fast-path default optimization. The reason is that a STABLE function could still return different values across transactions, so PostgreSQL cannot store a single value in the catalog.

Backfill Strategies

When backfilling existing rows, there are several approaches:

Batched updates by primary key range

-- Process 10,000 rows at a time
DO $$
DECLARE
  batch_size INT := 10000;
  min_id BIGINT;
  max_id BIGINT;
  current_id BIGINT;
BEGIN
  SELECT min(id), max(id) INTO min_id, max_id FROM users;
  current_id := min_id;

  WHILE current_id <= max_id LOOP
    UPDATE users
    SET status = 'active'
    WHERE id >= current_id
      AND id < current_id + batch_size
      AND status IS NULL;

    current_id := current_id + batch_size;
    COMMIT;  -- Release locks between batches (PG 11+ procedures)
  END LOOP;
END $$;

Using a temporary helper function

-- For large backfills, a loop in a script is often simpler
-- Run this from your application or a migration runner:

-- Bash / psql loop:
for offset in $(seq 0 10000 1000000); do
  psql -c "UPDATE users SET status = 'active'
           WHERE id IN (SELECT id FROM users
                        WHERE status IS NULL
                        ORDER BY id LIMIT 10000)"
done

How to Check if a Default Triggers a Rewrite

You can check the volatility of a function using:

SELECT proname, provolatile
FROM pg_proc
WHERE proname = 'now';

-- provolatile values:
-- 'i' = immutable (safe)
-- 's' = stable (triggers rewrite in ADD COLUMN DEFAULT)
-- 'v' = volatile (triggers rewrite)

If provolatile is anything other than 'i' (immutable), the default will trigger a table rewrite when used in ADD COLUMN. Use the three-step pattern instead.

Version-Specific Summary

PG Version	Constant Default	Volatile Default
PG 10 and earlier	Full table rewrite	Full table rewrite
PG 11+	Instant (catalog-only)	Full table rewrite

Catch This Automatically

Remembering which defaults are volatile and which are safe is easy to get wrong under pressure. MigrationPilot detects volatile defaults in ADD COLUMN statements (rule MP003) and suggests the safe three-step pattern automatically. It also auto-detects your PostgreSQL version and adjusts its advice accordingly. Run it in your CI pipeline to catch these issues before they reach production.

npx migrationpilot analyze migrations/003_add_timestamps.sql

Adding NOT NULL Constraints to Existing PostgreSQL Columns Safely

Mickel Samuel — Mon, 02 Mar 2026 14:05:22 +0000

Adding a NOT NULL constraint to an existing column is one of the most common schema changes, and one of the most dangerous. The naive approach scans the entire table under an ACCESS EXCLUSIVE lock. On a large table, this causes an outage. Here is the safe pattern.

The Problem with SET NOT NULL

When you run ALTER TABLE ... ALTER COLUMN ... SET NOT NULL, PostgreSQL must verify that no existing rows have NULL values in that column. To do this, it scans the entire table while holding an ACCESS EXCLUSIVE lock.

-- DANGEROUS: Full table scan under ACCESS EXCLUSIVE
-- On a 50M row table, this blocks ALL queries for minutes
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- What happens internally:
-- 1. Acquires ACCESS EXCLUSIVE lock (blocks everything)
-- 2. Scans every row to verify email IS NOT NULL
-- 3. If any NULL found: fails with error
-- 4. If all rows pass: sets the constraint
-- 5. Releases lock

-- During step 2, no query can read or write the table

ACCESS EXCLUSIVE means nothing else can happen on this table. No SELECT, no INSERT, no UPDATE, no DELETE. And it queues — any query that tries to access the table while the scan is running gets queued, filling up your connection pool.

The Safe Pattern: CHECK + VALIDATE

The safe approach uses a CHECK constraint with NOT VALID to split the operation into two steps with different lock levels:

-- Step 1: Add the constraint as NOT VALID
-- Acquires ACCESS EXCLUSIVE but is INSTANT (no table scan)
-- Only new inserts/updates are checked going forward
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint
-- Acquires SHARE UPDATE EXCLUSIVE (does NOT block reads or writes)
-- Scans the entire table to verify existing rows
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

The key differences from the direct SET NOT NULL approach:

Aspect	SET NOT NULL	CHECK + VALIDATE
Lock type (add)	ACCESS EXCLUSIVE	ACCESS EXCLUSIVE (instant)
Lock type (validate)	Same lock, same operation	SHARE UPDATE EXCLUSIVE
Blocks reads?	Yes (during scan)	No
Blocks writes?	Yes (during scan)	No
New rows checked?	After constraint set	Immediately after Step 1

PostgreSQL 12+: Converting CHECK to NOT NULL

Starting with PostgreSQL 12, if a validated CHECK constraint of the form CHECK (column IS NOT NULL) exists, PostgreSQL recognizes that the column already has a not-null guarantee. In this case, SET NOT NULL skips the table scan and completes instantly:

-- Full safe pattern for PG 12+:

-- Step 1: Add NOT VALID CHECK constraint (instant)
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate (scans table, but under safe lock)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- Step 3: Convert to real NOT NULL (instant — no scan needed!)
-- PG 12+ skips the scan because it sees the validated CHECK
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the now-redundant CHECK constraint
ALTER TABLE users DROP CONSTRAINT chk_email_not_null;

The result is identical to running SET NOT NULL directly, but without the dangerous full-table scan under ACCESS EXCLUSIVE. The total lock time is a few milliseconds in Steps 1, 3, and 4. Step 2 scans the table but does not block any queries.

PostgreSQL 11 and Earlier

On PostgreSQL 11 and earlier, the SET NOT NULL optimization does not exist. After validating the CHECK constraint, you have two options:

Option A: Keep the CHECK constraint (recommended)

-- Just use the CHECK constraint as your not-null guarantee
-- This provides the same data integrity as SET NOT NULL
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- Done. The CHECK constraint enforces not-null for all
-- existing and future rows. No need for SET NOT NULL.

The CHECK constraint provides the same guarantee as SET NOT NULL. The only difference is cosmetic: \d users will not show "not null" next to the column, but the constraint is visible in the constraints section. Some ORMs may not recognize the CHECK constraint as a not-null guarantee, but the data integrity is identical.

Option B: Accept the brief lock (small tables only)

-- If the table is small enough (< 100K rows), the scan
-- under ACCESS EXCLUSIVE is fast enough to be acceptable
-- Use lock_timeout to prevent the lock queue problem
SET lock_timeout = '5s';
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
RESET lock_timeout;

Handling Existing NULL Values

Before adding a NOT NULL constraint, you need to ensure no NULL values exist. The VALIDATE step will fail if it finds any. Backfill NULLs first:

-- Step 0: Backfill NULL values (before adding the constraint)
-- Do this in batches to avoid long-running transactions

-- Check how many NULLs exist
SELECT count(*) FROM users WHERE email IS NULL;

-- Backfill in batches
UPDATE users
SET email = 'unknown@example.com'
WHERE id IN (
  SELECT id FROM users
  WHERE email IS NULL
  ORDER BY id
  LIMIT 10000
);
-- Repeat until no NULLs remain

-- Then proceed with the CHECK + VALIDATE pattern
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

Common Mistakes

1. Forgetting NOT VALID

-- BAD: Without NOT VALID, ADD CONSTRAINT scans the table
-- under SHARE ROW EXCLUSIVE (blocks writes)
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL);

-- GOOD: NOT VALID makes it instant
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

2. Running SET NOT NULL on PG 11 after CHECK validation

-- BAD (on PG 11): SET NOT NULL still scans the table
-- even though the CHECK constraint is validated
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- This does a full scan under ACCESS EXCLUSIVE!

-- On PG 11: Just keep the CHECK constraint
-- On PG 12+: SET NOT NULL is instant (skips scan)

3. Not setting lock_timeout

-- BAD: Both steps should have lock_timeout
-- Even though the locks are brief, they can queue

-- GOOD: Set lock_timeout for both operations
SET lock_timeout = '5s';
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
RESET lock_timeout;

-- VALIDATE doesn't need lock_timeout (it uses a safe lock)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

The Complete Pattern

Here is the full, production-safe pattern for adding NOT NULL to an existing column:

-- Migration file: 005_add_email_not_null.sql

-- 1. Backfill any existing NULLs (if they exist)
-- Run this in a separate migration or script before this one
-- UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

-- 2. Add NOT VALID CHECK constraint (instant, brief ACCESS EXCLUSIVE)
SET lock_timeout = '5s';
ALTER TABLE users ADD CONSTRAINT chk_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;
RESET lock_timeout;

-- 3. Validate (full scan, but SHARE UPDATE EXCLUSIVE — safe)
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- 4. Convert to formal NOT NULL (PG 12+ only — instant)
SET lock_timeout = '5s';
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
RESET lock_timeout;

-- 5. Drop redundant CHECK (PG 12+ only)
SET lock_timeout = '5s';
ALTER TABLE users DROP CONSTRAINT chk_email_not_null;
RESET lock_timeout;

Automate This Check

Catching a direct SET NOT NULL before it reaches production is exactly the kind of thing that should be automated. MigrationPilot flags SET NOT NULL without the CHECK pattern (rule MP002) and direct ADD CONSTRAINT without NOT VALID (rule MP030). It is version-aware, so on PG 12+ it knows that SET NOT NULL after a validated CHECK is safe. Add it to your CI pipeline to catch these patterns automatically.

npx migrationpilot analyze migrations/005_add_email_not_null.sql

Which ALTER TABLE Operations Lock Your PostgreSQL Table?

Mickel Samuel — Mon, 02 Mar 2026 14:05:18 +0000

ALTER TABLE is not a single operation. PostgreSQL has dozens of ALTER TABLE sub-commands, and they acquire different lock levels. Some are instant and harmless. Others lock your entire table and block all traffic. This is the complete reference.

ACCESS EXCLUSIVE Operations (Block Everything)

These ALTER TABLE operations acquire ACCESS EXCLUSIVE, which blocks all reads and writes on the table. On a busy production table, these are the operations most likely to cause outages.

ADD COLUMN with volatile DEFAULT

-- Rewrites the entire table (all PostgreSQL versions)
ALTER TABLE users ADD COLUMN request_id UUID DEFAULT gen_random_uuid();

-- Safe alternative: three-step pattern
ALTER TABLE users ADD COLUMN request_id UUID;
ALTER TABLE users ALTER COLUMN request_id SET DEFAULT gen_random_uuid();
-- Backfill in batches...

On PostgreSQL 11+, non-volatile defaults (constants like 'active', 0, false) are instant and do not rewrite the table. Volatile defaults (now(), gen_random_uuid(), random()) still rewrite the table.

ALTER COLUMN TYPE

-- Rewrites the entire table under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN amount TYPE BIGINT;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);

-- Safe alternative: expand-contract pattern
ALTER TABLE orders ADD COLUMN amount_v2 BIGINT;
-- Sync trigger + backfill + app code switch + drop old column

There are a few exceptions where ALTER COLUMN TYPE does not rewrite the table: changing VARCHAR(n) to VARCHAR(m) where m > n (widening), changing VARCHAR(n) to TEXT, and changing NUMERIC(p,s) to NUMERIC (removing precision constraint). These are metadata-only changes that still acquire ACCESS EXCLUSIVE but complete instantly.

SET NOT NULL

-- Scans entire table under ACCESS EXCLUSIVE to verify no NULLs exist
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Safe alternative: CHECK + VALIDATE pattern
ALTER TABLE users ADD CONSTRAINT chk_email_nn
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_nn;

-- PG 12+: After validation, SET NOT NULL is instant
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT chk_email_nn;

ADD CONSTRAINT (UNIQUE, PRIMARY KEY, EXCLUDE)

-- Scans table + builds index under ACCESS EXCLUSIVE
ALTER TABLE users ADD CONSTRAINT uniq_email UNIQUE (email);

-- Safe alternative: build index concurrently first
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users (email);
ALTER TABLE users ADD CONSTRAINT uniq_email UNIQUE USING INDEX idx_users_email;
-- The ADD CONSTRAINT USING INDEX is instant

SET LOGGED / SET UNLOGGED

-- Rewrites the entire table
ALTER TABLE events SET UNLOGGED;
ALTER TABLE events SET LOGGED;
-- No safe alternative — avoid in production

Other ACCESS EXCLUSIVE operations

DROP COLUMN — instant but holds ACCESS EXCLUSIVE briefly
RENAME COLUMN — instant but holds ACCESS EXCLUSIVE briefly
RENAME TABLE — instant but holds ACCESS EXCLUSIVE briefly
ADD COLUMN ... GENERATED ALWAYS AS (expr) STORED — rewrites table
CLUSTER — rewrites table

SHARE ROW EXCLUSIVE Operations (Block Writes)

These operations block INSERT, UPDATE, DELETE but allow SELECT queries to continue.

ADD CONSTRAINT ... FOREIGN KEY (without NOT VALID)

-- Validates all existing rows — blocks writes during scan
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);

-- Safe alternative: NOT VALID + VALIDATE
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
-- NOT VALID acquires SHARE ROW EXCLUSIVE but is instant

ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
-- VALIDATE acquires SHARE UPDATE EXCLUSIVE (does not block writes)

Note that foreign keys lock both the child table (orders) and the parent table (users). The lock on the parent table is SHARE ROW EXCLUSIVE as well. This is one of the most overlooked aspects of FK constraints — your migration might not touch the parent table, but it still locks it.

CREATE TRIGGER

-- Acquires SHARE ROW EXCLUSIVE — blocks writes briefly
-- This is typically instant, so the lock duration is negligible
CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified_at();

SHARE UPDATE EXCLUSIVE Operations (Safe)

These operations do not block reads or writes. They only conflict with other SHARE UPDATE EXCLUSIVE operations and stronger locks.

ALTER TABLE VALIDATE CONSTRAINT — validates CHECK or FK constraints
ALTER TABLE SET STATISTICS — changes column statistics target
ALTER TABLE SET (fillfactor = ...) — changes storage parameters
ALTER TABLE SET (autovacuum_enabled = ...) — changes autovacuum settings

Quick Reference Table

Operation	Lock	Duration	Safe Alternative
ADD COLUMN (no default)	ACCESS EXCLUSIVE	Instant	N/A (already safe)
ADD COLUMN (constant default, PG11+)	ACCESS EXCLUSIVE	Instant	N/A (already safe)
ADD COLUMN (volatile default)	ACCESS EXCLUSIVE	Table rewrite	Three-step pattern
ALTER COLUMN TYPE	ACCESS EXCLUSIVE	Table rewrite	Expand-contract
SET NOT NULL	ACCESS EXCLUSIVE	Full scan	CHECK + VALIDATE
DROP NOT NULL	ACCESS EXCLUSIVE	Instant	N/A
ADD CONSTRAINT UNIQUE	ACCESS EXCLUSIVE	Index build	CONCURRENTLY + USING INDEX
ADD CONSTRAINT FK	SHARE ROW EXCL	Full scan	NOT VALID + VALIDATE
ADD CONSTRAINT CHECK	SHARE ROW EXCL	Full scan	NOT VALID + VALIDATE
VALIDATE CONSTRAINT	SHARE UPDATE EXCL	Full scan	N/A (already safe)
DROP COLUMN	ACCESS EXCLUSIVE	Instant	N/A
RENAME COLUMN	ACCESS EXCLUSIVE	Instant	Expand-contract
SET DEFAULT	ACCESS EXCLUSIVE	Instant	N/A
SET STATISTICS	SHARE UPDATE EXCL	Instant	N/A

The Key Insight: Lock Level vs Duration

A common misconception is that ACCESS EXCLUSIVE always means danger. That is not quite right. The danger comes from ACCESS EXCLUSIVE held for a long time.

Adding a column without a default acquires ACCESS EXCLUSIVE, but it completes in milliseconds. The lock is barely noticeable. ALTER COLUMN TYPE also acquires ACCESS EXCLUSIVE, but it rewrites the entire table, which can take minutes. Both are ACCESS EXCLUSIVE, but only one is dangerous.

The risk formula is: Lock Severity x Duration x Traffic. A brief ACCESS EXCLUSIVE on a low-traffic table is fine. A long-running SHARE lock on a high-traffic table can bring down your application.

Catch Lock Issues Before Production

Static analysis can catch most dangerous ALTER TABLE patterns before they reach production. MigrationPilot reports the exact lock type each ALTER TABLE sub-command acquires, flags operations that cause table rewrites or full scans, and suggests the safe alternative pattern. All 80 rules run in milliseconds without any database connection.

npx migrationpilot analyze migrations/005_alter_users.sql

How to Add Database Migration Checks to Your CI/CD Pipeline

Mickel Samuel — Mon, 02 Mar 2026 14:05:14 +0000

Code review catches logic bugs, linting catches style issues, and type checking catches type errors. But what catches dangerous database migrations? Most teams ship migration SQL without any automated safety checks. Here is how to fix that.

Why CI Checks for Migrations

Database migrations have a unique risk profile compared to application code:

Irreversible by default. A deployed app rollback takes seconds. A schema change rollback can take hours or be impossible (data loss from DROP COLUMN).
Production-specific risk. A migration that runs in 100ms on dev can take 10 minutes on production with 50M rows.
Cascading failures. A single bad lock can queue all queries, exhaust connection pools, and take down the entire application.
Low review expertise. Most engineers review SQL migrations less carefully than application code because the risks are not obvious.

Automated CI checks solve these problems by catching known-dangerous patterns before the migration is merged. The check runs in seconds, requires no database connection, and provides actionable feedback directly in the pull request.

What to Check

An effective migration linter should catch these categories of issues:

Lock safety

CREATE INDEX without CONCURRENTLY (blocks writes)
Missing lock_timeout before DDL (can queue all traffic)
ADD COLUMN with volatile DEFAULT (full table rewrite)
SET NOT NULL without CHECK pattern (ACCESS EXCLUSIVE scan)
UNIQUE constraint without USING INDEX (builds index under ACCESS EXCLUSIVE)
Foreign key without NOT VALID (scans both tables under lock)

Data safety

DROP TABLE, DROP COLUMN (irreversible data loss)
TRUNCATE CASCADE (cascading data deletion)
Data type narrowing (silent data truncation)
DROP NOT NULL on columns that should never be null

Best practices

Prefer TEXT over VARCHAR (no performance difference in PostgreSQL)
Prefer BIGINT over INT for primary keys (avoid future overflow)
Prefer TIMESTAMPTZ over TIMESTAMP (timezone-aware)
Prefer IDENTITY over SERIAL (SQL standard, better semantics)
Always name indexes explicitly (for reliable migrations later)

GitHub Actions Setup

The simplest way to add migration checks to a GitHub repository. Create a workflow file:

# .github/workflows/migration-check.yml
name: Migration Safety Check

on:
  pull_request:
    paths:
      - 'migrations/**'
      - 'db/migrate/**'
      - 'prisma/migrations/**'

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: mickelsamuel/migrationpilot@v1
        with:
          migration-path: "migrations/*.sql"
          fail-on: critical

This workflow runs whenever a PR modifies files in the migrations directory. It analyzes every SQL file, posts a safety report as a PR comment, and fails the check if any critical violations are found.

Customizing the check

# More detailed configuration
- uses: mickelsamuel/migrationpilot@v1
  with:
    # Path to migration files (glob pattern)
    migration-path: "migrations/*.sql"

    # Fail the check on "critical" or "warning" violations
    fail-on: critical

    # Exclude specific rules (comma-separated)
    # e.g., if you intentionally want DROP TABLE in a cleanup migration
    exclude: "MP026,MP017"

    # PostgreSQL version for version-specific advice
    pg-version: "16"

    # Output SARIF for GitHub Code Scanning integration
    sarif-file: "results.sarif"

SARIF integration with GitHub Code Scanning

For richer integration, MigrationPilot can output SARIF (Static Analysis Results Interchange Format), which GitHub Code Scanning understands natively. Violations appear as inline annotations directly in the PR diff:

# .github/workflows/migration-check.yml
name: Migration Safety Check

on:
  pull_request:
    paths: ['migrations/**']

jobs:
  check:
    runs-on: ubuntu-latest
    permissions:
      contents: read
      security-events: write  # Required for SARIF upload

    steps:
      - uses: actions/checkout@v4

      - uses: mickelsamuel/migrationpilot@v1
        with:
          migration-path: "migrations/*.sql"
          fail-on: critical
          sarif-file: "migrationpilot.sarif"

      - uses: github/codeql-action/upload-sarif@v3
        if: always()
        with:
          sarif_file: "migrationpilot.sarif"

GitLab CI Setup

# .gitlab-ci.yml
migration-check:
  image: node:22-slim
  stage: test
  rules:
    - changes:
        - migrations/*.sql
  script:
    - npx migrationpilot check migrations/*.sql --fail-on critical
  allow_failure: false

Bitbucket Pipelines Setup

# bitbucket-pipelines.yml
pipelines:
  pull-requests:
    '**':
      - step:
          name: Migration Safety Check
          image: node:22-slim
          script:
            - npx migrationpilot check migrations/*.sql --fail-on critical
          condition:
            changesets:
              includePaths:
                - "migrations/**"

Generic CI (Any System)

MigrationPilot works as a plain CLI tool, so it runs anywhere Node.js is available:

# Install and run
npm install -g migrationpilot

# Check migrations — exits with code 2 on critical violations
migrationpilot check migrations/*.sql --fail-on critical

# Or use npx (no install needed)
npx migrationpilot check migrations/*.sql --fail-on critical

# Output formats for different CI systems
migrationpilot check migrations/*.sql --format json      # Machine-readable
migrationpilot check migrations/*.sql --format sarif      # Code scanning
migrationpilot check migrations/*.sql --format markdown   # Wiki/docs
migrationpilot check migrations/*.sql --quiet             # gcc-style one-liner

Framework-Specific Migration Paths

Different migration frameworks store SQL files in different locations. Here are the common paths:

Framework	Migration Path
Flyway	src/main/resources/db/migration/*.sql
Liquibase	src/main/resources/db/changelog/*.sql
Rails	db/migrate/*.rb (use schema.sql)
Django	/migrations/.py (use sqlmigrate)
Alembic	alembic/versions/*.py
Prisma	prisma/migrations/*/migration.sql
Knex	migrations/*.js (pipe through knex)
goose	migrations/*.sql
dbmate	db/migrations/*.sql
Sqitch	deploy/*.sql

ORM frameworks (Django, Rails, Alembic)

For frameworks that generate SQL from ORM code (Django, Rails, Alembic), you can pipe the generated SQL through stdin:

# Django: Generate SQL from migration, then analyze
python manage.py sqlmigrate myapp 0042 | npx migrationpilot analyze --stdin

# Alembic: Generate SQL from revision
alembic upgrade head --sql | npx migrationpilot analyze --stdin

# Rails: Use schema.sql format
# Set config.active_record.schema_format = :sql in application.rb
# Then analyze the generated SQL file
npx migrationpilot analyze db/structure.sql

Pre-Commit Hook (Local Check)

For even earlier feedback, add a pre-commit hook that runs before code leaves the developer's machine:

# Install the pre-commit hook
npx migrationpilot hook install

# Or add to your existing husky setup
# .husky/pre-commit
npx migrationpilot check migrations/*.sql --fail-on critical --quiet

Configuration File

For project-wide settings, create a configuration file in your repository root:

# .migrationpilotrc.yml
# Use a built-in preset as a starting point
extends: ci

# PostgreSQL version (affects version-specific advice)
pgVersion: 16

# Override severity for specific rules
rules:
  MP026:
    severity: warning  # Allow DROP TABLE with a warning
  MP037:
    severity: off      # Don't enforce TEXT over VARCHAR

# Exclude rules globally
exclude:
  - MP015  # Allow SERIAL (team preference)

What the PR Comment Looks Like

When running as a GitHub Action, MigrationPilot posts a detailed safety report as a PR comment. The comment includes:

Overall risk score (RED / YELLOW / GREEN)
Per-statement lock analysis (which lock each DDL acquires)
Violation details with explanations of why each pattern is dangerous
Safe alternative SQL you can copy-paste
Auto-update on each push (no comment spam)

The comment is automatically updated on each push to the PR branch, so there is no comment spam. If all violations are resolved, the comment shows a green checkmark.

Gradual Adoption Strategy

You do not need to fix every existing migration to start using CI checks. A practical adoption strategy:

Week 1: Warning mode. Add the check with allow_failure: true (GitLab) or no fail-on (GitHub Action). Team sees reports but is not blocked.
Week 2: Block critical only. Set fail-on: critical to block the most dangerous patterns (missing CONCURRENTLY, table rewrites).
Month 2: Block warnings. Once the team is comfortable, switch to fail-on: warning to enforce best practices too.
Ongoing: Customize rules. Tune severity overrides and exclusions based on your team's conventions.

Get Started

Adding migration safety checks to CI takes less than a minute. MigrationPilot is open-source (MIT), runs 80 safety rules in under a second, and requires no database connection. It works as a CLI, GitHub Action, GitLab CI step, or Node.js library.

# Try it now on your existing migrations
npx migrationpilot analyze migrations/*.sql

Flyway vs Liquibase for PostgreSQL: An Honest Comparison

Mickel Samuel — Mon, 02 Mar 2026 14:05:10 +0000

Flyway and Liquibase are the two most widely used database migration tools in the Java ecosystem. Both support PostgreSQL. Both are mature. And both have significant differences in philosophy, syntax, and capabilities. This guide compares them honestly, including where each one falls short.

Philosophy: Convention vs Configuration

Flyway follows a convention-over-configuration approach. Migrations are plain SQL files with a naming convention (V1__description.sql). There is one way to do things, and it is simple. If you know SQL, you know Flyway.

Liquibase takes a configuration-heavy approach. Migrations (called "changesets") can be written in XML, YAML, JSON, or SQL. Liquibase provides a database-agnostic abstraction layer with its own DSL for schema changes. This gives more flexibility at the cost of complexity.

Migration Syntax

Flyway: Plain SQL

-- V2__add_users_email_index.sql
-- Flyway migration: just SQL with a naming convention

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- That's it. The filename determines the version and description.
-- V2 = version 2
-- add_users_email_index = description (underscores become spaces)

Liquibase: XML/YAML/SQL changesets

<!-- db.changelog-2.xml -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">

  <changeSet id="2" author="dev">
    <createIndex indexName="idx_users_email"
                 tableName="users"
                 unique="false">
      <column name="email"/>
    </createIndex>
  </changeSet>
</databaseChangeLog>

Or in YAML:

# db.changelog-2.yaml
databaseChangeLog:
  - changeSet:
      id: 2
      author: dev
      changes:
        - createIndex:
            indexName: idx_users_email
            tableName: users
            columns:
              - column:
                  name: email

Or as raw SQL (Liquibase also supports this):

-- db.changelog-2.sql
-- liquibase formatted sql
-- changeset dev:2
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Feature Comparison

Feature	Flyway	Liquibase
Migration format	SQL, Java	XML, YAML, JSON, SQL
PostgreSQL support	Excellent	Excellent
Rollback support	Paid (Teams)	Free (auto + manual)
Schema diff	No	Yes (diff command)
Database-agnostic DSL	No (SQL is DB-specific)	Yes (XML/YAML changesets)
Conditional execution	No	Yes (preconditions)
Transaction control	Per-migration only	Per-changeset
Learning curve	Low	Medium-High
Community edition	Apache 2.0	Apache 2.0
SQL safety linting	No	No

Rollback Strategies

Flyway

Flyway's free tier does not support rollbacks at all. You can only move forward. The Teams/Enterprise tier adds "undo migrations" (files named U2__description.sql) that you write manually. There is no auto-generated rollback.

-- V2__add_status_column.sql
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- U2__add_status_column.sql (Flyway Teams only)
ALTER TABLE users DROP COLUMN status;

Liquibase

Liquibase has built-in rollback support in the free tier. For XML/YAML changesets, Liquibase auto-generates rollback SQL for many operations (CREATE TABLE, ADD COLUMN, etc.). For complex or SQL-based changesets, you specify rollback manually:

<changeSet id="2" author="dev">
  <addColumn tableName="users">
    <column name="status" type="TEXT" defaultValue="active"/>
  </addColumn>
  <!-- Rollback auto-generated: DROP COLUMN status -->
</changeSet>

<!-- For SQL changesets, specify rollback explicitly -->
<changeSet id="3" author="dev">
  <sql>
    CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
  </sql>
  <rollback>
    DROP INDEX CONCURRENTLY idx_users_email;
  </rollback>
</changeSet>

PostgreSQL-Specific Considerations

Transaction handling

Both Flyway and Liquibase wrap migrations in transactions by default. This is generally good for atomicity but causes problems with specific PostgreSQL operations:

CREATE INDEX CONCURRENTLY cannot run inside a transaction
ALTER TYPE ... ADD VALUE cannot run inside a transaction (PG 11 and earlier)
DROP INDEX CONCURRENTLY cannot run inside a transaction

-- Flyway: Disable transaction for specific migration
-- Add this comment at the top of the file:
-- flyway:executeInTransaction=false
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Liquibase: Disable transaction per changeset

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Liquibase abstraction layer limitations

Liquibase's database-agnostic DSL does not support many PostgreSQL-specific features:

No CONCURRENTLY support in the XML <createIndex> tag
No NOT VALID support for constraints
No support for partitioning (PARTITION BY)
No support for RLS policies
Limited enum type support

For any PostgreSQL-specific migration, you end up using raw SQL changesets anyway, which negates the benefit of the abstraction layer. If you are only targeting PostgreSQL, the XML/YAML format adds complexity without benefit.

When to Choose Flyway

You want simplicity. Flyway's SQL-first approach means there is almost nothing to learn. Write SQL, name the file correctly, done.
You only target PostgreSQL. No need for a database-agnostic abstraction layer.
Your team knows SQL well. Flyway encourages SQL fluency rather than hiding behind an abstraction.
You want minimal tooling overhead. Fewer config files, fewer concepts, fewer surprises.

When to Choose Liquibase

You need multi-database support. If your application targets PostgreSQL, MySQL, and Oracle, the abstraction layer helps.
You need rollback in the free tier. Flyway's rollback is a paid feature.
You need conditional execution. Liquibase preconditions let you run changesets conditionally (e.g., only if a table does not exist).
You need schema diff. Liquibase can compare two databases and generate the diff as changesets.
You have complex deployment workflows. Liquibase's changeset model with contexts and labels provides more granular control.

What Neither Tool Does: SQL Safety Analysis

Both Flyway and Liquibase are migration runners. They track which migrations have been applied, execute them in order, and handle versioning. What neither tool does is analyze the SQL itself for safety.

Neither Flyway nor Liquibase will warn you about:

CREATE INDEX without CONCURRENTLY (blocks writes)
Missing lock_timeout (can queue all traffic)
ALTER COLUMN TYPE causing a full table rewrite
SET NOT NULL without the CHECK pattern (ACCESS EXCLUSIVE scan)
Volatile defaults that rewrite the table
Foreign keys that lock both tables

These tools apply your migrations. They do not tell you whether those migrations are safe to run against a production database. This is a fundamentally different concern, and it is where migration linting tools come in.

Combining Migration Runners with Safety Linting

The best approach is to use both: a migration runner (Flyway or Liquibase) for execution, and a linter for safety analysis. They complement each other:

# Example CI pipeline combining Flyway + MigrationPilot

# Step 1: Lint the migration SQL for safety issues
npx migrationpilot check src/main/resources/db/migration/*.sql \
  --fail-on critical

# Step 2: If lint passes, run the migration with Flyway
flyway migrate

Or with Liquibase:

# For Liquibase SQL changesets:
npx migrationpilot check src/main/resources/db/changelog/*.sql \
  --fail-on critical

# For Liquibase XML/YAML, generate SQL first:
liquibase update-sql > /tmp/pending.sql
npx migrationpilot analyze /tmp/pending.sql

MigrationPilot auto-detects both Flyway and Liquibase (plus 12 other frameworks) and works with any migration runner. It analyzes the SQL for lock safety, table rewrites, and 80 other patterns — the exact gap that migration runners do not fill. It runs in CI as a GitHub Action, GitLab CI step, or plain CLI command.

Summary

Flyway : Simple, SQL-first, great for PostgreSQL-only projects. Rollback is paid.
Liquibase : Flexible, multi-format, rollback included. More complex, abstraction layer has PostgreSQL gaps.
Both : Run migrations but do not analyze SQL for safety. Pair with a linter for production-safe migrations.
For PostgreSQL-only projects, Flyway's simplicity usually wins.
For multi-database environments or when you need free rollback, Liquibase is the better choice.

Atlas Paywalled Their Migration Linter — Here Are Your Free Alternatives

Mickel Samuel — Mon, 02 Mar 2026 14:05:06 +0000

In October 2025, Atlas moved atlas migrate lint out of their free Starter plan. If you relied on Atlas for catching dangerous PostgreSQL migrations in CI, you now need a paid plan or a new tool. This guide covers what changed, what's still available for free, and the open-source alternatives you can adopt today.

What Changed in Atlas v0.38

Atlas v0.38, released October 30, 2025, restructured its pricing tiers. The key change: the atlas migrate lint command is no longer available in the free Starter plan. To use migration linting, you now need a Pro plan at $9/developer/month plus $59/CI project/month.

For a team of 5 engineers with 2 CI projects, that's $163/month for migration linting alone. Not unreasonable for a well-funded team, but a significant cost for startups, open-source projects, and smaller teams that were getting this for free.

What Atlas lint actually checked

Atlas's PostgreSQL-specific analyzers were genuinely useful. Their Pro-only rules include:

PG301 : Adding a non-nullable column without a default
PG302 : Adding a column with a volatile default
PG303 : Modifying a column type (table rewrite)
PG304 : Adding a primary key to an existing table
PG305 : Adding a unique constraint (ACCESS EXCLUSIVE)
PG307 : Creating an index non-concurrently
PG311 : Adding an exclusion constraint (table rewrite)

These are real production safety checks. If your team was relying on Atlas to catch these in CI, the paywall means you either pay up or lose that safety net.

The Community Edition and Hacker License

Atlas still offers two free options, but with limitations:

Community Edition (Apache 2.0): An open-source build of Atlas with the basic migration engine. It includes migrate lint with a basic set of analyzers, but the PostgreSQL-specific rules (PG301-PG311) are Pro-only. You get generic checks like destructive change detection, but not the nuanced PostgreSQL lock and rewrite analysis that made Atlas lint valuable.

Hacker License : Free for students, open-source contributors, and hobby projects. This is generous, but it explicitly excludes commercial use. If your company is running PostgreSQL in production, you cannot use the Hacker License for your CI pipeline.

The Broader Trend: Paywalls Everywhere

Atlas is not alone in restricting free tiers. Within a 6-month window in 2025:

Liquibase changed to a Functional Source License (FSL) in October 2025. FSL is not OSI-approved open source. Apache Fineract has already downgraded their Liquibase dependency, and CNCF projects cannot use FSL-licensed software.
Flyway discontinued their Teams tier in May 2025, leaving only Community (limited) and Enterprise (expensive, quote-based).
Atlas moved lint to Pro in October 2025.

Three major migration tools restricting access within six months is not a coincidence — it's a market consolidation pattern. If you want migration safety tooling that will remain free and open source, the options are now more limited than they were a year ago.

Free, Open-Source Alternatives

If you need a free PostgreSQL migration linter in CI, here are your options, assessed honestly:

1. Squawk

Squawk is the most established free migration linter. Written in Rust, it's fast and focused.

Rules: 32
Language: Rust (CLI binary + npm wrapper)
GitHub: ~1,000 stars
Downloads: ~600K/month (npm + PyPI combined)
License: Apache 2.0
GitHub Action: Yes (free)

Strengths: Fast execution (Rust native), well-tested rules for common PostgreSQL lock hazards, active development. The core rules cover CREATE INDEX without CONCURRENTLY, adding columns with volatile defaults, and other common issues.

Limitations: No lock type classification (doesn't tell you which lock a statement acquires). No production context awareness — it can't factor in table sizes or active queries. No auto-fix suggestions. Exit code 1 for both errors and warnings (no distinction). Cannot analyze PL/pgSQL function bodies or DO blocks.

# Install and run Squawk

npm install -g squawk-cli

  
  
  Analyze a migration file


squawk migrations/V1__add_users_email.sql

  
  
  In CI (GitHub Action)


  
  
  Uses: sbdchd/squawk-action@v1

strong_migrations (Rails only)

If your team uses Ruby on Rails, strong_migrations is the gold standard. It integrates directly with ActiveRecord and catches dangerous migrations at the ORM level.

GitHub: ~4,400 stars
Language: Ruby (Rails gem)
License: MIT
Used by: Instacart, many Rails shops

Strengths: Excellent developer experience — when a migration is dangerous, it shows you the exact safe alternative with a clear explanation of why. This "explain the risk, show the fix" UX is what every migration linter should aspire to.

Limitations: Rails and ActiveRecord only. If your team uses Go, Python, Node.js, or plain SQL files, strong_migrations is not an option. This is the most common complaint in migration safety discussions:

"My conundrum is we don't use Django lol. Wish this sort of thoroughness existed in standalone pg migration tooling." — Hacker News commenter

3. Eugene

Eugene is a newer tool that takes a unique approach: both static lint and dynamic lock tracing. It can run your migration against a temporary PostgreSQL server and observe which locks are actually acquired.

GitHub: ~52 stars
Language: Rust
Approach: Static lint + dynamic lock tracing
Status: Active but early-stage

Strengths: The dynamic tracing approach can catch issues that static analysis misses. Supabase's postgres-language-server team has acknowledged that Eugene is "more advanced" than their Squawk-based port for lock analysis.

Limitations: Very early-stage (52 stars). Requires a running PostgreSQL instance for dynamic tracing. No GitHub Action in the marketplace. Smaller community and less documentation.

4. MigrationPilot

MigrationPilot is a newer entry with the deepest free rule set. Disclaimer: this article is published on the MigrationPilot blog, so take this section with appropriate skepticism and verify the claims against the open-source repository.

Rules: 80 (77 free, 3 paid)
Language: TypeScript
License: MIT
GitHub Action: Yes (free)
Auto-fix: 12 rules

Strengths: Lock type classification (tells you which lock each statement acquires). Risk scoring (RED/YELLOW/GREEN). Auto-fix suggestions for 12 rules. Works with any framework — analyzes plain SQL. Free GitHub Action. VS Code extension. Every rule includes an explanation of why the operation is dangerous and the safe alternative.

Limitations: New project with a small community. Fewer real-world battle-testing hours than Squawk. TypeScript, not Rust — slower on very large migration files (though still sub-second for typical migrations). The 3 paid rules require a $19/month subscription for production context analysis.

# Install and run MigrationPilot

npx migrationpilot analyze migrations/*.sql

  
  
  Auto-fix common issues


npx migrationpilot analyze migrations/*.sql --fix

  
  
  In CI (GitHub Action)


  
  
  Uses: mickelsamuel/migrationpilot@v1

Comparison Table

Feature	Atlas (free)	Squawk	MigrationPilot
Free CI linting	No (since v0.38)	Yes	Yes
PostgreSQL rules	Basic only (PG-specific paywalled)	32	77 free, 3 paid
Lock classification	Partial	No	Yes
Auto-fix	No	No	12 rules
Risk scoring	No	No	Yes
GitHub Action	Paid only	Free	Free
VS Code extension	Yes	Yes	Yes
Multi-database	Yes (15+)	PostgreSQL only	PostgreSQL only
Community size	~8,100 stars	~1,000 stars	New
License	Apache 2.0 (CE)	Apache 2.0	MIT

Migration Guide: Switching from Atlas Lint

If you were using atlas migrate lint in your CI pipeline, here is how to switch to a free alternative. The examples below use MigrationPilot, but the same concept applies to Squawk.

GitHub Actions

Replace your Atlas lint workflow step:

# Before: Atlas (now requires paid Pro plan)


uses: ariga/atlas-action/migrate/lint@v1
with:
dir: file://migrations
dev-url: "sqlite://dev?mode=memory"


  
  
  After: MigrationPilot (free)



uses: mickelsamuel/migrationpilot@v1
with:
paths: migrations/*.sql
fail-on: critical

Local development

# Before: Atlas
atlas migrate lint --dir file://migrations --dev-url "postgres://..."


  
  
  After: MigrationPilot


npx migrationpilot analyze migrations/*.sql

  
  
  Or install globally


npm install -g migrationpilot

migrationpilot analyze migrations/*.sql

Rule mapping: Atlas to MigrationPilot

Here is how Atlas's PostgreSQL-specific rules map to MigrationPilot rules:

Atlas Rule	What It Checks	MigrationPilot Equivalent
PG301	Non-nullable column without default	MP002
PG302	Volatile default (table rewrite)	MP003
PG303	Column type change (table rewrite)	MP007
PG304	Adding primary key to existing table	MP027
PG305	Adding unique constraint	MP027
PG307	Non-concurrent index creation	MP001
PG311	Exclusion constraint (table rewrite)	MP027

MigrationPilot covers all of Atlas's PostgreSQL-specific rules, plus 73 additional checks that Atlas does not have — including foreign key lock analysis, lock_timeout enforcement, VACUUM FULL detection, enum type safety, trigger cascade analysis, and more.

When You Should Still Use Atlas

This is not an anti-Atlas article. Atlas is a good product with capabilities that go beyond linting:

Multi-database support : If you run MySQL, MariaDB, SQLite, and PostgreSQL, Atlas handles all of them. Squawk and MigrationPilot are PostgreSQL-only.
Schema-as-code : Atlas's declarative HCL schema and drift detection are features that linters don't provide.
Managed migration planning : Atlas can generate migration files from schema diffs, which is a migration runner feature, not a linter feature.
Enterprise features : Audit trails, access control, and deployment orchestration for larger teams.

If your team needs a full migration management platform and can justify the cost, Atlas Pro is still a strong choice. The alternatives listed here are specifically for teams that need free, open-source migration linting in CI.

The Bigger Question: Why Lint Migrations at All?

If you are considering dropping migration linting from your CI pipeline rather than switching tools, consider the cost of not linting:

GoCardless had a 15-second API outage from a foreign key constraint that locked two tables simultaneously.
Resend suffered a 12-hour outage from a production DROP operation.
Every CREATE INDEX without CONCURRENTLY on a table with more than a few thousand rows blocks all writes until the index build completes.
Every ALTER TABLE ... SET NOT NULL without the CHECK constraint pattern scans the entire table under an ACCESS EXCLUSIVE lock.

These are not theoretical risks. They are recurring patterns that cause production incidents at companies of every size. A migration linter in CI is the cheapest insurance against these issues — it catches the mistake before it reaches production, not after.

Summary

Atlas lint is no longer free as of v0.38 (October 2025). The Community Edition has basic linting but not the PostgreSQL-specific rules.
Squawk is the most established free alternative with 32 rules and ~600K downloads/month.
strong_migrations is excellent if you use Rails.
Eugene has a unique dynamic tracing approach but is early-stage.
MigrationPilot has the most rules (77 free) with lock classification and auto-fix, but is newer and less battle-tested.
All four free tools have GitHub Actions or CI integrations.
Pick the one that fits your stack. If you use Rails, use strong_migrations. If you want the established option, use Squawk. If you want the most coverage, try MigrationPilot.

Squawk vs MigrationPilot: PostgreSQL Migration Linters Compared

Mickel Samuel — Mon, 02 Mar 2026 14:05:03 +0000

Squawk and MigrationPilot are both open-source PostgreSQL migration linters. Both analyze SQL files for dangerous operations and both integrate with CI. But they differ significantly in approach, rule depth, and what information they give you. This comparison is published on the MigrationPilot blog — take that into account and verify claims against the source code of both tools.

Quick Comparison

Feature	Squawk	MigrationPilot
Language	Rust	TypeScript
Safety rules	32	80 (77 free, 3 paid)
Lock type classification	No	Yes
Risk scoring	No	RED/YELLOW/GREEN
Auto-fix	No	12 rules
GitHub Action	Yes	Yes (with PR comments)
VS Code extension	Yes	Yes
Config file	.squawk.toml	.migrationpilotrc.yml
Output formats	Text, TSV, JSON	CLI, JSON, SARIF, Markdown
Production context	No	Yes (paid, 3 rules)
Community	~1,000 stars, 600K downloads/mo	New project
License	Apache 2.0	MIT
Price	Free (100%)	Free (97% of rules), $19/mo for production context

Where Squawk Is Better

Let's start with where Squawk wins. Being honest about this matters more than marketing.

1. Speed

Squawk is written in Rust and is fast. For typical migration files (1-50 statements), both tools complete in under a second, so this doesn't matter in practice. But if you have very large migration files (thousands of statements), Squawk's Rust parser will be noticeably faster than MigrationPilot's TypeScript + WASM parser.

2. Maturity and community

Squawk has ~1,000 GitHub stars and ~600,000 downloads per month. It has been used in production by many teams for years. MigrationPilot is a new project with a small community. If you value battle-tested stability and want a tool that has already encountered and handled edge cases in the wild, Squawk has a significant advantage.

3. 100% free

Squawk is completely free with no paid tier. Every feature is available to everyone. MigrationPilot has 3 rules (out of 80) that require a $19/month subscription. While 97% of MigrationPilot's rules are free, Squawk's fully-free model is simpler and carries no risk of future paywall expansion.

Where MigrationPilot Is Better

1. Rule depth (80 vs 32)

MigrationPilot has 80 safety rules compared to Squawk's 32. The additional 48 rules cover:

Data safety: TRUNCATE TABLE, DROP CASCADE, unvalidated enum changes
Best practices: VARCHAR vs TEXT, TIMESTAMP vs TIMESTAMPTZ, missing IF NOT EXISTS
Lock safety: VACUUM FULL, LOCK TABLE, DISABLE TRIGGER, CLUSTER
Partition safety: partition key in PK, detach partition concurrently
Extension safety: PostGIS spatial indexes, pgvector HNSW vs IVFFlat
Transaction safety: uncommitted transactions, concurrent ops in transactions

The core rules overlap substantially — both tools catch CREATE INDEX without CONCURRENTLY, volatile defaults, and column type changes. The difference is in the long tail: patterns that are less common but equally dangerous when they occur.

2. Lock type classification

When MigrationPilot flags a dangerous statement, it tells you which lock the statement acquires. This is important because different locks have different impacts:

# MigrationPilot output
  MP001 [critical] CREATE INDEX without CONCURRENTLY
    Acquires: SHARE lock (blocks writes, allows reads)
    Table: orders
    Safe alternative:
      CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

# Squawk output
migrations/001.sql:1:1: warning: prefer-create-index-concurrently
  Instead of `CREATE INDEX`, use `CREATE INDEX CONCURRENTLY`.

Squawk tells you the operation is dangerous and suggests the fix. MigrationPilot also tells you the specific lock type (SHARE, ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE, etc.), which helps you understand the actual impact on running queries. A SHARE lock blocks writes but allows reads; an ACCESS EXCLUSIVE lock blocks everything.

3. Auto-fix

MigrationPilot can automatically fix 12 rule violations:

# Preview what would change
npx migrationpilot analyze migration.sql --fix --dry-run

# Apply fixes directly
npx migrationpilot analyze migration.sql --fix

# Example: MP001 auto-fix
# Before: CREATE INDEX idx_orders_customer ON orders (customer_id);
# After:  CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

Squawk does not have auto-fix. It reports the violation and links to documentation, but you make the change manually. For the 12 rules MigrationPilot can auto-fix, the fix is a deterministic text transformation — not a heuristic guess.

4. Risk scoring and prioritization

MigrationPilot assigns a risk score to each migration: RED (critical — likely to cause outage), YELLOW (warning — may cause issues), GREEN (safe). This helps teams with many migration files prioritize which ones to fix first.

Squawk reports violations with severity levels but does not provide an aggregate risk score for the migration as a whole.

5. Multiple output formats

MigrationPilot outputs in four formats:

CLI : Colored terminal output with lock types, timing, and risk scores
JSON : Structured, versioned schema for programmatic consumption
SARIF : For GitHub Code Scanning and IDE integration
Markdown : For documentation, wikis, or PR comments

SARIF output is particularly useful — it integrates with GitHub's Code Scanning to show violations directly in the "Files changed" tab of pull requests. Squawk outputs in text, TSV, and JSON formats.

Rule Overlap

Most of Squawk's 32 rules have equivalents in MigrationPilot. Here are the key mappings:

Check	Squawk	MigrationPilot
CREATE INDEX without CONCURRENTLY	prefer-create-index-concurrently	MP001
NOT NULL without default	adding-not-nullable-field	MP002
Volatile default value	adding-field-with-default	MP003
Column type change	changing-column-type	MP007
SET NOT NULL	adding-required-field	MP018
FK without NOT VALID	adding-foreign-key-constraint	MP005
DROP INDEX without CONCURRENTLY	prefer-drop-index-concurrently	MP009
SERIAL vs IDENTITY	prefer-identity	MP015

The overlap confirms that both tools catch the most critical migration safety issues. The difference is in coverage breadth: MigrationPilot has 48 additional rules for patterns that Squawk does not check.

CI Integration

Both tools provide GitHub Actions. Here is how they look in a workflow:

Squawk GitHub Action

- uses: sbdchd/squawk-action@v1
  with:
    pattern: "migrations/*.sql"

MigrationPilot GitHub Action

- uses: mickelsamuel/migrationpilot@v1
  with:
    paths: "migrations/*.sql"
    fail-on: critical

Both actions post comments on pull requests with the violations found. MigrationPilot additionally provides inline annotations in the "Files changed" tab and a Job Summary with aggregate metrics. Squawk's action is simpler and more focused.

Known Limitations

Squawk's known issues

Cannot analyze PL/pgSQL function bodies or DO blocks (GitHub issues #411, #528)
False positives in some valid contexts (#937, #973)
Exit code 1 for both errors and warnings — no way to distinguish (#348)
No cross-file validation or schema-level context
Pre-commit integration has been broken since June 2024 (#363)

MigrationPilot's known issues

New project — fewer real-world battle-testing hours
TypeScript is slower than Rust on very large files (sub-second for typical use)
Small community — fewer contributors finding and fixing edge cases
3 rules behind a paywall ($19/month for production context analysis)
No dynamic lock tracing (purely static analysis, like Squawk)

When to Use Squawk

You want a proven, battle-tested tool. Squawk has years of production usage and a larger community. If stability and track record matter most, use Squawk.
You want 100% free with zero paid components. Squawk is fully free with no paid tier.
You need Rust-native speed. If you lint thousands of migration files in CI, Squawk's Rust parser is faster.
You want minimal dependencies. Squawk is a single binary. MigrationPilot requires Node.js.

When to Use MigrationPilot

You want the most comprehensive rule set. 80 rules cover patterns that Squawk's 32 rules do not check.
You want lock type information. Knowing which lock a statement acquires helps you assess real-world impact.
You want auto-fix. 12 rules can be automatically fixed with --fix.
You want SARIF output for GitHub Code Scanning. Violations appear directly in PR diffs.
You need production context analysis. Table size and query impact awareness (paid tier) is unique to MigrationPilot.
You want config presets. Five built-in presets (recommended, strict, ci, startup, enterprise) for different deployment contexts.

Can You Use Both?

Yes. Both tools analyze plain SQL files and can run independently in CI. Running both gives you the union of their rule sets and two independent opinions on each migration. The overhead is minimal — both complete in under a second for typical migrations.

# Run both in CI


name: Squawk lint

uses: sbdchd/squawk-action@v1

with:

pattern: "migrations/*.sql"
name: MigrationPilot lint

uses: mickelsamuel/migrationpilot@v1

with:

paths: "migrations/*.sql"

fail-on: critical

Summary

Squawk is the established choice: proven, fast, 100% free, and sufficient for catching the most common PostgreSQL migration hazards.
MigrationPilot offers more coverage: 80 vs 32 rules, lock type classification, auto-fix, risk scoring, and SARIF output. But it's newer and less battle-tested.
Both tools catch the critical issues (missing CONCURRENTLY, volatile defaults, column type changes, SET NOT NULL).
The core rules overlap significantly. The difference is in the long tail of less common but still dangerous patterns.
If stability and simplicity matter most, use Squawk. If coverage and features matter most, try MigrationPilot. If you want maximum safety, use both.