Easier batching of bulk inserts #710

SebastienGllmt · 2022-05-02T03:41:58Z

SebastienGllmt
May 2, 2022

Summary

Postgres only allows a maximum of u16::MAX input parameters. If you exceed this amount, you will get an error in sqlx. Getting around this limitation while using entities is kind of ugly

Motivation

sea-orm provides a function on entities called insert_many such as the the following example

let apple = cake::ActiveModel {
    name: Set("Apple Pie".to_owned()),
    height: Set(0.5),
    ..Default::default()
};

let insert_result = cake::Entity::insert_many(vec![apple])
     .exec(&db)
     .await?;

This insertion requires two SQL parameters because insert_many uses the VALUES syntax

INSERT INTO `cake` (`name`) VALUES (?), (?)

This works fine up until you hit the u16 limit of parameters. The naive workaround for the Postgres limit would be to do something like this

let inserts = vec![apple];
for chunk in inserts.chunks(u16::MAX as usize) {
    cake::Entity::insert_many(chunk.to_vec()).exec(&db).await?;
}

However, this won't work because we have to divide by the number of columns!

That means the proper solution (as far as I can tell) ends up being

use entity::sea_orm::Iterable;

for chunk in transactions
        .chunks((u16::MAX / <cake::Entity as EntityTrait>::Column::iter().count() as u16) as usize)
    {
    cake::Entity::insert_many(chunk.to_vec()).exec(&db).await?;
}

This is already kind of ugly for a one-time patch in your codebase, but having this kind of chunk logic all over the place makes things even worse

It would be nice if insert_many either handled this for you or if there was some other utility function for this.

billy1624 · 2022-05-04T03:29:56Z

billy1624
May 4, 2022
Maintainer

Hey @SebastienGllmt, thanks for the suggestions! I think we can have a insert_many_chunk method in EntityTrait which takes an iterator of ActiveModel and the chunk size.

Basically, it will perform... for you

for chunk in inserts.chunks(chunk_size) {
    Self::insert_many(chunk.to_vec()).exec(&db).await?;
}

Regarding parameters limit with number of column take into account, it's db dependent and it should be provided by the user. User can substitute it in the batch size just like what you have done.

PR are welcomed! :P

5 replies

SebastienGllmt May 10, 2022
Author

Hmm, if sea-orm isn't dealing with dividing by the number of columns for you depending on the database you're using, I'm not sure if there is much use in adding this feature to sea-orm. if the function is just a wrapper for .chunks the user may as well do that on the user-side.

< 10000 div class="avatar-parent-child flex-column flex-items-center TimelineItem-avatar left-0 ml-3">

rustonaut Feb 12, 2024

I think the chunking should (if possible) be handled internally and fully opaque to the caller. A insert_many without such functionality is a huge foot gun (but a common foot gun).

Needing chunking is a fundamental limitation/aspect of many-value insert statements(1) no matter the db/binding/orm (The limit for Postgres and MySql might be 65535 binds but for SQLite it is only 32766 and had been 999 in the past. For MSSQL it is 2100 in case sqlx supports it again in the future.)

The important thing for such a functionality is that the number of chunks are not a input which should be provided by the caller but one which should be automatically derived from the query statement based on the number of binds per column.

IMHO if for implementation reasons insert_many can't provide this for whatever reason there should be an alternative method which does provide it.

tomellm Jun 6, 2025

Not sure if this Topic is stale or has already been implemented but I'm of the same opinion. I've already shot myself in the foot twice, and would rather not do it again. Tbh doing the chunking automatically and then allowing for a opt out seems like the better way, all in the name of "sensible defaults". But in any case, since this Discussion board is so old, I'm not sure what the state is.
Tom ;)

Expurple Jun 6, 2025
Collaborator

@tomellm, nothing has changed in this regard and the problem is still there.

There have been some good discussions with potential solutions. I think, the best solution for Postgres is UNNEST ARRAY: #710 (comment). MySQL and SQLite can do a chunked insert in a transaction (savepoint): #710 (comment).

We can do this implicitly by default. But also we're currently preparing the next major release, so in theory we could use it and add a mandatory enum parameter that configures this behavior. I've edited #2548 and listed this as a possibility.

Contributions are welcome!

tomellm Jun 7, 2025

Hey @Expurple, thanks for the info. I would actually be interested in trying to contribute, but since my schedule is already quite packed I'm not sure if ill have the time. When I do end up getting around to it I would definitely contact you if that's okay ;)

tyt2y3 · 2022-05-10T16:33:34Z

tyt2y3
May 10, 2022
Maintainer

I imagine such an API to be like insert_many().chunked(10) returning an Iterator<Item = InsertStatement> where the user is responsible for executing them.

PR welcomed. Edit: the 10 shall means number of items per chunk.

0 replies

zdcthomas · 2023-04-14T19:50:55Z

zdcthomas
Apr 14, 2023

I ran into the same problem this afternoon. I'm considering just going with a less performant approach and just chunking on a low number but I thought I'd check in here to see if anyone's made any progress toward a more elegant solution

0 replies

gitmalong · 2023-10-18T20:36:51Z

gitmalong
Oct 18, 2023

I don't consider the suggested solutions as performant. When thinking about batch inserts remember that inserting 20k rows can be as fast as inserting 10k rows. Hence you don't want to waste time executing the query in relatively small chunks.

I think this problem would not occur if seaorm translates batch inserts to an insert with unnest and array.

insert into table(a, b) 
values(1, unnest(array[1, 2, 3]));

2 replies

rustonaut Feb 12, 2024

For context: Example with multiple columns of different type:

select * from unnest(ARRAY[1,2,3], ARRAY['a','b','c']);

Expurple Jun 2, 2025
Collaborator

More discussion (and code examples) of this approach with UNNEST and ARRAY: #1922

tyt2y3 · 2023-10-20T08:37:00Z

tyt2y3
Oct 20, 2023
Maintainer

Using array is certainly better since we can just put in 1 placeholder.

1 reply

rustonaut Feb 12, 2024

Probably the best solution for postgres.

But AFIK it doesn't work (well) with Sqlite and MySql which have the same issue when it comes to insert_many.

rustonaut · 2024-02-12T18:10:53Z

rustonaut
Feb 12, 2024

One edge case which needs careful consideration in case of any function using internal chunking:

What happens if the call is not run in a transaction but is split into chunks?

a. Should an implicit transaction be started to keep up the behavior that inserts normally all succeed or all fail?
b. Or should be be documented that if used outside of transactions with very a high number of rows*columns

Not an issue with PG if PG uses the array(s) + unnest approach.

1 reply

Expurple Jun 6, 2025
Collaborator

Good catch! I think, on MySQL and SQLite, we should always implicitly wrap the chunked insert in a transaction (savepoint). This whole thread is about eliminating unexpected errors and problems. A partially-executed insert would be an even worse gotcha than the original error. And that's the case even when the user code already uses a transaction, because the resulting state that the transaction sees will be unexpected (after calling this method and seeing a database error, I'd expect to see no new rows in the databaase).

F438

Uh oh!

Easier batching of bulk inserts #710

Uh oh!

Uh oh!

Summary

Motivation

Replies: 6 comments · 9 replies

Uh oh!

billy1624 May 4, 2022 Maintainer

Uh oh!

SebastienGllmt May 10, 2022 Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Expurple Jun 6, 2025 Collaborator

Uh oh!

Uh oh!

Uh oh!

tyt2y3 May 10, 2022 Maintainer

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Expurple Jun 2, 2025 Collaborator

Uh oh!

Uh oh!

tyt2y3 Oct 20, 2023 Maintainer

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Expurple Jun 6, 2025 Collaborator

Replies: 6 comments 9 replies

billy1624
May 4, 2022
Maintainer

SebastienGllmt May 10, 2022
Author

Expurple Jun 6, 2025
Collaborator

tyt2y3
May 10, 2022
Maintainer

Expurple Jun 2, 2025
Collaborator

tyt2y3
Oct 20, 2023
Maintainer

Expurple Jun 6, 2025
Collaborator