Moving and Upgrading Databases Safely

Why Database Migrations Fail in Production

Most tutorials teach database migrations as a straightforward pattern: write an "up" method to apply the change, write a "down" method to reverse it, run the command. This works in development. It fails in production for three reasons, and each one becomes more acute as your system scales beyond a single server.

The gap between tutorial-level migrations and production-grade database migration strategy is where most teams get bitten. Without disciplined schema evolution, you end up with schema drift between environments and no reliable way to reproduce what ran where. The patterns that follow close that gap.

Safe Operations Versus Dangerous Operations

Not all schema migrations carry the same risk. The distinction matters because it determines your deployment strategy.

The rule is straightforward: anything additive is generally safe. Anything that modifies or removes existing structures needs a multi-step approach with code changes deployed before and after the schema change. This applies to your application code, but also to any API integrations that query the affected tables directly.

Zero-Downtime Migration Patterns

For any application that cannot tolerate downtime during deploys, database migrations must be backward-compatible. Old code and new code must both function correctly against the schema at every intermediate state.

The expand-contract pattern

This is the workhorse pattern for any non-trivial schema change. It works in three phases.

Deployment ordering

The sequence matters. For adding a column: deploy the migration first, then deploy the code that uses it. For removing a column: deploy the code that stops using it first, then deploy the migration that drops it. Getting this backwards is one of the most common causes of production incidents during deploys.

In our CI/CD pipelines, we enforce this ordering through deployment checklists and migration review gates.

Feature flags as a safety net

For complex schema transitions, feature flags add a layer of control that deployment ordering alone cannot provide. The pattern works like this: deploy the expand-phase migration, gate the new code path behind a feature flag, enable the flag for a small percentage of traffic, verify correctness, then ramp to 100%. If anything goes wrong, disable the flag immediately. The schema stays expanded, no rollback needed, and you debug at your own pace.

This is particularly valuable during the dual-write phase of an expand-contract migration. The flag controls which code path is active, so you can switch between old and new behaviour without redeploying. Once the contract phase is complete and the old structure is removed, retire the flag. The discipline is in the lifecycle: a flag that outlives its migration becomes technical debt. We tie flag cleanup to the same ticket that tracks the contract-phase migration.

Large Table Migrations

When a table exceeds one million rows, standard ALTER TABLE statements become risky. The database engine may need to rewrite the entire table, locking it for the duration. On a busy table, this means blocked writes, queued requests, and eventually timeouts.

Row count risk thresholds

Online schema change tools

These tools modify large tables without holding long-running locks.

PostgreSQL's built-in advantages

PostgreSQL handles large table migrations better than MySQL in several material ways, which is one reason we prefer it for production systems.

• Non-blocking column additions: Adding a nullable column does not rewrite the table. Since version 11, ALTER TABLE ... ADD COLUMN with a DEFAULT value also avoids table rewrites, making it safe even on tables with tens of millions of rows.
• Concurrent index creation: CREATE INDEX CONCURRENTLY builds the index without holding an ACCESS EXCLUSIVE lock, so writes continue uninterrupted. The trade-off is a longer build time and the possibility of an invalid index if the operation fails partway through (check for invalid indexes by querying pg_index where indisvalid = false, drop any invalid indexes, then retry).
• Non-blocking constraint validation: Add a constraint with NOT VALID to skip checking existing rows, then run VALIDATE CONSTRAINT separately. Validation takes a weaker SHARE UPDATE EXCLUSIVE lock, allowing concurrent reads and writes. This is the safe pattern for adding foreign keys or CHECK constraints to large tables.
• Transactional DDL: Unlike MySQL, where DDL statements are auto-committed and partial failures leave orphaned structures, PostgreSQL wraps DDL in transactions. A failed migration rolls back cleanly, leaving the schema untouched.

These capabilities often eliminate the need for external tools like gh-ost or pt-online-schema-change. We select the approach based on the database engine, table size, write volume, and acceptable risk window. For MySQL, where native online DDL is more limited, external tools remain essential for high-row-count tables.

Data Migrations Versus Schema Migrations

Schema migrations change the structure of your database: columns, indexes, constraints, tables. Data migrations change the content: backfilling a new column, transforming existing values, merging duplicate records. Mixing them in a single migration file is a common mistake. They should be separate because they differ in critical ways.

In Laravel's migration system, we structure these as separate migration files with clear naming conventions: add_status_column_to_orders for schema, backfill_order_status_from_legacy_field for data. The ordering ensures the schema change runs first.

For data migrations that touch millions of rows, we use background jobs with progress tracking. The migration file dispatches the job; the job batches the data transformation. Each batch must be idempotent so that a partial failure followed by a retry does not corrupt already-migrated rows. This keeps the deploy fast and the data migration observable.

Rollback Viability by Migration Type

Everyone assumes rollbacks work until they need one. The reality is that rollback safety depends entirely on the type of migration and how much time has passed since it ran. This table covers the five common categories.

The key question is not "can I roll back?" but "when does rolling forward become safer than rolling back?" Once new data has been written to a new column or new structure, rolling back destroys that data. After that threshold, fixing the problem in place and deploying a corrective migration is almost always the right call. This is why every destructive migration should have a documented roll-forward plan, not just a down() method. Understanding your recovery point objective (RPO) and recovery time objective (RTO) before you deploy helps you decide how much risk each migration type actually carries.

Multi-Tenant Migration Patterns

Running database migrations across multiple tenants introduces coordination challenges that single-database applications do not face. These patterns handle both shared-database and database-per-tenant architectures in multi-tenant Laravel systems.

This is one area where the data model design has long-term consequences. Choosing shared versus per-tenant databases in the early stages of the project determines your migration complexity for the life of the application.

Migration File Discipline

Migration files are not throwaway scripts. They are a permanent record of your schema's evolution, and treating them with the same rigour as application code prevents an entire category of team-level problems.

These rules matter most when multiple developers are creating migrations on different branches. Timestamp-based ordering (Laravel's default) handles most conflicts, but two developers modifying the same table simultaneously will still produce migrations that work individually and fail when merged. The fix is a review convention: any migration touching a shared table gets flagged in the pull request for sequencing review.

Testing Migrations in CI/CD Pipelines

Migrations that are not tested before production will eventually fail in production. The testing strategy depends on what can go wrong.

Migration review checklist

Before any migration reaches production, we verify six conditions.

• ✓
  Deployment order documented Does it require migration before code, or code before migration?
• ✓
  Table size assessed Does it touch a table with more than 100,000 rows?
• ✓
  Destructive changes flagged Does it modify or remove an existing column?
• ✓
  Rollback tested Has the "down" method been verified on a test database?
• ✓
  Data migration separated Does it include a data migration that should be its own file?
• ✓
  Production-scale timing measured Has it been tested against realistic data volumes?

This checklist catches the majority of migration incidents before they reach production. We enforce it through pull request templates and automated CI checks.

Common Failure Modes

These are the migration failure patterns that cause the most damage in production. Each one is drawn from a real incident.

What good migration practice looks like

Database migrations are one of those areas where the difference between "it works" and "it works in production" is significant. Every pattern described above comes from real incidents, real production systems, and real fixes applied under pressure.

Treating migrations as first-class code means they go through code review, they have tests, and they have a deployment strategy. For systems with strict audit requirements, migrations are logged and tied to deploy records.

The result is that schema changes become routine. Changes that once required weekend maintenance windows deploy during business hours without anyone noticing. That is the goal: treating your database as code, with versioning that is as boring and reliable as the rest of your deployment pipeline. Tools like Flyway and Liquibase follow the same principles for non-Laravel stacks, but Laravel's built-in migration system handles the vast majority of what we need. For applications already in production, schema management is part of our ongoing support service, where migration discipline is maintained alongside security patches and feature work.