A while back, we got the email every team dreads a little: a payment processor we'd been integrated with for years announced it was killing its old API. Not eventually, a hard date. Move to their new SDK or lose the integration.
That should've been contained. It wasn't because the new SDK wanted a different data shape underneath, not just a different way of calling the same endpoints. Customer profiles, payment methods, transaction logs, all of it had been sitting in one sprawling users table for years, and the new provider needed that normalized into proper tables with external ID mappings. The SDK had also quietly dropped PHP 7 support, so the Laravel upgrade wasn't optional anymore; it was bundled into the same deadline.
So the real task: upgrade the framework across several major versions, restructure a production database with millions of rows, and never take the site down while doing it. Here's how that went.
A few terms first
cursor() and LazyCollection, Laravel's way of reading a huge result set without loading it all into memory. Stream rows one at a time instead of buffering a million-row array first.
Chunking, processing a big dataset in smaller batches. Here it also means turning those batches into background jobs instead of working through them inline.
Keyset pagination (chunkById), paging with where('id', '>', $lastId) instead of skip()->take(). Sounds like a small difference, turned out to be the single biggest fix in this project.
Upsert, one statement that inserts a new row or updates an existing one. Makes a job safe to retry, a worker dying mid-batch and running again doesn't create duplicates.
Idempotent jobs, jobs that produce the same result no matter how many times they run. At this scale, something will eventually fail or get interrupted, and if jobs aren't idempotent, a retry becomes its own bug.
Why we didn't write one big migration script
The instinct is to write a single script and let it run overnight. We didn't, on purpose. A blocking ALTER TABLE or a big INSERT INTO ... SELECT on a multi-million row table locks things up, and with live traffic hitting the site, that's an outage with extra steps, not a maintenance window.
Instead, three stages that could each ship independently. Add the new schema alongside the old one, fully non-blocking. Backfill the new tables in the background, in small pieces, while both schemas stay live. Once the backfill is verified, flip the application code to the new tables, then clean up the old columns.
Stage one was a normal migration:
// Phase 1 Migration: non-blocking schema creation for the new provider
Schema::create('user_profiles_v2', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained()->cascadeOnDelete();
$table->string('provider_customer_id')->nullable()->index();
$table->json('preferences')->nullable();
$table->string('formatted_phone', 32)->index();
$table->timestamps();
});
Deliberately unexciting. Additive, doesn't touch anything the running app depends on.
Streaming the backfill instead of loading it all at once
The obvious way to write this command is to grab every user that needs migrating and loop over them. At a million-plus rows, that's an instant Allowed memory size exhausted, before any real work even starts.
We built the command around cursor() instead:
namespace App\Console\Commands;
use App\Models\User;
use App\Jobs\MigrateUserProfileChunkJob;
use Illuminate\Console\Command;
class MigrateUserProfilesCommand extends Command
{
protected $signature = 'data:migrate-profiles';
protected $description = 'Dispatches async backfill jobs for profile normalization';
public function handle(): void
{
// Unbuffered streaming keeps memory consumption under 15MB
User::query()
->whereNull('migrated_at')
->cursor()
->chunk(1000)
->each(function ($chunk) {
dispatch(new MigrateUserProfileChunkJob($chunk->pluck('id')->toArray()));
});
}
}
That command's job is small on purpose: read a chunk of IDs, hand them off, move on. The transformation happens elsewhere.
One upsert instead of a thousand writes
Each dispatched job takes its chunk of IDs, builds the new payload, and writes it as a single upsert:
namespace App\Jobs;
use App\Models\User;
use App\Models\UserProfileV2;
use App\Helpers\PhoneHelper;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
class MigrateUserProfileChunkJob implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
public function __construct(public array $userIds) {}
public function handle(): void
{
$payload = User::whereIn('id', $this->userIds)
->get()
->map(fn ($user) => [
'user_id' => $user->id,
'preferences' => json_encode($user->legacy_preferences),
'formatted_phone' => PhoneHelper::sanitize($user->phone),
'created_at' => $user->created_at,
'updated_at' => now(),
])
->toArray();
UserProfileV2::upsert(
$payload,
['user_id'],
['preferences', 'formatted_phone', 'updated_at']
);
}
}
Fast, and also what makes retries safe. If this job dies and Laravel's queue retries it, the same upsert overwrites the same rows with the same values. No duplicates, nothing to clean up by hand.
Three things that only showed up at real scale
Testing on a smaller slice of data, everything looked fine. Against the real dataset, three separate problems showed up that never would have surfaced any earlier.
Offset pagination degrading with depth. Plain skip($offset)->take(1000) was fast for the first few batches and got progressively worse the deeper in we went. At offset 500,000, a single batch took over 4 seconds. MySQL has to actually read and discard every row before your offset just to know where to start counting, half a million rows read and thrown away, every batch. Switching to keyset pagination, where('id', '>', $lastProcessedId), fixed it completely. 4,200ms down to 12ms, flat regardless of depth. Biggest single win in the whole project.
PDO's placeholder ceiling. Upserting rows with more than 30 columns started throwing PDOException: SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders. MySQL's PDO driver caps prepared statements at 65,535 placeholders total, rows times columns, not just rows. A chunk size fine for a narrow table blew past that on a wider one. Fix: stop hardcoding chunk size, calculate it from column count, max chunk size is 65,535 divided by column count, rounded down.
Queue workers slowly eating all available memory. Left running for hours against millions of records, workers gradually consumed enough RAM that Linux started killing processes. Long-lived queue:work processes hang onto query logs, event listeners, and cached Eloquent state across every job, none of it clears automatically. Fixed with --max-jobs=1000 --max-time=3600 so workers recycle themselves, plus an explicit DB::disconnect() at the end of every chunk.
Takeaways
- Write every migration job assuming it'll fail partway through, because eventually one will. Upsert instead of insert, it's what makes retries safe instead of dangerous.
- Never load a big Eloquent collection in one step if it might grow past a few thousand rows.
cursor(),chunkById(), or raw streaming, something that doesn't try to hold it all in memory. - Keep deployment separate from schema change: add new structures first without touching anything live, backfill in the background on its own schedule, only flip the app over once the backfill is verified, and only then touch the old columns.
- Offset pagination looks fine in early testing and quietly falls apart at depth, test paginated queries against realistic offsets, not just page one.
None of this needed a third-party ETL platform. Artisan commands, queue workers, and Laravel's own collection primitives were enough to move millions of rows through a live production database without anyone outside the team noticing. Full writeup with more of the backstory is on our Substack: https://ucodesoft.substack.com/p/upgrading-legacy-laravel-and-moving
Top comments (0)