Checklist · Cloud and DevOps
Database Migration Checklist
Opens with creditsA pre-release checklist for schema and data migrations on a live database. It covers backward-compatible expand and contract steps, how each kind of change locks a table in PostgreSQL and MySQL, building indexes online, batching backfills, rehearsing on production-sized data, backups and rollback, deploy ordering with application code and what to watch while it runs, with notes for MongoDB.
Written by Tauseef Fayyaz11 chapters, 49 checksAbout 26 minReviewed 13 October 2026What you will learn
11 chapters · about 26 min
1Plan and classify the changeChecks that the migration is tracked, small, and that you know what each statement will do to a live table before you run it.4 checks · 2 min
- 1The migration is a versioned file applied by a migration tool, not a manual query
- 2For every statement, you know the lock it takes and whether it rewrites the table
- 3Schema changes and data backfills are separate migrations
- 4Each release carries at most one risky migration
4 in this chapterOpen this chapter2Backward compatibility: expand and contractChecks that both the old and new versions of the application keep working at every step, because during a rolling deploy both are running.5 checks · 3 min
- 1The currently deployed application works against the migrated schema
- 2Renames and type changes use expand, migrate, contract instead of one step
- 3Columns and tables are dropped only after no deployed code references them
- 4New required columns get a default or are filled before NOT NULL is enforced
- 5New enum values, lookup rows and constraints are added before code depends on them
5 in this chapterOpen this chapter- Locks and rewrites in PostgreSQLChecks that a PostgreSQL migration cannot block the table for longer than you can afford, and that slow steps run in forms that allow reads and writes to continue.6 checks · 4 minOpens with credits
- Online DDL in MySQLChecks that a MySQL (InnoDB) migration uses the online algorithm you expect, fails fast when it cannot, and does not stall behind metadata locks.5 checks · 3 minOpens with credits
- Creating indexes onlineChecks that new and replaced indexes are built without blocking writes, and that a failed build does not leave a broken index behind.5 checks · 2 minOpens with credits
- Backfills and data migrationsChecks that rewriting existing rows happens in small, throttled, restartable batches instead of one huge transaction.5 checks · 2 minOpens with credits
- Rehearse on production-sized dataChecks that the migration has been run, timed and rolled back on data that looks like production before production sees it.4 checks · 2 minOpens with credits
- Backups and rollback planChecks that you can get back to a known good state if the migration goes wrong, and know how long that takes.3 checks · 2 minOpens with credits
- Deploy ordering with application codeChecks that the migration and the code that needs it reach production in the right order, exactly once.4 checks · 2 minOpens with credits
- Monitoring during and afterChecks that you can see the migration hurting the system while it runs, and confirm afterwards that it left the schema and data as intended.3 checks · 1 minOpens with credits
- Notes for MongoDBMongoDB has no ALTER TABLE, but documents still change shape, indexes still have to be built on live collections and old and new code still run side by side.5 checks · 3 minOpens with credits
About this checklist
A database migration checklist exists because the dangerous part of a migration is rarely the SQL itself. It is what the database does while running it, and what the running application does at the same time. An ALTER TABLE that takes milliseconds on your laptop can wait for a lock behind a long transaction and freeze every query on the table. A new NOT NULL column can break the version of the app still serving traffic. A one-line UPDATE over fifty million rows can fill the disk with write-ahead log and leave replicas minutes behind. None of this shows up in a test suite running against an empty schema.
This checklist is for the review before a migration ships. The chapters follow the order in which things go wrong: planning and classifying the change, keeping every step backward compatible, locking behaviour in PostgreSQL and in MySQL, creating indexes online, running backfills in batches, rehearsing on production-sized data, backups and a rollback plan, ordering the migration against the application deploy, and watching it during and after. A final chapter covers how the same ideas apply to MongoDB, where there is no ALTER TABLE but the application still has to cope with two shapes of document.
Each check has a severity. Must means skipping it is how migrations cause outages or lose data. Should means it prevents a common, painful problem. Nice adds safety for large tables or busy systems. Every check says how to verify it, with a query, a setting or a step in the rehearsal, and the examples use PostgreSQL and MySQL syntax side by side where they differ.
Run it with the person who wrote the migration and someone who knows the production database. For a small additive change it takes ten minutes. For a rename, a type change or a backfill on a large table, it is the difference between a quiet deploy and an incident.
Common questions
What is the expand and contract pattern for database migrations?
It splits a breaking change into steps that are each backward compatible. First you expand: add the new column or table alongside the old one and write to both. Then you migrate: backfill old data and move reads to the new shape. Finally you contract: once no running code uses the old shape, you remove it. Old and new application versions keep working at every step.
Why did a simple ALTER TABLE take down my PostgreSQL database?
Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including plain reads. If a long transaction already holds a lock on the table, the ALTER waits, and new queries queue behind it. Setting a short lock_timeout makes the migration fail and retry instead of freezing the table.
How do I add an index to a large table without downtime?
In PostgreSQL, use CREATE INDEX CONCURRENTLY in a migration that does not run inside a transaction, and check for invalid indexes afterwards. In MySQL, use ALTER TABLE ... ADD INDEX with ALGORITHM=INPLACE, LOCK=NONE. In MongoDB, index builds yield to reads and writes, but plan for the commit quorum and check for duplicates before unique indexes.
Should migrations run before or after the code deploy?
Additive changes run before the deploy, so the new code finds what it needs. Removals run after the deploy has finished and nothing references the old column or table. Running both in one step breaks either the old code during the rollout or your ability to roll back.
How big should backfill batches be?
Big enough to finish in reasonable time, small enough that each batch commits in well under a second or two on production hardware. Measure it during rehearsal on a production-sized copy, add a short pause between batches, and slow down when replica lag rises.
Do I need gh-ost or pt-online-schema-change on MySQL?
Only for changes MySQL cannot do instantly or in place with concurrent writes, such as changing a column type, on tables large enough that a blocking copy would be noticed. Many common changes, such as adding columns and secondary indexes, are already online in current MySQL versions.
Sources (8)
Checked against these, last on 13 October 2026.
- ALTER TABLE (PostgreSQL 18), PostgreSQL docswww.postgresql.org/docs/18/sql-altertable.html
- CREATE INDEX (PostgreSQL 18), PostgreSQL docswww.postgresql.org/docs/18/sql-createindex.html
- Client Connection Defaults (PostgreSQL 18), PostgreSQL docswww.postgresql.org/docs/18/runtime-config-client.html
- Online DDL Operations (MySQL 9.7), MySQL docsdev.mysql.com/doc/refman/9.7/en/innodb-online-ddl-operations.html
- Online DDL Performance and Concurrency (MySQL 9.7), MySQL docsdev.mysql.com/doc/refman/9.7/en/innodb-online-ddl-performance.html
- Index Builds on Populated Collections, MongoDB docswww.mongodb.com/docs/manual/core/index-creation/
- Specify Validation Level for Existing Documents, MongoDB docswww.mongodb.com/docs/manual/core/schema-validation/specify-validation-level/
- Parallel Change, martinfowler.commartinfowler.com/bliki/ParallelChange.html
Related on Sefism
Picked from the other libraries by shared topics.
Roadmaps
See allCheat sheets
See allBest practices
See all- PostgreSQL Best PracticesDesign, index, query and run PostgreSQL well: data types, constraints, EXPLAIN, zero-downtime migrations, pooling, transactions, backups and roles.
- MongoDB Best PracticesModel, index, query and run MongoDB well: access-pattern schemas, validation, the ESR rule, aggregation limits, write concern, transactions, drivers and security.
- Data Pipeline Best PracticesHow to build data pipelines you can re-run, test and trust: idempotent tasks, orchestrator hygiene, layered transformations, data tests, freshness, ownership and monitoring.
Checklists
See allProject structures
See all- Spring Boot project structureA REST API in Java with Spring Boot 4, PostgreSQL, Flyway and Testcontainers, organised by feature the way the Spring docs suggest. It grows from the Initializr scaffold to a service with JWT security, problem-detail errors, MapStruct DTOs, slice and integration tests, CI, containers and observability, and finally Modulith boundaries, contract and load tests. For students who know some Java and want to see how a real service is organised.
- ASP.NET Core Web API project structureA REST API in C# on .NET 10 with minimal API endpoints grouped by feature, EF Core and PostgreSQL, JWT auth with policies, xUnit and Testcontainers tests, and everything production needs: health checks, OpenTelemetry, rate limits, versioning, caching and a CI pipeline. It shows the popular clean architecture split (Api, Application, Domain, Infrastructure) honestly, and at Expert grows into Aspire, a modular monolith and enforced boundaries.
- Go REST API project structureA JSON API in Go using the standard library router, PostgreSQL through pgx and sqlc, SQL migrations with goose, and tests next to the code. It follows the layout the Go team itself documents: commands in cmd/, everything else in internal/, one package per feature plus a small platform package for the plumbing, and nothing added until it is needed.
Career questions
See all- Should I use PostgreSQL or MongoDB for my project?For most student and startup projects, PostgreSQL is the safer default: relational data, joins, constraints and transactions fit most apps, and it handles JSON and vector search too. MongoDB is a good fit when your data is naturally document-shaped and varies a lot, or your team already knows it well. Model your data first, then choose.
- Should I use Firebase or Supabase for my app?Both let you ship an app fast with auth, a database, storage and hosting-style services. Supabase is built on PostgreSQL, so it suits relational data and SQL skills you will reuse anywhere. Firebase is a mature Google platform with a document database and strong mobile tooling. Choose by your data shape, your platform, and how much you want to learn transferable database skills.
- Do I need a vector database for my AI project?Often not. For a student project or a small app, a vector extension on a database you already use (such as pgvector in PostgreSQL), or even an in-memory index, is enough. A dedicated vector database earns its place with very large collections, heavy query traffic, or features like advanced filtering and multi-tenancy at scale.
Stuck on something specific?
Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.
- 1:1 Mentorship
- Fresh Graduate Mentorship
- Career Guidance
Brand deals, promos and partnerships: tauseeffayyaz@gmail.com