ORM Bulk Insert and Upsert Plans #

An import job uses the ORM’s bulk_create and takes eleven minutes for 400,000 rows. The database reports 400,000 individual INSERT statements averaging 0.09 ms — 36 seconds of server time inside an eleven-minute job. The helper fell back to per-row inserts because the model has a trigger-backed callback, and nothing in the application log said so.

The shapes available are introduced in ORM pagination and batch writes. This page is about verifying which one you got and choosing between them.

The Condition #

ORM bulk helpers promise batching, and deliver it conditionally:

So the reliable check is not the API name but the statement count in pg_stat_statements or the driver log.

Beyond multi-row INSERT, two shapes matter:

Four ways to write 400,000 rows A table. Per-row inserts issue 400,000 statements and support callbacks. Multi-row inserts issue 400 statements and support ON CONFLICT. COPY issues one statement but has no conflict handling. COPY into staging plus a merge issues two statements and supports full conflict handling. statements conflict handling per-row INSERT 400,000 per statement multi-row INSERT 400 ON CONFLICT COPY 1 none COPY + staging merge 2 full batch size 1,000 for the multi-row case

Annotated Evidence #

The fallback, seen from the database:

SELECT calls, round(mean_exec_time::numeric, 3) AS ms, left(query, 60)
FROM pg_stat_statements WHERE query LIKE 'INSERT INTO events%' ORDER BY calls DESC;
 calls  |  ms   |                        left
--------+-------+------------------------------------------------------
 400000 | 0.090 | INSERT INTO events (account_id, kind, payload) VALUES ($1, $2, $3)
-- one statement per row: the helper did not batch

After forcing true batching:

 calls | ms     | left
-------+--------+------------------------------------------------------
   400 | 41.204 | INSERT INTO events (account_id, kind, payload) VALUES ($1,$2,$3),($4,$5,$6),…
EXPLAIN (ANALYZE, BUFFERS, WAL)
INSERT INTO events (account_id, kind, payload)
VALUES (…1000 rows…);

Insert on events  (actual time=38.6..38.6 rows=0 loops=1)
  Buffers: shared hit=4102 dirtied=812
  WAL: records=2014 bytes=1204880
  ->  Values Scan on "*VALUES*"  (actual rows=1000 loops=1)
Execution Time: 41.2 ms
-- 2 WAL records per row: heap + one index

The staging approach for an upsert load:

CREATE UNLOGGED TABLE events_stage (LIKE events INCLUDING DEFAULTS);
COPY events_stage FROM STDIN;                 -- 400,000 rows
ANALYZE events_stage;

INSERT INTO events (account_id, kind, payload, external_id)
SELECT account_id, kind, payload, external_id FROM events_stage
ON CONFLICT (external_id) DO UPDATE SET payload = EXCLUDED.payload
WHERE events.payload IS DISTINCT FROM EXCLUDED.payload;
Insert on events  (actual time=8402.1..8402.1 rows=0 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: events_external_id_key
  Tuples Inserted: 381204   Conflicting Tuples: 18796
  Rows Removed by Conflict Filter: 17204
  ->  Seq Scan on events_stage  (actual rows=400000 loops=1)
Execution Time: 8410.6 ms
Did the helper actually batch? Three items are annotated. A statement call count equal to the row count shows per-row inserts. A Values Scan under an Insert node shows a multi-row statement. WAL records per row show how much index maintenance each row costs. calls = 400,000 for one shape per-row inserts Insert on events → Values Scan (rows=1000) one multi-row statement WAL: records=2014 for 1,000 rows heap + one index per row the API name does not tell you; the call count does

Step-by-Step Resolution #

  1. Count statements for one job run. pg_stat_statements_reset(), run the job, then read calls for the insert shape.

  2. If it is per-row, find out why. Callbacks, returning generated values, polymorphic models and some driver configurations all force it. Use the ORM’s documented bulk API and accept that it skips callbacks.

  3. Set a batch size explicitly — 500 to 5,000 rows — and measure per-row time at two or three sizes.

  4. For very large loads, use COPY. Every major driver exposes it: psycopg’s copy, pg-copy-streams in Node, PG::Connection#copy_data in Ruby.

  5. For upserts at volume, stage then apply. An unlogged staging table avoids WAL for the staged copy; analyze it so the apply step is planned correctly.

  6. Keep the conflict filter (WHERE target IS DISTINCT FROM EXCLUDED…) so unchanged rows are not rewritten.

  7. Watch the write side of indexes. Every index on the target is maintained per inserted row; dropping unused indexes before a very large load and rebuilding afterwards can be faster overall, though it costs availability of those indexes meanwhile.

Loading 400,000 rows Per-row inserts take 660 seconds. Multi-row inserts in batches of 1,000 take 48 seconds. COPY into a staging table plus one upsert statement takes 19 seconds. The same with the unchanged-row conflict filter takes 14 seconds. per-row INSERT 660 s multi-row, 1,000/batch 48 s COPY + upsert 19 s + conflict filter 14 s most of the first bar is round trips, not server work

Before and After #

-- BEFORE: helper fell back to per-row inserts
calls 400,000  mean 0.09 ms  job elapsed 11 min

-- AFTER: COPY into unlogged staging + one upsert with a conflict filter
2 statements  job elapsed 14 s

Why unlogged staging is safe here #

An unlogged table is not WAL-logged, so it is faster to write and is truncated automatically after a crash. That makes it unsuitable for durable data and ideal for staging: if the server crashes mid-load, the staging content is gone and the job re-runs from its source, which it must be able to do anyway. The target table’s insert is fully logged, so the loaded data is as durable as any other write.

Two cautions. Unlogged tables are not replicated, so a standby cannot read the staging table — irrelevant for a load job, relevant if something else expects to query it. And TRUNCATE between batches is cheaper than DELETE and resets the table’s statistics, which is why the ANALYZE must come after each load rather than once at setup.

For loads that run continuously rather than as discrete jobs, a permanent staging table per worker with a TRUNCATE at the start of each batch keeps the pattern without repeated DDL, which would otherwise invalidate cached plans across the fleet on every batch — the mechanism in plan invalidation after DDL and ANALYZE.

Common Pitfalls #

Trusting the helper’s name. Some fall back to per-row inserts silently. Diagnostic signal: calls equal to row count. Fix: verify, then use a lower-level API.

Callbacks forcing per-row writes. Bulk APIs skip them by design; loops do not. Diagnostic signal: business logic in model callbacks. Fix: move that logic into the job, or accept per-row writes for small volumes.

One enormous statement. Locks and WAL for minutes. Diagnostic signal: replication lag during loads. Fix: moderate batches with commits.

Unanalyzed staging tables. The apply step is planned for a tiny table. Diagnostic signal: nested loops over hundreds of thousands of staged rows. Fix: ANALYZE after COPY.

Frequently Asked Questions #

How do I know whether my ORM’s bulk insert is really batching? #

Reset pg_stat_statements, run the job and compare the insert statement’s call count with the number of rows. Equal counts mean one statement per row.

Is COPY better than multi-row INSERT for bulk loading? #

For large volumes, yes — it avoids parsing SQL per row. It does not support ON CONFLICT, so upserts usually COPY into a staging table and then apply the rows with one set-based statement.

Should I drop indexes before a large load? #

Sometimes. Index maintenance dominates large loads, and rebuilding afterwards can be faster overall — but the indexes are unavailable meanwhile, and rebuilding needs time and memory. It suits initial loads more than incremental ones.

Up: ORM Pagination and Batch Writes