timescale/pg-aiguide / skills/postgres-database-migration
postgres-database-migration
VerifiedUse this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns safely on a live table - Change a column's data type without downtime - Add or drop indexes, constraints, or foreign keys on large tables - Understand which ALTER TABLE operations lock the table - Roll back a failed migration - Plan a zero-downtime migration strategy - Fork a database to test a migration safely **Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, rollback, backfill, deploy Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 24.2 KB |
| references/backfill-strategies.md | markdown | 2.3 KB |
| references/complete-example.md | markdown | 2.0 KB |
| references/validation-queries.md | markdown | 3.4 KB |
references/validation-queries.md
View on GitHub# Migration Validation Queries
## Pre-Migration Validation
Run these checks **before** applying a migration. On a database fork, you can run them against real production data without any risk.
### Check for NULLs Before Adding NOT NULL
```sql
-- Will the NOT NULL constraint fail?
SELECT COUNT(*) AS null_count
FROM orders
WHERE order_status IS NULL;
-- Find sample rows to understand why they're NULL
SELECT id, created_at
FROM orders
WHERE order_status IS NULL
LIMIT 20;
```
### Check for Duplicates Before Adding UNIQUE
```sql
-- Will a UNIQUE constraint fail?
SELECT tracking_number, COUNT(*) AS occurrences
FROM orders
WHERE tracking_number IS NOT NULL
GROUP BY tracking_number
HAVING COUNT(*) > 1
ORDER BY occurrences DESC
LIMIT 20;
```
### Check for Orphans Before Adding a Foreign Key
```sql
-- Will a FK constraint fail?
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL AND o.user_id IS NOT NULL
LIMIT 20;
```
### Check for Cast Failures Before Changing Type
```sql
-- Will the type change fail on any existing values?
SELECT id, amount
FROM orders
WHERE amount IS NOT NULL
AND NOT (amount::TEXT ~ '^\d+(\.\d{1,2})?$');
-- Or try the cast and catch failures
SELECT id, amount
FROM orders
WHERE pg_typeof(amount) != 'numeric'
AND amount IS NOT NULL;
```
### Estimate Migration Duration
```sql
-- Table size and row count (estimate for planning)
SELECT
pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
pg_size_pretty(pg_relation_size('orders')) AS data_size,
reltuples::BIGINT AS estimated_rows
FROM pg_class
WHERE relname = 'orders';
-- Estimate backfill time: run a small batch and extrapolate
-- WARNING: EXPLAIN ANALYZE actually executes the statement — this WILL update rows.
-- Run this on a fork, or wrap in a transaction and ROLLBACK after.
BEGIN;
EXPLAIN ANALYZE
UPDATE orders SET amount_new = amount::NUMERIC(12,2)
WHERE id BETWEEN 1 AND 1000 AND amount_new IS NULL;
-- If 1,000 rows takes 50ms and you have 10M rows → ~500s total
ROLLBACK;
```
## Post-Migration Validation
Run these **after** the migration to confirm it worked correctly.
### Schema Verification
```sql
-- Verify column was added/changed
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'amount';
-- Verify constraint exists
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;
-- Verify index exists and is valid
SELECT indexrelid::regclass AS index_name,
indisvalid AS is_valid,
indisunique AS is_unique,
pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'orders'::regclass;
```
### Data Integrity
```sql
-- Verify backfill completed (no NULLs remaining)
SELECT COUNT(*) AS remaining_nulls
FROM orders
WHERE amount_new IS NULL AND amount IS NOT NULL;
-- Verify no data was lost
SELECT
COUNT(*) AS total_rows,
COUNT(amount) AS old_column_non_null,
COUNT(amount_new) AS new_column_non_null
FROM orders;
-- Spot-check: old and new values match
SELECT id, amount AS old_value, amount_new AS new_value
FROM orders
WHERE amount::NUMERIC(12,2) != amount_new
LIMIT 10;
```
### Query Performance
```sql
-- Verify the new index is being used
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;
-- Check for sequential scans on the migrated table
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE relname = 'orders';
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the work | Stars: 321k | Updated | Claude CodeCodex | Verified | Install |
| systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixes | Stars: 321k | Updated | Claude CodeCodex | Verified | Install |
| test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation code | Stars: 321k | Updated | Claude CodeCodex | Verified | Install |
| skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy. | Stars: 197k | Updated | Claude CodeCodex | Verified | Install |