Postgres Database Migration
Use this skill for planning, testing, and safely executing PostgreSQL schema migrations...
Install
npx skills add timescale/pg-aiguide --skill postgres-database-migration -g -a claude-code -y- Repository
- timescale/pg-aiguide1.9k
- Category
- Testing
- Updated
- 8d ago
- License
- Apache-2.0
- Context cost
- Mediumabout 3,000 tokens
- Runs scripts
- No, instructions only
What it does
Use 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.
PostgreSQL Database Migrations
A schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely.
DDL Lock Reference
Every schema change acquires a lock. The critical question is: does it block reads and writes, and for how long?
Fast, Non-Blocking Operations
These complete in milliseconds regardless of table size. They only hold a brief AccessExclusiveLock for the catalog update, not for data rewriting.
| Operation | Lock Level | Notes |
|---|---|---|
ADD COLUMN (nullable, no default) | AccessExclusiveLock (brief) | Fast. No table rewrite. Metadata-only change. |
ADD COLUMN ... DEFAULT x (PG 11+) | AccessExclusiveLock (brief) | Fast. Non-volatile defaults stored in catalog, not backfilled. |
DROP COLUMN | AccessExclusiveLock (brief) | Fast. Column marked invisible; space reclaimed by VACUUM over time. |
SET DEFAULT / DROP DEFAULT | AccessExclusiveLock (brief) | Metadata change only. Does not touch existing rows. |
CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock | Non-blocking. Allows reads and writes during build. Slower than regular index creation. |
DROP INDEX CONCURRENTLY | ShareUpdateExclusiveLock | Non-blocking. Waits for queries using the index to finish, then drops. No table-level exclusive lock. |
RENAME COLUMN | AccessExclusiveLock (brief) | Metadata change only. Fast. |
RENAME TABLE | AccessExclusiveLock (brief) | Metadata change only. Fast. |
ADD CONSTRAINT ... NOT VALID | ShareUpdateExclusiveLock | Adds constraint for new rows only. Does not scan existing data. |
VALIDATE CONSTRAINT | ShareUpdateExclusiveLock | Scans existing rows but allows concurrent reads and writes. |
CREATE/DROP TRIGGER | ShareRowExclusiveLock | Brief catalog update. |
