Fast Million-Row Upserts with Laravel

You have a CSV with 800,000 product records. Your import job needs to insert new rows and update existing ones — without duplicating data and without timing out. The naive approach (firstOrCreate in a loop) will take 40 minutes and hammer your database with individual queries. Here is how to do it in under two minutes.

Prerequisites

• Laravel 11.x or 12.x (PHP 8.2+)
• MySQL 8.0+ or MariaDB 10.6+ (both support INSERT ... ON DUPLICATE KEY UPDATE)
• A primary key or unique index on the column(s) that define uniqueness
• Basic familiarity with Eloquent and the query builder

---

The Core Tool: upsert()

Laravel's query builder has had upsert() since version 8.x. It maps directly to the database's native upsert syntax and sends one query per batch instead of one query per row.

// Basic signature
DB::table('products')->upsert(
    $rows,           // array of associative arrays — the data to insert/update
    ['sku'],         // unique columns that identify an existing row
    ['name', 'price', 'stock'] // columns to update when a match is found
);

On MySQL this compiles to:

INSERT INTO `products` (`sku`, `name`, `price`, `stock`)
VALUES (?, ?, ?, ?), (?, ?, ?, ?), ...
ON DUPLICATE KEY UPDATE
  `name` = VALUES(`name`),
  `price` = VALUES(`price`),
  `stock` = VALUES(`stock`);

One round-trip. One lock window. No SELECT before INSERT.

---

Reading a Million Rows Without Exhausting Memory

Before you upsert anything you need to load the source data without pulling it all into PHP memory at once. For CSV files, use a generator:

function readCsvLazy(string $path): Generator
{
    $handle = fopen($path, 'r');
    $headers = fgetcsv($handle); // first row is header

    while (($row = fgetcsv($handle)) !== false) {
        yield array_combine($headers, $row);
    }

    fclose($handle);
}

For data already in another database table, use cursor() (which wraps a PHP generator around a streaming PDO cursor) or lazy() which uses chunked queries behind the scenes:

// cursor() — single query, server-side cursor, one model in memory at a time
SourceProduct::cursor()->each(function (SourceProduct $row) {
    // process row
});

// lazy() — multiple chunked queries, safer for very long jobs
SourceProduct::lazy(1000)->each(function (SourceProduct $row) {
    // process row
});

See the Laravel Performance Guide: Databases, Livewire, Octane, and Caching for a broader look at cursor() vs chunk() tradeoffs in high-throughput pipelines.

---

The Complete Import Job

Here is a production-ready Artisan command that processes a million-row CSV in batches of 1,000 rows:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ImportProducts extends Command
{
    protected $signature = 'import:products {file}';
    protected $description = 'Upsert product records from a CSV file';

    private const BATCH_SIZE = 1000;

    public function handle(): int
    {
        $path = $this->argument('file');

        if (! file_exists($path)) {
            $this->error("File not found: {$path}");
            return self::FAILURE;
        }

        $batch  = [];
        $total  = 0;
        $bar    = $this->output->createProgressBar();

        foreach ($this->readCsv($path) as $row) {
            $batch[] = [
                'sku'        => trim($row['sku']),
                'name'       => trim($row['name']),
                'price'      => (float) $row['price'],
                'stock'      => (int) $row['stock'],
                'updated_at' => now(),
                'created_at' => now(), // ignored on update
            ];

            if (count($batch) >= self::BATCH_SIZE) {
                $this->flush($batch);
                $total += count($batch);
                $batch  = [];
                $bar->advance(self::BATCH_SIZE);
            }
        }

        // flush the final partial batch
        if ($batch) {
            $this->flush($batch);
            $total += count($batch);
            $bar->advance(count($batch));
        }

        $bar->finish();
        $this->newLine();
        $this->info("Done. Processed {$total} rows.");

        return self::SUCCESS;
    }

    private function flush(array $batch): void
    {
        DB::table('products')->upsert(
            $batch,
            ['sku'],                             // unique key
            ['name', 'price', 'stock', 'updated_at'] // columns to update
        );
    }

    private function readCsv(string $path): \Generator
    {
        $handle  = fopen($path, 'r');
        $headers = fgetcsv($handle);

        while (($row = fgetcsv($handle)) !== false) {
            yield array_combine($headers, $row);
        }

        fclose($handle);
    }
}

Run it:

php artisan import:products /path/to/products.csv

---

Choosing the Right Batch Size

Batch size is the single biggest lever on import speed.

Batch size	Queries for 1M rows	Approx. time (local SSD)
100	10,000	~8 min
500	2,000	~3 min
1,000	1,000	~90 sec
5,000	200	~60 sec
10,000	100	may hit max_allowed_packet

MySQL's max_allowed_packet defaults to 64 MB. A batch of 10,000 rows with several VARCHAR columns can exceed it and throw a PDO exception. Start at 1,000 and raise it only after confirming packet limits:

SHOW VARIABLES LIKE 'max_allowed_packet';
-- Typical default: 67108864 (64 MB)

For most schemas, 1,000–2,000 rows per batch is a safe and fast default.

---

Disabling and Re-enabling Indexes

For a cold import (empty table or full replacement), disabling non-unique indexes before the import and rebuilding them after is dramatically faster:

// Pseudocode — adapt table name and index names to your schema
DB::statement('ALTER TABLE products DISABLE KEYS');

// ... run all your upsert batches ...

DB::statement('ALTER TABLE products ENABLE KEYS');
// MySQL rebuilds the index in a single sorted pass — much faster than per-row updates

Note: DISABLE KEYS only applies to non-unique indexes in MyISAM. On InnoDB (the default engine), use SET FOREIGN_KEY_CHECKS = 0 and SET UNIQUE_CHECKS = 0 cautiously — only when you are certain the incoming data has no duplicates and no FK violations:

DB::statement('SET FOREIGN_KEY_CHECKS = 0');
DB::statement('SET UNIQUE_CHECKS = 0');

// ... batched upserts ...

DB::statement('SET FOREIGN_KEY_CHECKS = 1');
DB::statement('SET UNIQUE_CHECKS = 1');

This is a data-integrity risk. Use it only in controlled migration scenarios, not for live production imports where data quality is uncertain.

---

Common Mistakes

1. Forgetting updated_at in the update column list.
Laravel does not automatically manage timestamps in raw DB::table()->upsert() calls. Always include updated_at explicitly in both the row data and the update columns array.

2. Using Eloquent upsert() and expecting model events.
Product::upsert(...) is available on Eloquent but it bypasses creating, updating, saved, and observer events entirely — identical behavior to the query builder. If your business logic depends on observers, use firstOrCreate in a chunked loop instead (and accept the speed tradeoff).

3. Omitting the unique index.
upsert() requires a real database unique constraint on the columns you pass as the second argument. Passing ['sku'] without a UNIQUE index on sku means MySQL will always insert and never update. Verify:

SHOW INDEX FROM products WHERE Key_name != 'PRIMARY';

4. Sending created_at in the update columns list.
Include created_at in the row data so new rows get a creation timestamp, but exclude it from the third argument (update columns). If you include it in updates, every upsert will overwrite the original creation date.

5. Not wrapping batches in a transaction for partial-failure safety.
If your job dies mid-import, partial batches can leave data in an inconsistent state. Wrapping each batch in a transaction keeps rollback clean:

private function flush(array $batch): void
{
    DB::transaction(function () use ($batch) {
        DB::table('products')->upsert(
            $batch,
            ['sku'],
            ['name', 'price', 'stock', 'updated_at']
        );
    });
}

---

Testing and Verification

After running the import, verify correctness with a few spot checks:

// Count should match source
$imported = DB::table('products')->count();

// Sample a known SKU and assert values match the CSV
$product = DB::table('products')->where('sku', 'TEST-001')->first();
assert($product->price === 29.99);

// Confirm updated_at changed on rows that were updated (not just inserted)
$updated = DB::table('products')
    ->where('updated_at', '>=', now()->subMinutes(5))
    ->count();

For automated testing, use a seeded SQLite database in your test suite to verify the upsert logic without touching production data:

// In a Feature test
public function test_upsert_updates_existing_row(): void
{
    DB::table('products')->insert([
        'sku' => 'ABC-123', 'name' => 'Old Name', 'price' => 9.99,
        'stock' => 5, 'created_at' => now(), 'updated_at' => now(),
    ]);

    DB::table('products')->upsert(
        [['sku' => 'ABC-123', 'name' => 'New Name', 'price' => 19.99,
          'stock' => 10, 'created_at' => now(), 'updated_at' => now()]],
        ['sku'],
        ['name', 'price', 'stock', 'updated_at']
    );

    $this->assertDatabaseHas('products', ['sku' => 'ABC-123', 'name' => 'New Name']);
    $this->assertDatabaseCount('products', 1); // no duplicate inserted
}

---

Limitations

• No model events. As noted, observers and lifecycle hooks do not fire.
• No soft-delete awareness. If your table uses deleted_at, upsert will happily overwrite a soft-deleted row's columns without restoring it — add 'deleted_at' => null to the update columns if needed.
• SQLite in tests. SQLite uses INSERT OR REPLACE syntax, which deletes and re-inserts the row rather than updating in place. This can change auto-increment IDs in tests — be aware when asserting on IDs.
• PostgreSQL. Laravel maps upsert() to INSERT ... ON CONFLICT DO UPDATE on Postgres, which requires specifying constraint names in some edge cases. MySQL and MariaDB use the simpler ON DUPLICATE KEY UPDATE which works with any unique index automatically.

---

For the complete picture of Laravel query optimization, caching strategies, and Octane configuration, see the Laravel Performance Guide: Databases, Livewire, Octane, and Caching.

If you need Laravel development in Mumbai, Mumbai Web Designer builds production-grade Laravel applications.