# Postgres Database Migration

Use this skill for planning, testing, and safely executing PostgreSQL schema migrations...

- Repository: https://github.com/timescale/pg-aiguide (1877 stars)
- Category: Testing
- License: Apache-2.0
- Last updated: 2026-10-28
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add timescale/pg-aiguide --skill postgres-database-migration -g -a claude-code -y`
Codex: `npx skills add timescale/pg-aiguide --skill postgres-database-migration -g -a codex -y`
Cursor: `npx skills add timescale/pg-aiguide --skill postgres-database-migration -g -a cursor -y`
Gemini CLI: `npx skills add timescale/pg-aiguide --skill postgres-database-migration -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add timescale/pg-aiguide`

## Description

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.

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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. |

The rest: https://github.com/timescale/pg-aiguide/blob/HEAD/skills/postgres-database-migration/SKILL.md