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/backfill-strategies.md
View on GitHub# Backfill Strategies
Backfilling (updating existing rows to populate a new column) on large tables must be done in batches to avoid long-running transactions, excessive locking, and WAL bloat.
## Batch by Primary Key
```sql
-- Backfill in chunks of 10,000 rows
-- Run this repeatedly until 0 rows affected
WITH batch AS (
SELECT id FROM orders
WHERE amount_new IS NULL
ORDER BY id
LIMIT 10000
FOR UPDATE SKIP LOCKED
)
UPDATE orders
SET amount_new = amount::NUMERIC(12,2)
WHERE id IN (SELECT id FROM batch);
```
## Batch with Progress Tracking
```sql
-- Create a tracking table to resume if interrupted
CREATE TABLE migration_progress (
migration_name TEXT PRIMARY KEY,
last_processed_id BIGINT NOT NULL DEFAULT 0,
rows_updated BIGINT NOT NULL DEFAULT 0,
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO migration_progress (migration_name) VALUES ('backfill_amount_new');
-- Run in a loop (from application code or script):
DO $$
DECLARE
v_batch_size CONSTANT INTEGER := 10000;
v_last_id BIGINT;
v_rows INTEGER;
BEGIN
SELECT last_processed_id INTO v_last_id
FROM migration_progress WHERE migration_name = 'backfill_amount_new';
LOOP
UPDATE orders
SET amount_new = amount::NUMERIC(12,2)
WHERE id > v_last_id AND id <= v_last_id + v_batch_size
AND amount_new IS NULL;
GET DIAGNOSTICS v_rows = ROW_COUNT;
EXIT WHEN v_rows = 0;
v_last_id := v_last_id + v_batch_size;
UPDATE migration_progress
SET last_processed_id = v_last_id,
rows_updated = rows_updated + v_rows,
updated_at = now()
WHERE migration_name = 'backfill_amount_new';
COMMIT;
-- Yields to other transactions between batches
PERFORM pg_sleep(0.1);
END LOOP;
END;
$$;
```
## Backfill Considerations
- **Batch size:** Start with 10,000. Increase if each batch completes in under 1 second; decrease if it causes lock contention.
- **Sleep between batches:** 50–200ms gives other queries room. Tune based on your write load.
- **Monitor replication lag:** If you have replicas, check that the backfill doesn't cause them to fall behind.
- **VACUUM:** Run `VACUUM` (not `VACUUM FULL`) after a large backfill to reclaim dead tuple space without locking the table.
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: 322k | Updated | Claude CodeCodex | Verified | Install |
| systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixes | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation code | Stars: 322k | 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: 198k | Updated | Claude CodeCodex | Verified | Install |