Bulk Import Implementation: When 150,000 Positions Bring Down the Site

When 150,000 Positions Bring Down the Site

Picture this: a client brings a price list with 150,000 products. You try to load everything at once — and the site hangs, the database stops responding. This is a classic request for Bulk Import. Over 5 years, we've implemented more than 80 projects with volumes up to 500,000 SKUs. We've gained experience in avoiding slow loading, N+1 queries, and database crashes. Here's how to achieve stability with chunks, queues, and preloading dictionaries.

What Problems We Solve

Slow loading: row-by-row INSERT/UPDATE for 100,000 rows takes hours. N+1 queries when resolving dictionaries kill performance. Database crashes due to suboptimal transactions. Data loss on errors mid-import. All these pains are eliminated with proper architecture. Our development services can solve these issues.

How Mass Import Affects Performance

The key factor is data volume. For 1,000 positions, synchronous processing is enough; for 100,000, an async queue with chunks is required.

Bulk upsert is 10x faster than row-by-row queries — our practice confirms this.

Why Chunk + Queue Is Key to Stability

A file with 100,000 rows is not processed in a single Job. We split it into chunks of 500 rows, each chunk as a separate Job on the bulk-import queue. Workers (2–4) process in parallel without touching the main queue.

class BulkImportDispatcher { private const CHUNK_SIZE = 500; public function dispatch(ImportFile $file): void { $import = ImportRun::create([ 'file_id' => $file->id, 'status' => 'dispatching', 'total' => 0, ]); $chunkIndex = 0; foreach ($file->parser()->chunks(self::CHUNK_SIZE) as $chunk) { ProcessImportChunkJob::dispatch($import->id, $chunkIndex, $chunk) ->onQueue('bulk-import'); $chunkIndex++; } $import->update([ 'status' => 'processing', 'total_chunks' => $chunkIndex, ]); } } 

Technical Implementation: From Chunks to Upsert

Bulk Upsert Instead of Row-by-Row INSERT/UPDATE

The main performance tool is INSERT ... ON CONFLICT DO UPDATE (upsert). Laravel supports this via Model::upsert(). One upsert operation for 500 rows in PostgreSQL takes ~50–200 ms, compared to 500 × 5 ms = 2500 ms for row-by-row queries.

class ProcessImportChunkJob implements ShouldQueue { public int $timeout = 120; public function handle(): void { $rows = []; foreach ($this->chunk as $item) { $rows[] = [ 'sku' => $item['sku'], 'name' => $item['name'], 'price' => $item['price'], 'qty' => $item['qty'], 'category_id' => $this->resolveCategory($item['category']), 'source_id' => $this->import->source_id, 'updated_at' => now(), 'created_at' => now(), ]; } Product::upsert( $rows, uniqueBy: ['sku'], update: ['name', 'price', 'qty', 'category_id', 'updated_at'] ); DB::table('import_runs') ->where('id', $this->importId) ->increment('processed_chunks'); } } 

Preloading Dictionaries into Memory

The most expensive operation is DB queries to resolve dependencies. The solution: load all dictionaries into memory before processing.

class ImportContext { private array $categoryMap; private array $supplierMap; private array $existingSkus; public function preload(int $sourceId): void { $this->categoryMap = Category::pluck('id', 'name_normalized')->all(); $this->supplierMap = Supplier::pluck('id', 'code')->all(); $this->existingSkus = Product::where('source_id', $sourceId) ->pluck('id', 'sku')->all(); } public function resolveCategoryId(string $name): ?int { return $this->categoryMap[mb_strtolower(trim($name))] ?? null; } public function productExists(string $sku): bool { return isset($this->existingSkus[$sku]); } } 

Final Job: Aggregation of Results

We use Bus::batch() — Laravel's built-in mechanism for grouping tasks with a completion callback.

Bus::batch( collect($chunks)->map(fn($chunk, $i) => new ProcessImportChunkJob($importId, $i, $chunk)) )->then(function (Batch $batch) use ($importId) { ImportRun::find($importId)->update([ 'status' => 'completed', 'completed_at' => now(), ]); PostImportPipeline::dispatch($importId); })->onQueue('bulk-import')->dispatch(); 

Post-Import Pipeline

After import completes, we need to update denormalized data: recalculate stock, update search index, and facets.

class PostImportPipeline { public function handle(int $importId): void { $productIds = ImportedProduct::where('import_id', $importId)->pluck('product_id'); Product::whereIn('id', $productIds)->each(function (Product $p) { $p->update(['in_stock' => $p->qty > 0]); }); Product::whereIn('id', $productIds)->searchable(); FilterValueRebuilder::dispatch($productIds); } } 

Monitoring and Load Limiting

In the admin interface, the operator sees real-time progress: processed count, errors, remaining time. Data is taken from the import_runs table. We dedicate the bulk-import queue with 2–4 workers, leaving the default queue untouched. Heavy imports run at night via the scheduler. Each error is logged, and the operator can restart only failed chunks — a typical case for large-catalog e-commerce sites. We also support integration with 1C and CommerceML, popular data sources in Russian e-commerce. Contact us for a consultation — we can configure this mechanism for your project.

Scope of Work and Timeline

• Development of chunk dispatcher and bulk upsert.
• Preloading dictionaries and final Job.
• Result report (processed count, errors).
• Documentation for setup and execution.
• Operator training.
• Stable operation guarantee — 3 months of support.
• Work by certified Laravel engineers.

Timeline: from 3 to 5 days turnkey. Get a free consultation for your project. Contact us for an assessment.

Data import — Wikipedia