Zero-Downtime Database Migrations in Laravel & Node.js SaaS: Expand-Contract & Lock Timeouts

Most SaaS outages I get called in to fix after a deploy are not caused by bad application code. They are caused by a migration that looked harmless in review: ALTER TABLE orders ADD COLUMN ... NOT NULL DEFAULT ..., a rename, or a new index on a 40-million-row table. The migration waits for a lock, every query behind it queues up, the connection pool fills, and the health check starts failing across the whole fleet.

At Loop Origin we run Laravel and Node.js SaaS backends on PostgreSQL and MySQL for clients who cannot schedule maintenance windows. This post is the playbook we use: expand-contract migrations, lock timeouts on every DDL statement, batched backfills, and a deploy order that lets old and new code run against the same schema at the same time.

📚 Series Roadmap — Production AI & SaaS Systems Engineering

• Part 1: Building Production MCP Servers & Tool-Calling AI Agents: Sandboxing, Schemas & Approval Gates
• Part 2 (This Post): Zero-Downtime Database Migrations in Laravel & Node.js SaaS: Expand-Contract & Lock Timeouts
• Part 3: Multi-Tier Caching & Stampede Protection with Redis/Valkey: Singleflight Locks & SWR
• Part 4: SaaS Usage-Based Billing & Metering Architecture: Event Ingestion, Rollups & Stripe Invoicing
• Part 5: Production Observability on a Budget: OpenTelemetry Traces, Structured Logs & Tail Sampling
• Part 6: LLM Gateway & FinOps Architecture for B2B SaaS: Semantic Caching, Routing & Tenant Quotas

How do you run zero-downtime database migrations in a Laravel or Node.js SaaS?

Direct answer: Split every breaking schema change into expand and contract phases so old and new code can run against the same schema. Set a short lock_timeout on every DDL statement and retry instead of queuing, build indexes concurrently, backfill data in small batches outside the migration, and only drop old columns after every running process stops reading them.

Why "small" migrations take production down

The dangerous part of a migration is rarely how long it runs. It is which lock it asks for and what is already holding a lock on that table.

In PostgreSQL, most ALTER TABLE forms need an ACCESS EXCLUSIVE lock. That lock conflicts with everything, including plain SELECT. If a long-running report query or an idle-in-transaction worker holds even a weak lock on orders, your ALTER waits. While it waits, it sits at the front of the lock queue, and every new SELECT on orders queues behind it. A migration that would have taken 20 milliseconds turns into a two-minute full outage of every page that touches orders.

MySQL 8 has a similar problem with metadata locks (MDL). Online DDL (ALGORITHM=INPLACE or INSTANT) avoids copying the table, but it still needs a brief exclusive metadata lock at the start and end, and that request blocks behind any open transaction on the table.

Here is how common operations behave on recent versions:

Operation	PostgreSQL 14+	MySQL 8.0 (InnoDB)	Safe pattern
Add nullable column	Instant, brief exclusive lock	INSTANT, brief MDL	Safe with lock_timeout
Add column with constant default	Instant (PG 11+)	INSTANT (8.0.12+)	Safe with lock_timeout
Add NOT NULL to existing column	Full table scan under exclusive lock	Table rebuild	Add CHECK ... NOT VALID, validate, then set
Create index	Blocks writes	INPLACE, allows writes	CREATE INDEX CONCURRENTLY / ALGORITHM=INPLACE, LOCK=NONE
Rename column	Instant, but breaks running code	Instant, but breaks running code	Expand-contract with a new column
Change column type	Usually a full rewrite	Table copy	New column + backfill + swap
Add foreign key	Scans the table under lock	Can rebuild	NOT VALID then VALIDATE CONSTRAINT
Drop column	Instant	INSTANT (8.0.29+)	Only after code stops reading it

The pattern in the last column is the same every time: never ask the database to do a long operation while holding a strong lock, and never make a change that the currently deployed code cannot survive.

The expand-contract pattern, step by step

Say we need to rename customers.phone to customers.phone_e164 and enforce a normalized format. A direct rename breaks every web pod and queue worker still running the previous release during a rolling deploy. Expand-contract turns it into five deploys:

1. Expand: add phone_e164 as a nullable column. No code reads it yet.
2. Dual write: release code that writes both phone and phone_e164, and still reads phone.
3. Backfill: copy and normalize existing rows in batches from a queued job, not from the migration.
4. Switch reads: release code that reads phone_e164 and still writes both, so a rollback is safe.
5. Contract: stop writing phone, wait for every old process to drain, then drop it.

That looks slow on paper. In practice, steps 1 and 2 ship together, and steps 4 and 5 are a day or a week apart. What you buy is that every single deploy can be rolled back without touching data.

Lock timeouts on every DDL statement

The single most effective change we made across client projects was refusing to run DDL without a lock timeout. If the lock is not available in a few seconds, fail fast, let the deploy retry, and nobody notices.

In a Laravel migration on PostgreSQL:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement("SET lock_timeout = '3s'");
        DB::statement("SET statement_timeout = '15s'");

        Schema::table('customers', function (Blueprint $table) {
            $table->string('phone_e164', 20)->nullable();
        });
    }

    public function down(): void
    {
        DB::statement("SET lock_timeout = '3s'");
        Schema::table('customers', fn (Blueprint $t) => $t->dropColumn('phone_e164'));
    }
};

For MySQL, the equivalent is SET SESSION lock_wait_timeout = 3; before the statement. Wrap php artisan migrate --force in your deploy script with a retry loop of three to five attempts and a short sleep, so a busy moment does not fail the whole release.

In Node.js with Knex, the same idea:

// migrations/20261031_add_phone_e164.js
exports.config = { transaction: false };

exports.up = async (knex) => {
  await knex.raw("SET lock_timeout = '3s'");
  await knex.schema.alterTable('customers', (t) => {
    t.string('phone_e164', 20).nullable();
  });
};

exports.down = async (knex) => {
  await knex.raw("SET lock_timeout = '3s'");
  await knex.schema.alterTable('customers', (t) => t.dropColumn('phone_e164'));
};

Note transaction: false. That matters for the next step.

Building indexes without blocking writes

CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and both Laravel and most Node migration tools wrap PostgreSQL migrations in a transaction by default. In Laravel, set public $withinTransaction = false; on the migration class:

return new class extends Migration
{
    public $withinTransaction = false;

    public function up(): void
    {
        DB::statement("SET lock_timeout = '3s'");
        DB::statement('CREATE INDEX CONCURRENTLY IF NOT EXISTS customers_phone_e164_idx ON customers (phone_e164)');
    }

    public function down(): void
    {
        DB::statement('DROP INDEX CONCURRENTLY IF EXISTS customers_phone_e164_idx');
    }
};

If a concurrent build fails halfway, PostgreSQL leaves an INVALID index behind. Check for it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, drop it, and rerun. Column order still matters for composite indexes; I covered that in Composite Indexes in PostgreSQL & MySQL: Column Order Guide.

Backfills belong in jobs, not migrations

A migration that runs UPDATE customers SET phone_e164 = ... across 12 million rows holds row locks for the whole statement, bloats the WAL, and can stall replicas. Instead, dispatch a queued job that walks the primary key in chunks:

// app/Jobs/BackfillPhoneE164.php
public function handle(): void
{
    DB::table('customers')
        ->whereNull('phone_e164')
        ->whereNotNull('phone')
        ->orderBy('id')
        ->chunkById(1000, function ($rows) {
            foreach ($rows as $row) {
                DB::table('customers')
                    ->where('id', $row->id)
                    ->whereNull('phone_e164')
                    ->update(['phone_e164' => PhoneNormalizer::toE164($row->phone)]);
            }
            usleep(50_000); // give replicas and autovacuum room to breathe
        });
}

The whereNull guard makes the job idempotent, so it can be retried or run twice without overwriting values the dual-write code already set. Run it on a dedicated low-concurrency queue; the patterns in Running Laravel Queues in Production apply directly. Watch replica lag while it runs and lower the chunk size if lag climbs above a few seconds.

Enforcing NOT NULL without a long lock

Once the backfill is done, you want the database to guarantee the column is filled. ALTER COLUMN ... SET NOT NULL scans the whole table under an exclusive lock. On PostgreSQL 12+, add a check constraint as NOT VALID first, validate it separately (validation takes a weaker lock that allows reads and writes), and then SET NOT NULL becomes nearly instant because PostgreSQL can use the validated constraint as proof:

SET lock_timeout = '3s';
ALTER TABLE customers ADD CONSTRAINT customers_phone_e164_nn CHECK (phone_e164 IS NOT NULL) NOT VALID;

-- separate migration, can take minutes, does not block writes
ALTER TABLE customers VALIDATE CONSTRAINT customers_phone_e164_nn;

SET lock_timeout = '3s';
ALTER TABLE customers ALTER COLUMN phone_e164 SET NOT NULL;
ALTER TABLE customers DROP CONSTRAINT customers_phone_e164_nn;

The same NOT VALID then VALIDATE trick works for foreign keys.

Deploy order for rolling releases

In a rolling deploy on Kubernetes, Forge with zero-downtime releases, or a blue/green setup, old and new code overlap for seconds to minutes. Queue workers are the ones people forget: a Horizon or BullMQ worker can keep running the previous release for as long as its longest job takes. The rule we follow:

• Expand migrations run before the new code goes live. The old code must tolerate the new schema.
• Contract migrations run in a later release, after php artisan queue:restart or the BullMQ worker rollout has fully finished.
• Never mix expand and contract in one release. If you need both, it is two releases.

If you deploy to a single VPS, the same rules apply; the overlap is just shorter. I compared those hosting setups in VPS vs Managed PaaS for Laravel & Node SaaS: Real TCO.

Pre-merge migration checklist

1. Does every DDL statement set lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL)?
2. Can the currently deployed code run against the new schema? If not, split the change.
3. Are indexes built CONCURRENTLY or with ALGORITHM=INPLACE, LOCK=NONE, outside a transaction?
4. Is any data backfill in a chunked, idempotent job rather than the migration?
5. Are NOT NULL and foreign keys added as NOT VALID and validated separately?
6. Is the down() method safe to run, or is the rollback plan "deploy the previous code" instead?
7. Have you checked pg_stat_activity for long-running or idle-in-transaction sessions before deploying?
8. Is there a contract ticket with a date so the old column actually gets removed?

Frequently asked questions

Should I use pt-online-schema-change or gh-ost on MySQL?

For tables in the tens of millions of rows where a change still needs a table copy, yes. gh-ost uses the binlog instead of triggers, which puts less load on the primary. For INSTANT operations on MySQL 8, they are unnecessary.

Is php artisan migrate during deploy safe at all?

Yes, as long as each migration follows the rules above and runs once, before new pods take traffic. Use --isolated (Laravel 9.24+) so two deploy hosts do not race to run the same migration.

What lock timeout value should I use?

Two to five seconds works for most SaaS traffic. Long enough to get the lock in a normal moment, short enough that users barely notice the queued requests if it waits.

How do I handle a column rename that an ORM model depends on?

Keep the model reading the old attribute during the dual-write phase, using an accessor or a mapped column name, then switch the mapping in the read-switch release. Prisma's @map and Eloquent accessors both make this clean.

Do multi-tenant schemas (one schema per tenant) change the approach?

The pattern is the same, but you run each migration N times. Throttle across tenants, track progress per tenant, and make every migration idempotent so a partial run can resume.

How do I test that a migration is actually safe?

Restore a recent production snapshot into staging, run a load generator against it, then run the migration and watch pg_locks and p99 latency. A migration that is safe on an empty dev database tells you nothing.

Next in this series, Part 3 covers multi-tier caching and stampede protection with Redis and Valkey. If you are planning a risky schema change on a live SaaS and want a second pair of eyes, our Laravel development team and server setup & maintenance service do exactly this kind of work, or you can get in touch directly.

---

Follow & Connect

• Website: Loop Origin
• LinkedIn: Loop Origin
• X: Muhammad Ali Mughal (@AliMughal887)
• GitHub: Loop Origin (@LoopOrigin)