Schema Migration and Change Management
Schema migration is the controlled process of changing a database structure while keeping its data and applications usable. Change management adds version history, review, testing, deployment sequencing, monitoring, and recovery decisions around those changes.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Don't Panic
Don't Panic: Schema Migration and Change Management
A schema migration is a controlled change to the shape of a database. It exists because tables, columns, indexes, and constraints eventually stop fitting the application that depends on them. Before migrations were part of delivery, a database could acquire important changes by hand, at different times, in different places. That is how a schema becomes both mysterious and very confident about it.
The useful mental model is a versioned contract. Migration files say what should happen. The live schema is what traffic meets. The migration history records what happened before. Those three things need to agree, but agreement does not mean the operation is harmless. A small-looking ALTER TABLE can ask for a lock, scan a large table, or make replicas work overtime.
The surprising bit is that the dangerous moment is often not the command. It is the overlap. Old and new application processes can run together during a deployment, each expecting a slightly different database. The familiar answer is expand-and-contract: add a compatible new form, move data and application behavior toward it, then remove the old form later. The temporary duplication is not clutter. It is the bridge that lets incompatible versions avoid meeting in a dark alley.
Data movement deserves its own attention. Adding a nullable column changes structure. Filling it changes rows. Enforcing a constraint changes what future writes may do. A bounded backfill gives each batch a defined retry shape and a chance to observe locks, latency, disk pressure, and replica lag. Completion is useful, but verification is the real destination: check the history ledger, the schema, the data, and the critical reads and writes.
Rollback is less magical than it sounds. A transaction can abort uncommitted work when the engine supports it. A compensating migration can restore compatible behavior. But removing a field after new data has arrived may remove the evidence needed to recover. Applied migration files are shared history, so a correction is normally a new change, not an edit to an old one.
Read the intro for the full control loop and deployment path. Use the slides for the sequence and failure shapes. Keep the cheatsheet nearby when reviewing locks, engine behavior, stop signals, and recovery. Then try Practice and Exercise in a disposable database, where the only customer complaint is a row with an unexpectedly empty name.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.postgresql.org/docs/current/sql-altertable.html
Supports
- ALTER TABLE subcommands have documented lock levels, scans, and rewrite behavior
- NOT VALID and VALIDATE CONSTRAINT can separate constraint creation from checking existing rows
- Database-specific operation details determine production impact
- NOT VALID is accepted only for CHECK and foreign key constraints, not for SET NOT NULL (through PostgreSQL 17)
- SET NOT NULL skips the full table scan when a validated CHECK constraint already proves the column contains no NULL
- https://www.postgresql.org/docs/current/sql-createindex.html
Supports
- Concurrent index creation preserves normal writes with additional work and restrictions
- A failed concurrent build can leave an invalid index requiring cleanup or retry
- https://www.postgresql.org/docs/current/sql-lock.html
Supports
- Conflicting lock acquisition can wait or fail through NOWAIT
- Locks remain until the transaction ends
- https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html
Supports
- InnoDB DDL operations differ in instant, in-place, copy, rebuild, and concurrent-DML support
- Online behavior must be checked for the exact alteration
- https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-failure-conditions.html
Supports
- Online DDL can fail on lock timeout, temporary-space exhaustion, log growth, or conflicting concurrent data
- Online operations can need exclusive locks during initial or final phases
- https://guides.rubyonrails.org/active_record_migrations.html
Supports
- Versioned migration files evolve a schema over time
- Rails records applied migration versions in schema_migrations
- Framework migration operations can be reversible or require explicit direction
- https://documentation.red-gate.com/fd/migrations-271585107.html
Supports
- Migration scripts capture incremental schema and data changes in version control
- Flyway orders pending versioned migrations and tracks them in schema history
- Transaction handling depends on migration and database support
- https://documentation.red-gate.com/flyway/flyway-concepts/migrations/flyway-schema-history-table
Supports
- Schema history records applied migration state and checksums
- Validation detects differences between resolved and applied migrations
- Baselines and repairs alter how existing history is interpreted
- https://documentation.red-gate.com/flyway/reference/commands/repair
Supports
- flyway repair removes failed migration entries, realigns the checksums, descriptions and types of applied migrations, and marks missing migrations as deleted
- flyway repair does not execute a migration and does not record an applied-but-unlogged migration in the schema history
- https://documentation.red-gate.com/fd/flyway-skip-executing-migrations-setting-277579035.html
Supports
- skipExecutingMigrations updates the Flyway schema history to mark a migration as applied without executing its contents
- It is used to bring an out-of-process change (such as a hotfix) into Flyway's change control
- https://documentation.red-gate.com/fd/versioned-migrations-273973333.html
Supports
- Versioned migrations apply once in order
- Applied versioned migrations should not be edited; corrections use a new migration
- https://docs.liquibase.com/concepts/changelogs/changeset.html
Supports
- Liquibase identifies and tracks changesets in a database changelog
- Changeset checksums and preconditions support controlled execution
- https://docs.liquibase.com/commands/utility/changelog-sync.html
Supports
- Liquibase changelog-sync marks all undeployed changesets as executed in DATABASECHANGELOG without running their SQL
- https://docs.prisma.io/docs/guides/database/data-migration
Supports
- Expand-and-contract can replace a field while preserving data and coordinating application releases
- Expansion, data migration, deployment, and contraction are distinct steps
- https://docs.prisma.io/docs/orm/prisma-migrate/workflows/patching-and-hotfixing
Supports
- Approved production hotfixes must be reconciled with migration history and other environments
- Migration resolve can record a change without blindly replaying it
- https://docs.gitlab.com/development/database/avoiding_downtime_in_migrations/
Supports
- Old and new processes can overlap during zero-downtime deployment
- Renames, drops, required values, and large backfills need multi-release or background strategies
- Large data changes can be split from schema changes and processed in batches
- https://github.com/sindresorhus/awesome
Supports
- The curated Awesome index identifies Awesome Database Tools as a relevant ecosystem list
- https://github.com/mgramin/awesome-db-tools
Supports
- The Schema Changes section lists Atlas, Flyway, gh-ost, Liquibase, pg-osc, Reshape, Skeema, Sqitch, and related migration tools
- https://github.com/github/gh-ost
Supports
- gh-ost uses a ghost table and binary-log stream for MySQL online schema changes
- gh-ost exposes throttling, pause, testing, and cutover controls
- --ok-to-drop-table is disabled by default, so gh-ost keeps the original table renamed to _<table>_del after a successful cut-over
- gh-ost installs no reverse flow, so the retained _<table>_del table is frozen as of the cut-over
- https://github.com/shayonj/pg-osc
Supports
- pg-osc implements online PostgreSQL schema change with copy, change replay, and table swap phases
- https://github.com/fabianlindfors/reshape
Supports
- Reshape focuses PostgreSQL migrations on compatibility while old and new application versions overlap
- https://www.skeema.io/docs/
Supports
- Skeema provides declarative schema diffing and linting for MySQL and MariaDB
- Skeema can integrate with online schema-change tools
- https://sqitch.org/docs/
Supports
- Sqitch uses deploy, revert, and verify scripts for database-native change management
- https://atlasgo.io/docs/
Supports
- Atlas supports declarative and versioned migration workflows, linting, and integrity checks
- https://www.bytebase.com/docs/
Supports
- Bytebase provides review, policy, rollout, and audit workflows around database changes
- https://www.prisma.io/migrate
Supports
- Prisma Migrate generates editable SQL migrations from declarative Prisma schema changes
- Prisma separates development and production migration workflows
- https://alembic.sqlalchemy.org/en/latest/api/commands.html
Supports
- Alembic provides migration scripts and autogeneration for SQLAlchemy projects
- alembic stamp sets the revision table to the given revision and runs no migrations
- https://docs.djangoproject.com/en/stable/ref/django-admin/
Supports
- Django migrate --fake marks migrations as applied without running the SQL that would change the schema
- https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
Supports
- pt-online-schema-change copies a table in chunks and uses triggers to capture concurrent changes
- Load and replica-lag checks can throttle or pause work
- The original table is dropped by default after a successful rename (--drop-old-table defaults to yes); --no-drop-old-table retains it
- --reverse-triggers applies post-cutover writes on the new table back to the retained old table, so it stays a current rollback target
- https://rubyonrails.org/2005/7/6/rails-0-13-225-features-fixes-in-75-days
Supports
- Rails 0.13 introduced migrations on 2005-07-06
- Migrations described version-controlled schema transformations across databases
- https://www.liquibase.com/blog/liquibase-5-0-release
Supports
- Liquibase open-source history dates to 2006
- https://www.red-gate.com/blog/the-next-chapter-of-flyway
Supports
- Flyway 0.0.1 was released on 2010-04-20
- Flyway was created to deploy plain-SQL changes consistently with applications
- https://alembic.sqlalchemy.org/en/latest/changelog.html
Supports
- Alembic 0.1.0 was released on 2011-11-30
- The initial release included production use and multiple-database support
- https://www.percona.com/blog/percona-toolkit-2-1-with-new-online-schema-change-tool/
Supports
- Percona Toolkit 2.1 was released on 2012-04-05
- Its rebuilt online schema-change tool added replication-aware throttling for large MySQL tables
- https://github.blog/news-insights/company-news/gh-ost-github-s-online-migration-tool-for-mysql/
Supports
- GitHub open-sourced gh-ost on 2016-08-01
- The tool uses binary-log changes instead of triggers and provides pause and throttle controls
- https://www.postgresql.org/docs/release/11.0/
Supports
- PostgreSQL 11 was released on 2018-10-18
- It could avoid a table rewrite for ADD COLUMN with a non-null constant default
- https://www.prisma.io/blog/prisma-migrate-ga-b5eno5g08d0b
Supports
- Prisma Migrate reached general availability on 2021-03-16
- The release separated development and production migration workflows
- https://atlasgo.io/blog/2022/09/05/announcing-migration-execution
Supports
- Atlas released its migration execution engine on 2022-09-05
- The engine added ordered application, revision history, directory integrity hashes, and baselining
- https://stripe.com/blog/online-migrations
Supports
- Stripe describes a four-phase online migration: dual write, move reads, move writes, then remove the old data
- Incremental rollout, parity checks, and observable comparisons reduce risk while old and new data models overlap
- https://shopify.engineering/add-not-null-colums-to-database
Supports
- Shopify documents compatibility and data-loss checks for shadow-table schema changes
- Adding a NOT NULL column or unique index during an online migration can break writes or lose rows under documented conditions