AI Skill MartBrowse skillsPromptsProgramsLearnWhat is a Skill?
 
← Back to list

PostgreSQL Zero-Downtime Migrations

A skill for planning, rehearsing, and safely executing PostgreSQL schema migrations on live production tables without locking them up.

Dev & CodingAdvanced★ 1,877⑂ 120AI score 9/10Last updated: Oct 28, 2026

What it does

  • Ships a lock-level reference table for every common DDL operation, split into fast metadata-only changes vs. full-rewrite/full-scan blockers.
  • Provides step-by-step safe SQL patterns for adding/dropping/renaming columns, changing types, adding NOT NULL, foreign keys, unique constraints, and redefining primary keys.
  • Compares timeout strategies: lock_timeout vs statement_timeout, a PL/pgSQL retry-with-backoff loop, and LOCK TABLE ... NOWAIT for high-traffic systems.
  • Covers batched backfills, pre/post-migration validation queries, reversible vs. irreversible operations, and fork-based rehearsal (with dump/restore fallback).
  • Calls out 9 common pitfalls: testing on empty tables, forgetting CONCURRENTLY, leaving invalid indexes, ignoring replication lag, and more.

Who it's for

  • Backend engineers, DBAs, and platform teams running PostgreSQL tables with millions of rows.
  • Teams who want migration SQL generated by Rails/Django/Prisma reviewed for production safety.
  • Organizations coordinating code deploys with schema changes for zero-downtime releases.

Example uses

  1. "Is ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now() safe?" → flags the volatile default as a full table rewrite and rewrites it as nullable add → batched backfill → SET DEFAULT → SET NOT NULL.
  2. "I need an index on user_id for a 10M-row table" → prescribes CREATE INDEX CONCURRENTLY outside a transaction, plus how to detect and clean up an INVALID index via pg_index.indisvalid.
  3. "Change amount from INTEGER to NUMERIC" → walks through the new-column + batched backfill + column-swap pattern, rollback planning, and rehearsing on a database fork first.

· · · Install guide · · ·

Try it now, no install

Paste this into Claude to use the skill without installing anything.

Read the instructions in this file and follow them to help me:
https://raw.githubusercontent.com/timescale/pg-aiguide/HEAD/skills/postgres-database-migration/SKILL.md

What I want: (describe your task here)

If Claude can't open the link, open it yourself and paste the contents instead.

↓ If it works for you, download the ZIP below and install it. Then it runs on its own — no pasting each time.

Install in the Claude app (no terminal)
  1. Download the ZIP with the button below.
  2. In Claude, open Settings → Capabilities and turn on 'Code execution and file creation'. (one time)
  3. Go to Customize → Skills → + → 'Upload a skill' and upload the ZIP.
↓ Download ZIP
Install in Claude Code

Let Claude do it — paste this into Claude Code

Install the skill I found on AI Skill Mart.
Copy the skills/postgres-database-migration folder from the GitHub repo timescale/pg-aiguide into my ~/.claude/skills/postgres-database-migration/.
When it's done, tell me in one line what this skill can do.

Install with a command instead

git clone https://github.com/timescale/pg-aiguide.git && mkdir -p ~/.claude/skills && cp -r pg-aiguide/skills/postgres-database-migration ~/.claude/skills/

⚠ This is a third-party skill. Check the source repository before installing.

  1. Open a terminal.
  2. Create the skills folder: mkdir -p ~/.claude/skills
  3. Clone the repo: git clone https://github.com/timescale/pg-aiguide.git
  4. Copy the skill: cp -r pg-aiguide/skills/postgres-database-migration ~/.claude/skills/
  5. Confirm the references/ files (backfill-strategies.md, validation-queries.md, complete-example.md) came along.
  6. Restart Claude Code, then ask something like "Will adding a column to this production table lock it?" to trigger the skill.
  7. Always rehearse the generated SQL on a backup or database fork before touching production.
View source on GitHub ↗License: Apache-2.0