postgres-migrations_skill

Overview

This skill helps you write safe PostgreSQL migrations that avoid blocking reads and writes in production. It encodes Squawk-style rules and concrete patterns for adding columns, indexes, constraints, and changing types so migrations run with minimal locking and downtime. Use it to produce stepwise, idempotent migration plans that are safe for large tables.

How this skill works

The skill inspects migration intent and recommends safe sequences: add nullable columns, backfill in batches, add NOT VALID constraints then validate, create indexes CONCURRENTLY, and use trigger-sync for type swaps. It flags unsafe single-step operations (large table rewrites, blocking ALTERs) and replaces them with multi-step, low-lock alternatives and timeout settings. It also provides Alembic/SQLAlchemy examples and idempotency patterns.

When to use it

• Adding columns with defaults to large production tables
• Making an existing column NOT NULL or adding constraints
• Creating or dropping indexes on busy tables
• Adding foreign keys, unique, or check constraints without blocking traffic
• Changing column types that require table rewrites
• Planning destructive operations like drop/rename with coordinated deploys

Best practices

• Set lock_timeout and statement_timeout at migration start
• Prefer CONCURRENTLY for index creation and dropping outside transactions
• Add constraints NOT VALID, validate separately, then set NOT NULL when applicable
• Backfill large tables in bounded batches and use trigger-sync for writes during migration
• Make migrations idempotent with IF EXISTS / IF NOT EXISTS
• Prefer bigint/IDENTITY/TIMESTAMPTZ/TEXT over legacy types to avoid future costly changes

Example use cases

• Add a boolean 'active' column with safe default on a 100M-row users table
• Add a UNIQUE constraint on email by creating a UNIQUE INDEX CONCURRENTLY then attaching it as a constraint
• Convert an integer id to bigint using a new column, trigger sync, batch backfill, then swap
• Introduce a foreign key without long locks by adding it NOT VALID and validating later
• Remove a deprecated column safely by coordinating application deploys and then dropping the column