CODEWITHSUNDEEP
pg-promise
Code
Code

Reputation: 6251

pg-promise: when are BEGIN and COMMIT sent?

This question is for the purpose of latency optimization.

In pg-promise, when/how are the BEGIN and COMMIT commands sent when using transactions?

For example, if there is the code:

db.tx(t => {
    const query1 = t.none(...);
    const query2 = t.none(...);
})

Will the BEGIN command be sent together with the first query or before, in a separate packet? Similarly, will the COMMIT command be sent together with the second query or after?

In other words, will this transaction take 2, 3, or 4 round trips?

Upvotes: 2

Views: 566

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

I'm the library's author.

Will the BEGIN command be sent together with the first query or before, in a separate packet?

BEFORE.

Will the COMMIT command be sent together with the second query or after?

AFTER.

Will this transaction take 2, 3, or 4 round trips?

4

Would you say that there is room for optimization, or that using a lower-level library could be beneficial (for latency)?

There may be some, depending on what you are trying to achieve. The default implementation makes it easy/standard to handle the transaction logic, and log everything that's going on (see pg-monitor). Everything is optimized within method tx, which makes it easy to logically handle any situation in a safe way.

The only situation where you would want to optimize it, if you need to execute huge number of micro transactions, because otherwise it is not worth doing, performance-wise.

In a very special case like that you may want to execute the entire transaction as a single query, by concatenating all queries into one. To that end, there is method helpers.concat which helps you build such a concatenated query dynamically.

It is worth noting that if you need nested transactions, then you cannot optimize it anymore, as the logic of nested transactions is deeply dependent on the logic of individual queries that's translated into promises, and then chained in a way that's consistent with the PostgreSQL transaction logic. If you try to work-around that, the chances are things are gonna fall apart, as nested transactions are generally too complex to handle manually.

Upvotes: 2

Related Questions