Your Bulk Load Is Not Slow, It Is Sequenced

A developer writing on dev.to profiled the most ordinary insert loop in an ORM codebase and measured 1,790 rows per second on a laptop, after spending an afternoon tuning batch sizes that were never the bottleneck ^[8][3]. The reason matters more than the number: the per-row `flush()` that asks Postgres for a generated id is not latency you can amortise, it is a dependency that dictates the order of the entire load ^[6].

The shape is unremarkable. Two tables, `band` and `song` with a foreign key pointing at it, four tracks to an EP, twenty million bands, a hundred million rows all told ^[1] - twenty million parents and eighty million children ^[1]. The loop adds a band, flushes to get `band.id`, then constructs the song against it ^[4]. The band already has a name you chose; `song.band_id` does not reference that name, it references the integer the database picked, and that is the value you are standing there waiting for ^[5]. At the reported rate, a hundred million rows takes roughly 15 hours ^[2], which is consistent with the author coming back from coffee to find the job at 4% ^[2].

The useful move in the piece is refusing to file that wait under overhead. Skip the flush and the song does not get built slowly, it does not get built at all, because `band_id` is missing ^[6]. So the load is sequenced by construction: every band, then read every id back, then every song. The phases cannot overlap, the tables cannot be split across workers, the tracklists cannot start while the bands are still going in ^[7]. No batch size argument touches any of that.

SQLAlchemy has a supported answer and the author recommends using it. `insertmanyvalues` batches the parents into a handful of `INSERT ... RETURNING` statements and hands back a thousand ids at a time instead of one ^[9], and `sort_by_parameter_order=True` is the load-bearing flag: it guarantees that returned id *k* corresponds to input row *k* ^[10]. The guarantee is bought, not free - behind it sits a subsystem of sentinel columns and per-dialect batching rules whose only job is matching returned rows to parameter sets ^[11]. The author measures it at 15 times faster than the loop ^[12], which puts the same hundred million rows near an hour ^[3].

And the topology is identical. There is still an identity map in application memory, still a phase whose only purpose is to receive an answer, still a second phase that cannot begin until it arrives ^[13]. A faster sequence is still a sequence.

The alternative is to know the key before you insert. Mint it in the application: one column default, `uuid7` preferred over `uuid4` for a primary key since the ordering costs nothing when you are choosing fresh ^[15]. The code collapses back to `add_all` of `Band(name=..., songs=[Song(title=t) ...])` and a commit, with no flush anywhere in the loop ^[17].

Two caveats on the evidence. The throughput figures are one author's laptop measurement, not a benchmark suite ^[8]. And `uuid.uuid7()` requires Python 3.14; below that the author points to `uuid4` or a backport and flags a cost for doing so, which the excerpt I have does not spell out ^[16]. The author also says there are two ways to know a key before inserting it and that most writing covers only the first ^[14] - the second is not in the portion available here.

Worth watching: whether your driver and dialect actually get the batched `RETURNING` path, and what the author's stated cost of `uuid4` turns out to be. The real test after removing the flush is not rows per second but whether you can finally load both tables at once ^[7].