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
