Importing a Million Products From S3 Into Laravel, What Six Hours Taught Us

First time we ran this import, we kicked it off at 9 am, and it was still going when we packed up for the day. Six hours, and that wasn't a bad run; that's just what it did every time.

The data was diamond and jewelry inventory for a client, a bit over a million SKUs, carat weight, cut, color, clarity, dimensions, pricing tiers, image references, all of it. CSV on S3, needed to land in the product database with an admin panel on top for search and filtering.

Six hours means the catalog's stale for a quarter of every day. Not something we could live with long term, so we fixed it. Here's the whole thing, warts and all.

Quick vocabulary check

Chunking, splitting a huge dataset into smaller batches instead of one giant unit. "Do a thousand rows, a thousand times" instead of "do a million at once."

A queue job, work handed off to happen later, or somewhere else entirely, instead of making the thing that triggered it sit and wait.

Streaming, reading a file as it arrives, bit by bit, instead of waiting for the whole thing to land first.

Upsert, one statement that inserts a new row or updates an existing one, in a single round trip instead of two or three.

Turning off indexes for a bulk write, temporarily stopping MySQL from maintaining every index on every row, then rebuilding once the big write finishes. Only worth it for genuinely large batches.

What we started with

One artisan command doing everything in a straight line: pull the CSV from S3, parse row by row, find or create the product, update it, save it. No chunking, no queue, one PHP process, one database connection, start to finish. A million rows, a million individual Eloquent calls, each its own trip to the database.

Memory gave out first, around 200,000 rows, because we were loading the whole CSV into an array before processing even started. Bumped the limit, kept going, and then the database started struggling instead; a million INSERT and UPDATE statements with zero batching means constant fsync activity. On top of that, we were downloading the entire file before touching a single row; 15 to 20 minutes gone on a bad day before anything real happened.

Everything sequential, everything blocking on the last step, everything slow for its own reason. Six hours wasn't one bottleneck; it was four or five standing in line.

Actually looking at what we had

The real first move wasn't code; it was admitting this wasn't one job, it was three stitched together: get the data off S3, transform and validate a million rows, write it all to the database without falling over. Different bottleneck each time. Cram them into one process and the slowest one sets the pace for everything.

The command stayed the same, php artisan import: products. What happens once you run it changed almost completely.

Streaming instead of downloading

$s3Stream = Storage::disk('s3')->readStream('products/latest.csv');
$csv = Reader::createFromStream($s3Stream);
$csv->setHeaderOffset(0);

$chunk = [];
$chunkSize = 1000;

foreach ($csv->getRecords() as $record) {
    $chunk[] = $record;

    if (count($chunk) === $chunkSize) {
        ProcessProductChunk::dispatch($chunk);
        $chunk = [];
    }
}

if (!empty($chunk)) {
    ProcessProductChunk::dispatch($chunk);
}

The command finishes in a couple minutes now, just reading a stream and firing off jobs. The real work moved to the background.

1,000 rows per chunk came from actually testing sizes, not a guess. Too small and queue overhead eats you alive. Too big and one failure means redoing real work. A thousand landed us at just over a thousand jobs per run, each done in under 30 seconds.

Batching the writes

Original, inside each job:

foreach ($records as $record) {
    Product::updateOrCreate(
        ['sku' => $record['sku']],
        $this->mapAttributes($record)
    );
}

Reads nicely, brutal at scale, one query per row, SELECT to check, then INSERT or UPDATE. A thousand rows, up to two thousand queries in one job.

Swapped for a single upsert:

$mapped = array_map(fn($record) => $this->mapAttributes($record), $records);

Product::upsert(
    $mapped,
    ['sku'],                       // unique key to match on
    $this->updatableColumns()      // columns to update if row exists
);

One query, the whole batch. MySQL sorts out new vs. existing on its own. A chunk that took 18 seconds dropped to under 3. Bigger win than we expected.

Running jobs in parallel

php artisan queue:work redis --queue=product-import --sleep=1 --tries=3 --timeout=120

Dedicated queue, kept away from notifications, PDFs, order processing, so the import doesn't starve production traffic of workers. We spin up extra workers for the duration of a run and let them wind down after.

8 workers on 1,000-row chunks, roughly 8,000 rows every 3 seconds at peak. Same total work, just happening 8 at a time instead of 1.

Turning off indexes for the big runs

MySQL updates every index on every write. A million rows landing with all indexes active adds real overhead. For a full re-import specifically:

// Before import
DB::statement('ALTER TABLE products DISABLE KEYS');

// ... run the import ...

// After import
DB::statement('ALTER TABLE products ENABLE KEYS');
DB::statement('OPTIMIZE TABLE products');

Strictly for the full refresh case. Day-to-day incremental updates go through with indexes active the whole time, not enough volume there to justify it.

The admin panel had its own unrelated problem

Filter combinations against a million-row table with plain where chains were taking 4 to 8 seconds per query, import running or not. Fixed with composite indexes matching how people actually filter:

// Migration
Schema::table('products', function (Blueprint $table) {
    $table->index(['status', 'cut', 'carat_weight'], 'idx_admin_filters');
    $table->index(['status', 'price'], 'idx_price_filter');
});

Full table scans became index lookups. 4 to 8 seconds became under 200ms. Search is still a basic LIKE, fine for admin use, full-text search is still on the table for the customer-facing side.

Where we landed

Six hours down to three or four. What's left is mostly S3 read speed and the queue's throughput ceiling, both pushable further if it ever actually matters.

Takeaways

• A slow bulk job is usually several bottlenecks stacked together, not one, treat it that way from the start.
• updateOrCreate in a loop doesn't scale, a batch upsert() does, same intent, one query instead of thousands.
• Stream large files instead of downloading them fully first, especially when processing can start before the download's done.
• Disabling indexes for bulk writes is a real win, but scope it to genuinely large batches, not your everyday write.
• A slow admin panel and a slow import can share a table without sharing a cause, profile them separately, don't assume.

Full writeup with more of the backstory is on our Substack: https://ucodesoft.substack.com/p/importing-a-million-products-from