pg connection pool is creating more connections than allowed

[dieperdev]

Issue

When I use a connection pool with pg, it creates more connections than allowed.

Details

Node Bun Version: Bun v1.2.10
Postgres Version: v17.5 (docker)
pgbouncer Version: v1.24.0 (running on the same server as postgres)
pg Version: ^8.16.0

Code:

Creating the pool:

// src/lib/db.ts
import "dotenv/config";
import { Pool } from "pg";
import type { PoolConfig } from "pg";

const config: PoolConfig = {
  connectionString: process.env.PG_URL,
  max: 3,
  min: 1,
  allowExitOnIdle: false,
};

const pool = new Pool(config);

export default pool;

The pool is used in multiple routes (different files) like this for the API

// src/index.ts
import { Hono } from "hono";
import pool from "./lib/db";

const app = new Hono();

app.get('/', (c) => {
  const response = await pool.query('select * from users where id = $1::integer;', [1])
  return c.json(response.rows[0])
})

// The other routes are imported and this runs on one file
Bun.serve({
  fetch: app.fetch,
  port: 3000
})
console.log('running backend')

Additional details

The connection url uses a role named "backend". I start the server using bun src/index.js. When the server is started, running select * from pg_stat_activity where usename = 'backend'; in psql shows that multiple connections are created (~one for each route).

The connections that the query returns increases when more routes are added to the server. When the server is shut down, the connections still appear in pg_stat_activity. The postgres server is also behind pgbouncer (using session mode), so it's possible that it might be interfering.

[dieperdev]

After terminating the connections with pg_terminate_backend(<pid>) for each connection that wasn't doing anything, I checked the pgbouncer logs and found that it likely has something to do with pgbouncer holding the connections. For each connection that gets terminated, pgbouncer logs got packet 'E' from server when not linked. It looks like pg is still the one opening the connections.

[charmander]

The connections that the query returns increases when more routes are added to the server.

If this is accurate and not pgbouncer-related confusion: is it possible that your routes are importing different instances of the db.ts module? I don’t recommend module-level state regardless of whether this is the actual issue; consider storing the pool as a property of app or attaching it to the context with middleware or something.

Anyway, please reproduce this in a single minimal script without pgbouncer or Hono/dotenv, ideally on Node.

[dieperdev]

If this is accurate and not pgbouncer-related confusion: is it possible that your routes are importing different instances of the db.ts module?

I just tested this with the postgres port (not pgbouncer port). The issue isn't appearing when I do this so it likely is an issue with pgbouncer and not pg. pg is also properly disconnecting postgres connections through the pool now 😀. I'll consider looking into the middleware like you suggested.