Handling Large Data Imports: Scalable Batch Processing Strategies

Previously in this course, we discussed CI/CD Pipeline Fundamentals to automate your code delivery. In this lesson, we shift our focus to the "Data Layer" of your architecture, specifically addressing how to ingest massive datasets without overwhelming your application or database.

The Problem with Naive Imports

When you receive a 500MB CSV file to import, the most common beginner mistake is to read the entire file into memory, parse it, and attempt a single database transaction. This leads to OutOfMemory errors, request timeouts, and database lock contention.

To maintain system performance and achieve high throughput, you must design your imports as a pipeline rather than a single event.

Core Principles of Batch Processing

Efficient data ingestion relies on three pillars:

1. Streaming: Never load the full file into memory. Read the file line-by-line or chunk-by-chunk.
2. Chunking: Group records into batches (e.g., 500 records at a time) to balance database round-trips with transaction overhead.
3. Throttling: Control the rate of ingestion to ensure that background workers don't starve the primary application of CPU or database connections.

Worked Example: Streaming with Node.js

In a professional environment, you should use streams to handle large files. Here is a pattern for processing a CSV file using readline:

JAVASCRIPT
const fs = require(CE9178">'fs');
const readline = require(CE9178">'readline');

async function processImport(filePath) {
    const fileStream = fs.createReadStream(filePath);
    const rl = readline.createInterface({ input: fileStream, crlfDelay: Infinity });

    let batch = [];
    const BATCH_SIZE = 500;

    for await (const line of rl) {
        batch.push(parseLine(line));

        if (batch.length >= BATCH_SIZE) {
            await insertBatch(batch); // Perform DB operation
            batch = []; // Clear memory
        }
    }
    // Process remaining
    if (batch.length > 0) await insertBatch(batch);
}

This approach keeps memory usage constant, regardless of whether the file is 10MB or 10GB.

Architecting for Throughput

While streaming handles memory, you still need to protect your database. A common strategy involves using Laravel Pipelines and Redis Streams for High-Throughput Batch Processing to decouple the ingestion from the execution.

If you're dealing with extreme scale, consider this architecture:

1. Upload: Client uploads the file to object storage (like S3).
2. Queue: A background worker is triggered, receiving only the file path.
3. Partition: The worker breaks the file into smaller "jobs" and pushes them to a message queue.
4. Throttle: Workers consume the queue at a rate your database can handle, respecting the limits discussed in Rate Limiting and Throttling.

Hands-on Exercise

Refactor your current project's dummy "User Upload" feature. Instead of saving a file directly to the disk, write a script that:

1. Accepts a mock input stream of 10,000 JSON objects.
2. Implements a batch size of 100.
3. Logs "Inserting batch..." every time a chunk is processed.
4. Ensures that if an error occurs during one batch, the process logs the failure but continues to the next batch (the "partial success" pattern).

Common Pitfalls

• Transactional Bloat: Trying to wrap 100,000 inserts in a single transaction. This can cause massive undo/redo logs and block other queries. Keep your transactions scoped to the batch.
• Ignoring Backpressure: If your file parser is faster than your database, you will fill up your message queue memory. Use pause() and resume() on your read streams to apply backpressure.
• Silent Failures: Always include row-level error reporting. If row 50,000 fails, you need to know exactly why without failing the entire job.

FAQ

Q: How do I know the right batch size? A: Start with 100–500. Measure the time taken to write to the database. If it's too slow, increase the size. If you hit lock timeouts, decrease it.

Q: Should I use a message queue for everything? A: Not for small files. If it's < 5MB, a synchronous request is fine. Beyond that, use Handling Large Payloads: Bulk Operations and Streaming in REST principles to move to background processing.

Recap

We've moved from simple scripts to robust, memory-safe batch processing. By using streaming and chunking, you protect your system's stability. These techniques ensure that your data imports remain efficient and performant as your user base grows.

Up next: We will explore how to manage concurrency across multiple instances using Distributed Locking.