Faster Bulk Inserts in PostgreSQL

Two tables. band, and song pointing at it with a foreign key. Four tracks to an EP, twenty million bands, a hundred million rows all told. The most ordinary shape in any schema you have ever worked on. You write the obvious loop, kick it off, go get a coffee.

You come back. It's at 4%.

Nothing is wrong. No lock. No missing index. No N+1 you can point at in review and feel clever about. The query plan is fine, the code does exactly what it looks like it does, and at the rate it's going it will be finished tomorrow afternoon. You already told the team it would be done before lunch.

It took me an embarrassingly long time to work out why, mostly because I spent that time tuning batch sizes. The inserts were never the problem.

The song that cannot exist yet

band = Band(name="Toaster Incident")
session.add(band)
session.flush()          # <- "so, what did you call that band?"
song = Song(band_id=band.id, title="Bagel Dawn")

Look at what that flush() is for. The band has a name. You just gave it one. But song.band_id doesn't reference the name you chose. It references the one Postgres chose, an integer nobody will ever read aloud. That's the name you're standing there waiting for.

It's easy to file that wait under overhead: a bit of latency, a tax per row, annoying but survivable. That framing is what cost me the afternoon.

Ask what happens if you skip it. The song doesn't get built slowly, it doesn't get built at all. It's missing band_id, the one column that makes it a song by anybody. So this isn't overhead, it's a dependency, and a dependency has consequences that latency doesn't. Your load is now sequenced by construction: every band, then read every id back, then every song. You can't overlap the phases, can't split the tables across workers, can't start on the tracklists while the bands are still going in. Not because of a lock or a setting you forgot to flip, but because that data does not exist yet.

You didn't write a slow loop. You wrote a loop that stops to ask permission a hundred million times. Measured, on my laptop: 1,790 rows per second.

Making the question faster

The natural instinct, mine included, is to make the asking cheaper, and SQLAlchemy has a proper answer for it. insertmanyvalues batches the bands into a handful of INSERT ... RETURNING statements and hands back a thousand ids at a time instead of one:

band_ids = session.scalars(
    insert(Band).returning(Band.id, sort_by_parameter_order=True),
    [{"name": name} for name in names],
).all()
session.execute(insert(Song), [
    {"band_id": band_ids[i], "title": t}
    for i, tracks in enumerate(tracklists) for t in tracks
])

sort_by_parameter_order=True is the load-bearing part: it makes band_ids[k] correspond to input row k, guaranteed. That guarantee isn't free though, it's bought. Behind the flag sits a subsystem of sentinel columns and per-dialect batching rules whose entire job is working out which returned row belongs to which parameter set. Good engineering, and it exists for exactly one reason. You didn't know the key.

Use it. It's the supported answer and it's 15 times faster than the loop.

And the shape of the problem hasn't moved an inch. There's still an identity map in your application's memory. There's still a phase that exists only to receive an answer, and a second phase that can't start until it arrives. You still can't run the two tables at the same time, in whatever order suits you. The load is still a sequence, just a faster one.

So don't ask

There are two ways to know a key before you insert it, and most writing on this only mentions the first.

The first is to mint it yourself. One column default, and then you go back to writing ordinary objects. Prefer uuid7 over uuid4 if you can, since this is a primary key and the ordering costs nothing when you're choosing fresh. (uuid.uuid7() needs Python 3.14. Below that, use uuid.uuid4 or a backport, and see the section further down for what that costs you.)

class Band(Base):
    __tablename__ = "band"
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid7)
    name: Mapped[str]
    songs: Mapped[list["Song"]] = relationship(back_populates="band")

class Song(Base):
    __tablename__ = "song"
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid7)
    band_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("band.id"))
    band: Mapped[Band] = relationship(back_populates="songs")
    title: Mapped[str]

session.add_all([
    Band(name=name, songs=[Song(title=t) for t in tracks])
    for name, tracks in lineup
])
session.commit()

That's the whole thing. No flush in the loop, no id to carry around, no correlation, just objects and relationships written exactly as you'd write them for a single row. SQLAlchemy fills the defaults in Python at flush time, discovers it already knows every key, and quietly batches. On my machine that's 25,000 rows in 25 INSERT statements, not one of them carrying a RETURNING clause.

The second way is to reserve it. Keep your bigint identity column and its sequence, but ask that sequence for a block, once, instead of one id per row after the fact:

SELECT nextval('band_id_seq');          -- 1, so the block 1 .. 250000 is yours
-- ... build the whole graph against it, load it ...
SELECT setval('band_id_seq', 250000);   -- park the sequence past what you used

That works because you're the only one touching the table, which is the normal case for a migration. If something else is inserting while you load it will happily take id 2 from inside your block. For that case use ALTER SEQUENCE band_id_seq INCREMENT BY 250000 instead, so every nextval hands out a whole block atomically — the value it returns through that value plus 249,999 — and put the increment back only once nobody else is still allocating. What you should not use is classic hi-lo, which multiplies the sequence value rather than using it, and so collides with anything reading that sequence literally.

That's it. That's the whole article. Which one you pick matters much less than that you picked one.

Notice what disappeared. No flush, no RETURNING, no round trip per row. But also no sentinel columns, no ordering guarantee to rely on, no identity map, no correlation to get right. That subsystem is still sitting there inside SQLAlchemy. It just has nothing left to do. None of it got optimised away; it stopped being relevant, which is much better than fast.

The entire graph now sits in memory with every foreign key already resolved, before a single byte crosses the socket. The two tables stop being a sequence and turn into two independent piles of rows. Any order, either table alone, as many processes as you like.

The name of the band isn't something you wait for. It's something you decided.

The rows that matter are the last two, and the interesting thing about them is that they're the same row. Same statement, same schema, same speed to within a rounding error. Only the origin of the key moved, and the moment it stopped coming from a round trip the load got over 70% faster. The reserved block just does it in half the index bytes.

That uuid row is uuid4, the pessimistic one, because it's the version everybody already has. Swapping in uuid7 moves it the right way: under COPY further down, the same rows go from 72,599 to 106,118.

Then COPY opens a door

COPY is the fastest way into Postgres and it is structurally incapable of telling you what it wrote:

COPY band (id, name) FROM STDIN RETURNING id
-- ERROR:  syntax error at or near "RETURNING"

That's the door. With a key the database picks at insert time you could never open it, since the songs need ids COPY will never hand back. With the key already decided, the question never comes up, and the write side is about as plain as it gets:

with cursor.copy("COPY band (id, name) FROM STDIN") as copy:
    for band in bands:
        copy.write_row((band.id, band.name))

Fifteen hours at the top of this article, fourteen minutes here.

And every engine makes the same bargain. MySQL has LOAD DATA INFILE, SQL Server has bcp, Oracle has SQL*Loader, and not one of them returns generated keys, because a loader that stopped to report what it wrote wouldn't be a loader. Every technique before this section was machinery bolted on to survive not knowing the key. COPY is a tool with no machinery at all, available only once you stopped needing any.

You can go further with parallel workers. Four of them reached 225,940 rows/s, seven minutes for the whole load. I'd leave it alone. It buys a factor of two over the row above, and costs you a worker pool, failure handling for a worker that dies holding half a block, and an index that packs at 58 to 77% instead of 90%, because only one leaf page in a B-tree is the rightmost one and only the rightmost one splits efficiently. Reach for it when fourteen minutes is genuinely the problem.

About those uuids

Why uuid7 above and not uuid4. A uuid4 is sixteen random bytes, so every insert lands somewhere different in the index and full pages split down the middle, leaving both halves half empty. A uuid7 puts a timestamp in the high bits, so keys sort in the order you mint them, pile onto the same hot right-hand page, and split at the end the way a serial does.

The effect is real. Starve Postgres of memory, load ten million rows, and the ordered key fetches 8 index blocks from disk across the entire load where the random one fetches 325,278, leaving an index a fifth smaller.

So take it when you're choosing fresh. What I wouldn't do is migrate an existing schema for it. It only fixes the index you change, and your song table has six, the other five still randomly distributed. uuid7 was only standardised in 2024, Postgres grew uuidv7() in 18 and Python got uuid.uuid7() in 3.14, so below those versions you're taking a dependency or hand-rolling bit-packing. And I had to squeeze Postgres into a 1 GB container to make uuid4 look bad at all. The gap above is what happens once the index stops fitting in memory; while it still fits, it's a rounding error, and rolling your own is a lot of care for it.

One thing to be careful about: making a uuid your primary key is a bigger decision on some engines than on others, because there the primary key decides how and in what order your rows are physically stored. Postgres doesn't work that way. It keeps the table in a heap and the primary key in an ordinary index alongside every other one, which is the only reason I can shrug at uuid4 above. InnoDB and SQL Server cluster the table on the primary key, so there a random uuid isn't one badly-packed index, it's the physical order of your data, and it costs you a great deal more. The shrug doesn't transfer. The rewrite in this article does.

What this doesn't cover

A load is more than a rate, and none of the following is in the numbers above: transactions and what a half-finished load leaves behind, restartability, logging and progress reporting, how many connections you should open, retries and fallbacks. Decide those separately. They will matter more to you at 3am than the difference between 16 and 14 minutes.

Two specifics worth naming. COPY is not a faster INSERT, it's a different thing. No RETURNING, which is the whole point above, but also no ON CONFLICT, so no upserts. Rewrite rules don't fire, though constraints do. And one malformed row aborts the entire stream unless you're on PostgreSQL 17 or later and opt into ON_ERROR ignore. If your load needs deduplication or partial success, COPY into a staging table and merge from there.

Triggers deserve their own line, because they fire and people expect otherwise. A row-level trigger runs once per row, so a COPY into a heavily triggered table can land back down near INSERT speed and take most of the reason you came here with it. A statement-level trigger goes the other way and fires once for the whole stream, which sounds cheap until you remember that a transition table then holds your entire load at once. Check what's attached to the table before you quote anyone a number.

The other well-known lever is dropping indexes and rebuilding them after the load. It's real, it stacks with everything here, and it's orthogonal, since it tells you nothing about where your keys come from, which is why I held the schema fixed throughout. Just note that it isn't always available: if the table is still serving queries during the migration, you can't take its indexes away for an hour.

So which one should you use?

If your API writes a handful of rows at a time, don't bother with any of this. At one or two rows there's no throughput argument to have. A single round trip for a single row is fine, which is this whole article read backwards. Per-row asking only hurts in bulk.

If bulk insertion is what your API is for, then it's worth deciding, and both answers are good ones. What follows is the part the measurements can't settle.

The reservation is faster and denser, and it will stay that way. Look at song: it carries id and band_id, so uuids on both mean thirty-two bytes of key per child row before you count the indexes dragging it around. That's what the 22.5 against 41.3 really is, a whole-schema multiplier rather than a per-column tax.

But it's something you do, not something you have. Every place you want it, you write it: an allocator, a block size, a reset, wiring per table. A three-level graph needs it three times. Anything that touches those tables without knowing about the trick goes back to asking per row. (Hibernate ships this as pooled. SQLAlchemy has nothing equivalent, which is a fair signal of how much most applications actually need it.)

The uuid, by contrast, is a property of the schema. Insert or COPY, reuse the object, hand it to a different ORM, nest it four levels deep, and nothing changes and nothing needs arranging. Batching is available by design, everywhere, whether anyone thought about it that morning or not. No trick to remember and no site to remember it at. The price is bytes and a looser index. It also stops anyone reading the next id out of a URL, which you may have wanted anyway.

There's a newer argument on that side too, and I've come to weight it more than I expected. Most of us now write alongside an assistant, and assistants reproduce what they've seen a lot of. They have seen an enormous quantity of session.add_all(...) and bulk COPY. They have seen very little sequence arithmetic. In my experience a uuid-keyed bulk load is something an AI assistant will write, review and refactor without any special briefing, while a hand-rolled block allocator is the sort of thing that gets quietly routed around or subtly broken three commits later, by a person or a model, both for the same reason. That's not an argument about correctness. It's an argument about what your code still looks like after fifty more commits.

So: speed and density at the cost of doing something specific each time, against a property that holds everywhere at the cost of sixteen bytes. Neither is the right answer. The balance runs across things this benchmark never touched, like how your API is used, how much of the codebase has to stay aware of the trick, what your ORM makes pleasant, and what you'll still find obvious in two years. Your taste, your needs, your call.

What isn't a matter of taste is the thing underneath both. Know the key before you insert. You do not get from fifteen hours to fourteen minutes by adjusting a setting. That decision was made months earlier, in a migration file, by someone typing out a primary key column without thinking about it for even a second. Possibly you. Definitely me.

The database is very good at answering questions, and the fastest question is the one you didn't need to ask.

Method. PostgreSQL 18 in a 1 GB container, shared_buffers=256MB, client and server on the same eight-core laptop. Each strategy gets the same 180-second budget and a freshly created, checkpointed, stat-reset schema. The "100M rows" column is the measured rate projected onto the full load, not a completed run. The row-by-row loop is capped at 100,000 rows, because nobody is sitting through fifteen hours to confirm a rate that was stable after the first minute.

rows/s is rows divided by database insert time, and the block reservation's round trip is charged to that side of the ledger. Minting uuids in Python is real work, but it's client CPU and parallelises trivially where the write path doesn't. Put everything on one clock instead and the uuid rows lose ground rather than gain it: COPY with uuid7 falls from 106,118 to 73,466, while the reserved block only falls from 116,322 to 114,185.

Every number in the tables comes from one invocation against one container, which matters more than I assumed. Re-running the identical configuration in a later session moved every absolute rate by about 70%, the warmer machine slower straight across the board and flat over all slices both times. A laptop that has just spent an hour under sustained load is not the same machine as a cold one. The ratios were unmoved, which is the only reason any of this is reportable, so read the columns against each other and never against your hardware. Within a run the slice-to-slice spread is about 2%, so the uuid4 and reserved-block rows really are a tie.

And I'd like to hear the other direction too. There are more ways to get a lot of rows into a database than fit in one article: block allocators, client-side keys, COPY into a staging table and merge, partition swaps, things I've never had a reason to try. What's your favourite, and what did it cost you?