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
Intro
Schema Migration and Change Management
Schema migration is the controlled movement of a database from one structural version to another. A migration can add a table, change a column, introduce a constraint, build an index, transform stored data, or remove an obsolete object. Change management surrounds that database operation with version control, review, testing, deployment coordination, observation, and recovery.
A schema is a contract between stored data and every client that reads or writes it. The database enforces part of that contract through types, constraints, keys, and indexes. Application code, reports, integration jobs, and operational tools depend on the same structure. A schema change therefore crosses several independently deployed systems.
The migration control loop
A durable migration system has three forms of state:
- Desired change: a reviewed migration file or declarative schema describes the intended transition.
- Database state: tables, columns, constraints, indexes, and data reflect what the database currently serves.
- Migration history: a ledger records which migrations ran, in which order, and often with a checksum.
The migration runner compares its ordered files with the history table. It validates the history, applies pending work, and records success or failure. This makes the same transition repeatable across development, test, staging, and production. It does not make the SQL safe by itself.
Versioned systems preserve the steps that produced the current schema. Declarative systems compare current and desired states, then plan the steps. Both approaches still require review because a correct end state can have several operationally different paths.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
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
- 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/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.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
- 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/
Supports
- Alembic provides migration scripts and autogeneration for SQLAlchemy projects
- 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
- 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