@opentelemetry/instrumentation-pg: multiple pools cause db.client.connection.count to be recorded incorrectly

[sfozz]

What version of OpenTelemetry are you using?

"@opentelemetry/api": "^1.9.0"
"@opentelemetry/auto-instrumentations-node": "^0.67.0"
"@opentelemetry/core": "^2.2.0"
"@opentelemetry/sdk-metrics": "^2.2.0"
"@opentelemetry/sdk-node": "^0.208.0"
"@opentelemetry/sdk-trace-node": "^2.2.0"

What version of Node are you using?

22.17.1

What did you do?

We are using instrumentation-pg via auto instrumentation to monitor the connections to a db. One of our services has 2 connection pools (different dbs soon to be different schema in the same db) but is looks like the counters are not working as expected. One of the pools connection counters just seems to increment where as the other just decrements.

db.client.connection.count{pool=poolA,state=used} 47
db.client.connection.count{pool=poolB,state=used} -47 

The pool only has a maximum size of 10, so it should never get beyond that.
Other services with only one pool are not impacted and are reporting fine

We are using FoalTS v5

OTEL Config

OTEL_SERVICE_NAME=our-api
OTEL_TRACES_SAMPLER=parentbased_always_off
OTEL_EXPORTER_OTLP_ENDPOINT=http://opentelemetry-kube-stack-collector:4318
OTEL_TRACES_EXPORTER=otlp
OTEL_METRICS_EXPORTER=otlp
OTEL_SEMCONV_STABILITY_OPT_IN=http
OTEL_NODE_RESOURCE_DETECTORS=env,host,os
OTEL_LOGS_EXPORTER=none
OTEL_RESOURCE_ATTRIBUTES=deployment.environment=prod,service.version=production

What did you expect to see?

for the default pool size I would expect the used count to go up to at most 10 and reduce as used connections are returned the the pool

What did you see instead?

That one pool always increments and the other pool always decrements

Additional context

Will supply example code

_{Tip: React with 👍 to help prioritize this issue. Please use comments to provide useful context, avoiding +1 or me too, to help us triage it. Learn more here.}

[trentm]

I can reproduce. The pool counts tracking in instr-pg is assuming there is only ever one pool.
I've a start at changing that to track per pool name.
Hopefully the getPoolName() calculation is sufficient to keep actual separate pool instances separate.

different dbs soon to be different schema in the same db

I worry that the current instrumentation will not handle this well. Currently getPoolName does not consider the schema to come up with the unique (enough) name:

opentelemetry-js-contrib/packages/instrumentation-pg/src/utils.ts

Lines 364 to 371 in 6437f2a

	export function getPoolName(pool: PgPoolOptionsParams): string {
	let poolName = '';
	poolName += (pool?.host ? `${pool.host}` : 'unknown_host') + ':';
	poolName += (pool?.port ? `${pool.port}` : 'unknown_port') + '/';
	poolName += pool?.database ? `${pool.database}` : 'unknown_database';
	return poolName.trim();
	}

The semconv for Postgres DB spans has a note for how db.namespace should be calculated:
https://opentelemetry.io/docs/specs/semconv/db/postgresql/#spans

[1] db.namespace: db.namespace SHOULD be set to the combination of database and schema name following the {database}|{schema} pattern. If either {database} or {schema} is unavailable, db.namespace SHOULD be set to the other (without any | separator).

[... a lot more details that I haven't copied here ...]

I could use some help from people that know Postgres and pg better than me. I'm not really a user of the DB or lib.
Is there a reasonable way for the instrumentation here to know the schema being used for a particular pg Pool instance?

[trentm]

Repro

collector

First start some collector to watch the db.client.connection.count metric being exported. Personally for dev, I use @elastic/mockotlpserver:

% npx -y @elastic/mockotlpserver -o metrics-summary,spacer
{"name":"mockotlpserver","level":30,"msg":"OTLP/HTTP listening at http://[::1]:4318/","time":"2026-01-29T20:55:25.538Z"}
{"name":"mockotlpserver","level":30,"msg":"OTLP/gRPC listening at http://localhost:4317/","time":"2026-01-29T20:55:25.541Z"}

postgres

You can use the test-services in opentelemetry-js-contrib to run a Postgres to test against:

git clone git@github.com:open-telemetry/opentelemetry-js-contrib.git
cd opentelemetry-js-contrib
npm ci
cd packages/instrumentation-pg
npm run compile:with-dependencies

npm run test-services:start   # requires Docker

test script

play-pg-2-pools.js saved in the .../packages/instrumentation-pg dir.
This cheats and uses the createTestNodeSdk convenience used for local testing to setup an OTel SDK.
It creates two PG databases, creates a pool for each, then does some concurrent queries to use a known number of connections from each. See the comments.

/* eslint-disable */

// Usage:
//    OTEL_METRIC_EXPORT_INTERVAL=5000 OTEL_METRIC_EXPORT_TIMEOUT=5000 node play-pg-2-pools.js

const { trace } = require('@opentelemetry/api');
const { createTestNodeSdk } = require('@opentelemetry/contrib-test-utils');

const { PgInstrumentation } = require('./build/src/index.js');

process.env.OTEL_EXPORTER_OTLP_ENDPOINT = 'http://localhost:4318'
const sdk = createTestNodeSdk({
  serviceName: 'play-pg-2-pools',
  instrumentations: [new PgInstrumentation()],
});
sdk.start();

const baseConfig = {
  user: 'postgres',
  password: 'postgres',
  host: 'localhost',
  port: 54320,
};
const configA = { ...baseConfig, database: 'dba' };
const configB = { ...baseConfig, database: 'dbb' };

const {Client} = require('pg');
const Pool = require('pg-pool');
const poolA = new Pool(configA);
const poolB = new Pool(configB);

async function main() {
  setInterval(() => {
    console.log('hanging around...')
  }, 10000);

  // setup
  // PGHOST=localhost PGPORT=54320 PGUSER=postgres PGPASSWORD=postgres psql
  const client = new Client(baseConfig);
  await client.connect();
  try {
    await client.query('create database dba');
  } catch { /* pass */ };
  try {
    await client.query('create database dbb');
  } catch { /* pass */ };
  client.end();

  const tracer = trace.getTracer();
  tracer.startActiveSpan('test-span', async span => {
    await poolA.query('SELECT NOW()');
    await poolB.query('SELECT NOW()');
    span.end();

    let stage = 10;
    setInterval(async () => {
      stage -= 1;
      if (stage > 0) {
        // Use A=3,B=1 connections for ~10s...
        await Promise.all([
          poolA.query('SELECT NOW()'),
          poolA.query('SELECT NOW()'),
          poolA.query('SELECT NOW()')
        ]);
        await Promise.all([
          poolB.query('SELECT NOW()'),
        ]);
      } else {
        // ... then switch to A=2,B=0 connections.
        // Observing the sent metrics
        await Promise.all([
          poolA.query('SELECT NOW()'),
          poolA.query('SELECT NOW()'),
        ]);
      }
    }, 1000);
  });

  // poolA.end();
  // poolB.end();
  // sdk.shutdown();
}

main();

Run the test script with options to export metrics more frequently (every 5s):

OTEL_METRIC_EXPORT_INTERVAL=5000 OTEL_METRIC_EXPORT_TIMEOUT=5000 node play-pg-2-pools.js

exported metrics without fix

Watching the mockotlpserver output (editted for clarity) we see totally messed up values (18, -17, etc.):

------ metrics ------
histogram "db.client.operation.duration" (unit=s): ...
sum "db.client.connection.count" (unit={connection}, aggTemp=cumulative):
    8  { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    1  { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    -7 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
    -1 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
sum "db.client.connection.pending_requests" (unit={connection}, aggTemp=cumulative): ...

------ metrics ------
histogram "db.client.operation.duration" (unit=s): ...
sum "db.client.connection.count" (unit={connection}, aggTemp=cumulative):
    18  { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    1   { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    -17 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
    -1  { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
sum "db.client.connection.pending_requests" (unit={connection}, aggTemp=cumulative): ...

with the fix

We expected to see:

• 3 conns from pool A, 1 conn from pool B used for a while (~10s)
• then settling on 2 conns from pool A, 0 conns from pool B

And that is what we see:

------ metrics ------
histogram "db.client.operation.duration" (unit=s): ...
sum "db.client.connection.count" (unit={connection}, aggTemp=cumulative):
    3 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    0 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    1 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
    0 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
sum "db.client.connection.pending_requests": ...

------ metrics ------
histogram "db.client.operation.duration" (unit=s): ...
sum "db.client.connection.count" (unit={connection}, aggTemp=cumulative):
    2 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    1 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    1 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
    0 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
sum "db.client.connection.pending_requests": ...

------ metrics ------
histogram "db.client.operation.duration" (unit=s): ...
sum "db.client.connection.count" (unit={connection}, aggTemp=cumulative):
    2 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    0 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dba' }
    0 { 'db.client.connection.state': 'used', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
    0 { 'db.client.connection.state': 'idle', 'db.client.connection.pool.name': 'localhost:54320/dbb' }
sum "db.client.connection.pending_requests": ...

PR coming.

As I said above, I don't have tests yet, and could use help on a better getPoolName that includes a PG schema if possible.

[sfozz]

Have had a little look into the pg, and pg-pool. They don't seem to have a reference to the schema that is used for a connection.

So I'm wondering if the potential schema issue is a none issue. However having 2 pools will still be a problem.

[trentm]

However having 2 pools will still be a problem.

Yah, that's the part I worry about. When you said:

different dbs soon to be different schema in the same db

In the multiple pools using "different schema in the same db" case, this instrumentation will be:

1. Using the same db.client.connection.pool.name attribute for metrics, and
2. (relatedly) confusing the two in metrics like db.client.connection.count, leading to confusing/wrong metric data.

[trentm]

Perhaps we punt this to a separate issue and get the current fix in.