Skill · postgres/zero-downtime-migrations · v1.0.0 · MIT
Zero-Downtime Postgres Migrations
Schema changes on large, busy PostgreSQL tables without blocking reads or writes, for PostgreSQL 18 with notes for 12-17.
Teaches an agent to write and review PostgreSQL migrations that are safe on hot production tables: which lock each DDL statement takes and whether it scans or rewrites, CREATE INDEX CONCURRENTLY in non-transactional steps (and cleaning up INVALID indexes), foreign keys and CHECKs added NOT VALID and validated in a later transaction, NOT NULL via PostgreSQL 18's NOT VALID not-null constraints or a validated CHECK on 12-17, unique constraints USING INDEX, metadata-only versus rewriting column changes, batched backfills that COMMIT, lock_timeout and the lock queue, and how common migration runners (node-pg-migrate, drizzle-orm, dbmate, graphile-migrate, Django, Rails) handle transactions. Every lock and rewrite claim was measured on PostgreSQL 18.6.
- Databases
- Backend
- DevOps
- build
- ship
- operate
- postgresql
- sql
- django
- rails
- drizzle
uplift
No runs exist for this digest.
Not measured for this digest.
Untested is not the same as useless: nobody has measured it yet. Proof comes from A/B runs on hidden tests:
npx sigil prove catalog/skills/postgres/zero-downtime-migrations --suite <suite> --runs 6trust level · submitted
- in the catalog: met
- structure check: not met
- static scan, no high findings: not met
- canary sandbox passed: not met
- maintainer approved: not met
canary sandbox
Not run for this digest.
static scan
Not run for this digest.
compatibility · targeted, compiled and evaluated are separate claims
| agent | targeted by author | compiled | evaluated in the lab | notes |
|---|---|---|---|---|
| Claude Code | targeted: yes | no record for compiled | no record for evaluated | |
| Codex | targeted: yes | no record for compiled | no record for evaluated | |
| Cursor | targeted: yes | no record for compiled | no record for evaluated | |
| GitHub Copilot | targeted: yes | no record for compiled | no record for evaluated | |
| Gemini CLI | targeted: yes | no record for compiled | no record for evaluated | |
| OpenCode | targeted: yes | no record for compiled | no record for evaluated |
declared permissions
- network
- none
- shell
- psql
- filesystem
- write
- env
- none
Undeclared access is a finding in the scan and a tripwire in the canary.
source
- path
- catalog/skills/postgres/zero-downtime-migrations
- authors
- dgesteves
- entry
- SKILL.md (agent-skill)
- registry
- 2026-11-02 · 5cb851d
install
$ npx sigil add postgres/zero-downtime-migrations --agent claude-codeNo compile record for Claude Code on this digest. Install never runs capability scripts, and refuses on any digest mismatch.
known limitations
- Covers single-node PostgreSQL semantics. Citus, Aurora-specific behaviour, logical replication and tools like pg_repack or pg_osc are out of scope.
- The proof suite runs against PGlite, a single connection, so it measures locks, scans, rewrites and batching but cannot reproduce lock-queue waits; lock_timeout use is taught, not scored.
- Runner notes cover node-pg-migrate, drizzle-orm 0.45, dbmate, graphile-migrate, Django and Rails. Prisma's transaction behaviour for hand-edited SQL is not covered.
- Batched backfills inside deploy-time migrations suit tables up to tens of millions of rows; larger backfills belong in a throttled background job.
- Partitioned tables are covered for indexes only.