The problem
TTMI runs an in-house retail ERP used by 50 stores, 500 employees and 4 brands. Sales, purchasing and accounting documents often arrive in large batches, and one of the heaviest was an invoice import: about 7,000 invoice headers, each with 50 to 100 detail lines.
The import went through the Django ORM one row at a time. Every header and every line was its own insert, and every foreign key on a line (product, store, account and so on) was looked up separately. That is the classic N+1 pattern multiplied by hundreds of thousands of rows. Most of the time went into network round trips and the Python work between them. A full run took about three hours.
There was a second requirement that made a quick fix risky. Imports sometimes failed partway, and someone had to be able to run the same file again without creating duplicate invoices.
What I did
I wrote a reusable bulk-load layer and moved the invoice import onto it, then integrated it into several other document types.
Use PostgreSQL COPY for bulk rows
COPY streams many rows to the database in one operation, so the cost per row drops to almost nothing. I used it for both headers and detail lines.
The trade-off is that COPY skips the ORM entirely. Model signals, save hooks and field defaults computed in Python do not run. I went through each side effect the old path relied on and made it explicit in the import code, so nothing happened silently anymore, and nothing silently stopped happening either.
Stage large batches in a temporary table
For large batches, rows are first copied into a temporary table that lives only for the transaction. A single insert-select then moves them into the real tables and skips any row that already exists. That is what makes a re-run safe: rows that landed on the first attempt are ignored, and only the missing ones are added.
Small batches skip the staging step and go straight to the target tables. Staging costs an extra copy of the data, which is worth it for thousands of rows and pure overhead for a handful. A size threshold picks the path.
Resolve foreign keys once per batch
Before inserting anything, the layer collects every referenced entity in the batch, fetches them in one query per entity type and builds an in-memory map. Each row then resolves its keys from that map with no database call.
This uses more memory per batch. For the batch sizes we deal with, that cost was small compared to removing hundreds of thousands of lookups.
Write detail lines in chunks
Detail lines are written in chunks across many headers instead of one header at a time. This keeps each write large enough to be efficient while keeping memory bounded on very large files.
One layer for many document types
I built this as a shared layer with a generic interface rather than a one-off fix for invoices. Other document imports now use the same path. The cost is a more abstract API to maintain; the benefit is one well-tested route into the database instead of several slightly different ones.
Result
In production, the import of about 7,000 invoice headers with 50 to 100 lines each went from about 3 hours to about 15 minutes. Re-running a partly failed import no longer creates duplicates, because the staged insert skips rows that already exist. Other document types reuse the same layer, so new bulk imports start from a tested path.
The homepage widget is an illustrative model of round trips for per-row ORM, batched ORM and COPY with staging. It shows the shape of the difference, and its numbers are not measurements.
What I’d do differently
I would write down the measurement method at the time: which dataset, which environment, and how many runs. The before-and-after figure is real, but I can describe it more confidently than I can reproduce it. I would also add a small benchmark to the test suite, so a future change that brings back per-row work shows up in review instead of in production.