How does pgroll help with database migrations?

pgroll automates the expand-contract pattern by creating versioned schemas and handling dual-writes through database triggers, allowing for seamless application updates and safe rollbacks.

Why should I use CONCURRENTLY for index creation?

Standard index creation locks the table for writes, which can cause downtime on large tables. The CONCURRENTLY keyword allows PostgreSQL to build the index while the table remains available for active reads and writes.

How do I safely add a NOT NULL constraint to an existing column?

The safest way is to add the constraint using the 'NOT VALID' flag, which applies the constraint to new rows instantly without a full table scan, and then run a separate 'VALIDATE CONSTRAINT' command to check existing data without blocking writes.

What is the expand-contract pattern?

It is a migration strategy where you first add new fields (expand), migrate data while supporting both the old and new versions in the application code, and finally remove the old fields (contract) once the transition is verified.

Zero-Downtime Database Migrations

Name: Zero-Downtime Database Migrations
Author: yonatangross

byyonatangross

•

Database Management

Implements safe, non-blocking database schema changes using the expand-contract pattern and online migration tools.

This skill equips Claude with production-ready patterns for performing complex database migrations without service interruptions. It focuses on the expand-contract (parallel-schema) pattern, allowing multiple versions of an application to run simultaneously during a transition. By utilizing tools like pgroll and PostgreSQL-specific techniques such as concurrent indexing and 'NOT VALID' constraints, this skill ensures that tasks like renaming columns, adding constraints, or re-indexing large tables never cause table locks or downtime in high-traffic environments.

Key Features

01Non-blocking index creation using PostgreSQL CONCURRENTLY patterns

02Safe constraint management using NOT VALID and separate validation phases

0369 GitHub stars

04Batch-based data backfilling strategies to prevent transaction log bloat

05Automated migration orchestration using pgroll for versioned database schemas

06Expand-Contract pattern implementation for safe column renames and deletions

Use Cases

01Renaming or removing database columns in a live production environment without breaking the API

02Creating indexes on multi-million row tables while maintaining full read/write availability

03Adding NOT NULL constraints to existing columns without locking the table for a full scan

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add yonatangross/orchestkit zero-downtime-migration

For use in Claude.ai and ChatGPT

Download Skill