Sample Header Ad - 728x90

Postgres batch insert with custom return value

1 vote
0 answers
669 views
I use Postgres 13 with Golan and pgx/v4 package for batch insert items Now we're migrating from UUID to serial ID's. For one table with parent ID, I need to generate one sort of mapping of parentds ids, to put them on children's rows. I split this insert in 2 batches, In the first one, I do something like this:
batch.Queue(fmt.Sprintf("%s, %d", query, entity.TmpId), record.Values()...)
I think generated output is similar to this:
insert into foo(txt) values ('a') returning id, 'key1' as externalKey;
After insert, I can generate this map of entity.TmpId and serialId. The problem is in performance, before now to insert 1.5k rows It takes ~2 sec. Now to insert only the parent's rows, which is less than 1.5k, it takes ~ 1 min. I did a test to return only id col, or id and some static value and the performance is good, comparable to what we have at the moment. At the moment, I want to generate this map using order of insert, although the documentation does not specify that this order will be guaranteed . Or maybe you suggest me a better solution? Here I created a SQL fiddle
Asked by Darii Petru (142 rep)
Jul 11, 2022, 09:20 AM