# Zero-Downtime Postgres Migrations

`postgres/zero-downtime-migrations` · Skill · v1.0.0

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.

## Verdict

- level: **submitted** — In the catalog, not yet checked for this digest.
- uplift: **untested** — No runs exist for this digest.
- digest: `sha256:291e6ec3f80da4966a97ed91e83297f9c503fd6aae3cbd183f0a27967b9655a8`

## Uplift runs

None for this digest. Untested is not the same as useless: nobody has measured it yet.

## Security

No checks recorded for this digest.

## Compatibility

| agent | targeted | compiled | evaluated |
| --- | --- | --- | --- |
| Claude Code | yes | no record | no record |
| Codex | yes | no record | no record |
| Cursor | yes | no record | no record |
| GitHub Copilot | yes | no record | no record |
| Gemini CLI | yes | no record | no record |
| OpenCode | yes | no record | no record |

## Permissions

- network: none
- shell: psql
- filesystem: write
- env: none

## Files

- `SKILL.md` 18194 B sha256:271abcf80499d926555b8d2fc614cc23f31507a0e1266addef701ef540652c7d
- `references/lock-matrix.md` 5672 B sha256:7e77e97ac14efa4ba2166c93310c3790fdebc53fad1d88ec0a7d32ab7b42efab
- `references/recipes.md` 9472 B sha256:07a0285c84ffc19b6d41f0b4af5b2603e084e83c4a4e2361f49115e82325bdda
- `references/runners.md` 4203 B sha256:c7501fd4cdb36b4882987e8fe485e9e3c6efc0b2c814b2ab136fc8f526ecaafb
- `references/sources.md` 4575 B sha256:35bc2937c3d5f9b762f89b1d9aa250ec53864afaf6ed548aea61c97d6825bebd

## 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.

## Install

```sh
npx sigil add postgres/zero-downtime-migrations --agent claude-code,codex,cursor
```

Source: https://github.com/dgesteves/sigil/tree/5cb851d5b2f2f14446ddf631ef5e91bf9d3263dd/catalog/skills/postgres/zero-downtime-migrations