How pg-promise handles transactions with Promise.all()-CodePudding

In one of my projects, I want to execute 2 queries in parallel with pg-promise. I have these queries wrapped inside the transaction as added below. I am also using Promise.all() to make database calls in paralllel. I want to understand how this works internally, since transaction uses single db connection for bothe queries, does it mean the second query can only execute after first is completed as the txn is hold by the first query?


const {TransactionMode} = pgp.txMode;

// Create a reusable transaction mode (serializable   read-only   deferrable):
const mode = new TransactionMode({
    readOnly: true,
    deferrable: true
});

db.tx({mode}, t => {
    return Promise.all([t.any('SELECT * FROM table1'),t.any('SELECT * FROM table2')]);
})
.then(data => {
   // success;
})
.catch(error => {
   // error
});

Since the transaction acquires a single db connection to run all queries within the transaction , it would be interesting to know how it is done.

CodePudding user response：

Since transaction uses single db connection for both queries, does it mean the second query can only execute after first is completed?

Yes, exactly that. Postgres does not support parallel queries^[1,2] on the same client connection, and certainly not within the same transaction.

_{1: Query parallelization for single connection in Postgres

2: The Postgres wire protocol does support pipelining, but the queries are still executed sequentially.}

Update from the author of pg-promise:

If it is the best performance the OP seeks for 2 select queries, then the best way is to use multi:

const [sel1, sel2] = await db.multi('SELECT * FROM table1; SELECT * FROM table2');

i.e. you do not need a transaction for this at all.

CodePudding user response：

How pg-promise handles transactions with Promise.all()

It is not about how pg-promise handles transactions, it is about how PostgreSql transactions handle queries. And it does so, that they are always sequential. You cannot parallel anything inside a transaction.

The only way you can parallel-execute 2 queries like that within pg-promise is to do it against the root interface:

const [sel1, sel2] = await Promise.all([
    db.any('SELECT * FROM table1'),
    db.any('SELECT * FROM table2')
]);

The code above will result in two connections allocated from the pool, and executing queries in parallel.

In general, this is not a good practice, using more than one connection from the pool at the same time, because connections are a valuable resource, and so such solutions cannot scale well. You can only do this somewhere as an exception.

Also, using Promise.all makes the whole thing kind of pointless. A solution with Promise.race would be more suitable for this.