--- title: Zero-Downtime Database Migrations with Expand-Contract Pattern description: Learn how to implement zero-downtime database migrations using the expand-contract pattern to ensure smooth schema changes without outages. image: https://images.unsplash.com/photo-1544383835-bda2bc66a55d?w=1400&q=80 --- [![Thought Parameters](https://legacy.thoughtparameters.com/logos/logo-horizontal-inverted.png)](https://thoughtparameters.com/) Products [Identity & Access Directory, SSO, SCIM and passkey MFA for every app.](https://thoughtparameters.com/products/identity) [SRE Reliability SLOs, error budgets, dependency graphs and status pages.](https://thoughtparameters.com/products/sre) [DORA Metrics Delivery metrics tied to reliability and error budgets.](https://thoughtparameters.com/products/dora) [Incident & On-call Schedules, escalations, SMS and voice paging.](https://thoughtparameters.com/products/oncall) [ITSM & Service Desk AI-triaged service desk with a shared CMDB.](https://thoughtparameters.com/products/itsm) [Developer Portal Service catalog, scorecards and golden paths.](https://thoughtparameters.com/products/portal) [Secrets & Feature Flags Secrets, config and edge-evaluated flags.](https://thoughtparameters.com/products/vault) [Platform overview → How the products fit together, plus early-access demos.](https://thoughtparameters.com/platform) Services [DevOps & CI/CD Automated pipelines and deployment workflows that ship safely.](https://thoughtparameters.com/services/devops-cicd) [Platform Engineering Internal platforms and golden paths for engineering teams.](https://thoughtparameters.com/services/platform-engineering) [Site Reliability Engineering SLOs, observability, and incident response that hold up.](https://thoughtparameters.com/services/site-reliability-engineering) [Cloud Infrastructure GCP and AWS architecture, migrations, and infrastructure as code.](https://thoughtparameters.com/services/cloud-infrastructure) [Cybersecurity & Compliance Security built into delivery, not bolted on after an incident.](https://thoughtparameters.com/services/cybersecurity-compliance) [Custom Software Development Web apps, APIs, and internal tools built to last.](https://thoughtparameters.com/services/custom-software-development) [Fractional CTO & Advisory Senior technical leadership without the executive headcount.](https://thoughtparameters.com/services/fractional-cto-advisory) [View all services →](https://thoughtparameters.com/services) Industries [SaaS & Software Startups Scaling infrastructure ahead of growth, without over-engineering it.](https://thoughtparameters.com/industries/saas-startups) [Fintech & Financial Services Auditable, secure, and built to hold up under scrutiny.](https://thoughtparameters.com/industries/fintech-financial-services) [E-commerce & Retail Tech Infrastructure that survives your busiest day of the year.](https://thoughtparameters.com/industries/ecommerce-retail-tech) [Healthcare & Health Tech Software that respects the weight of the data it holds.](https://thoughtparameters.com/industries/healthcare-health-tech) [Enterprise & Mid-Market Modernization without the multi-year rewrite.](https://thoughtparameters.com/industries/enterprise-midmarket) [Developer Tools & Infrastructure Held to the standard your own customers hold you to.](https://thoughtparameters.com/industries/developer-tools-infrastructure) [View all industries →](https://thoughtparameters.com/industries) Company [About Who we are, where we're based, and how we think about the work.](https://thoughtparameters.com/about) [How We Work Our process, from first call to delivered software.](https://thoughtparameters.com/how-we-work) [News Product launches, site updates, and other Thought Parameters news.](https://thoughtparameters.com/news) [Blog Engineering notes on DevOps, cloud infrastructure, and security.](https://thoughtparameters.com/thought-parameters-llc-blog) [Careers Open roles at Thought Parameters — when the work calls for one.](https://thoughtparameters.com/careers) [Latest news →](https://thoughtparameters.com/news) [Call toll-free: 1 (888) 875-1399](tel:+18888751399) [Free Consultation](https://thoughtparameters.com/free-consultation) [1 (888) 875-1399](tel:+18888751399) [Free Consultation](https://thoughtparameters.com/free-consultation) ☰ [← Back to blog](https://thoughtparameters.com/thought-parameters-llc-blog) SRE # Zero-Downtime Database Migrations with Expand-Contract Pattern [Jason Miller](https://thoughtparameters.com/thought-parameters-llc-blog/author/jason-miller) · Sep 19, 2026, 3:42:06 AM ![](https://images.unsplash.com/photo-1544383835-bda2bc66a55d?w=1400&q=80) ![Zero-Downtime Database Migrations](https://images.unsplash.com/photo-1544383835-bda2bc66a55d?w=1400&q=80) Somewhere in your production database is a migration that ran fine in staging and then took down the app for six minutes. Usually it's an `ALTER TABLE` that looked innocent — adding a `NOT NULL` column, renaming something, adding an index — and turned into a full table lock on a table with 40 million rows. The deploy pipeline didn't fail. The database just stopped taking writes while everyone stared at a spinning dashboard. Zero-downtime database migrations are not a checkbox in your migration tool. They're a discipline: you stop thinking of a schema change as one atomic step and start thinking of it as a sequence of backward-compatible steps, each safe to ship on its own. The name for that discipline is the **expand-contract pattern**, and if you're running any service that can't afford a maintenance window, it should be the default way your team changes schema — not the thing you reach for only on the scary migrations. ## Why "just run the migration" breaks in production The naive approach — write the migration, run it in the same deploy as the code that depends on it — works fine on a database with a few thousand rows and no live traffic. It falls apart for three reasons at scale: 1. **Locking.** Many schema changes take locks that block reads or writes for the duration of the operation. `ADD COLUMN ... NOT NULL DEFAULT` in older Postgres versions rewrites the entire table. Adding a foreign key validates every existing row under a lock. On a large table, that's not milliseconds — it's minutes. 2. **Ordering.** A single deploy is not atomic across your fleet. If you deploy new application code and a new schema in the same release, there is a window — sometimes seconds, sometimes the length of a rolling deploy — where old code is running against the new schema, or new code against the old schema. If the schema change isn't backward-compatible with the previous app version, that window produces errors or corrupted data. 3. **No rollback path.** If the migration itself is destructive (dropped column, renamed table), rolling back the application code doesn't undo the database change. You're stuck fixing forward under pressure. The fix isn't a smarter migration tool. It's separating "the database supports both old and new shapes" from "the application only uses the new shape" into two distinct, independently deployable steps. ## The expand-contract pattern The pattern has three phases: - **Expand** — add the new schema alongside the old one. Nothing is removed. Both old and new application code can run against this schema simultaneously. - **Migrate** — backfill data and cut application code over to the new schema, in a rolling deploy, while the old schema is still present as a fallback. - **Contract** — once every instance is running the new code and you've verified nothing depends on the old shape, remove the old schema. Each phase is its own migration, its own deploy, and — critically — its own decision point where you can pause, verify, or abort without an outage. ### Example: renaming a column Renaming `users.email` to `users.email_address` is the textbook case for why you can't do this in one step. Here's the expand-contract version using a tool like `golang-migrate`, Flyway, or Rails migrations — the SQL is the same regardless of tool. **Expand (migration 1):** ``` ALTER TABLE users ADD COLUMN email_address TEXT; -- Backfill in batches, not one giant UPDATE UPDATE users SET email_address = email WHERE id BETWEEN 1 AND 10000 AND email_address IS NULL; -- repeat in batches until done, or use a background job ``` At this point, `email` and `email_address` both exist and are kept in sync. To handle rows written by old code during the rollout, add a trigger or, more simply, dual-write from the application: ``` def create_user(email): db.execute( "INSERT INTO users (email, email_address) VALUES (%s, %s)", (email, email), ) ``` **Migrate (deploy, then migration 2):** Ship application code that reads from `email_address` instead of `email`. Because the expand step guarantees `email_address` is populated for every row — old and new — this deploy is safe to roll out gradually. If it goes wrong, you roll back the app deploy, not the database. **Contract (migration 3, days or weeks later):** ``` ALTER TABLE users DROP COLUMN email; ``` Only run this after you've confirmed — via query logs, APM, or a grep across the codebase — that nothing still references the old column. This is the step teams skip, and skipped contracts are how you end up with a database full of zombie columns nobody's brave enough to remove. ### Example: adding a NOT NULL column safely This is the single most common way teams accidentally lock a production table. Don't do this: ``` ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'; ``` On Postgres 11+ this specific case is actually safe because a constant default doesn't require a full rewrite — but the moment your default is an expression, or you're on an older engine, or you're adding a `NOT NULL` to an existing nullable column, you're back to a full table scan under lock. The expand-contract version sidesteps engine version trivia entirely: ``` -- Expand: nullable, no default constraint yet ALTER TABLE orders ADD COLUMN status TEXT; -- Backfill in batches UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN 1 AND 50000; -- Once fully backfilled and app writes it on every insert: ALTER TABLE orders ALTER COLUMN status SET NOT NULL; ``` Enforce the `NOT NULL` constraint only after every row has a value and every code path writes one. Validating a `NOT NULL` constraint on Postgres does still require a scan, but with `NOT VALID` / `VALIDATE CONSTRAINT` for check constraints (and native support for this pattern with foreign keys) you can split even that into a non-blocking step: ``` ALTER TABLE orders ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT status_not_null; -- scans without blocking writes ``` ## Implementation checklist If you're introducing this pattern to a team that's used to single-step migrations, here's the sequence that works: 1. **Classify the migration.** Additive (new column, new table, new index) is usually safe in one step if you use `CREATE INDEX CONCURRENTLY` and nullable columns. Destructive or renaming changes always need expand-contract. 2. **Write the expand migration** and get it deployed and backfilled before touching application code. 3. **Batch your backfills.** A single `UPDATE` across 40 million rows holds locks and bloats your WAL. Batch in chunks of 1,000–50,000 rows with a short sleep between batches, or push the backfill into a background job with retry logic. 4. **Deploy app code changes separately** from schema changes, and make sure the app can tolerate the schema in both its old and new states for the duration of the rollout. 5. **Verify before contracting.** Check query logs, dashboards, or add temporary logging on the old column/table to confirm nothing still touches it. 6. **Set a contract deadline.** Expand steps that never get contracted are how schemas accumulate cruft. Put the contract migration on the sprint board, not just "someday." ## Common pitfalls **Doing it all in one deploy anyway, because "it's a small migration."** Table size isn't the only risk — lock duration depends on your database's specific mechanics per operation. `ADD COLUMN` behavior differs across Postgres versions and wildly between Postgres and MySQL. Don't estimate risk by table size alone; know what your specific migration does under your specific engine version. **Forgetting the dual-write step.** If you backfill once and don't dual-write during the migrate phase, any row created or updated between your backfill and your cutover deploy is missing data in the new column. This is the single most common bug in expand-contract migrations — a silent partial backfill that only surfaces later as null-pointer-shaped bugs in production. **Skipping the contract phase indefinitely.** Every unfinished contract is a small tax: extra storage, extra confusion for new engineers, extra surface area for someone to write new code against the wrong column. Track expand migrations in the same place you track tech debt, and set a real deadline for contracting them. **Running backfills without rate limiting.** A backfill that saturates your primary's I/O will show up as latency spikes for every other query hitting that table. Batch it, and if you have read replicas, consider running the backfill against off-peak traffic windows or throttling based on replication lag. **Testing migrations only against a small local database.** A migration that runs in 40ms locally can take 20 minutes against production data volume, and lock behavior under concurrent traffic doesn't show up in a single-connection local test at all. Run migration timing tests against a production-sized snapshot before you ship anything destructive. **Treating this as a database-only concern.** Expand-contract only works if application code is written to tolerate both schema states during the migrate phase. That's a code review discipline, not just a DBA discipline — reviewers need to know a migration is in flight and check that new code doesn't assume the contract phase has already happened. ## The payoff None of this is exotic. Every mature engineering org running a database at scale ends up converging on some version of expand-contract, usually after their first bad lock-related outage. The teams that do it well just formalize it earlier — as a written playbook, a migration template, and a CI check that flags destructive schema changes so they get routed through the multi-step process instead of shipped in one commit. If your team is still debating whether a migration needs a maintenance window, or you've had a deploy take down writes because of a lock nobody anticipated, that's usually a sign the process needs codifying, not just the next migration needing more care. We help engineering teams build exactly this kind of migration discipline into their CI/CD pipeline — from templated expand-contract migrations to the automated checks that catch a risky `ALTER TABLE` before it ships. [Get in touch](https://thoughtparameters.com/contact) if you want a second set of eyes on your migration strategy before your next major schema change. ## Have a project in mind? The first call is always free — tell us what you're building. [Book a free consultation](https://thoughtparameters.com/free-consultation) [![Thought Parameters](https://legacy.thoughtparameters.com/logos/logo-horizontal-inverted.png)](https://thoughtparameters.com/) Where Ideas Meet Intelligent Systems. - - - - - - Products [Identity & Access](https://thoughtparameters.com/products/identity) [SRE Reliability](https://thoughtparameters.com/products/sre) [DORA Metrics](https://thoughtparameters.com/products/dora) [Incident & On-call](https://thoughtparameters.com/products/oncall) [ITSM & Service Desk](https://thoughtparameters.com/products/itsm) [Developer Portal](https://thoughtparameters.com/products/portal) [Secrets & Feature Flags](https://thoughtparameters.com/products/vault) [Request a demo →](https://thoughtparameters.com/platform#request-demo) Services [DevOps & CI/CD](https://thoughtparameters.com/services/devops-cicd) [Platform Engineering](https://thoughtparameters.com/services/platform-engineering) [SRE](https://thoughtparameters.com/services/site-reliability-engineering) [Cloud Infrastructure](https://thoughtparameters.com/services/cloud-infrastructure) [Cybersecurity](https://thoughtparameters.com/services/cybersecurity-compliance) [View all services →](https://thoughtparameters.com/services) Company [About](https://thoughtparameters.com/about) [Industries](https://thoughtparameters.com/industries) [How We Work](https://thoughtparameters.com/how-we-work) [Get Started](https://thoughtparameters.com/get-started) [Careers](https://thoughtparameters.com/careers) [Blog](https://thoughtparameters.com/thought-parameters-llc-blog) [News](https://thoughtparameters.com/news) Get in touch [1 (888) 875-1399 (toll-free)](tel:+18888751399) [Free Consultation](https://thoughtparameters.com/free-consultation) [Contact](https://thoughtparameters.com/contact) [Newsletter](https://thoughtparameters.com/newsletter) [hello@thoughtparameters.com](mailto:hello@thoughtparameters.com) © 2026 Thought Parameters LLC. All rights reserved. Placentia, CA ```json { "@context" : "https://schema.org", "@type" : "ProfessionalService", "address" : { "@type" : "PostalAddress", "addressCountry" : "US", "addressLocality" : "Placentia", "addressRegion" : "CA" }, "areaServed" : "US", "description" : "Senior DevOps, cloud infrastructure, cybersecurity, and custom software development consulting for startups and growing engineering teams.", "email" : "hello@thoughtparameters.com", "image" : "https://legacy.thoughtparameters.com/logos/logo-horizontal-inverted.png", "logo" : "https://legacy.thoughtparameters.com/logos/logo-horizontal-inverted.png", "name" : "Thought Parameters LLC", "priceRange" : "$$", "url" : "https://thoughtparameters.com" } ``` ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Jason Miller", "url" : "https://thoughtparameters.com/thought-parameters-llc-blog/author/jason-miller" }, "dateModified" : "2026-09-19T07:42:06.577Z", "datePublished" : "2026-09-19T07:42:06.000Z", "headline" : "Zero-Downtime Database Migrations with Expand-Contract Pattern", "image" : [ "https://images.unsplash.com/photo-1544383835-bda2bc66a55d?w=1400&q=80" ], "mainEntityOfPage" : { "@id" : "https://thoughtparameters.com/thought-parameters-llc-blog/zero-downtime-database-migrations-the-expand-contract-pattern-in-practice", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "https://thoughtparameters.com/hubfs/logo-horizontal-inverted-1.png" } } } ```