Skip to content
Skills Library
Programming · Building & coding

postgres-safe-migration

/postgres-safe-migration

Use when writing a migration for a table that holds data, when changing a column, adding a required column, an index or a constraint, or when a pg- check refuses a migration.

Licence
MIT
Last repository change
11.11.2026

Assessment and security

96/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content31 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 11.11.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Use when writing a migration for a table that holds data, when changing a column, adding a required column, an index or a constraint, or when a pg- check refuses a migration.

Original author description

Use when writing a migration for a table that holds data, when changing a column, adding a required column, an index or a constraint, or when a pg- check refuses a migration.

How it works

SKILL.md has no level-two headings · 42 lines, 388 words

SKILL.md preview
---
name: postgres-safe-migration
description: Use when writing a migration for a table that holds data, when changing a column, adding a required column, an index or a constraint, or when a pg- check refuses a migration.
---

# Change a live Postgres table (one job: the change lands, and no query waits for it)

<constraints>
Always do:
1. Find out how big the table is and how busy, or ask. A new or empty table needs none of this.
2. Find out how the migration tool leaves a transaction, before writing `CONCURRENTLY`. Each has its own way:
   | Tool | How |
   |---|---|
   | Rails | `disable_ddl_transaction!` |
   | Django | `atomic = False` |
   | Diesel | `run_in_transaction = false` in `metadata.toml` |
   | goose | `-- +goose NO TRANSACTION` |
   | dbmate | `-- migrate:up transaction:false` |
   | sqlx | `-- no-transaction` on the first line |
   | Alembic | `autocommit_block()` |
   | Knex | `exports.config = { transaction: false }` |
   | node-pg-migrate | `pgm.noTransaction()` |

   Drizzle runs every pending migration in one transaction: take the index out of its migrations and run it apart.
3. Add a required column:
   ```sql
   ALTER TABLE t ADD COLUMN owner_id bigint;              -- 1. nullable, instant
   -- 2. the code writes it, and a backfill runs in batches
   ALTER TABLE t ADD CONSTRAINT t_owner CHECK (owner_id IS NOT NULL) NOT VALID;
   ALTER TABLE t VALIDATE CONSTRAINT t_owner;             -- 3. a later transaction, no write lock
   ALTER TABLE t ALTER COLUMN owner_id SET NOT NULL;      -- 4. the valid check skips the scan
   ALTER TABLE t DROP CONSTRAINT t_owner;
   ```
4. Add a foreign key: `ADD CONSTRAINT ... NOT VALID`, then `VALIDATE CONSTRAINT` in a later transaction. In one transaction it gains nothing.
5. Add a unique constraint: `CREATE UNIQUE INDEX CONCURRENTLY`, then `ADD CONSTRAINT ... UNIQUE USING INDEX`.
6. Change a type: check first whether it rewrites. `varchar` to `text` does not. Most others do: add a new column, write both, backfill, switch reads, drop the old one.
7. A failed `CONCURRENTLY` leaves an invalid index: drop it, then build it again.
8. Set a short `lock_timeout` on a statement that takes a strong lock, and retry it.
9. Deploy the code that can read both shapes first, and drop what is old only after nothing reads it.

Never do:
- Backfill a large table in one `UPDATE`.
- Rename a column in one step while old code still runs.
- Refuse `now()` or a constant default as unsafe: since PostgreSQL 11 both are instant.
</constraints>

Versions

The database has no other skills named postgres-safe-migration.

Database snapshot: 13.11.2026 · package version 52653d08894a (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 95db-migration
    Rules for database schema changes. Use for any new table, column, index, constraint, enum or trigger, any data backfill, and any migration file.
  • 87postgres-migration
    Change the Postgres schema without breaking the previous release, which keeps serving on the new schema: the expand/contract recipes for adding a column or table, removing one, renaming one, changing its type, making it NOT NULL, adding an index or a unique constraint and…
  • 100postgres-safe-migrations
    Use when writing or reviewing a Postgres schema migration for a live app — adding columns, indexes, constraints, renames or drops without locking tables or breaking running code. Triggers on any new migration file, schema change, or "add a column/index" request.
  • 100alembic-safe-migrations
    Write and review Alembic migrations that will not lock up a production PostgreSQL database. Use whenever a migration adds an index, adds a NOT NULL column, changes a column type, adds a foreign key or unique constraint, backfills data, renames anything, or touches a table that…
  • 100migration-helper
    Write safe database schema and data migrations with rollback steps, lock and timeout checks, and an expand-and-contract plan for zero-downtime deploys. Use when the user asks to add, rename or drop a column or table, add an index or constraint, backfill data, or review a…
  • 100add-migration
    Add or change a Postgres schema migration. Use when a feature needs a new table, column or index, or when running `npm run migrate`.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.