Question 90627 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search