𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗼𝗻 𝗦𝘁𝗿𝗮𝘁𝗲𝗴𝗶𝗲𝘀

𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗼𝗻 𝗦𝘁𝗿𝗮𝘁𝗲𝗴𝗶𝗲𝘀

Database migrations are risky. Wrong moves lead to downtime or data loss. A system reduces these risks.

Write idempotent migrations. They must produce the same result every time. Use IF NOT EXISTS. This makes retries safe.

Test with production data. Dev databases are too clean. Use a clone or anonymized data to find bugs.

Keep changes backward compatible. Do not rename tables in one go. Add a new column. Write to both columns. Remove the old column later.

Use the expand-migrate-contract pattern.

• Expand: Add new schema.
• Migrate: Move data and update code.
• Contract: Remove old schema. Deploy each part separately.

Avoid table locks. Adding columns with defaults locks tables in PostgreSQL. Add the column without a default. Update data in batches. Set the default at the end.

Run migrations before app startup. Use a pipeline or a dedicated runner. Do this before deploying new code.

Plan your rollbacks. Every migration needs a tested rollback script. Know the time and lock impact. Tell your team the schedule.

Source: https://dev.to/therizwansaleem/database-migration-strategies-zero-downtime-rollback-and-testing-feh