/postgres-database-migration

name: postgres-database-migration
description: |
  Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases.

  **Trigger when user asks to:**
  - Test a schema migration before applying it to production
  - Add, remove, or rename columns safely on a live table
  - Change a column's data type without downtime
  - Add or drop indexes, constraints, or foreign keys on large tables
  - Understand which ALTER TABLE operations lock the table
  - Roll back a failed migration
  - Plan a zero-downtime migration strategy
  - Fork a database to test a migration safely

  **Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, rollback, backfill, deploy

  Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.

PostgreSQL Database Migrations

A schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely.

DDL Lock Reference

Every schema change acquires a lock. The critical question is: **does it block reads and writes, and for how long?**

Fast, Non-Blocking Operations

These complete in milliseconds regardless of table size. They only hold a brief `AccessExclusiveLock` for the catalog update, not for data rewriting.

| Operation | Lock Level | Notes | |-----------|-----------|-------| | `ADD COLUMN` (nullable, no default) | `AccessExclusiveLock` (brief) | **Fast.** No table rewrite. Metadata-only change. | | `ADD COLUMN ... DEFAULT x` (PG 11+) | `AccessExclusiveLock` (brief) | **Fast.** Non-volatile defaults stored in catalog, not backfilled. | | `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. | | `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. | | `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. | | `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. | | `RENAME COLUMN` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. | | `RENAME TABLE` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. | | `ADD CONSTRAINT ... NOT VALID` | `ShareUpdateExclusiveLock` | Adds constraint for new rows only. Does not scan existing data. | | `VALIDATE CONSTRAINT` | `ShareUpdateExclusiveLock` | Scans existing rows but allows concurrent reads and writes. | | `CREATE/DROP TRIGGER` | `ShareRowExclusiveLock` | Brief catalog update. |

Slow or Blocking Operations

These rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours.

| Operation | Lock Level | Why It's Slow | |-----------|-----------|---------------| | `ADD COLUMN ... DEFAULT x` (volatile, e.g. `now()`, `gen_random_uuid()`) | `AccessExclusiveLock` | Full table rewrite. Every row gets the computed value. | | `ALTER COLUMN TYPE` (most type changes) | `AccessExclusiveLock` | Full table rewrite to convert stored data. | | `SET NOT NULL` (PG < 12, or without existing CHECK) | `AccessExclusiveLock` | Full table scan to verify no NULLs. See safe pattern below. | | `ADD CONSTRAINT ... CHECK/UNIQUE/FK` (validated) | `AccessExclusiveLock` or `ShareRowExclusiveLock` | Scans all rows to verify, blocks writes. | | `CREATE INDEX` (without CONCURRENTLY) | `ShareLock` | Blocks writes for the entire build duration. | | `CLUSTER` | `AccessExclusiveLock` | Rewrites entire table in index order. | | `VACUUM FULL` | `AccessExclusiveLock` | Rewrites table to reclaim space. |

**Key insight:** `AccessExclusiveLock` blocks everything — reads and writes. Even if the operation itself is fast (milliseconds), it must wait for all in-flight transactions to finish before acquiring the lock. A long-running query or idle transaction can cause an `ALTER TABLE` to hang and queue up all subsequent queries behind it.

Safe Migration Patterns

Add a Column

-- SAFE: nullable column, no default — instant
ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- SAFE (PG 11+): column with non-volatile default — instant
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;

-- UNSAFE: column with volatile default — full table rewrite
-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();
-- DO: add nullable, then backfill, then set default + NOT NULL
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;
-- Backfill in batches (see Backfill section)
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();
ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL;  -- only if PG12+ or CHECK exists

Drop a Column

-- SAFE: instant (column marked invisible, space reclaimed by VACUUM)
ALTER TABLE orders DROP COLUMN old_status;

**Application coordination:** Ensure your application no longer references the column before dropping it. For zero-downtime deploys, this requires two steps: 1. Deploy code that doesn't read/write the column 2. Then drop the column in a separate migration

**Security caveat:** `DROP COLUMN` does not physically delete the data. The column is marked as dropped in `pg_attribute` but the values remain on disk until `VACUUM` reclaims the space — and even then, a superuser could recover them. If the column contains sensitive data, run `VACUUM FULL` on the table after dropping, or use dump/restore to ensure the data is truly go