Module 7: Bulk Operations

Module 7: Bulk Operations

Next →

An INSERT in a loop is one of the most persistent anti-patterns in Python backends. The student "knows" it's slow — but doesn't know how much. A loop of 100,000 inserts can take 47 seconds where COPY does them in 0.8. A 60x difference that only gets internalized by seeing the numbers.

This module is the last of the patterns before the final project. It goes last because it's the most specific — it isn't a cross-cutting pattern, it's a tool for specific cases: ETL, imports, backfills. When it shows up, the technical options are different and the trade-offs are measurable. Learning the 4 approaches (a loop, executemany, bulk_insert_mappings, COPY) with real benchmarks gives you the decision matrix for each case.

In this capsule we introduce the scenario, the module map, and the decision matrix by size. Capsules 02-04 cover the 4 approaches with their trade-offs. 05 introduces upserts (ON CONFLICT). 06 covers bulk upserts with a temporary table — the canonical pattern for large imports with duplicates. 07 covers errors and streaming. And 08 closes with the mini-project: a POST /tasks/bulk endpoint that imports 10k tasks in <2 seconds.

Where are we? Where are we going?

What you already know (modules 1 to 6):

  • Cursor pagination with tiebreakers (module 1).
  • Correct soft deletes (module 2).
  • Audit logs via triggers (module 3).
  • Multi-tenancy with RLS (module 4).
  • Zero-downtime migrations with expand-contract (module 5).
  • Optimistic locking + schema versioning (module 6).

What you're going to build this time:

The tools to process large volumes of data efficiently. You're going to learn the 4 bulk-insert approaches with real benchmarks (a loop = 47s, executemany = 12s, bulk_insert = 4.3s, COPY = 0.8s for 100k rows), when to choose each one, how to implement COPY FROM STDIN with async asyncpg, atomic upserts with ON CONFLICT, and the canonical pattern of bulk upserts with a temporary table.

Why this module comes here:

Modules 1-6 gave you cross-cutting patterns — applicable to almost any endpoint. Bulk operations is different: it only shows up in specific cases (data imports, backfills, ETL). Putting it last lets you focus on the specific patterns without getting distracted from the main flow. And it leaves you ready for module 8's final project, which is going to use bulk operations for one of its endpoints.

And there's a direct connection with module 5: the backfill of the expand-contract pattern is a variant of a bulk operation (a batched UPDATE instead of a bulk INSERT). What you learn here also applies to that part of migrations.

Professional objective

By the end of this module you'll be able to:

  • Distinguish the 4 bulk-insert approaches with measured benchmarks: a loop, executemany, bulk_insert_mappings, COPY.
  • Implement COPY FROM STDIN with async asyncpg (copy_records_to_table), including serialization to CSV in memory.
  • Recognize the limitations of each approach: bulk_insert_mappings skips ORM events and validators; COPY doesn't fire Python triggers.
  • Implement atomic upserts with INSERT ... ON CONFLICT (col) DO UPDATE SET col1 = EXCLUDED.col1.
  • Apply bulk upserts with the canonical pattern: COPY into a temporary table → INSERT...ON CONFLICT from the temp table.
  • Handle transactional errors: COPY fails all or nothing — patterns for isolating the problematic row.
  • Apply the decision matrix by size: <100 rows → the normal ORM; <1k → executemany; <10k → bulk_insert_mappings; >10k → COPY.
  • Stream large data without loading everything into memory.

Why does this module matter?

The gap between "slow" and "fast" in bulk operations is 60x — visible to users. It isn't theoretical:

  • An import endpoint: the client uploads a CSV with 50k rows. With an INSERT loop: 23 seconds. With COPY: 400ms. The difference between "OK, wait" and "perfect, instant feedback".
  • A nightly backfill: updating priority on 10M tasks. With a loop: days. With COPY into a temp table + INSERT...SELECT: minutes.
  • A daily ETL: importing millions of events into the data warehouse. With a loop: the ETL doesn't finish before the next day. With COPY: it finishes in hours.
  • Load tests: seeding the DB with 1M rows for a performance test. A loop: 8 minutes. COPY: 20 seconds. Fast tests vs slow ones.

In the real senior backend dev role, this is what separates "toy endpoints" from "production-ready endpoints". Any dev does a POST with a single record. Only people with real operational experience know when to use COPY, when to use unnest(), when to use bulk_insert_mappings, and why each one has its place.

For senior interviews, this topic shows up in questions like "I have to import a CSV of 100k rows in an API, how would you do it?". Without this module, the answer is vague ("a loop with a commit every 100?"). With this module it's methodical: validate the CSV, COPY into a temporary table, INSERT...ON CONFLICT from the temp table, return stats. And a concrete answer: "100k rows in less than 1 second".

A scenario that illustrates the module

Your team built TaskFlow. It works well for users who create tasks one at a time. An enterprise customer arrives with a new case: "We need to import our 50,000 existing tasks from the CSV exported from Jira."

Your first impulse is a loop:

@router.post("/tasks/import")
async def import_tasks(file: UploadFile, db: AsyncSession = Depends(get_db)):
    rows = parse_csv(await file.read())
    for row in rows:
        task = Task(**row)
        db.add(task)
    await db.commit()
    return {"imported": len(rows)}

It works in testing with 100 rows (<1s). You send the URL to the customer. They upload their 50k-row CSV. The browser request times out at 30 seconds. The customer is frustrated. Your app is down for all the other users while the transaction of 50k inserts gets processed.

Without this module, the flailing solutions:

  • "Upload it in batches of 1k" → making the customer do the work.
  • A background job → more infrastructure, more complexity.
  • "We don't support anything that large" → losing the customer.

With this module:

  1. Capsule 02 (the decision matrix): 50k rows → clearly COPY, not a loop or executemany.
  2. Capsule 03 (COPY with asyncpg): you implement in-memory serialization to CSV + copy_records_to_table. 50k rows in <500ms.
  3. Capsule 05 (ON CONFLICT): the CSV may have duplicates (re-imports). You use ON CONFLICT (jira_id) DO UPDATE for idempotence — re-importing the same CSV doesn't cause errors, it updates the existing rows.
  4. Capsule 06 (bulk upsert with a temp table): for cases where you need per-row validation before the INSERT, COPY into a temporary table → validate → INSERT...ON CONFLICT from the temp table with joins/filters.
  5. Capsule 07 (errors): if one row has an invalid format, COPY fails everything. The strategy: validate first, COPY afterward; or COPY into a temp table + filter.
  6. Capsule 08 (the mini-project): a complete endpoint with documented benchmarks — 50k rows in <500ms, 10k in <100ms.

The result: the customer imports their 50k rows in <1s. The app doesn't go down. They see you as "the team that solves complex cases" instead of "the ones who said it couldn't be done".

Module map

CapsuleTopicWhat you'll learn
01Module introductionYou are here. The decision matrix by size, the scenario, the map.
02The 4 approaches with benchmarksA loop vs executemany vs bulk_insert vs COPY: 47s vs 12s vs 4.3s vs 0.8s.
03COPY in depth with asyncpgcopy_records_to_table, CSV serialization, NULL handling, the binary format.
04bulk_insert_mappings and its limitationsWhen it beats COPY, when it loses. The ORM events it skips.
05Upserts with ON CONFLICTDO UPDATE SET col = EXCLUDED.col, idempotence, anti-patterns.
06Bulk upserts with a temporary tableThe canonical pattern: COPY → a temp table → INSERT...ON CONFLICT from the temp table.
07Error handling + streamingValidation before COPY, streaming for giant datasets.
08Project: POST /tasks/bulk with benchmarksA complete TaskFlow endpoint with 50k tasks in <1s.

The narrative flow: first you see the 4 approaches with real benchmarks (02). Then you go deep on COPY, the most powerful tool (03). Then on bulk_insert_mappings with its warnings (04). Then we introduce upserts for idempotence (05) and the temp-table pattern (06). Then you handle the edge cases (errors, streaming) in 07. You close with the project in 08.

The decision matrix by size

This is the heuristic rule you're going to apply:

Batch sizeApproachTool
< 100 rowsThe normal ORMsession.add_all() + commit
100 - 1,000 rowsexecutemanysession.execute(insert(Task), [...])
1,000 - 10,000 rowsbulk_insert_mappingssession.bulk_insert_mappings(Task, [...])
> 10,000 rowsCOPYcopy_records_to_table (asyncpg)
With potential duplicatesON CONFLICTCombine it with any approach above
With potential duplicates + > 10kCOPY into a temp table + INSERT...ON CONFLICTThe canonical pattern

These numbers are orders of magnitude, not exact. The boundary between "1k → executemany" and "1k → bulk_insert" depends on your hardware, schema complexity, DB connection. But the shape of the curve is consistent: each jump reduces the time significantly.

Connection with the integrative project

The final project (module 8 — the complete TaskFlow) implements:

The POST /tasks/bulk endpoint:

  • Receives a JSON or CSV payload with N tasks (up to 10k).
  • Validates each task (Pydantic).
  • COPY into the temporary table tmp_tasks_import.
  • INSERT INTO tasks (...) SELECT ... FROM tmp_tasks_import ON CONFLICT (external_id) DO UPDATE.
  • Returns stats: {"inserted": 8523, "updated": 1477, "elapsed_ms": 1450}.

Module 8's mini-project is focused: only the POST /tasks/bulk endpoint with documented benchmarks. Module 8's final project integrates it with everything else (auth, multi-tenancy, optimistic locking, etc.).

What is NOT covered in this module

  • The bulk DELETE pattern: similar to UPDATE but rarer in production. Mentioned briefly, not covered in depth.
  • pg_bulkload: an external extension for extreme cases (>100M rows). Out of scope.
  • AWS Aurora bulk loaders / Google Cloud Spanner imports: specific to managed cloud offerings. Mentioned, not covered in depth.
  • Streaming inserts via Kafka / event-driven: a different architecture, out of scope.
  • Bulk operations in other databases (MySQL's LOAD DATA INFILE, MongoDB's insertMany): conceptually similar patterns but different specific implementations.
  • pandas.to_sql() and other data science libraries: relevant for data engineering but outside the backend scope.

Traps to avoid while taking the module

1. "I'm going to use COPY for everything, it's the fastest."

COPY has costs: it doesn't fire Python triggers (ORM events), it doesn't return autogenerated IDs the same way an INSERT does, it requires manual CSV serialization. For small batches, executemany or the normal ORM are simpler and sufficient. Capsule 02 gives you the matrix.

2. "An INSERT loop with a commit() every 100 makes it as fast as bulk."

No. Each INSERT has the overhead of a network round-trip, parse, plan, execute. Committing every 100 reduces the durability cost but not the per-row cost. For 100k rows, it's still seconds vs milliseconds.

3. "For upserts, I do a SELECT first, then decide INSERT or UPDATE."

A race condition between the SELECT and the INSERT. Another process can insert in between. Use ON CONFLICT, which is atomic.

4. "bulk_insert_mappings is like add_all but faster."

It isn't just faster — it also skips ORM events. If your model has @validates or listeners, they do NOT run. Take that consciously. Capsule 04 covers it.

5. "COPY with 10M rows and a CSV in memory is fine, I have 16GB of RAM."

10M rows × ~200 bytes/row = 2GB in memory. Plus Python's overhead = OOM at any moment. Streaming is mandatory for large imports. Capsule 07.

6. "If the CSV has one bad row, my code has to skip it."

COPY is atomic — it fails all or nothing. If you need skip-on-error, validate before COPY or use a temporary table with filters.

Self-assessment questions

Before starting this module, can you answer these questions?

  • What's the difference between executemany and bulk_insert_mappings in SQLAlchemy?
  • What does COPY FROM STDIN do and why is it so fast?
  • What does bulk_insert_mappings skip that add_all does run?
  • What does ON CONFLICT (col) DO UPDATE SET col1 = EXCLUDED.col1 do?
  • Why doesn't a direct COPY support ON CONFLICT?
  • What is the "COPY into a temp table + INSERT...ON CONFLICT from the temp table" pattern?
  • What happens if a row in a COPY has an invalid format?

If you hesitate on more than three, the module is well calibrated for you.

Evidence of success

By the end of the module, you'll know you succeeded if:

  • Looking at an endpoint that imports data, you identify the right approach based on the payload's size.
  • You implement POST /tasks/bulk with COPY + ON CONFLICT, measuring real benchmarks.
  • In code review, you spot an INSERT loop and propose the refactor with the right tool.
  • You know how to serialize to CSV in memory with correct escaping of strings and NULLs.
  • You argue when bulk_insert_mappings is fine and when it is NOT (when ORM events matter).
  • You apply the "COPY into a temp table + INSERT...ON CONFLICT" pattern for cases with potential duplicates.

We start in the next capsule

We start with capsule 02: the 4 approaches with real benchmarks. You're going to see the benchmark setup, run each approach, see the numbers: 47s → 12s → 4.3s → 0.8s. It's the point where the difference becomes visceral instead of abstract. After that, the next capsules go deep on COPY (cap 03) and bulk_insert (cap 04) with their details.

Before moving on, make sure you have PostgreSQL 16 running, Python 3.12+, and asyncpg/SQLAlchemy 2.0 installed. We're going to use asyncio.run() and time.perf_counter() for the benchmarks.

Resources for the module

  1. PostgreSQL Docs — COPY — the complete official reference.
  2. asyncpg — Bulk Methods — the copy_records_to_table reference.
  3. SQLAlchemy 2.0 — Bulk Operations — the official reference with warnings.
  4. PostgreSQL Docs — INSERT ... ON CONFLICT — the upsert syntax.
  5. Brandur Leach — "PostgreSQL bulk inserts" — an analysis with benchmarks.
  6. Citus Data — Bulk inserts comparison — a detailed comparison.
  7. PostgreSQL Wiki — Performance Tips — includes bulk insert tips.

Module 7 — SQL Patterns for Production APIs Guide

← PreviousMini-project: TaskFlow with optimistic locking + 3 schema versionsNext →The 4 approaches with real benchmarks