how to stop connections from closing when idle

[rhyek]

Expected behavior

For my setup, I want database connections to remain idle forever

Actual behavior

In a small local setup, hitting my database via a restful api will take around 2s after some amount of idle time. Using pg-monitor, I can see useCount === 0 every time this happens. After this, every subsequent request will respond in 10ms or less and useCount will increment.

I have tried the following to no effect:

import pgpLib from 'pg-promise';
import monitor from 'pg-monitor';
import promise from 'bluebird';

const initOptions = {
  promiseLib: promise,
};

monitor.attach(initOptions);
monitor.setTheme('matrix');
monitor.setLog((...args) => {
  console.log(...args);
});

const pgp = pgpLib(initOptions);
pgp.pg.defaults.max = 20;
// pgp.pg.defaults.idleTimeoutMillis = 0;
pgp.pg.defaults.idleTimeoutMillis = 10000000;

export const pgDb = pgp(process.env.DB_URL);

For my setup, I want database connections to remain idle forever until they're needed to avoid this new connection overhead. Could someone please provide code example of how to achieve this? Thanks.

Environment

• Version of pg-promise: 10.4.4
• OS type (Linux/Windows/Mac): Linux
• Version of Node.js: 12.14.1

[vitaly-t]

Are you using the default automatic connection or are you using it manually, via method connect?

By default, if you do not set idleTimeoutMillis, it defaults to 30 seconds, as per this line.

[rhyek]

@vitaly-t thanks. I am using the methods that automatically connect. should this already work?

[vitaly-t]

Yes, it should just work. Not sure why would your connection be not reused.

[rhyek]

the connection is being reused as long as I keep hitting my endpoint without waiting too long (I see useCount increase every time). let me run some more tests and I will get back to you.

[rhyek]

@vitaly-t i enabled connection logs on my pg instance (which is a helm package installed in my local kubernetes cluster using stable/postgresql) and this is some of what i'm seeing:

2020-03-08 16:58:53.578 GMT [2314] LOG:  connection received: host=10-244-0-10.users.default.svc.cluster.local port=46574
2020-03-08 16:58:53.578 GMT [2314] LOG:  connection authorized: user=postgres database=db
2020-03-08 16:58:53.673 GMT [2315] LOG:  connection received: host=10-244-0-9.tasks.default.svc.cluster.local port=60834
2020-03-08 16:58:53.678 GMT [2315] LOG:  connection authorized: user=postgres database=db
2020-03-08 16:58:54.623 GMT [2323] LOG:  connection received: host=localhost port=56786
2020-03-08 16:59:02.890 GMT [2330] LOG:  connection received: host=localhost port=56914
2020-03-08 16:59:03.664 GMT [2314] LOG:  disconnection: session time: 0:00:10.087 user=postgres database=db host=10-244-0-10.users.default.svc.cluster.local port=46574
2020-03-08 16:59:03.683 GMT [2315] LOG:  disconnection: session time: 0:00:10.010 user=postgres database=db host=10-244-0-9.tasks.default.svc.cluster.local port=60834

all connections end after around 10 seconds of inactivity. the pg conf is pretty much what comes out of the box.

[rhyek]

ok, i figured it out. pgp.pg.defaults.idleTimeoutMillis = 0; does not work at all, but this does:

import pgpLib from 'pg-promise';

const pgp = pgpLib();

const db = pgp({
  connectionString: process.env.DB_URL as string,
  idleTimeoutMillis: 0,
});

[vitaly-t]

This looks like a bug in the driver then.

But then again, overriding defaults isn't the standard way of doing it, it should be all in the connection:

const db = pgp({
  connectionString: process.env.DB_URL as string,
  idleTimeoutMillis: 0,
  max: 20
});

My advise - do not do any overrides in the defaults.

[rhyek]

ok, I have fixed my problem. should this issue be closed or what do you prefer? I feel like there is not enough documentation regarding this.

[vitaly-t]

You can log this issue against the driver, but here, there is nothing we can do, except use it through connection details, as I suggested. And I'm going to update the connection page now.

[vitaly-t]

@rhyek I have thoroughly updated the Connection Syntax page ;)