Writing safe migrations
Skill pumarogie/claude-postgres-skills/skills/writing-safe-migrations
Guides Postgres migrations when altering large or live tables, adding indexes or constraints, backfilling columns, changing column types, or planning zero-downtime rollouts. Covers lock levels, lock_timeout, CREATE INDEX CONCURRENTLY, NOT VALID constraints, and expand-and-contract.From its SKILL.md
npx -y skills add pumarogie/claude-postgres-skills --skill writing-safe-migrationsAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- 2 stars2 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.
SKILL.md
6.8 KB, as published. Nobody here has run it
Writing Safe Migrations
Overview
Two questions before any migration runs against a live table:
- Does this rewrite the table? A rewrite holds
ACCESS EXCLUSIVEfor the whole rewrite — every read and write blocks. - Can it wait for its lock without taking the table down? A statement waiting for
ACCESS EXCLUSIVEqueues ahead of every query that arrives after it. One long-runningSELECTplus one unguardedALTER TABLEstalls the entire table, even for statements the migration itself would never have blocked.
Question 2 causes more outages than question 1, and lock_timeout is the whole fix.
When to Use
- Adding an index to an existing large table.
ALTER TABLE, adding or dropping columns, changing types, adding constraints.- Backfilling a column across many rows.
- Any migration on a table with meaningful traffic.
Always set a lock timeout
Never issue DDL against a live table without bounding the wait:
SET lock_timeout = '5s'; -- fail instead of building a lock queue
SET statement_timeout = '0'; -- but let a long index build finish
ALTER TABLE tasks ADD COLUMN priority int;
If the lock isn't acquired in 5s the statement errors — retry it later. That is the correct outcome: a failed migration is recoverable, a stalled table is an incident.
lock_timeout only bounds acquiring a lock, not holding one. It will not save you from a rewrite that takes ten minutes once it starts — check the lock table for that.
Find what's blocking you:
SELECT pid, pg_blocking_pids(pid), wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
Quick Reference
| Operation | Danger | Safe way |
|---|---|---|
CREATE INDEX | Blocks writes for the whole build | CREATE INDEX CONCURRENTLY, outside a transaction |
| Add check / FK constraint | Validation scan blocks writes | NOT VALID, then VALIDATE CONSTRAINT |
SET NOT NULL | Full scan blocks reads and writes | Add an equivalent CHECK (col IS NOT NULL) NOT VALID, validate, then SET NOT NULL |
ALTER COLUMN TYPE | Usually a full rewrite | New column + backfill + swap (expand-and-contract) |
Backfill in one UPDATE | Long transaction, bloat, blocked autovacuum | Batch in separate transactions |
| Dropping columns | Hard to roll back | Keep migrations additive; expand-and-contract |
Full per-operation lock levels and which operations rewrite: reference/lock-levels.md.
Patterns
-- Index without blocking writes. Cannot run inside a transaction block,
-- and do NOT set statement_timeout here — the build may be long.
SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_tasks_tenant ON tasks (tenant_id);
Never wrap CREATE INDEX CONCURRENTLY in a transaction block: PostgreSQL rejects it. Run it as a top-level statement. Do not set a short statement_timeout; a healthy build on a large table may take a long time.
A failed CREATE INDEX CONCURRENTLY leaves an invalid index behind. It is not automatically cleaned up, it consumes writes and disk, and the planner won't use it. Always check before retrying:
SELECT c.relname
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;
DROP INDEX CONCURRENTLY idx_tasks_tenant; -- then retry the create
-- Constraint without a write-blocking full-table scan
ALTER TABLE tasks ADD CONSTRAINT chk_status
CHECK (status IN ('pending','running','done')) NOT VALID;
-- Validate separately: SHARE UPDATE EXCLUSIVE, does not block reads or writes
ALTER TABLE tasks VALIDATE CONSTRAINT chk_status;
-- Batched backfill: one transaction per chunk, not one for the whole table
UPDATE tasks SET priority = 0
WHERE id IN (
SELECT id FROM tasks WHERE priority IS NULL LIMIT 5000
);
-- commit, sleep briefly, repeat until zero rows updated
Large column-type change
Never use one in-place ALTER COLUMN TYPE for a large live table when it rewrites the table. Changes such as integer to bigint rewrite all rows and rebuild dependent indexes while ACCESS EXCLUSIVE is held. Use expand-and-contract across separate deploys:
- Add the new column with
lock_timeoutset. - Dual-write old and new columns, then backfill bounded key ranges in separate transactions.
- Build replacement indexes with
CREATE INDEX CONCURRENTLY; add replacement constraints withNOT VALIDand validate separately where supported. - Switch reads, stop writing the old column, verify, and only then remove old objects in a later deploy.
Rules
- Bound every lock.
SET lock_timeoutbefore DDL, then retry on failure. - Additive migrations. Prefer adding over removing. For unavoidable changes use expand-and-contract across separate deploys: add new → backfill → switch reads/writes → drop old.
- Adding a column with a default is cheap in PostgreSQL 11+ — a non-volatile default is stored as metadata and does not rewrite the table. Do not backfill merely to materialize that value. A volatile default (
gen_random_uuid(),random()) does rewrite it; add the column nullable and backfill in batches instead. - Short transactions. Every
UPDATEholds row locks until commit, and a long transaction holds back the oldest-transaction horizon so autovacuum cannot reclaim dead tuples (seetuning-autovacuum-and-bloat). - No external calls mid-transaction. Don't hold a transaction open across an HTTP/RPC round trip — the locks stay held for its full duration.
- Wrap in a transaction where possible so failure rolls back cleanly.
CREATE INDEX CONCURRENTLYandDROP INDEX CONCURRENTLYcannot run inside one.
Common Mistakes
- DDL with no
lock_timeout— the statement queues forACCESS EXCLUSIVEand every query behind it queues too, taking the table down without ever acquiring the lock. CREATE INDEXwithoutCONCURRENTLYon a big live table — blocks writes for the whole build.- Retrying a failed
CREATE INDEX CONCURRENTLYwithout dropping the invalid index it left behind. - Adding a check or FK constraint without
NOT VALID— the validation scan blocks writes. SET NOT NULLdirectly on a large table — full scan underACCESS EXCLUSIVE.- Backfilling a whole large table in one
UPDATE— long transaction, mass dead tuples, autovacuum stalled behind it. - A transaction that makes an external API call while holding row locks.
- Destructive column drops in the same release as the code change — no clean rollback.
What ships with it: 1 file
5.7 KB alongside SKILL.md
reference/
- lock-levels.md5.7 KB
Gives 0 of the 12 instructions most docs writing skills give
Counted across 1,951 of the 3,904 authors here whose files we hold, read 2026-09-06
- Use third-person for skill descriptionsin 54 of 1951, across 35 files
- Start descriptions with Use whenin 43 of 1951, across 29 files
- Run baseline scenarios before writing any skillin 40 of 1951, across 26 files
- Use active voicein 40 of 1951, across 36 files
- Map file responsibilities before defining tasksin 36 of 1951, across 29 files
- Use checkbox syntax for tracking stepsin 35 of 1951, across 27 files
- Ask one question at a timein 35 of 1951
- Offer execution options after saving the planin 33 of 1951, across 24 files
- Include complete code in every stepin 33 of 1951, across 27 files
- Design units with clear boundaries and interfacesin 31 of 1951, across 23 files
- Announce the skill usage at the startin 30 of 1951
- Verify agent compliance after adding the skillin 29 of 1951, across 17 files
Said here and by no other author read
- create indexes concurrently on live tables
- set lock_timeout before issuing any DDL
- retry DDL statements if lock acquisition fails
- run CREATE INDEX CONCURRENTLY outside transaction blocks
- use NOT VALID for adding constraints
- validate constraints in a separate step
Grouped from the skills themselves: near-identical wordings counted once, and counted by distinct author, so one author publishing three of these counts once.