agentsclimarketplace

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

Install
npx -y skills add pumarogie/claude-postgres-skills --skill writing-safe-migrations

Assembled 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:

  1. Does this rewrite the table? A rewrite holds ACCESS EXCLUSIVE for the whole rewrite — every read and write blocks.
  2. Can it wait for its lock without taking the table down? A statement waiting for ACCESS EXCLUSIVE queues ahead of every query that arrives after it. One long-running SELECT plus one unguarded ALTER TABLE stalls 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

OperationDangerSafe way
CREATE INDEXBlocks writes for the whole buildCREATE INDEX CONCURRENTLY, outside a transaction
Add check / FK constraintValidation scan blocks writesNOT VALID, then VALIDATE CONSTRAINT
SET NOT NULLFull scan blocks reads and writesAdd an equivalent CHECK (col IS NOT NULL) NOT VALID, validate, then SET NOT NULL
ALTER COLUMN TYPEUsually a full rewriteNew column + backfill + swap (expand-and-contract)
Backfill in one UPDATELong transaction, bloat, blocked autovacuumBatch in separate transactions
Dropping columnsHard to roll backKeep 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:

  1. Add the new column with lock_timeout set.
  2. Dual-write old and new columns, then backfill bounded key ranges in separate transactions.
  3. Build replacement indexes with CREATE INDEX CONCURRENTLY; add replacement constraints with NOT VALID and validate separately where supported.
  4. Switch reads, stop writing the old column, verify, and only then remove old objects in a later deploy.

Rules

  • Bound every lock. SET lock_timeout before 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 UPDATE holds row locks until commit, and a long transaction holds back the oldest-transaction horizon so autovacuum cannot reclaim dead tuples (see tuning-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 CONCURRENTLY and DROP INDEX CONCURRENTLY cannot run inside one.

Common Mistakes

  • DDL with no lock_timeout — the statement queues for ACCESS EXCLUSIVE and every query behind it queues too, taking the table down without ever acquiring the lock.
  • CREATE INDEX without CONCURRENTLY on a big live table — blocks writes for the whole build.
  • Retrying a failed CREATE INDEX CONCURRENTLY without dropping the invalid index it left behind.
  • Adding a check or FK constraint without NOT VALID — the validation scan blocks writes.
  • SET NOT NULL directly on a large table — full scan under ACCESS 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/

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.

Keep looking

Skills are one crate of 613,373. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.