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

Question

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.

There is no such thing as parallel query execution inside one transaction. You can only parallel-execute queries on a separate connection context. Transaction is one such context. — vitaly-t, Dec 08 '22 at 21:00
You never accepted any answer. Is there something still unresolved? — vitaly-t, Dec 18 '22 at 14:50

Bergi · Accepted Answer · 2022-12-08T21:14:54.413

1

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.

edited Dec 08 '22 at 21:14

answered Dec 08 '22 at 19:22

Bergi

630,263
148
957
1,375

That is correct (author of `pg-promise`). – vitaly-t Dec 08 '22 at 21:04
1

@vitaly-t I think you were missing a "*not*" in "*need a transaction*"? (I've edited it already) – Bergi Dec 08 '22 at 21:15
Thanks for the answers, much appreciated. For my use case, I wanted to track the query time of both the queries that run in parallel. To achieve it, I tried wrapping it in a time-bound function but to my surprise, sometime `query1` was faster than query2 and vice versa. I was hoping `query2` to be always slow than `query1` as the queries are made in sequence. Also, what should be the right way to track the query time of parallel queries? – Arjun Londhey Dec 08 '22 at 21:40
@ArjunLondhey For your original approach with two promises, you could use [this solution](https://stackoverflow.com/a/44158747/1048572). If you run them in the same statement, I don't know. – Bergi Dec 08 '22 at 23:03
I don't think playing with promises will make any difference here, since there is no parallelism on the server for this. – vitaly-t Dec 09 '22 at 09:57

vitaly-t · Answer 2 · 2022-12-12T11:39:32.300

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.

Any ideas on how to measure the query time of individual queries when running in parallel?

Method result exposes query duration:

const res = await Promise.all([
    db.result('SELECT * FROM table1'),
    db.result('SELECT * FROM table2')
]);

res.forEach(r => {
    const = {rows, duration} = r;
    // here're your rows + query duration
});

Got it, any ideas on how to measure the query time of individual queries when running in parallel? In my use case, I cannot avoid using txn. — Arjun Londhey, Dec 12 '22 at 06:39
I have added that piece to the answer ;) Also, check out [pg-monitor](https://github.com/vitaly-t/pg-monitor) that automatically exposes information about duration, among other things. — vitaly-t, Dec 12 '22 at 11:40

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

2 Answers2