Skip to content
Skills Library
Data & analytics · Review & audit

pgsql-migrations

/pgsql-migrations

This skill should be used when a task writes or reviews a migration or DDL that runs against a live PostgreSQL 18 table, such as ALTER TABLE, ADD COLUMN, SET NOT NULL, ADD CONSTRAINT or a foreign key, CREATE INDEX, DROP INDEX or REINDEX on a populated or partitioned table, an INVALID index left by a failed concurrent build, a column type change, a rename or drop, ALTER TYPE ADD VALUE, a backfill, a goose or golang-migrate SQL file, lock_timeout or statement_timeout, a squawk finding, a deploy whose migration hung or blocked traffic, or a question like "is this migration safe to run in producti

Licence
MIT
Last repository change
27.10.2026

Assessment and security

100/ 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.
Content35 / 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
4
Scripts
0
HTML comments
0
URLs in SKILL.md
32
Deprecation

No signals detected. Repository changed on 27.10.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

This skill should be used when a task writes or reviews a migration or DDL that runs against a live PostgreSQL 18 table, such as ALTER TABLE, ADD COLUMN, SET NOT NULL, ADD CONSTRAINT or a foreign key, CREATE INDEX, DROP INDEX or REINDEX on a populated or partitioned table, an INVALID index left by a failed concurrent build, a column type change, a rename or drop, ALTER TYPE ADD VALUE, a backfill, a goose or golang-migrate SQL file, lock_timeout or statement_timeout, a squawk finding, a deploy whose migration hung or blocked traffic, or a question like "is this migration safe to run in producti

Original author description

This skill should be used when a task writes or reviews a migration or DDL that runs against a live PostgreSQL 18 table, such as ALTER TABLE, ADD COLUMN, SET NOT NULL, ADD CONSTRAINT or a foreign key, CREATE INDEX, DROP INDEX or REINDEX on a populated or partitioned table, an INVALID index left by a failed concurrent build, a column type change, a rename or drop, ALTER TYPE ADD VALUE, a backfill, a goose or golang-migrate SQL file, lock_timeout or statement_timeout, a squawk finding, a deploy whose migration hung or blocked traffic, or a question like "is this migration safe to run in producti

How it works

  1. Rules
  2. Read the reference
  3. Sources
Original SKILL.md headings · 44 lines, 962 words
SKILL.md preview
---
name: pgsql-migrations
description: This skill should be used when a task writes or reviews a migration or DDL that runs against a live PostgreSQL 18 table, such as ALTER TABLE, ADD COLUMN, SET NOT NULL, ADD CONSTRAINT or a foreign key, CREATE INDEX, DROP INDEX or REINDEX on a populated or partitioned table, an INVALID index left by a failed concurrent build, a column type change, a rename or drop, ALTER TYPE ADD VALUE, a backfill, a goose or golang-migrate SQL file, lock_timeout or statement_timeout, a squawk finding, a deploy whose migration hung or blocked traffic, or a question like "is this migration safe to run in production". It covers zero-downtime changes, lock levels and the lock queue, table rewrites, NOT VALID then VALIDATE CONSTRAINT, CONCURRENTLY outside a transaction, expand and contract, and batched backfills. Not for the target design (`pgsql-schema`), which index to build (`pgsql-indexing`), lock waits outside a migration (`pgsql-performance`), or installing squawk (`pgsql-lint-setup`).
---

# pgsql-migrations: zero-downtime schema changes

Scope: applying DDL to a PostgreSQL 18 table that live traffic reads and writes, so that no statement stalls that traffic or leaves the schema half-changed.

Deterministic backstop: `squawk 'migrations/*.sql'` on every migration file, with the config from `/pgsql-lint-setup`; each finding names a rule id from the [squawk rules](https://squawkhq.com/docs/rules). A clean squawk run is not a safe run: squawk cannot see whether the deploy retries the migration when its lock timeout fires ([squawk: applying migrations safely](https://squawkhq.com/docs/safe_migrations)).

## Rules

- **Set `lock_timeout` before DDL that takes `ACCESS EXCLUSIVE`, `SHARE ROW EXCLUSIVE` or `SHARE`, and retry on timeout.** A statement waiting for a lock also blocks every later request that conflicts with it ([`pg_blocking_pids`](https://www.postgresql.org/docs/18/functions-info.html#FUNCTIONS-INFO-SESSION-TABLE)). An `ALTER TABLE` queued behind one long transaction therefore stops every `SELECT` on the table. `SET lock_timeout = '1s';` makes it fail fast instead; rerun until it succeeds ([`lock_timeout`](https://www.postgresql.org/docs/18/runtime-config-client.html#GUC-LOCK-TIMEOUT)). Add a `statement_timeout` too. squawk: `require-lock-timeout`, `require-statement-timeout`.
- **Give each slow step its own transaction.** A lock is held until the transaction ends ([13.3.1](https://www.postgresql.org/docs/18/explicit-locking.html#LOCKING-TABLES)), so a quick `ACCESS EXCLUSIVE` step followed by a table scan in the same transaction holds the strong lock for the whole scan. squawk `constraint-missing-not-valid` flags `NOT VALID` and `VALIDATE CONSTRAINT` in one transaction.
- **Add constraints `NOT VALID`, then `VALIDATE CONSTRAINT` in a later migration.** `NOT VALID` works for foreign-key, `CHECK` and not-null constraints. Inserts and updates are checked from then on; only the scan of existing rows is skipped. Validation takes only `SHARE UPDATE EXCLUSIVE`, so reads and writes continue ([ALTER TABLE notes](https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-NOTES)). squawk: `constraint-missing-not-valid`, `adding-foreign-key-constraint`.
- **Make an existing column NOT NULL after the backfill, through a `NOT VALID` not-null constraint (PG 18).** Deploy code that writes the column on every insert and update, backfill, then `ADD CONSTRAINT t_c_not_null NOT NULL c NOT VALID`, then `VALIDATE CONSTRAINT`. Added before the backfill, the constraint rejects every update of a row still NULL ([ADD CONSTRAINT](https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT)). A bare `SET NOT NULL` scans the table under `ACCESS EXCLUSIVE` ([SET NOT NULL](https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-DROP-NOT-NULL), [release notes 18](https://www.postgresql.org/docs/18/release-18.html)). squawk: `adding-not-nullable-field`.
- **Build, drop and rebuild indexes `CONCURRENTLY`, outside a transaction block.** A plain `CREATE INDEX` blocks writes for the whole build. `CONCURRENTLY` does not, but it cannot run inside a transaction block, and it waits for every older transaction. Its file sets `lock_timeout = 0` and `statement_timeout = 0`: a short `lock_timeout` aborts it on a busy system, while its waiting blocks no reads or writes, and a timeout set by an earlier file stays on the session (`lock-levels.md`). A failure leaves an `INVALID` index that still slows writes and that `IF NOT EXISTS` keeps; drop it before a rerun ([CREATE INDEX](https://www.postgresql.org/docs/18/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY)). A partitioned table builds its index `ON ONLY` the parent, then per partition ([5.12.2.2](https://www.postgresql.org/docs/18/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE)). squawk: `require-concurrent-index-creation`, `require-concurrent-index-deletion`, `require-concurrent-reindex`, `ban-concurrent-index-creation-in-transaction`.
- **Create a unique or primary key from a concurrent index.** `CREATE UNIQUE INDEX CONCURRENTLY`, then `ADD CONSTRAINT ... UNIQUE USING INDEX` ([ALTER TABLE](https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT-USING-INDEX)). squawk: `disallowed-unique-constraint`, `adding-serial-primary-key-field`.
- **Know which change rewrites the table.** A volatile default, a stored generated column, an identity column, a domain with constraints, or a type change that is not binary coercible rewrites the table and its indexes under `ACCESS EXCLUSIVE`. A non-volatile default, a virtual generated column and `varchar(n)` to `text` do not ([ALTER TABLE notes](https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-NOTES)). Turn a rewrite into a new column, a backfill and a switch. squawk: `adding-field-with-default`, `changing-column-type`.
- **Rename and drop in two deploys: expand, then contract.** While a deploy rolls out, the old application version still runs, and a rename or drop breaks it. Ship code that stops using the old name first; rename or drop in a later migration ([squawk `renaming-column`](https://squawkhq.com/docs/renaming-column), [`ban-drop-column`](https://squawkhq.com/docs/ban-drop-column)). `DROP COLUMN` is quick but frees no space until rows are rewritten ([ALTER TABLE notes](https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-NOTES)).
- **Backfill in batches, one transaction per batch, outside the schema migration.** Row locks taken by `UPDATE` last until the transaction ends ([13.3.2](https://www.postgresql.org/docs/18/explicit-locking.html#LOCKING-ROWS)), so one `UPDATE` over the whole table holds them all and can outrun `statement_timeout`. Walk the primary key in ranges ([squawk `adding-field-with-default`](https://squawkhq.com/docs/adding-field-with-default)).
- **Add an enum value in its own migration, before code writes it.** A value added inside a transaction block cannot be used until that transaction commits, and there is no `DROP VALUE` ([ALTER TYPE](https://www.postgresql.org/docs/18/sql-altertype.html)). Give `BEFORE` or `AFTER` when the order matters. squawk: `require-enum-value-ordering`.
- **Match the migration tool's transaction handling.** goose runs a file in one transaction unless the file carries `-- +goose NO TRANSACTION` ([goose annotations](https://pressly.github.io/goose/documentation/annotations/)). golang-migrate sends a file as one `Exec`, which PostgreSQL runs as one implicit transaction ([golang-migrate postgres](https://github.com/golang-migrate/migrate/blob/master/database/postgres/README.md)). A file that runs outside a transaction must be safe to rerun. squawk: `prefer-robust-stmts`, `transaction-nesting`.

Column types in new DDL (`prefer-identity`, `prefer-bigint-over-int`, `prefer-text-field`, `prefer-timestamptz`) belong to `pgsql-schema`.

## Read the reference

| The task involves | Read |
|---|---|
| which lock a statement takes, the lock queue, `lock_timeout`, `statement_timeout`, `SET` versus `SET LOCAL`, a migration that is waiting right now | [lock-levels.md](references/lock-levels.md) |
| a recipe for one change: add a column, NOT NULL, CHECK, foreign key, unique or primary key, building or rebuilding an index (also on a partitioned table, and an `INVALID` leftover), a type change, a rename or drop, an enum value, a backfill, a partition detach | [patterns.md](references/patterns.md) |
| goose or golang-migrate, statements that cannot run in a transaction block, rerunnable files, squawk's `assume_in_transaction` | [migration-tools.md](references/migration-tools.md) |

## Sources

- PostgreSQL 18, explicit locking: <https://www.postgresql.org/docs/18/explicit-locking.html> (checked 2026-10-27)
- PostgreSQL 18, ALTER TABLE: <https://www.postgresql.org/docs/18/sql-altertable.html> (checked 2026-10-27)
- PostgreSQL 18, CREATE INDEX: <https://www.postgresql.org/docs/18/sql-createindex.html> (checked 2026-10-27)
- PostgreSQL 18, ALTER TYPE: <https://www.postgresql.org/docs/18/sql-altertype.html> (checked 2026-10-27)
- PostgreSQL 18, client connection defaults: <https://www.postgresql.org/docs/18/runtime-config-client.html> (checked 2026-10-27)
- PostgreSQL 18, session information functions: <https://www.postgresql.org/docs/18/functions-info.html> (checked 2026-10-27)
- PostgreSQL 18 release notes: <https://www.postgresql.org/docs/18/release-18.html> (checked 2026-10-27)
- squawk rules and "Applying migrations safely": <https://squawkhq.com/docs/rules>, <https://squawkhq.com/docs/safe_migrations> (checked 2026-10-27)
- goose annotations: <https://pressly.github.io/goose/documentation/annotations/> (checked 2026-10-27)
- golang-migrate PostgreSQL driver: <https://github.com/golang-migrate/migrate/blob/master/database/postgres/README.md> (checked 2026-10-27)
Files in this package (4)
  • SKILL.md 10 KB
  • references/lock-levels.md 9 KB
  • references/migration-tools.md 7 KB
  • references/patterns.md 11 KB

Versions

The database has no other skills named pgsql-migrations.

Database snapshot: 13.11.2026 · package version 8b57a4296c06 (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

  • 100pgsql-lint-setup
    This skill should be used when the user runs /pgsql-lint-setup or asks to "set up squawk", "add SQL linting", "lint our Postgres migrations", "add a migration linter to CI", "fail the build on unsafe migrations" or "scaffold .squawk.toml", or asks how to set squawk's pg_version…
  • 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…
  • 100pgsql-coding
    PostgreSQL coding-standards router for PostgreSQL 18 and later (19 additions flagged as hints). This skill should be used when a task writes, reviews, tunes or debugs SQL, a .sql file, a migration, a schema, or a query string in Go (pgx) or PHP (PDO) code, when the task spans…
  • 100zero-downtime-migrations
    Writes and reviews PostgreSQL schema migrations that run against large, busy production tables without blocking reads or writes. Covers CREATE INDEX CONCURRENTLY outside transactions, foreign keys and CHECKs added NOT VALID then validated in a later transaction, NOT NULL the…
  • 100migration-safety-checker
    Rewrite a schema migration into independently deployable, zero-downtime steps that never take a long-held blocking lock on a live table. Use when you are about to run an ALTER TABLE, CREATE INDEX, add a constraint, rename a column, or change a column type against a production…
  • 100review-database
    Use when the user asks for a database review, SQL review, migration review, migration safety check, schema review, schema design review, query performance review, index review, connection pool review, transaction management review, "is this migration safe", "will this lock the…

Feedback and reports

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