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 ?

[sehrope]

You would need to handle this yourself by creating your own version of pool.query(...) that uses the pool.connect(...) and tracks the time it took to retrieve a connection from the pool separately from the time it took to execute the query.

[sathishsoundharajan]

@sehrope Is this something already done somewhere ? Like a blog post explaining things ?

[eladchen]

Here's what I ended up writing:

import * as Postgres from 'pg';
import { hrtime } from 'node:process';

const hrtimeToMs = (value: bigint) => Number(hrtime.bigint() - value) / 1000000;

// Calling this method will monkey patch the
// pool instance, and every time a client is acquired via pool.query(...) / pool.connect(...)
// invoke the passed in callback, with 3 parameters:
// - The duration it took to get a client from the pool
// - Any error that occurred during the acquisition (may be undefined)
// - The acquired client (may be undefined)
const monitorPoolConnect = (pool: Postgres.Pool, cb: (duration: number, error?: Error, client?: Postgres.PoolClient) => void) => {
  const connect = pool.connect;

  pool.connect = (...args: any[]) => {
    const start = hrtime.bigint();

    if (args.length) {
      const callback = args[0];

      args[0] = (...p: any[]) => {
        const client = p[1];

        cb(hrtimeToMs(start), p[0], client);

        callback(...p);
      };

      return connect.apply(pool, args);
    }

    const handleError = (error: Error) => {
      cb(hrtimeToMs(start), error, undefined);

      return Promise.reject(error);
    };

    const handleClient = (client: Postgres.PoolClient) => {
      cb(hrtimeToMs(start), undefined, client);

      return client;
    };

    return connect.apply(pool, null).then(handleClient, handleError);
  };
};

Example:

monitorPoolConnect(pool, (duration, error) => {
   if (!error) {
     console.log(`acquiring connection took ${duration / 1000} second(s)`)
   }
})

P.S
it best to avoid calling monitorPoolConnect with the same pool more than once...

[sathishsoundharajan]

Thank you @eladchen .. :) Came to know we are using upper level abstraction pg-promise, where we won't be able to override pool.connect method :( .
I'm trying to figure out with @vitaly-t to see how we can override/monkey patch the pool method to collect this metric.

This seems like very important metric which is required & plot in graph to understand do we have increase pg-pool count or what should be average or 90th percentile for connectTimeoutMills

[eladchen]

Wouldn't it be possible to patch the pg-pool still? You should be able to patch the exported Pool class.

node-postgres/packages/pg-pool/index.js

Line 66 in 6cd0aeb

class Pool extends EventEmitter {

You should be able to patch the above class prototype, but I agree, it would have been nice if we had a way without having to patch things.

[vitaly-t]

@sathishsoundharajan You can do whatever you want with the original pool within pg-promise library, as it is exposed as db.$pool.