database-migrations

Zero-downtime database migrations on PostgreSQL use expand-contract patterns and concurrent index creation to avoid table locks. Batched data backfills and separate DDL/DML operations prevent long-running transactions from blocking service availability during schema updates.

The expand-contract pattern for schema changes involves adding new nullable columns first, backfilling existing data in batches, deploying application code to use the new field, and dropping the old column later. This prevents table locks and data inconsistency during zero-downtime deployments.

Prisma and Django are supported for safe data backfills in production. The skill standardizes migration workflows across Prisma, Drizzle, Kysely, Django, and golang-migrate, enforcing batched backfills and separation of DDL and DML operations to prevent locking large production tables.

Adding a non-null column to a 10 million row table without locking requires adding the column as nullable first, then running a separate batched data backfill migration. Application code updates deploy next, followed by a later migration to enforce constraints, ensuring no user-facing downtime.

Separating DDL and DML operations in database migrations prevents long-running transactions that lock large production tables. Combining schema alterations with data updates in a single transaction causes blocking; isolating them ensures safe, reversible, and zero-downtime deployment workflows.

golang-migrate supports immutable production migration files within this workflow. The skill enforces tested, reversible migration practices across PostgreSQL and MySQL environments, requiring immutable files and expand-contract patterns to meet zero-downtime deployment requirements safely.