PG Pool leaking connections to the application and all requests are waiting forever.

[srebalaji]

I have a background process in NodeJS that connects to PSQL. And the background process is quite busy in I/O.

I'm using pg-pool submodule to maintain connection pool. I need to know the best way of implementing pooling globally, in that way pg connections won't get leaked in the applications.

Global module to be included in every file.

// psql.js
const pg = require('pg')
const {
  parse,
} = require('pg-connection-string')

const pgCred = "postgres://db-user:password@localhost:5432/db-name"
class PsqlConnector{
  constructor () {
    this.db = undefined
    this.connect = this.connect.bind(this)
    this.get = this.get.bind(this)
  }

  /**
   * estalibshes db connection
   */
  async connect (url) {
    if (this.db) {
      console.log('psql db connection already exist')
      return this.db
    } else if (pgCred) { // Will come from env variable.
      const config = parse(pgCred)
      const pgPool = new pg.Pool(config)
      return pgPool.connect()
        .then((conn) => {
          console.log('psql db connection established')
          this.db = conn
          return this.db
        }).catch((e) => {
          console.log(e.toString())
          throw e
        })
    }
  }

  async get (query) {
    return this.connect()
      .then((db) => {
        return db.query(query)
      })
  }
}

module.exports = new PsqlConnector()

Example code to connect to PSQL

// users.js
const psql = require('psql')

psql.get('select * from users limit 1')
 .then(result => result.rows)

Is the above strategy violates any node-postgres rules?

And will the above code prevent the application from leaking connections?

Note:
In the below code, I tried to release the client after each query executed. But db.release() returns an error stating that Release called on client which has already been released to the pool. (This happens when the second query is getting executed through the client. The first query is not throwing any error.)

So I figured that the client is already released. Refer to the below code.

// psql.js

async get (query) {
    return this.connect()
      .then((db) => {
        const result = db.query(query)
        db.release() // Throws error.
        return result
      })
  }

[sehrope]

You're creating a new pool for each invocation of connect(...). Move the creation of the pool outside so that it's created just once and then referenced repeatedly.

If you are connecting to many distinct databases and need to manage separate pools, you'll need to track the lifecycle of them yourselves. Creating a pool to the same connection config multiple times will create multiple pools, it won't automatically re-use an existing one as its up to you to manage that.

If it's a small number of distinct configs you can do something like using the config serialized to a stable string (ex: via https://www.npmjs.com/package/json-stable-stringify) as a key to a map that contains your pools. If it doesn't exist, create it on demand.

If there are a lot of different databases (like a lot lot) and you're doing a handful of tasks with each, then you're probably better off not using any pooling. Instead you should connect directly to each one, do your work, and then close the connection when you're finished. A pool is only useful if you repeatedly use the same one. Plus maintaining open connections to an unbounded number of pools might run into other resource limits.

[srebalaji]

@sehrope Thank you for the reply.

You can see that I'm not invoking connect multiple times. connect() will be called once in the app starts and the instance of the connect is used after that. You can see the else part where I'm saving the connect part in this.db

[charmander]

That uses a pool to obtain exactly one client that can’t be closed the normal way. (To close a client from a pool, you release the client then end the pool, but you don’t save the pool anywhere.)

I think you’re looking for something like this?

// psql.js
const { Pool } = require('pg')
const { parse } = require('pg-connection-string')

const pgCred = "postgres://db-user:password@localhost:5432/db-name"

const config = parse(pgCred)
module.exports = new Pool(config)

// users.js
const psql = require('psql')  // ?

psql.query('select * from users limit 1')
 .then(result => result.rows)

for short names and no this requirement (seems inconvenient):

// psql.js
const { Pool } = require('pg')
const { parse } = require('pg-connection-string')

const pgCred = "postgres://db-user:password@localhost:5432/db-name"

const config = parse(pgCred)
const pool = new Pool(config)

module.exports = {
  connect: () => pool.connect(),
  get: query => pool.query(query),
}

// users.js
const psql = require('psql')  // ?

psql.get('select * from users limit 1')
 .then(result => result.rows)