The Table Lock Problem in PostgreSQL
Running php artisan migrate on a production table with five million rows can bring down your app in seconds. PostgreSQL takes an ACCESS EXCLUSIVE lock when modifying table structures in specific ways. While PostgreSQL 11 and newer allow adding columns with constant default values instantly without rewriting the entire table, other operations still stall incoming queries. If a web worker attempts to write to that table while the migration waits on a lock, your HTTP request pool fills up, connections exhaust, and PHP-FPM starts dropping 502 errors.
In Laravel 12 running on PHP 8.3, migrations execute sequentially in single transactions unless explicitly told otherwise. If one statement gets stuck waiting for a table lock, every subsequent migration statement halts. Your application thread pool dries up in about three seconds.
Setting Lock Timeouts in Laravel Migrations
By default, PostgreSQL will wait indefinitely to acquire an ACCESS EXCLUSIVE lock. If a long-running reporting query is scanning the table, your migration statement queue hangs until that query finishes. Meanwhile, every incoming app query piles up behind your migration statement.
You can break this cascade by configuring a strict lock_timeout. If the migration can't acquire the lock in 2000 milliseconds, PostgreSQL kills the migration immediately instead of letting web requests stack up behind it. Here is how you enforce this inside a Laravel schema builder statement:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
return new class extends Migration
{
public function up(): void
{
// Fail fast if lock cannot be acquired within 2 seconds
DB::statement('SET lock_timeout = "2000ms"');
Schema::table('users', function (Blueprint $table) {
$table->string('display_name')->nullable();
});
}
public function down(): void
{
DB::statement('SET lock_timeout = "2000ms"');
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('display_name');
});
}
};If this migration fails due to a timeout, your deployment script aborts, but your API continues serving traffic normally. You retry the deployment during a lower-traffic window or kill the long-running SELECT query holding the lock.
The Expand-Contract Pattern
Zero downtime deployments require running old code and new code simultaneously against the same database schema. When deploying a Next.js 16 frontend backed by Laravel 12, your rolling deployment means some web servers execute the new code while others still run the previous commit.
If you rename a column directly or add a NOT NULL column without a default value, the old application version breaks instantly. You must follow three separate deployment phases:
- Expand: Add the new column as nullable or with a non-blocking default value. Deploy app code that writes to both old and new columns.
- Backfill: Migrate historical data from the old structure to the new structure in background worker jobs.
- Contract: Update app code to read exclusively from the new column, then drop the old column in a subsequent release.
Safely Backfilling Data Without Melting the CPU
Never run massive UPDATE users SET ... queries inside a migration file. A single query updating one million rows creates a massive database transaction log, locks affected rows, and causes read/write replication lag on standby nodes. On an AWS Aurora PostgreSQL instance, updating 100,000 rows in one sweep can push CPU usage from 12% to 98% and lag read replicas by 45 seconds.
Instead, write an Artisan command that processes records in deterministic chunks using chunkById. This keeps transactions tiny, allows locks to release instantly, and prevents memory leaks inside PHP CLI workers.
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
class BackfillUserDisplayNames extends Command
{
protected $signature = 'db:backfill-display-names {--chunk=1000}';
protected $description = 'Backfills display_name column in chunks without locking users table';
public function handle(): int
{
$chunkSize = (int) $this->option('chunk');
$count = 0;
$this->info("Starting backfill in chunks of {$chunkSize}...");
DB::table('users')
->whereNull('display_name')
->whereNotNull('first_name')
->chunkById($chunkSize, function ($users) use (&$count) {
foreach ($users as $user) {
$displayName = trim($user->first_name . ' ' . $user->last_string_name);
DB::table('users')
->where('id', $user->id)
->update(['display_name' => $displayName]);
}
$count += $users->count();
$this->info("Processed {$count} records...");
// Sleep 50ms between chunks to let Postgres breathe
usleep(50000);
});
$this->info('Backfill complete.');
return Command::SUCCESS;
}
}Notice the usleep(50000) call between iterations. Yielding 50 milliseconds gives PostgreSQL CPU time to clean up dead tuples via autovacuum and allows high-priority web request transactions to acquire locks without queuing up.
Handling Schema Caching and Dropping Columns
Dropping a column is where teams get burned. You migrated the data, pointed the new Laravel code to display_name, and verified everything works. You write a migration with $table->dropColumn('first_name'), push to production, and instantly watch error trackers light up with SQL errors like Column not found: 7 ERROR: column "first_name" of relation "users" does not exist.
This happens because Eloquent generates queries like select * from users under the hood unless you explicitly select specific attributes. Furthermore, if you are running Octane or long-lived PHP processes, ORM model instances cache the database schema in memory upon boot.
To safely drop a column, complete these two deployment steps in order:
First step: Ignore the column in Eloquent model definitions before dropping it from PostgreSQL. In Laravel 12, you can inform Eloquent to ignore a column using the model's static attributes or query building filters.
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
// Prevent Eloquent from requesting or writing to deleted schema fields
protected $hidden = [
'first_name',
];
}Second step: In your next deployment, after all PHP-FPM processes or Octane workers have reloaded with the new code, execute the migration dropping the database column.
Adding Indexes Concurrently in Postgres
Standard index creation in PostgreSQL (CREATE INDEX) locks the table against all writes until the entire index build finishes. On a table with 20 million rows, this build process can take 8 to 15 minutes. During that entire window, every INSERT, UPDATE, and DELETE statement fails or hangs.
PostgreSQL provides CREATE INDEX CONCURRENTLY to build indexes without blocking writes. It requires scanning the table twice and takes slightly longer, but your users never notice it running. However, Postgres cannot run concurrent index builds inside a transaction block.
Laravel supports non-transactional migrations specifically for this reason. Set public $withinTransaction = false; on your migration class before invoking indexConcurrently():
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
return new class extends Migration
{
// Disable automatic transaction wrapper for concurrent index creation
public bool $withinTransaction = false;
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->indexConcurrently(['email', 'created_at'], 'idx_users_email_created');
});
}
public function down(): void
{
Schema::table('users', function (Blueprint $table) {
$table->dropIndexByName('idx_users_email_created');
});
}
};If a concurrent index build fails midway—due to a dropped connection or dead lock—PostgreSQL leaves behind an INVALID index. You must manually drop that invalid index before re-running the migration, or php artisan migrate will throw a duplicate index name error on subsequent attempts.
Building resilient database pipelines isn't about avoiding schema changes; it's about treating schema changes as multi-phase deployment pipelines. Set strict lock timeouts, execute backfills in low-impact background chunks, and decouple column deletion from application code deployment.
