Cheat sheet · Business processes
Bulk import
The problem
A wholesale company uploads its product catalog as a CSV: 200 000 rows. Parsing it inside the request times out, eats memory and throws everything away for one bad row. The file goes to object storage instead, an import job streams it, validates every row and reports errors, survives restarts and repeated uploads, writes in short batches, shows progress and stays within per-tenant limits.
What you learn
- Why a big file goes straight to object storage with a pre-signed URL and is read by a worker, not by the web server
- Per-row validation with row numbers and a downloadable error report: partial success is the normal result
- File-hash dedup, checkpoints every N rows, and batched upserts that keep locks on the main table short
- Progress the UI can poll, and limits per tenant on file size, rows and running jobs
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_client(["Browser"])
n_api["App API"]
n_db[("Catalog DB")]
n_storage[("Object storage")]
n_queue[["Job queue"]]
n_worker("Import worker")
n_client --x n_api
n_client --> n_storage
n_api --> n_queue
n_queue --> n_worker
n_worker --> n_storage
n_worker --> n_db
n_api --> n_dbMermaid: request sequence
sequenceDiagram
participant n_client as Browser
participant n_api as App API
participant n_storage as Object storage
participant n_worker as Import worker
participant n_queue as Job queue
participant n_db as Catalog DB
Note over n_client,n_api: The web server parses the upload in the request
n_client->>n_api: POST /products/import HTTP/1.1
n_api-->>n_client: HTTP/1.1 200 OK
Note over n_client,n_api: 200 000 rows: timeout, memory, one bad row
n_client->>n_api: 14:02:10 POST /products/import 41.9 MB, 200 000 rows
Note over n_client,n_storage: Upload straight to object storage
n_client->>n_storage: PUT /t_19/imp_77.csv?expires=900#38;sig=*** HTTP/1.1
n_storage-->>n_client: HTTP/1.1 200 OK
Note over n_client,n_api: An import job and a worker that streams the file
n_client->>n_api: POST /imports HTTP/1.1
n_api-->>n_client: HTTP/1.1 202 Accepted
Note over n_worker,n_storage: Validate each row, collect errors, keep the good rows
n_worker->>n_storage: t_19/imp_77.errors.csv
Note over n_queue,n_worker: A restart starts over#59; a second upload starts another job
n_queue->>n_worker: 15:10:04 deploy: worker restarted, imp_77 was at row 122 000
Note over n_worker,n_db: File hash for dedup, checkpoint every 1 000 rows
n_worker->>n_db: UPDATE import_jobs
n_db-->>n_worker: imp_77
Note over n_worker,n_db: One giant write locks the catalog
n_worker->>n_db: 16:00:02 imp_81: UPDATE products FROM staging
Note over n_worker,n_db: Batched upserts with a natural key
n_worker->>n_db: INSERT INTO products (tenant_id, sku, name, price_minor)
n_db-->>n_worker: INSERT 0 1000
Note over n_client,n_api: Progress the UI can poll
n_client->>n_api: GET /imports/imp_77 HTTP/1.1
n_api-->>n_client: HTTP/1.1 200 OK
Note over n_client,n_api: Limits per tenant
n_client->>n_api: POST /imports HTTP/1.1
n_api-->>n_client: HTTP/1.1 429 Too Many RequestsD2
direction: right
n_client: "Browser" {
shape: rectangle
}
n_api: "App API" {
shape: rectangle
}
n_db: "Catalog DB" {
shape: cylinder
}
n_storage: "Object storage" {
shape: stored_data
}
n_queue: "Job queue" {
shape: queue
}
n_worker: "Import worker" {
shape: rectangle
style.multiple: true
}
n_client -> n_api {
target-arrowhead.shape: cross
}
n_client -> n_storage
n_api -> n_queue
n_queue -> n_worker
n_worker -> n_storage
n_worker -> n_db
n_api -> n_dbPlantUML: request sequence
@startuml
participant "Browser" as n_client
participant "App API" as n_api
database "Object storage" as n_storage
participant "Import worker" as n_worker
queue "Job queue" as n_queue
database "Catalog DB" as n_db
note over n_client, n_api : The web server parses the upload in the request
n_client -> n_api : POST /products/import HTTP/1.1
n_api --> n_client : HTTP/1.1 200 OK
note over n_client, n_api : 200 000 rows: timeout, memory, one bad row
n_client -> n_api : 14:02:10 POST /products/import 41.9 MB, 200 000 rows
note over n_client, n_storage : Upload straight to object storage
n_client -> n_storage : PUT /t_19/imp_77.csv?expires=900&sig=*** HTTP/1.1
n_storage --> n_client : HTTP/1.1 200 OK
note over n_client, n_api : An import job and a worker that streams the file
n_client -> n_api : POST /imports HTTP/1.1
n_api --> n_client : HTTP/1.1 202 Accepted
note over n_worker, n_storage : Validate each row, collect errors, keep the good rows
n_worker -> n_storage : t_19/imp_77.errors.csv
note over n_queue, n_worker : A restart starts over; a second upload starts another job
n_queue -> n_worker : [duplicate] 15:10:04 deploy: worker restarted, imp_77 was at row 122 000
note over n_worker, n_db : File hash for dedup, checkpoint every 1 000 rows
n_worker -> n_db : UPDATE import_jobs
n_db --> n_worker : imp_77
note over n_worker, n_db : One giant write locks the catalog
n_worker -> n_db : 16:00:02 imp_81: UPDATE products FROM staging
note over n_worker, n_db : Batched upserts with a natural key
n_worker -> n_db : INSERT INTO products (tenant_id, sku, name, price_minor)
n_db --> n_worker : INSERT 0 1000
note over n_client, n_api : Progress the UI can poll
n_client -> n_api : GET /imports/imp_77 HTTP/1.1
n_api --> n_client : HTTP/1.1 200 OK
note over n_client, n_api : Limits per tenant
n_client -> n_api : POST /imports HTTP/1.1
n_api --> n_client : HTTP/1.1 429 Too Many Requests
@endumlPlantUML: components
@startuml
left to right direction
agent "Browser" as n_client
component "App API" as n_api
database "Catalog DB" as n_db
storage "Object storage" as n_storage
queue "Job queue" as n_queue
collections "Import worker" as n_worker
n_client -[#c62828]-> n_api
n_client --> n_storage
n_api --> n_queue
n_queue --> n_worker
n_worker --> n_storage
n_worker --> n_db
n_api --> n_db
@endumlRecap
Six rules of bulk import.
1) Never parse a big file in the web request: upload it to object storage with a short-lived pre-signed URL and let a worker stream it. 2) Validate each row, keep the good ones and give the user an error report with row numbers. 3) Deduplicate uploads by file hash, and save a checkpoint with each batch so a restart resumes. 4) Write in batches of about 1 000 rows with an upsert on a natural key, in short transactions. 5) Show progress from the job record. 6) Limit file size, rows and running jobs per tenant.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Import time | 3 s | 95 s |
| Rows imported | 100% | 99.8% |
| API memory | 180 MB | 60 MB |
| Checkout lock wait | 0 s | 0 s |
Steps
- Case The web server parses the upload in the request
An accountant uploads the product catalog from the old system as a CSV.
- Problem 200 000 rows: timeout, memory, one bad row
Now the file is 41.9 MB, 200 000 rows.
- Fix Upload straight to object storage
The browser first asks the API for an upload URL.
- Fix An import job and a worker that streams the file
The browser tells the API: import this file.
- Fix Validate each row, collect errors, keep the good rows
The worker checks each row on its own: required fields, numbers, units, duplicates inside the file.
- Problem A restart starts over; a second upload starts another job
A deploy restarts the worker at row 122 000.
- Fix File hash for dedup, checkpoint every 1 000 rows
Every 1 000 rows the worker saves its position in the job record: last finished row and counters.
- Problem One giant write locks the catalog
Writing row by row takes 200 000 round trips, about 7 minutes.
- Fix Batched upserts with a natural key
The worker writes 1 000 rows per statement: insert new products and update existing ones, matched by tenant and SKU.
- Fix Progress the UI can poll
The page polls the job every 2 seconds and shows a bar: rows done, errors so far, time left.
- Fix Limits per tenant
One tenant starts six imports of 2 GB files at once, and everyone else's jobs wait behind them.
- Recap Recap
Six rules of bulk import.
Terms
- Upsert
- Insert a row, or update it if a row with the same key exists, in one statement (ON CONFLICT ... DO UPDATE). Re-running it is harmless.
- Checkpoint
- The position a long job saves with each committed batch, so after a restart it resumes from there instead of starting over.