Practical guide

How to import millions of rows without exhausting memory

Read the file as a stream, process one record, and save and release state after a bounded batch.

25 minutes · PHP and CSV

In short

Memory should stay almost constant

A million rows must not become a million PHP array entries. A stream keeps only a small part of the input in memory and a batch bounds pending database changes.

An import is also a data process: it must validate format, resume after failure, and recognize an already processed record. Raising memory_limit only postpones the problem.

Prepare

What you need

Define the input format and behavior for invalid records before optimizing writes.

A sample CSV including header, encoding, delimiter, enclosure, empty values, and multiline fields.
A stable external identifier or another idempotency key for upsert and reruns.
Validation rules and a decision whether one bad row stops the import or enters a separate report.
Metrics for processed, inserted, updated, and rejected rows, batch duration, and memory use.

Steps 1 to 3

Build a restartable pipeline

Separate reading, transformation, and writing so batch size or database strategy can change without rewriting the parser.

1. Read one CSV record at a time

Use fopen and fgetcsv instead of file(), file_get_contents(), or loading every row into an array.
Set delimiter, enclosure, and escape explicitly. For normal RFC 4180 CSV, use an empty escape and doubled quotes inside a field.
Normalize the header once and verify the same column count for every record. Account for BOM and declared encoding.
Do not derive a logical record number blindly from a physical line when quoted fields can contain newlines.

$handle = fopen($path, 'rb');
while (($row = fgetcsv($handle, null, ',', '"', '')) !== false) {
    yield $row;
}

Official PHP fgetcsv documentation

2. Validate and write in batches

Map every row to a small input object, validate it, and immediately append it to a bounded batch. Do not accumulate unlimited error bodies in memory.
Measure a batch size such as 500 to 5,000 rows. Larger batches reduce overhead but extend transactions and use more memory.
For a plain PostgreSQL import, prefer a staging table and COPY. Complex domain behavior can use DBAL or ORM in smaller batches.
Commit every batch separately and save a checkpoint. One transaction over a million rows holds locks, WAL, and rollback state for too long.

COPY product_import (external_id, name, price) FROM STDIN WITH (FORMAT csv);

Official PostgreSQL COPY documentation

3. Release state and support resumption

With Doctrine ORM, call flush and clear after every batch. Otherwise the Unit of Work retains references to all entities.
Disable detailed SQL logging during a long import and keep only aggregate metrics and a bounded error sample.
Give the import its own ID and store the last committed checkpoint. After a crash, repeat the unfinished batch idempotently.
Keep the original file under a checksum and never change it during resumption. Different contents must create a new import.

$entityManager->flush(); $entityManager->clear();

Official Doctrine batch processing documentation

Step 4

Verify constant memory and recovery

The import must survive the full file, bad input, and a mid-run restart without duplicates.

Generate a large test file

Import one million representative rows and log memory_get_usage(true) after each batch. The curve must not keep growing.
```
php -d memory_limit=256M bin/console app:import products.csv
```
Insert malformed records

Test the wrong column count, invalid encoding, an empty key, and a failure in the middle of a batch. The report must keep bounded, useful context.
Terminate the process midway

Resume from the checkpoint. Final counts and unique keys must match one complete import.

When it goes wrong

Common mistakes

Memory grows after every batch

Inspect the Unit of Work, accumulated error report, SQL logger, and custom statistics arrays. Flush without clear does not release entity references.

The import is slow with low CPU use

You may be paying for individual INSERTs or I/O waits. Use prepared batches, DBAL, or COPY into staging and tune batch size.

A restart creates duplicates

The idempotency key is missing or the checkpoint points past uncommitted work. Repeat the last batch through upsert and move the checkpoint only after commit.

Quotes break CSV into wrong rows

Do not split with explode or a regular expression. Use a CSV parser with the correct delimiter, enclosure, and explicit escape.

Done

The import scales with time, not memory.

The pipeline now streams CSV, saves bounded batches, and resumes safely. Apply the same pattern to every large feed or backfill.

Read database in the glossary All guides