Schema migrations and zero-downtime change
You version schemas with migration tools, change columns and indexes on large tables without holding long locks, and carry out data backfills and rollback plans.
Level 301 · 15 lessons · 12 practice labs
Lessons
Migration tools
- Why migrations: versioning the schema as code
- Working with migration tools
- Up, down and repeatable migrations
- Testing migrations in continuous integration
- Short task: set up migrations for a project
Zero-downtime change
- Which DDL takes which lock
- The expand and contract pattern
- Adding and renaming columns safely
- Building indexes without locking the table
- Large data backfills
- Short task: rename a column on a live table
Assurance
- Rollback plans
- Validating a data migration
- Moving between engines: MySQL to PostgreSQL
- Capstone: a zero-downtime schema overhaul
Practice labs
- Write the first migration files: Turn an existing schema into versioned migration files and show it builds from scratch on an empty database.
- Write a reversible migration pair: Write up and down migrations for each change and verify an up-down-up cycle gives the same schema.
- Test migrations in continuous integration: Set up a CI step that runs migrations on a throwaway database on every change and catches schema drift.
- Measure which change locks the table: Measure how long adding a column and changing a type hold up writes on a table under load.
- Rename a column on a live table without downtime: Use expand-and-contract to add a new column, dual-write, move reads and drop the old column.
- Plan a migration that splits a table: Split address data out of a customers table in steps that stay compatible with app releases.
- Add an index without locking: Add an index to a large table without stopping writes and follow its progress.
- Backfill a new column in batches: Fill a new column on millions of rows in small batches with pauses and watch replication lag.
- Resume an interrupted backfill: Design a backfill that resumes where it stopped and show running it twice does no harm.
- Write a rollback plan: For a release migration, decide which steps can be undone and which need a forward fix.
- Validate a data migration with numbers: Prove no data was lost between old and new tables with row counts, totals and sample comparisons.
- Move a table from MySQL to PostgreSQL: Move an orders table across, mapping type differences, solving encoding and date issues and verifying the result.
About this course
You version schemas with migration tools, change columns and indexes on large tables without holding long locks, and carry out data backfills and rollback plans.
What you will learn
- Migration tools
- Zero-downtime change
- Assurance
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.