mufassall

Cheat sheet · Business processes

Bulk import

13 min · Intermediate · mufassall.dtwin.uz/en/l/bulk-import/

← Open the lesson

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
Final scene
30%60%45%

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_db

Mermaid: 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 Requests

D2

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_db

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Import time3 s95 s
Rows imported100%99.8%
API memory180 MB60 MB
Checkout lock wait0 s0 s

Steps

  1. Case The web server parses the upload in the request

    An accountant uploads the product catalog from the old system as a CSV.

  2. Problem 200 000 rows: timeout, memory, one bad row

    Now the file is 41.9 MB, 200 000 rows.

  3. Fix Upload straight to object storage

    The browser first asks the API for an upload URL.

  4. Fix An import job and a worker that streams the file

    The browser tells the API: import this file.

  5. 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.

  6. Problem A restart starts over; a second upload starts another job

    A deploy restarts the worker at row 122 000.

  7. 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.

  8. Problem One giant write locks the catalog

    Writing row by row takes 200 000 round trips, about 7 minutes.

  9. 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.

  10. 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.

  11. Fix Limits per tenant

    One tenant starts six imports of 2 GB files at once, and everyone else's jobs wait behind them.

  12. 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.