# Postgres Migrations

> Apply schema changes to a live PostgreSQL database without taking downtime or locking out writers.

- Skill: `jaycheng113/postgres-migrations` (Agent Skill)
- Install (CLI): `npx skillmds@latest add jaycheng113/postgres-migrations`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jaycheng113/postgres-migrations/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: JayCheng113 (https://skillmd.com/u/jaycheng113)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/jaycheng113/postgres-migrations

---


# PostgreSQL migrations without downtime

Adding a `NOT NULL` column to a large table rewrites it under an `ACCESS EXCLUSIVE`
lock, which blocks every reader and writer for the duration. Split it instead:

1. Add the column as nullable with no default.
2. Backfill in batches, committing between batches so the lock is never held long.
3. Add a `NOT VALID` check constraint, then `VALIDATE CONSTRAINT` separately —
   validation takes only a `SHARE UPDATE EXCLUSIVE` lock.

Dropping a column is metadata-only and safe. Renaming one is not: the old and new
name cannot both be live, so deploy a read-both/write-both shim first.

Always set `lock_timeout` before DDL. Without it a migration that cannot acquire
its lock will queue behind a long transaction and block everything behind it too.