How do I install the Postgres Migrations skill?

Run npx skillmds@latest add mine-fnl/postgres-migrations in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.

Is Postgres Migrations safe to use?

This skill has not completed SkillMD's automated safety review yet. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.

Which AI agents work with Postgres Migrations?

This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.

Is Postgres Migrations free to use?

Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.

Who published Postgres Migrations?

Mine-FNL (@mine-fnl) published this skill. Their other Agent Skills are listed on their SkillMD profile.

Postgres Migrations by mine-fnl — Agent Skill

Name: Postgres Migrations
Author: Mine-FNL

Postgres Migrations

Use when authoring or reviewing a PostgreSQL schema migration that runs against a live production database. Applies when the user asks for "add a column", "add an index", "backfill", "ALTER TABLE safely", or "zero-downtime migration". Do not use for application-level ORM migrations on a dev database, for greenfield schemas with no traffic, or for non-PostgreSQL databases (MySQL, SQLite).

Mine-FNL Updated Sep 21, 2026

File contents

When to use

Use when a schema change has to roll forward against a database that already carries traffic. The cardinal rule is that every migration is two migrations: the expand step that adds the new shape in parallel with the old, and the contract step that removes the old shape once every caller has moved over. In between, you backfill, you dual-write, and you read from both sides.

Always think about the lock each statement takes before issuing it:

ALTER TABLE ... ADD COLUMN (nullable, no default) → AccessExclusive briefly, but metadata-only since PostgreSQL 11.
CREATE INDEX (without CONCURRENTLY) → blocks writes for the duration of the build.
ALTER TABLE ... ALTER COLUMN ... SET NOT NULL → scans and rewrites the whole table; plan a separate expand step.
ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY → takes a ShareRowExclusive lock that queues behind every write.

Examples

A safe three-step rollout for adding a status column with a NOT NULL default to a 100M-row table:

-- 1. Expand: nullable, no default. Cheap metadata-only on PG ≥ 11.
ALTER TABLE orders ADD COLUMN status text;

-- 2. Backfill in batches so no single transaction holds the row lock
--    long enough to stall writes.
UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN $1 AND $2;

-- 3. Contract: flip to NOT NULL only when every row is populated.
ALTER TABLE ALTER COLUMN status SET NOT NULL;

For example, building the supporting index without blocking writes:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);

The CONCURRENTLY keyword lets the index build take two passes over the table while reads and writes proceed normally. It cannot run inside an explicit transaction block.

Pitfalls to avoid

Do not run CREATE INDEX (without CONCURRENTLY) on a hot table; the build will queue every write behind the lock and look like an outage.
Do not ALTER TABLE ... ALTER COLUMN ... TYPE in place on a wide table; the rewrite takes a long lock. Add a new column, dual-write, backfill, swap, drop.
Do not backfill in one transaction; the row lock and the WAL volume will stall replication. Always chunk by id or created_at and commit per batch.
Do not forget to commit between steps; a half-applied expand that fails between deploys leaves the schema inconsistent across rolling restarts.
Do not name constraints without an explicit identifier; PostgreSQL auto-generates long names that are awkward to reference in the contract migration.

---name: postgres-migrationsdescription: Use when authoring or reviewing a PostgreSQL schema migration that runs against a live production database. Applies when the user asks for "add a column", "add an index", "backfill", "ALTER TABLE safely", or "zero-downtime migration". Do not use for application-level ORM migrations on a dev database, for greenfield schemas with no traffic, or for non-PostgreSQL databases (MySQL, SQLite).---## When to useUse when a schema change has to roll forward against a database thatalready carries traffic. The cardinal rule is that every migration istwo migrations: the *expand* step that adds the new shape in parallelwith the old, and the *contract* step that removes the old shape onceevery caller has moved over. In between, you backfill, you dual-write,and you read from both sides.Always think about the lock each statement takes before issuing it:- `ALTER TABLE ... ADD COLUMN` (nullable, no default) → `AccessExclusive` briefly, but metadata-only since PostgreSQL 11.- `CREATE INDEX` (without `CONCURRENTLY`) → blocks writes for the duration of the build.- `ALTER TABLE ... ALTER COLUMN ... SET NOT NULL` → scans and rewrites the whole table; plan a separate expand step.- `ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY` → takes a `ShareRowExclusive` lock that queues behind every write.## ExamplesA safe three-step rollout for adding a `status` column with a NOT NULLdefault to a 100M-row table:```sql-- 1. Expand: nullable, no default. Cheap metadata-only on PG ≥ 11.ALTER TABLE orders ADD COLUMN status text;-- 2. Backfill in batches so no single transaction holds the row lock-- long enough to stall writes.UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN $1 AND $2;-- 3. Contract: flip to NOT NULL only when every row is populated.ALTER TABLE ALTER COLUMN status SET NOT NULL;```For example, building the supporting index without blocking writes:```sqlCREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);```The `CONCURRENTLY` keyword lets the index build take two passes over thetable while reads and writes proceed normally. It cannot run inside anexplicit transaction block.## Pitfalls to avoid- Do not run `CREATE INDEX` (without `CONCURRENTLY`) on a hot table; the build will queue every write behind the lock and look like an outage.- Do not `ALTER TABLE ... ALTER COLUMN ... TYPE` in place on a wide table; the rewrite takes a long lock. Add a new column, dual-write, backfill, swap, drop.- Do not backfill in one transaction; the row lock and the WAL volume will stall replication. Always chunk by `id` or `created_at` and commit per batch.- Do not forget to commit between steps; a half-applied expand that fails between deploys leaves the schema inconsistent across rolling restarts.- Do not name constraints without an explicit identifier; PostgreSQL auto-generates long names that are awkward to reference in the contract migration.

Frequently asked questions