postgres-migrations（Postgres migrations 开发）

Safe PostgreSQL Migrations

This skill helps you write migrations that avoid blocking reads/writes in production. Based on Squawk linter rules.

Verifying Migrations

After writing a migration, verify it with the Squawk CLI:

uv run squawk migrations/your_migration.sql

This will catch unsafe patterns before they reach production.

Quick Reference: Safe Patterns

Timeouts

Always set timeouts at the start of migrations:

SET lock_timeout = '2s';
SET statement_timeout = '30s';

Adding Columns

With Default Value (PG 11+)

Non-volatile defaults are safe on PostgreSQL 11+:

ALTER TABLE users ADD COLUMN active boolean DEFAULT true NOT NULL;

With Default Value (PG <11 or volatile defaults)

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN created_at timestamptz;
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();

-- Step 2: Backfill in batches
UPDATE users SET created_at = now() WHERE id BETWEEN 1 AND 10000;
-- ... repeat for all batches

-- Step 3: Add NOT NULL (see next section)

Making Column NOT NULL

-- Step 1: Add NOT VALID constraint (fast, minimal locking)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate (acquires lighter SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: Set NOT NULL (PG 12+ skips table scan due to existing constraint)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop redundant constraint
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

Required Field (NOT NULL without default)

Never add a NOT NULL column without a default to a table with data. Instead:

-- Option A: Add with default
ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'member';

-- Option B: Add nullable, backfill, then constrain
ALTER TABLE users ADD COLUMN role text;
UPDATE users SET role = 'member' WHERE role IS NULL;
-- Then use the NOT NULL pattern above

Constraints

Foreign Key

-- Step 1: Add NOT VALID (fast)
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: Validate in separate transaction
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Check Constraint

-- Step 1: Add NOT VALID
ALTER TABLE accounts ADD CONSTRAINT positive_balance
  CHECK (balance >= 0) NOT VALID;

-- Step 2: Validate
ALTER TABLE accounts VALIDATE CONSTRAINT positive_balance;

Unique Constraint

-- Step 1: Create index concurrently (allows reads/writes)
CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users(email);

-- Step 2: Attach as constraint (fast)
ALTER TABLE users ADD CONSTRAINT users_email_uniq
  UNIQUE USING INDEX users_email_idx;

Indexes

Create Index

-- Always use CONCURRENTLY (outside transaction)
CREATE INDEX CONCURRENTLY users_email_idx ON users(email);

Drop Index

DROP INDEX CONCURRENTLY users_email_idx;

Concurrent Index in Transaction

CREATE INDEX CONCURRENTLY cannot run inside a transaction. For migration tools that auto-wrap in transactions:

COMMIT;
CREATE INDEX CONCURRENTLY users_email_idx ON users(email);
BEGIN;

Changing Column Types

Safe Conversions (no rewrite)

• varchar(N) to text
• varchar(N) to varchar(M) where M > N
• numeric(P,S) to numeric(P2,S) where P2 > P

Unsafe Conversions (requires table rewrite)

For int to bigint or other incompatible types:

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN id_new bigint;

-- Step 2: Create trigger to sync writes
CREATE FUNCTION sync_id_new() RETURNS trigger AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_id_new_trigger
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_id_new();

-- Step 3: Backfill in batches
UPDATE users SET id_new = id WHERE id BETWEEN 1 AND 10000;

-- Step 4: Swap columns (requires downtime or careful coordination)

Destructive Operations

Drop Column

Risk: Breaks clients still reading/writing the column.

Safe process:

1. Stop application code from using the column
2. Deploy code changes
3. Wait for all instances updated
4. Drop the column

Drop Table

Risk: Breaks all clients using the table.

Safe process: Same as drop column - ensure no code references it first.

Rename Column/Table

Risk: Breaks clients using the old name.

Safer alternatives:

1. Rename in ORM only, keep database name unchanged
2. For tables: create a view with new name, migrate code, then swap

-- View approach for table rename
CREATE VIEW user_favorites AS SELECT * FROM user_stars;
-- Deploy code using user_favorites
-- Then:
BEGIN;
DROP VIEW user_favorites;
ALTER TABLE user_stars RENAME TO user_favorites;
COMMIT;

Type Preferences

Use BIGINT over INT

-- Avoid (2B limit)
CREATE TABLE posts (id serial PRIMARY KEY);
CREATE TABLE posts (id int PRIMARY KEY);

-- Prefer (9 quintillion limit)
CREATE TABLE posts (id bigserial PRIMARY KEY);
CREATE TABLE posts (id bigint PRIMARY KEY);

Use IDENTITY over SERIAL

-- Avoid (permission/schema issues)
CREATE TABLE posts (id bigserial PRIMARY KEY);

-- Prefer (SQL standard, better usability)
CREATE TABLE posts (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);

Use TEXT over VARCHAR

-- Avoid (changing size requires ACCESS EXCLUSIVE lock)
CREATE TABLE users (email varchar(255));

-- Prefer (add check constraint for length)
CREATE TABLE users (email text);
ALTER TABLE users ADD CONSTRAINT email_length CHECK (length(email) <= 255);

Use TIMESTAMPTZ over TIMESTAMP

-- Avoid (loses timezone info)
CREATE TABLE events (created_at timestamp);

-- Prefer (preserves timezone)
CREATE TABLE events (created_at timestamptz);

Avoid CHAR

-- Avoid (pads with spaces, unexpected behavior)
CREATE TABLE t (code char(3));

-- Prefer
CREATE TABLE t (code text);
ALTER TABLE t ADD CONSTRAINT code_length CHECK (length(code) = 3);

Idempotent Migrations

Use IF EXISTS / IF NOT EXISTS for retryable migrations:

-- Adding
ALTER TABLE users ADD COLUMN IF NOT EXISTS email text;
CREATE INDEX CONCURRENTLY IF NOT EXISTS users_email_idx ON users(email);

-- Removing
DROP INDEX CONCURRENTLY IF EXISTS users_email_idx;
DROP TABLE IF EXISTS old_users;
ALTER TABLE users DROP COLUMN IF EXISTS deprecated_col;

Lock Types Reference

Alembic/SQLAlchemy Examples

Concurrent Index

from alembic import op

def upgrade():
    with op.get_context().autocommit_block():
        op.create_index(
            'users_email_idx',
            'users',
            ['email'],
            postgresql_concurrently=True,
        )

NOT VALID Constraint

import sqlalchemy as sa
from alembic import op

def upgrade():
    op.create_check_constraint(
        'positive_balance',
        'accounts',
        'balance >= 0',
        postgresql_not_valid=True,
    )

def upgrade_validate():
    op.execute(sa.text('ALTER TABLE accounts VALIDATE CONSTRAINT positive_balance'))

Foreign Key with NOT VALID

from alembic import op

def upgrade():
    op.create_foreign_key(
        'orders_user_fk',
        'orders', 'users',
        ['user_id'], ['id'],
        postgresql_not_valid=True,
    )

def upgrade_validate():
    op.execute(sa.text('ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk'))