Question: How to calculate how long my query waited for a connection to execute it ?

[sathishsoundharajan]

Let say i have set connectionTimeoutMillis to 0 & pg-pool count is set to 20. Now i'm getting 30 concurrent request, first 20 of them already executing by using the connection pool, but last 10 requests will wait for the some of the connection to be freed. How to calculate how long my query waited for connection from pool/to become idle, before actually executing the query for last 10 requests ?

Is this something we can calculate using some metrics exposed by node-postgres ?

[vitaly-t]

There's nothing for this with the underlying driver, but this library exposes some metrics, ones used by pg-monitor.

[sathishsoundharajan]

@vitaly-t Is it possible to override pg.Pool.prototype constructor and get the metric. ? If so can you please give me an example, I know it is wrong to do this.

I kind of got this pesedo code which can used to achieve the metric.

Here is an example of how you might do it:

 javascript
 const { Pool } = require('pg');
 
 const pool = new Pool({
   // ...your pool configuration
 });
 
 // A map to keep track of query wait times by query ID
 const queryWaitTimes = new Map();
 
 pool.on('acquire', (client) => {
   const queryId = client.lastQueryId;
   if (queryWaitTimes.has(queryId)) {
     const startTime = queryWaitTimes.get(queryId);
     const waitTime = Date.now() - startTime;
     console.log(`Query ${queryId} waited for a connection for ${waitTime} ms`);
     queryWaitTimes.delete(queryId); // Clean up the entry
   }
 });
 
 async function executeQuery(queryText) {
   const queryId = generateUniqueQueryId(); // Implement this function to generate a unique ID for each query
   queryWaitTimes.set(queryId, Date.now()); // Record the time when the query is ready
 
   const client = await pool.connect();
   client.lastQueryId = queryId; // Attach the query ID to the client for tracking
   
   try {
     const result = await client.query(queryText);
     return result;
   } finally {
     client.release();
   }
 }
 
 // Remember our sample function to create a unique query ID (can be substituted for any unique identifier method)
 function generateUniqueQueryId() {
   return Math.random().toString(36).substring(2);
 }
 
 // Execute your query using this function

In this code snippet:

• We're using a Map object to track when each query is ready to execute (queryWaitTimes).
• We attach a unique ID to each query when it's ready (generateUniqueQueryId function to be implemented).
• We record the current timestamp when we're about to request a connection.
• We listen for the acquire event from the pool, which tells us when a connection has been granted, to calculate the wait time.
• We subtract the timestamp stored when the query was ready from the timestamp at the acquire event to determine the wait time.
• The lastQueryId property is added to clients to associate the acquire event with the query.

Reason I'm asking is I need this metric to figure out what would be the ideal number to set connectionTimeoutMills.

This seems Iike must needed metric.

[sathishsoundharajan]

@vitaly-t Do you think we can mock something like above & make it work ?

[sathishsoundharajan]

@vitaly-t Latest update from node-postgres discussion thread - brianc/node-postgres#3111 (comment)

[vitaly-t]

As I commented there, you have the original pool object, so you can do as you like ;)

[sathishsoundharajan]

Thank you @vitaly-t . I worked it out. Forgot to close this.