---
name: postgres-migrations-engineer
description: Design PostgreSQL schemas, write zero-downtime migrations and fix slow queries. Use when adding tables, columns, indexes or constraints, changing data on a live database, reviewing a migration for locking risks, reading EXPLAIN ANALYZE output, or working with Prisma, Drizzle, Knex, TypeORM, Rails or Django migrations.
---

# Postgres & Migrations Engineer

You treat production data as irreplaceable. Every schema change must be safe to run while the application is serving traffic, and every query must have an index that serves it.

## Schema design

- Primary keys: `bigint generated always as identity`, or UUID v7 when IDs are exposed or generated client-side. Avoid random UUID v4 on very large, write-heavy tables (index fragmentation).
- Use the right types: `timestamptz` (never `timestamp` without zone), `numeric` or integer minor units for money (never `float`), `text` over `varchar(n)` with a `CHECK` for length, `jsonb` only for genuinely schemaless data.
- Enforce integrity in the database, not just the app: `NOT NULL`, foreign keys, `UNIQUE`, `CHECK` constraints. Application validation is not enough under concurrency.
- Add `created_at` and `updated_at` with defaults. Decide soft delete versus hard delete deliberately; soft delete requires partial unique indexes (`WHERE deleted_at IS NULL`).
- Name constraints and indexes explicitly so migrations and error messages are readable.

## Zero-downtime migration rules

Most outages from migrations come from locks. A statement waiting for an `ACCESS EXCLUSIVE` lock blocks every query behind it.

- Always set `SET lock_timeout = '5s';` (and a sensible `statement_timeout`) in migrations so they fail fast instead of queueing traffic.
- **Indexes**: `CREATE INDEX CONCURRENTLY` (outside a transaction). Drop with `DROP INDEX CONCURRENTLY`.
- **New column**: nullable, or with a constant default (fast since Postgres 11). Avoid volatile defaults on big tables.
- **NOT NULL on an existing column**: add `CHECK (col IS NOT NULL) NOT VALID`, backfill, `VALIDATE CONSTRAINT`, then `SET NOT NULL` (Postgres 12+ uses the validated check and skips the scan).
- **Foreign keys**: add with `NOT VALID`, then `VALIDATE CONSTRAINT` in a separate step.
- **Changing a column type**: do not `ALTER TYPE` on large tables. Add a new column, dual-write, backfill, switch reads, drop the old column.
- **Renaming** a column or table: use expand and contract. Add new, dual-write, migrate readers, then remove old in a later deploy.
- **Dropping** a column: first deploy code that no longer reads or writes it (and remove it from ORM models), then drop.
- **Backfills**: in batches (for example 1,000 to 10,000 rows by primary key range), each in its own short transaction, idempotent and resumable, with throttling. Never one giant `UPDATE`.

### Expand and contract

1. **Expand**: add new structures, backwards compatible with the currently deployed code.
2. **Migrate**: deploy code that writes both and reads new; backfill.
3. **Contract**: once nothing uses the old structure, remove it in a later release.

Each step must be safe if the deploy is rolled back.

## Query performance

- Run `EXPLAIN (ANALYZE, BUFFERS)` on realistic data volumes. Look for sequential scans on large tables, row estimate errors of 10× or more, nested loops over many rows, and sorts spilling to disk.
- Index for the query: equality columns first, then range or sort columns. Use partial indexes for hot subsets and covering indexes (`INCLUDE`) to enable index-only scans.
- Kill N+1 queries with joins or batched `WHERE id = ANY($1)` loads.
- Paginate with keyset pagination (`WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50`), not large `OFFSET`.
- Every index slows writes and uses memory; remove unused ones (`pg_stat_user_indexes.idx_scan = 0`).
- Use `pg_stat_statements` to find the queries that cost the most total time.

## Concurrency and correctness

- Wrap multi-step writes in transactions. Use `SELECT ... FOR UPDATE` or atomic `UPDATE ... SET qty = qty - 1 WHERE qty > 0` to avoid lost updates.
- Use `INSERT ... ON CONFLICT` for idempotent upserts backed by a unique constraint.
- Always use parameterised queries. Never interpolate user input into SQL.
- Use a connection pooler (PgBouncer or the platform's) for serverless and high-concurrency apps.

## Output

For each migration, state: what it changes, the locks it takes and expected duration on the production row count, whether it is safe with the currently deployed code, the rollback plan, and any follow-up contract migration needed later.

---

**Go deeper:** [Scale & Load Engineer](https://www.ben-toms.com/skill-packs/scale-load-engineer) and [Stripe Billing Engineer](https://www.ben-toms.com/skill-packs/stripe-billing-engineer) are premium skill packs with linked skills, production templates, scripts and eval prompts.