Skip to content

skillDatabases

Zero Downtime Schema Change

Plans schema changes as expand, migrate and contract steps with batched backfills, so old and new code both work during every deploy.

npx workspacetemplates add zero-downtime-schema-change --tool claude-code

Writes 1 file to .claude/skills/zero-downtime-schema-change/

.claude/skills/zero-downtime-schema-change/SKILL.md29 lines · 1.7 KBFile
---
name: zero-downtime-schema-change
description: Plans schema changes as expand, migrate and contract steps with batched backfills, so old and new code both work during every deploy.
---

# Zero downtime schema change

Every deploy must work with the schema before and after it. Split the change so that holds.

## Steps

1. Describe the target change and the table size and write rate. Find all code that reads or writes the affected columns, including jobs, reports and other services.
2. Expand: add the new column or table as nullable with no default that forces a rewrite, or add the index with the non-blocking form (`CONCURRENTLY` in Postgres, online DDL in MySQL). Set a short `lock_timeout` so the migration fails fast instead of queuing behind a long transaction.
3. Dual write: deploy code that writes both old and new. Reads still use the old one.
4. Backfill existing rows in batches of 1,000 to 10,000 by primary key range, committing each batch, sleeping between batches and watching replication lag. The job must be restartable and idempotent.
5. Verify: compare old and new for every row (count mismatches, expect zero).
6. Switch reads to the new column behind a flag, then watch errors and latency.
7. Add constraints last: create `NOT VALID` foreign keys or checks, then `VALIDATE` them separately.
8. Contract: stop writing the old one, wait a full release cycle, then drop it in its own migration.

## Rules

- Never rename or change a column type in place on a live table.
- Never mix schema and data changes in one migration.
- Each step ships in its own deploy and has a stated rollback.

## Report

An ordered runbook with the SQL, the code change per step, batch settings and rollback for each.

What it does

A migration that locks a big table or breaks the running version takes the site down. This skill has the agent split each change into steps that are individually safe and that you can roll back.

Backfills run in small batches with pauses, and the final destructive step waits until nothing reads the old shape.

Example

you › Rename users.fullname to users.display_name on a 60M-row table

agent › Seven-step plan: add display_name nullable, dual-write in the model, backfill 5k rows per batch with 200 ms sleep (about 40 minutes), verify zero mismatches, flip reads behind a flag, add NOT NULL via validated check, and drop fullname two releases later.

Questions

Why not just use the framework migration rename?
A rename is instant in the database, but the old app version still running during the deploy will fail on the missing column.
How big should backfill batches be?
Start small, watch lock time and replica lag, and grow until a batch takes under a second or so. Keep the size adjustable without a deploy.
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Migration Safety

    SkillDatabases

    Free
  • Database Schema Design

    SkillDatabases

    Free
  • MongoDB Schema Modeling

    Skillmongodb

    Free