Is order preserved in the transaction sequence API of pg-promise?

Question

I have to insert a lot of records, and some columns have foreign keys from other tables. The way to do it in pure SQL is something like

INSERT (foo, fk) VALUES (1, (SELECT id FROM f_table WHERE blah = 'something'))

But on massive insert this is too slow (~5000 ms for 5000 rows in my use case). I'm already using the tx.sequence function to speed up the insertion of the dependency records, but to speed up the insertion of the dependent records, could I return the id's from the first query (in batches) and would the order be preserved?

for example,

const h = pgp.helpers

// insert dependency records
const registersCs = ColumnSet(['name', 'address'], { table: 'register' })
const metrics = await t.sequence(index => {
        const batch = results.slice(index * batchSize, (index * batchSize) + batchSize)
        if (batch && batch.length) {
          try {
            const bulk = `${h.insert(batch, registersCs)} ON CONFLICT (name, address) DO UPDATE ${h.sets(/* yada yada */)} RETURNING id`
            return t.many(bulk)
          } catch (err) {
            log.error(`Error inserting registers: ${err.message}`)
          }
        }
      }, {dest: (index, data) => { results[index].registerId = data.id }})

// insert dependent records, which now has the registerId I was originally going to have to use a subquery for...

Answer 1

could I return the id's from the first query (in batches)

You can replace t.many(bulk) with t.map(bulk, undefined, a => a.id). And sequence supports option track to accumulate all resolved data.

and would the order be preserved?

I don't see why not.