How to keep connection open and reuse clients?

[Rc85]

Currently, I am seeing a new row added to pg_stat_activity on every request. Once it reaches around 30 rows, I can no longer perform anymore queries. I could lower the idle timeout to like 1 second but it would still hit 30 rows if requests were made faster than the timeout. Here is my setup

// db.js
import { Pool } from 'pg';

const confings = { ... };

export const db = new Pool(configs);

// profile.js
import { db } from './configs/db';
import express from 'express';

const app = express();

app.post('/profile', async(req, resp, next) => {
  const client = await db.connect();

  try {
    await client.query('BEGIN');

    await client.query('COMMIT');
  } catch (e) {
    console.log(e);
  } finally {
    client.release();
  }
});

If 30+ simultaneous requests hits this route, I would get a connection timeout error and my app would crash.

Is this the right way to do it? How can I handle more than 30+ requests at the same or short time?

[charmander]

pg-pool’s default maximum pool size is 10:

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

Line 89 in 735683c

this.options.max = this.options.max || this.options.poolSize || 10

Are you increasing it in the [configs] that isn’t shown? And is this the only route? (If it isn’t, try reproducing the issue when it is.)

[Rc85]

This is my config

import pg from 'pg';

const config: pg.PoolConfig = {
  host: process.env.PG_HOST,
  database: process.env.PG_DATABASE,
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  max: 20,
  connectionTimeoutMillis: 20000,
  idleTimeoutMillis: 5000
};

export const db = new pg.Pool(config);

The route in my original post is just an example but all routes are basically set up like that where I call db.connect at the top. I reproduced the issue by continuously sending a request to the same route 20+ times, probably in 1 - 2 second intervals and eventually, the request returned an error saying something like pg connection timed out.

Lowering the idleTimeoutMillis allows me to send more requests but if the requests were made fast enough, it will eventually hit that error again.